在将 ROLLUP、CUBE 或 GROUPINGSETS 扩展用于 GROUP BY 子句时,有时可能很难区分由这些扩展生成的各级别小计以及结果集中的基础聚合行。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 函数中指定的列表达式由于行表示的小计包含该列的多个值,则 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 函数输出的最右侧三列中,只要小计包含对应列的值,就会在小计行上显示值 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 值而返回 null 值,而不是表示该列的小计。
为了阐释这一点,将以下行添加到 emp 表中。这会为 job 列提供一个具有 null 值的行。
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;
您会看到,在输出中,有两行在 loc 列中包含 BOSTON 而在 job 列中为空白(表中的第四个和第五个条目)。
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 函数返回 1,表示这是所有职位的小计。您会看到,该行在 employees 列包含小计值 9。
在第四行,对 job 列以及 loc 列执行的 GROUPING 函数返回 0,表示这是所有行的基础聚合,其中 loc 为 BOSTON,job 为 null 值,该行是为此示例插入的行。employees 列包含 1,这是插入的单个此类行的计数。
另外,您还会看到,在第九行(倒数第二行)中,对 job 列执行的 GROUPING 函数返回 0,而对 loc 列执行的 GROUPING 函数返回 1,表示这是 job 列为 null 值的所有地点的小计,同时也是为此示例插入的单个行的计数。