GROUPING SETS扩展
在 GROUPBY 子句中使用 GROUPING SETS 扩展提供了生成一个结果集的方法,该结果集实际上是基于不同分组的多个结果集的串联。换句话说,就是执行一项 UNION ALL 运算,将多个分组的结果集合并到一个结果集中。
您会看到,UNION ALL 运算以及 GROUPINGSETS 扩展不会从合并在一起的结果集中消除重复行。
单个 GROUPING SETS 扩展的语法如下:
GROUPING SETS (
{ expr_1 | ( expr_1a [, expr_1b ] ...) |
ROLLUP ( expr_list ) | CUBE ( expr_list )
} [, ...] )
GROUPING SETS 扩展可包含一个或多个逗号分隔表达式的任意组合、带圆括号的表达式列表、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)
以下示例显示如何在 GROUPING SETS 表达式列表中将各种类型的 GROUP BY 扩展一起使用。
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 运算符将前三个查询合并在一起,将生成三个结果集的串联。
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)
版权声明
本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
评论