GROUP BY 句内の GROUPING SETS 拡張は、さまざまなグループ化に基づく複数の結果セットを連結した 1 つの結果セットを生成するために使用されます。 UNION ALL 演算子は、複数のグループの結果セットを 1 つの結果セットに結合するために使用されます。
UNION ALL 演算子と GROUPING SETS 拡張は、結合された結果セットから重複行を削除しません。
単一の GROUPING SETS 拡張の構文は次のとおりです。
GROUPING SETS (
{ expr_1 | ( expr_1a [, expr_1b ] ...) |
ROLLUP ( expr_list ) | CUBE ( expr_list )
} [, ...] )GROUPING SETS 拡張には、1 つまたは複数のコンマ区切りの式、括弧で囲まれた式のリスト、ROLLUP 拡張、および CUBE 拡張の任意の組み合わせを含めることができます。
GROUPING SETS 拡張は、GROUP BY 句のコンテキスト内で指定されます。 次の例は、この拡張機能を示しています。
SELECT select_list FROM ...
GROUP BY [... ,] GROUPING SETS ( expression_list ) [, ...]select_list で指定された項目は、GROUPING SETS の expression_list に表示されるか、COUNT、SUM、AVG、MIN、MAXなどの集約関数であるか、定数またはグループ内の個々の行に依存しない戻り値を持つ SYSDATE 関数などの関数である必要があります。
GROUP BY 句では、複数の GROUPING SETS 拡張と、他の GROUP BY 拡張の複数の出現および個々の式を指定できます。
意味のある構造で出力を表示するには、ORDER BY 句を使用する必要があります。 ORDER BY 句が指定されていない場合、結果セットの順序は決定されません。
次のクエリは、loc 列、dname 列、および job 列で指定されたグループの和集合を生成します。
SELECT loc, dname, job, COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY GROUPING SETS (loc, dname, job)
ORDER BY 1, 2, 3;次の例は、このクエリの結果を示しています。
loc | dname | job | employees
----------+------------+-----------+-----------
BOSTON | | | 8
CHICAGO | | | 6
NEW YORK | | | 3
| ACCOUNTING | | 3
| OPERATIONS | | 3
| RESEARCH | | 5
| SALES | | 6
| | ANALYST | 3
| | CLERK | 5
| | MANAGER | 4
| | PRESIDENT | 1
| | SALESMAN | 4
(12 rows)同じ結果を取得するには、次のクエリで UNION ALL 演算子を使用することもできます。
SELECT loc AS "loc", NULL AS "dname", NULL AS "job", COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY loc
UNION ALL
SELECT NULL, dname, NULL, COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY dname
UNION ALL
SELECT NULL, NULL, job, COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY job
ORDER BY 1, 2, 3;UNION ALL クエリからの出力は、GROUPING SETS の出力と同じです。
loc | dname | job | employees
----------+------------+-----------+-----------
BOSTON | | | 8
CHICAGO | | | 6
NEW YORK | | | 3
| ACCOUNTING | | 3
| OPERATIONS | | 3
| RESEARCH | | 5
| SALES | | 6
| | ANALYST | 3
| | CLERK | 5
| | MANAGER | 4
| | PRESIDENT | 1
| | SALESMAN | 4
(12 rows)次の例は、さまざまなタイプの GROUP BY 拡張を GROUPING SETS 式リスト内で一緒に使用する方法を示しています。
SELECT loc, dname, job, COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY GROUPING SETS (loc, ROLLUP (dname, job), CUBE (job, loc))
ORDER BY 1, 2, 3;次の例は、前述のクエリの出力を示しています。
loc | dname | job | employees
----------+------------+-----------+-----------
BOSTON | | ANALYST | 3
BOSTON | | CLERK | 3
BOSTON | | MANAGER | 2
BOSTON | | | 8
BOSTON | | | 8
CHICAGO | | CLERK | 1
CHICAGO | | MANAGER | 1
CHICAGO | | SALESMAN | 4
CHICAGO | | | 6
CHICAGO | | | 6
NEW YORK | | CLERK | 1
NEW YORK | | MANAGER | 1
NEW YORK | | PRESIDENT | 1
NEW YORK | | | 3
NEW YORK | | | 3
| ACCOUNTING | CLERK | 1
| ACCOUNTING | MANAGER | 1
| ACCOUNTING | PRESIDENT | 1
| ACCOUNTING | | 3
| OPERATIONS | ANALYST | 1
| OPERATIONS | CLERK | 1
| OPERATIONS | MANAGER | 1
| OPERATIONS | | 3
| RESEARCH | ANALYST | 2
| RESEARCH | CLERK | 2
| RESEARCH | MANAGER | 1
| RESEARCH | | 5
| SALES | CLERK | 1
| SALES | MANAGER | 1
| SALES | SALESMAN | 4
| SALES | | 6
| | ANALYST | 3
| | CLERK | 5
| | MANAGER | 4
| | PRESIDENT | 1
| | SALESMAN | 4
| | | 17
| | | 17
(38 rows)出力は、GROUP BY loc、GROUP BY ROLLUP (dname, job)、および GROUP BY CUBE (job, loc) の結果セットを連結したものです。 次の例は、これらのクエリを示しています。
SELECT loc, NULL AS "dname", NULL AS "job", COUNT(*) AS "employees"
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY loc
ORDER BY 1;次の例は、GROUP BY loc 句の結果セットを示しています。
loc | dname | job | employees
----------+-------+-----+-----------
BOSTON | | | 8
CHICAGO | | | 6
NEW YORK | | | 3
(3 rows)次のクエリでは、GROUP BY ROLLUP (dname, job) 句を使用しています。
SELECT NULL AS "loc", dname, job, COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY ROLLUP (dname, job)
ORDER BY 2, 3;次のクエリは、GROUP BY ROLLUP (dname, job) 句の結果セットです。
loc | dname | job | employees
-----+------------+-----------+-----------
| ACCOUNTING | CLERK | 1
| ACCOUNTING | MANAGER | 1
| ACCOUNTING | PRESIDENT | 1
| ACCOUNTING | | 3
| OPERATIONS | ANALYST | 1
| OPERATIONS | CLERK | 1
| OPERATIONS | MANAGER | 1
| OPERATIONS | | 3
| RESEARCH | ANALYST | 2
| RESEARCH | CLERK | 2
| RESEARCH | MANAGER | 1
| RESEARCH | | 5
| SALES | CLERK | 1
| SALES | MANAGER | 1
| SALES | SALESMAN | 4
| SALES | | 6
| | | 17
(17 rows)次のクエリでは、GROUP BY CUBE (job, loc) 句を使用しています。
SELECT loc, NULL AS "dname", job, COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY CUBE (job, loc)
ORDER BY 1, 3;次の例は、GROUP BY CUBE (job, loc) 句の結果セットを示しています。
loc | dname | job | employees
----------+-------+-----------+-----------
BOSTON | | ANALYST | 3
BOSTON | | CLERK | 3
BOSTON | | MANAGER | 2
BOSTON | | | 8
CHICAGO | | CLERK | 1
CHICAGO | | MANAGER | 1
CHICAGO | | SALESMAN | 4
CHICAGO | | | 6
NEW YORK | | CLERK | 1
NEW YORK | | MANAGER | 1
NEW YORK | | PRESIDENT | 1
NEW YORK | | | 3
| | ANALYST | 3
| | CLERK | 5
| | MANAGER | 4
| | PRESIDENT | 1
| | SALESMAN | 4
| | | 17
(18 rows)UNION ALL 演算子を使用して上記の 3 つのクエリを組み合わせると、3 つの結果セットの連結が生成されます。
SELECT loc AS "loc", NULL AS "dname", NULL AS "job", COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY loc
UNION ALL
SELECT NULL, dname, job, count(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY ROLLUP (dname, job)
UNION ALL
SELECT loc, NULL, job, count(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY CUBE (job, loc)
ORDER BY 1, 2, 3;次の例は、GROUP BY GROUPING SETS (loc, ROLLUP (dname, job), CUBE (job, loc)) 句を使用した場合と同じ出力を示しています。
loc | dname | job | employees
----------+------------+-----------+-----------
BOSTON | | ANALYST | 3
BOSTON | | CLERK | 3
BOSTON | | MANAGER | 2
BOSTON | | | 8
BOSTON | | | 8
CHICAGO | | CLERK | 1
CHICAGO | | MANAGER | 1
CHICAGO | | SALESMAN | 4
CHICAGO | | | 6
CHICAGO | | | 6
NEW YORK | | CLERK | 1
NEW YORK | | MANAGER | 1
NEW YORK | | PRESIDENT | 1
NEW YORK | | | 3
NEW YORK | | | 3
| ACCOUNTING | CLERK | 1
| ACCOUNTING | MANAGER | 1
| ACCOUNTING | PRESIDENT | 1
| ACCOUNTING | | 3
| OPERATIONS | ANALYST | 1
| OPERATIONS | CLERK | 1
| OPERATIONS | MANAGER | 1
| OPERATIONS | | 3
| RESEARCH | ANALYST | 2
| RESEARCH | CLERK | 2
| RESEARCH | MANAGER | 1
| RESEARCH | | 5
| SALES | CLERK | 1
| SALES | MANAGER | 1
| SALES | SALESMAN | 4
| SALES | | 6
| | ANALYST | 3
| | CLERK | 5
| | MANAGER | 4
| | PRESIDENT | 1
| | SALESMAN | 4
| | | 17
| | | 17
(38 rows)