GROUPING SETS は、単一のクエリ内で複数の集約グループ化を生成する GROUP BY の拡張機能です。個別のクエリを別々に記述し、UNION ALL で結合する代わりに、必要なグループ化を明示的に指定すると、データベースが結果セットを自動的に連結します。
GROUPING SETSおよびUNION ALLのいずれも、結合後の結果セットから重複行を削除しません。
構文
GROUPING SETS (
{ expr_1 | ( expr_1a [, expr_1b ] ...) |
ROLLUP ( expr_list ) | CUBE ( expr_list )
} [, ...] )GROUPING SETS を GROUP BY 句内に使用します：
SELECT select_list FROM ...
GROUP BY [... ,] GROUPING SETS ( expression_list ) [, ...]select_list 内の各項目は、以下のいずれかである必要があります：
GROUPING SETS式リストに含まれる列または式COUNT、SUM、AVG、MIN、MAXなどの集約関数SYSDATEなどの定数または行に依存しない関数
GROUPING SETS 式リストでは、以下の任意の組み合わせを指定できます：
スカラ式
括弧で囲まれた式リスト
ROLLUP拡張CUBE拡張
GROUP BY 句には、他の GROUP BY 拡張や個別の式とともに、複数の GROUPING SETS 拡張を含めることができます。
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 クエリ
上記の GROUPING SETS クエリは、以下の 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; 所在地 | 部門名 | 役職 | 従業員数
----------+------------+-----------+-----------
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 行)GROUPING SETS と ROLLUP／CUBE の併用
GROUPING SETS 式リストには、ROLLUP および CUBE 拡張を含めることができます。その結果は、各拡張が生成する個別のグループ化の結果セットを連結したものになります。
以下のクエリは、3 つのグループ化仕様を組み合わせています：
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;出力は以下の結果の連結となります：
GROUP BY loc— 3 行GROUP BY ROLLUP (dname, job)— 17 行GROUP BY CUBE (job, loc)— 18 行
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`（3 行）：
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; loc | dname | job | employees
----------+-------+-----+-----------
BOSTON | | | 8
CHICAGO | | | 6
NEW YORK | | | 3
(3 rows)`GROUP BY ROLLUP (dname, job)`（17 行）：
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; 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)`（18 行）：
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; 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)これらの 3 つのクエリを UNION ALL で結合すると、同じ 38 行の結果が得られます：
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; 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)