GROUP BY 句に対して ROLLUP、CUBE、または GROUPING SETS 拡張を使用すると、拡張機能によって生成されるさまざまなレベルの小計が、結果セットの基本集計行と区別されない場合があります。 GROUPING 関数を使用するとこれらを区別できます。
GROUPING 関数には次の一般的な構文が含まれます。
SELECT [ expr ...,] GROUPING( col_expr ) [, expr ] ...
FROM ...
GROUP BY [...,]
{ ROLLUP | CUBE | GROUPING SETS }( [...,] col_expr
[, ...] ) [, ...]GROUPING 関数は、GROUP BY 句の ROLLUP、CUBE、または GROUPING SETS 拡張の式リストで指定されたディメンション列の式である必要がある単一のパラメーターを使用します。
GROUPING 関数によって返される値は 0 または 1 です。 クエリの結果セットでは、行がその列の複数の値の小計を表すため、GROUPING 関数で指定された列式が null の場合、GROUPING 関数 は値 1 の値を返します。 行が GROUPING 関数で指定された列の特定の値に基づく結果を返す場合、GROUPING 関数は値 0 を返します。 後者の場合、列は null または null 以外の値になります。 どちらの場合も、その列の特定の値に対するものであり、複数の値の小計ではありません。
次のクエリは、GROUPING 関数によって返される値が小計行にどのように対応するかを示しています。
SELECT loc, dname, job, COUNT(*) AS "employees",
GROUPING(loc) AS "gf_loc",
GROUPING(dname) AS "gf_dname",
GROUPING(job) AS "gf_job"
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY ROLLUP (loc, dname, job)
ORDER BY 1, 2, 3;GROUPING 関数によって返される右端の 3 つの列では、対応する列の値全体で小計が取られる場合は、小計行に値 1 が表示されます。
loc | dname | job | employees | gf_loc | gf_dname | gf_job
----------+------------+-----------+-----------+--------+----------+--------
BOSTON | OPERATIONS | ANALYST | 1 | 0 | 0 | 0
BOSTON | OPERATIONS | CLERK | 1 | 0 | 0 | 0
BOSTON | OPERATIONS | MANAGER | 1 | 0 | 0 | 0
BOSTON | OPERATIONS | | 3 | 0 | 0 | 1
BOSTON | RESEARCH | ANALYST | 2 | 0 | 0 | 0
BOSTON | RESEARCH | CLERK | 2 | 0 | 0 | 0
BOSTON | RESEARCH | MANAGER | 1 | 0 | 0 | 0
BOSTON | RESEARCH | | 5 | 0 | 0 | 1
BOSTON | | | 8 | 0 | 1 | 1
CHICAGO | SALES | CLERK | 1 | 0 | 0 | 0
CHICAGO | SALES | MANAGER | 1 | 0 | 0 | 0
CHICAGO | SALES | SALESMAN | 4 | 0 | 0 | 0
CHICAGO | SALES | | 6 | 0 | 0 | 1
CHICAGO | | | 6 | 0 | 1 | 1
NEW YORK | ACCOUNTING | CLERK | 1 | 0 | 0 | 0
NEW YORK | ACCOUNTING | MANAGER | 1 | 0 | 0 | 0
NEW YORK | ACCOUNTING | PRESIDENT | 1 | 0 | 0 | 0
NEW YORK | ACCOUNTING | | 3 | 0 | 0 | 1
NEW YORK | | | 3 | 0 | 1 | 1
| | | 17 | 1 | 1 | 1
(20 rows)これらの指標は、特定の小計をフィルタリングする条件として使用できます。 たとえば、前述のクエリでは、HAVING 句で GROUPING 関数を使用して、loc と dnameの 組み合わせの小計のみを表示できます。
SELECT loc, dname, job, COUNT(*) AS "employees",
GROUPING(loc) AS "gf_loc",
GROUPING(dname) AS "gf_dname",
GROUPING(job) AS "gf_job"
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY ROLLUP (loc, dname, job)
HAVING GROUPING(loc) = 0
AND GROUPING(dname) = 0
AND GROUPING(job) = 1
ORDER BY 1, 2;次の例はこのクエリの結果を示しています。
loc | dname | job | employees | gf_loc | gf_dname | gf_job
--------------+-----------------------------------+-----------+---------------
BOSTON | OPERATIONS | | 3 | 0 | 0 | 1
BOSTON | RESEARCH | | 5 | 0 | 0 | 1
CHICAGO | SALES | | 6 | 0 | 0 | 1
NEW YORK | ACCOUNTING | | 3 | 0 | 0 | 1
(4 rows)GROUPING 関数を使用して、小計行を基本集計行または特定の小計行と区別できます。 これらの行では、式の基になる null 列が原因で、式リスト内の項目の 1 つが nullを返します。 null 列は、テーブルの 1 つ以上の行に対応します。 アイテムは列の小計を表していません。
たとえば、次の行を emp テーブルに追加します。 その結果、null 値を持つ行が job 列に作成されます。
INSERT INTO emp (empno,ename,deptno) VALUES (9004,'PETERS',40);次のクエリでは、わかりやすくするために行数を減らしています。
SELECT loc, job, COUNT(*) AS "employees",
GROUPING(loc) AS "gf_loc",
GROUPING(job) AS "gf_job"
FROM emp e, dept d
WHERE e.deptno = d.deptno AND loc = 'BOSTON'
GROUP BY CUBE (loc, job)
ORDER BY 1, 2;次の出力では、2 つの行で loc 列に BOSTON が含まれ、job 列にスペースが含まれています。 表の 4 番目と 5 番目のエントリは、これらの 2 つの行を示しています。
loc | job | employees | gf_loc | gf_job
-------+------------+-----------+----------
BOSTON | ANALYST | 3 | 0 | 0
BOSTON | CLERK | 3 | 0 | 0
BOSTON | MANAGER | 2 | 0 | 0
BOSTON | | 1 | 0 | 0
BOSTON | | 9 | 0 | 1
| ANALYST | 3 | 1 | 0
| CLERK | 3 | 1 | 0
| MANAGER | 2 | 1 | 0
| | 1 | 1 | 0
| | 9 | 1 | 1
(10 rows)job 列 (gf_job) の GROUPING 関数は、5 番目の行に 1 を返し、この値がすべてのジョブの小計であることを示します。 行の employees 列に小計値 9 が含まれています。
job 列と loc 列の GROUPING 関数は、4 番目の行で 0 を返し、この値が loc が BOSTON で、 job が null であるすべての行の基本集計であることを示します。 この例では、4 行目が挿入されます。 employees 列には 1 が含まれており、これは null job 行の数を示します。
最終行の隣の 9 行目では、job 列の GROUPING 関数は 0 を返し、loc 列の GROUPING 関数は 1 を返します。 これらの値は、job 列が null であるすべての場所の小計です。 employees 列は、null job 行の数を示します。