[Oracle] Group By 语句的扩展 - Rollup、Cube和Grouping Sets

经常写SQL语句的人应该知道Group by语句的主要用法是进行分类汇总，下面是一种它最常见的用法（根据部门、职位分别统计业绩）：

[sql]  view plain copy print ?

1. SELECT  a.dname,b.job,SUM(b.sal) sum_sal  
2. FROM dept a,emp b  
3. WHERE a.deptno = b.deptno  
4. GROUP  BY a.dname,b.job;  
5.   
6. DNAME          JOB          SUM_SAL  
7. -------------- --------- ----------  
8. SALES          MANAGER         2850  
9. SALES          CLERK            950  
10. SALES          SALESMAN        5600  
11. ACCOUNTING     MANAGER         2450  
12. ACCOUNTING     PRESIDENT       5000  
13. ACCOUNTING     CLERK           1300  
14. RESEARCH       MANAGER         2975  
15. RESEARCH       ANALYST         6000  
16. RESEARCH       CLERK           1900  

这时候，如果有人跑过来跟你说：我除了以上数据之外，还要每个部门总的业绩以及所有部门加起来的业绩，这时候你很可能会想到如下的笨方法（union all）：

[sql]  view plain copy print ?

1. select * from (  
2. SELECT  a.dname,b.job,SUM(b.sal) sum_sal  
3. FROM dept a,emp b  
4. WHERE a.deptno = b.deptno  
5. GROUP  BY a.dname,b.job  
6. UNION ALL  
7. --实现了部门的小计  
8. SELECT  a.dname,NULL, SUM(b.sal) sum_sal  
9. FROM dept a,emp b  
10. WHERE a.deptno = b.deptno  
11. GROUP  BY a.dname  
12. UNION ALL  
13. --实现了所有部门总的合计  
14. SELECT  NULL,NULL, SUM(b.sal) sum_sal  
15. FROM dept a,emp b  
16. WHERE a.deptno = b.deptno)  
17. order by dname;  
18.   
19. DNAME          JOB          SUM_SAL  
20. -------------- --------- ----------  
21. ACCOUNTING     CLERK           1300  
22. ACCOUNTING     MANAGER         2450  
23. ACCOUNTING     PRESIDENT       5000  
24. ACCOUNTING                     8750  
25. RESEARCH       CLERK           1900  
26. RESEARCH       MANAGER         2975  
27. RESEARCH       ANALYST         6000  
28. RESEARCH                      10875  
29. SALES          CLERK            950  
30. SALES          MANAGER         2850  
31. SALES          SALESMAN        5600  
32. SALES                          9400  
33.                               29025  
34.   
35. union all 合并笨办法产生的执行计划  
36. -------------------------------------------------------------------------------  
37. Plan hash value: 2979078843  
38. -------------------------------------------------------------------------------  
39. | Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
40. -------------------------------------------------------------------------------  
41. |   0 | SELECT STATEMENT       |      |    29 |   812 |    23  (22)| 00:00:01 |  
42. |   1 |  SORT ORDER BY         |      |    29 |   812 |    23  (22)| 00:00:01 |  
43. |   2 |   VIEW                 |      |    29 |   812 |    22  (19)| 00:00:01 |  
44. |   3 |    UNION-ALL           |      |       |       |            |          |  
45. |   4 |     HASH GROUP BY      |      |    14 |   756 |     8  (25)| 00:00:01 |  
46. |*  5 |      HASH JOIN         |      |    14 |   756 |     7  (15)| 00:00:01 |  
47. |   6 |       TABLE ACCESS FULL| DEPT |     4 |    88 |     3   (0)| 00:00:01 |  
48. |   7 |       TABLE ACCESS FULL| EMP  |    14 |   448 |     3   (0)| 00:00:01 |  
49. |   8 |     HASH GROUP BY      |      |    14 |   672 |     8  (25)| 00:00:01 |  
50. |*  9 |      HASH JOIN         |      |    14 |   672 |     7  (15)| 00:00:01 |  
51. |  10 |       TABLE ACCESS FULL| DEPT |     4 |    88 |     3   (0)| 00:00:01 |  
52. |  11 |       TABLE ACCESS FULL| EMP  |    14 |   364 |     3   (0)| 00:00:01 |  
53. |  12 |     SORT AGGREGATE     |      |     1 |    39 |            |          |  
54. |* 13 |      HASH JOIN         |      |    14 |   546 |     7  (15)| 00:00:01 |  
55. |  14 |       TABLE ACCESS FULL| DEPT |     4 |    52 |     3   (0)| 00:00:01 |  
56. |  15 |       TABLE ACCESS FULL| EMP  |    14 |   364 |     3   (0)| 00:00:01 |  
57. -------------------------------------------------------------------------------  

其实，如果你知道Group By的Rollup扩展的话，这种需求只是小case：

[sql]  view plain copy print ?

1. SELECT  a.dname,b.job, SUM(b.sal) sum_sal  
2. FROM dept a,emp b   
3. WHERE a.deptno = b.deptno  
4. GROUP  BY ROLLUP(a.dname,b.job);  
5.   
6. DNAME          JOB          SUM_SAL  
7. -------------- --------- ----------  
8. SALES          CLERK            950  
9. SALES          MANAGER         2850  
10. SALES          SALESMAN        5600  
11. SALES                          9400  
12. RESEARCH       CLERK           1900  
13. RESEARCH       ANALYST         6000  
14. RESEARCH       MANAGER         2975  
15. RESEARCH                      10875  
16. ACCOUNTING     CLERK           1300  
17. ACCOUNTING     MANAGER         2450  
18. ACCOUNTING     PRESIDENT       5000  
19. ACCOUNTING                     8750  
20.                               29025  
21.   
22. rollup写法产生的执行计划  
23. -----------------------------------------------------------------------------  
24. Plan hash value: 1037965942  
25. -----------------------------------------------------------------------------  
26. | Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
27. -----------------------------------------------------------------------------  
28. |   0 | SELECT STATEMENT     |      |    14 |   756 |     8  (25)| 00:00:01 |  
29. |   1 |  SORT GROUP BY ROLLUP|      |    14 |   756 |     8  (25)| 00:00:01 |  
30. |*  2 |   HASH JOIN          |      |    14 |   756 |     7  (15)| 00:00:01 |  
31. |   3 |    TABLE ACCESS FULL | DEPT |     4 |    88 |     3   (0)| 00:00:01 |  
32. |   4 |    TABLE ACCESS FULL | EMP  |    14 |   448 |     3   (0)| 00:00:01 |  
33. -----------------------------------------------------------------------------  

可以发现，这种方法不但SQL书写方便，性能也能得到提高。

这时候，如果又有人跑过来说：除了以上数据，他还需要每个职位总的业绩，你只要把rollup换成cube就可以了，如下所示：

[sql]  view plain copy print ?

1. -- CUBE分组  
2. SELECT  a.dname,b.job, SUM(b.sal) sum_sal  
3. FROM dept a,emp b   
4. WHERE a.deptno = b.deptno  
5. GROUP  BY CUBE(a.dname,b.job);  
6.   
7. DNAME          JOB          SUM_SAL  
8. -------------- --------- ----------  
9.                               29025  
10.                CLERK           4150  
11.                ANALYST         6000  
12.                MANAGER         8275  
13.                SALESMAN        5600  
14.                PRESIDENT       5000  
15. SALES                          9400  
16. SALES          CLERK            950  
17. SALES          MANAGER         2850  
18. SALES          SALESMAN        5600  
19. RESEARCH                      10875  
20. RESEARCH       CLERK           1900  
21. RESEARCH       ANALYST         6000  
22. RESEARCH       MANAGER         2975  
23. ACCOUNTING                     8750  
24. ACCOUNTING     CLERK           1300  
25. ACCOUNTING     MANAGER         2450  
26. ACCOUNTING     PRESIDENT       5000  

从上面可以看出：cube比rollup的展现的粒度更细一些。

这时候，如果又有人跑过来说：他不需要那么细的数据，只需要汇总的数据，可以使用Grouping Sets：

[sql]  view plain copy print ?

1. ---GROUPING SETS分组  
2. SELECT to_char(b.hiredate,'yyyy') hire_year,a.dname,b.job, SUM(sal) sum_sal  
3. FROM dept a,emp b   
4. WHERE a.deptno = b.deptno  
5. GROUP BY GROUPING SETS(to_char(b.hiredate,'yyyy'),a.dname,b.job);  
6.   
7. HIRE DNAME          JOB          SUM_SAL  
8. ---- -------------- --------- ----------  
9. 1987                                4100  
10. 1980                                 800  
11. 1982                                1300  
12. 1981                               22825  
13.      ACCOUNTING                     8750  
14.      RESEARCH                      10875  
15.      SALES                          9400  
16.                     CLERK           4150  
17.                     SALESMAN        5600  
18.                     PRESIDENT       5000  
19.                     MANAGER         8275  
20.                     ANALYST         6000