Enhancements to the GROUP BY Clause
1.带ROLLUP或CUBE操作的GROUP BY语句
•带ROLLUP或CUBE的GROUP BY子句，通过交叉引用列来产生超合计行
•ROLLUP分组产生一个包含常规分组行和小计值的结果集
•CUBE分组产生一个包含ROLLUP行和交叉表行的结果集
2.ROLLUP 操作
•语法：
SELECT [列名 ... ] group_function(列名)...
FROM 表名 [WHERE 子句]
GROUP BY ROLLUP (列名 ... )
[HAVING 子句] [ORDER BY 子句];
•ROLLUP 是一个 GROUP BY 子句的扩展，用 ROLLUP 操作产生小计和累计
实际上，是先按group by子句进行分组，然后在分组的基础上进行横行小计和总计
•示例：
=============================================================================
SQL> SELECT department_id, job_id, SUM(salary)
2 FROM employees
3 WHERE department_id < 60
4 GROUP BY ROLLUP(department_id, job_id);
DEPARTMENT_ID JOB_ID SUM(SALARY)
------------- ---------- -----------
10 AD_ASST 4400 -----(group1) regular rows
10 4400 ----------(group2) superaggregate rows(subtotals)
20 MK_MAN 13000 -----(group1) regular rows
20 MK_REP 6000 -----(group1) regular rows
20 19000 ----------(group2) superaggregate rows(subtotals)
50 ST_MAN 5800 -----(group1) regular rows
50 ST_CLERK 11700 -----(group1) regular rows
50 17500 ----------(group2) superaggregate rows(subtotals)
40900 ----------(group3) superaggregate rows
=============================================================================
•关于ROLLUP(列名 ... )的括号中的列名列表
列名列表中各列的次序很重要，一般与select子句中的列名列表保持一致
如果列名列表中有n个列，则查询结果的行可以分为 n+1 组(见上面的示例)
基于列名列表中最后一个列的行称为regular rows(也就是上面示例的group1的行，基于job_id列)
结果集的最后一行总是最后的合计值
可以再参考下面的示例
示例2：
=============================================================================
SQL> select department_id,job_id,manager_id,sum(salary)
2 from employees where department_id < 60
3 group by rollup(department_id,job_id,manager_id)
4 order by 3,2,1;
DEPARTMENT_ID JOB_ID MANAGER_ID SUM(SALARY)
------------- ---------- ---------- -----------
20 MK_MAN 100 13000 --(group1) 基于列名列表中的第1、2、3列
50 ST_MAN 100 5800 --(group1)
10 AD_ASST 101 4400 --(group1)
50 ST_CLERK 124 11700 --(group1)
20 MK_REP 201 6000 --(group1)
10 AD_ASST 4400 --(group2) 基于列名列表中的第1、2列
20 MK_MAN 13000 --(group2)
20 MK_REP 6000 --(group2)
50 ST_CLERK 11700 --(group2)
50 ST_MAN 5800 --(group2)
10 4400 --(group3) 基于列名列表中的第1列
20 19000 --(group3)
50 17500 --(group3)
40900 --(group4) 总的合计值
=============================================================================
3.CUBE 操作
•语法：
SELECT [列名 ... ] group_function(列名)...
FROM 表名 [WHERE 子句]
GROUP BY CUBE (列名 ... )
[HAVING 子句] [ORDER BY 子句];
•CUBE 是一个 GROUP BY 子句的扩展，用CUBE操作能够产生带单个 SELECT 语句的交叉表值
cube操作可以应用于所有的分组统计函数
rollup操作只是生成部分分组的统计值，cube操作可以生成group by子句中全部列的所有分组的统计值
•rollup操作先按group by子句进行分组，然后在分组的基础上进行横行小计、纵向小计和总计
在group by子句中列名列表的各列的每一种组合都能生成统计行，如果列名列表中有n个列的话，则会有2的n次方个组合，在数学上就是一个n维立方体(n-dimensional cube)
•示例：
=============================================================================
SQL> SELECT department_id, job_id, SUM(salary)
2 FROM employees
3 WHERE department_id < 60
4 GROUP BY CUBE (department_id, job_id) ;
DEPARTMENT_ID JOB_ID SUM(SALARY)
------------- ---------- -----------
40900 --(group4) 总的合计值
MK_MAN 13000 -----(group3) 基于列名列表的第2列
MK_REP 6000 -----(group3) 基于列名列表的第2列
ST_MAN 5800 -----(group3) 基于列名列表的第2列
AD_ASST 4400 -----(group3) 基于列名列表的第2列
ST_CLERK 11700 -----(group3) 基于列名列表的第2列
10 4400 ----------(group2) 基于列名列表的第1列
10 AD_ASST 4400 --(group1) 基于列名列表的第1、2列
20 19000 ----------(group2) 基于列名列表的第1列
20 MK_MAN 13000 --(group1) 基于列名列表的第1、2列
20 MK_REP 6000 --(group1) 基于列名列表的第1、2列
50 17500 ----------(group2) 基于列名列表的第1列
50 ST_MAN 5800 --(group1) 基于列名列表的第1、2列
50 ST_CLERK 11700 --(group1) 基于列名列表的第1、2列
=============================================================================
可以看到，cube操作实际上是对rollup的扩展，上面的示例与rollup的示例1相比，多了group3而已
4.GROUPING 函数
•语法：
SELECT [列名...] ,group_function(列)... ,
GROUPING(表达式1)，GROUPING(表达式2)...
FROM 表名 [WHERE 子句]
GROUP BY ROLLUP|CUBE (列名 ... )
[HAVING 子句] [ORDER BY 子句];
•当使用ROLLUP/CUBE操作计算统计值时，有时候有些列参与了统计，有些则没有参与统计
grouping函数需要一个列名作为参数，如果该列参与了统计则返回0，如果没有参与统计则返回1
group by子句的列名列表中有多少列，就必须有几个对应的grouping函数表达式
•示例：
=============================================================================
SQL> SELECT department_id DEPTID, job_id JOB,SUM(salary),
2 GROUPING(department_id) GRP_DEPT, GROUPING(job_id) GRP_JOB
3 FROM employees
4 WHERE department_id < 50
5 GROUP BY ROLLUP(department_id, job_id);
DEPTID JOB SUM(SALARY) GRP_DEPT GRP_JOB
---------- ---------- ----------- ---------- ----------
10 AD_ASST 4400 0 0
10 4400 0 1
20 MK_MAN 13000 0 0
20 MK_REP 6000 0 0
20 19000 0 1
23400 1 1
=============================================================================
5.分组集合(GROUPING SETS)
•GROUPING SETS 是 GROUP BY 子句更进一步的扩展
•你能够用 GROUPING SETS 在同一查询中定义多重分组
•Oracle计算在 GROUPING SETS 子句中指定的所有分组，然后组合各个个单独分组的结果
•分组集合的效率：
–对基表仅进行一个查询
–不需要写复杂的 UNION 语句
–GROUPING SETS 有更多的元素，更好的执行性能
•示例：
=============================================================================
SELECT department_id, job_id,manager_id,avg(salary)
FROM employees
GROUP BY GROUPING SETS
((department_id,job_id), (job_id,manager_id));
等效于
SELECT department_id, job_id,NULL,avg(salary)
FROM employees
GROUP BY department_id,job_id
UNION ALL
SELECT NULL,job_id,jmanager_id,avg(salary)
FROM employees
GROUP BY job_id,manager_id
=============================================================================
•CUBE/ROLLUP操作也可以用grouping set来实现
------------------------------------------------------------------------------------------
CUBE(a,b,c) 等效于 grouping set((a,b,c), (a,b), (a,c), (b,c), (a), (b), (c), ())
ROLLUP(a,b,c) 等效于 grouping set((a,b,c), (a,b), (a), ())
------------------------------------------------------------------------------------------
6.复合列(Composite Columns)
•复合列是一个作为整体被处理的列集合
比如，ROLLUP (a,(b,c), d) ，其中 (b,c)是复合列
•为了指定复合列，GROUP BY 子句中在圆括号内的列可以进行分组(使用圆括号分组)
Oracle在进行 ROLLUP 或 CUBE 操作时将复合列作为一个整体来处理
•一个复合列(Composite Columns)是一个列的集合，在分组计算的时候被视为一个整体
可以在rollup、cube和grouping set中使用复合列
•当使用ROLLUP或CUBE时，复合列将某些分组被省略
比如：ROLLUP(a, (b, c)) 等效于 grouping set((a,b,c),(a),()) ，没有了(a,b)
•示例：
=============================================================================
SQL> select department_id, job_id, manager_id, sum(salary)
2 from employees where department_id < 60
3 group by rollup(department_id,(job_id,manager_id));
DEPARTMENT_ID JOB_ID MANAGER_ID SUM(SALARY)
------------- ---------- ---------- -----------
10 AD_ASST 101 4400
10 4400
20 MK_MAN 100 13000
20 MK_REP 201 6000
20 19000
50 ST_MAN 100 5800
50 ST_CLERK 124 11700
50 17500
40900
=============================================================================
7.连接分组(Concatenated Groupings)
•连接分组提供一种简明的方式来生成有用的分组组合
•为了指定连接分组集合，用逗号分开多重分组集合、ROLLUP和CUBE操作
这样，Oracle可以方便地将它们组合在一个单个的GROUP BY子句中
•结果集是每个分组集的交叉分组
比如：GROUP BY GROUPING SETS(a,b), GROUPING SETS(c,d) 等效 (a,c), (a,d), (b,c), (b,d)
•示例：
=============================================================================
SQL> select department_id, job_id, manager_id, sum(salary)
2 from employees where department_id < 60
3 group by department_id, rollup(job_id), cube(manager_id);
DEPARTMENT_ID JOB_ID MANAGER_ID SUM(SALARY)
------------- ---------- ---------- -----------
10 AD_ASST 101 4400
20 MK_MAN 100 13000
20 MK_REP 201 6000
50 ST_MAN 100 5800
50 ST_CLERK 124 11700 ---- (department_id, manager_id, job_id )
10 101 4400
20 100 13000
20 201 6000
50 100 5800
50 124 11700 ----- (department_id, manager_id)
10 AD_ASST 4400 ----- (department_id, job_id)
10 4400
20 MK_MAN 13000 ----- (department_id, job_id)
20 MK_REP 6000 ----- (department_id, job_id)
20 19000
50 ST_MAN 5800 ----- (department_id, job_id)
50 ST_CLERK 11700 ----- (department_id, job_id)
50 17500 ----- (department_id)
=============================================================================
