[Oracle] Group By 語句的擴充

經常寫SQL語句的人應該知道Group by語句的主要用法是進行分類匯總，下面是一種它最常見的用法（根據部門、職位分別統計業績）：

SELECT  a.dname,b.job,SUM(b.sal) sum_salFROM dept a,emp bWHERE a.deptno = b.deptnoGROUP  BY a.dname,b.job;DNAME          JOB          SUM_SAL-------------- --------- ----------SALES          MANAGER         2850SALES          CLERK            950SALES          SALESMAN        5600ACCOUNTING     MANAGER         2450ACCOUNTING     PRESIDENT       5000ACCOUNTING     CLERK           1300RESEARCH       MANAGER         2975RESEARCH       ANALYST         6000RESEARCH       CLERK           1900

這時候，如果有人跑過來跟你說：我除了以上資料之外，還要每個部門總的業績以及所有部門加起來的業績，這時候你很可能會想到如下的笨方法（union all）：

select * from (SELECT  a.dname,b.job,SUM(b.sal) sum_salFROM dept a,emp bWHERE a.deptno = b.deptnoGROUP  BY a.dname,b.jobUNION ALL--實現了部門的小計SELECT  a.dname,NULL, SUM(b.sal) sum_salFROM dept a,emp bWHERE a.deptno = b.deptnoGROUP  BY a.dnameUNION ALL--實現了所有部門總的合計SELECT  NULL,NULL, SUM(b.sal) sum_salFROM dept a,emp bWHERE a.deptno = b.deptno)order by dname;DNAME          JOB          SUM_SAL-------------- --------- ----------ACCOUNTING     CLERK           1300ACCOUNTING     MANAGER         2450ACCOUNTING     PRESIDENT       5000ACCOUNTING                     8750RESEARCH       CLERK           1900RESEARCH       MANAGER         2975RESEARCH       ANALYST         6000RESEARCH                      10875SALES          CLERK            950SALES          MANAGER         2850SALES          SALESMAN        5600SALES                          9400                              29025union all 合并笨辦法產生的執行計畫-------------------------------------------------------------------------------Plan hash value: 2979078843-------------------------------------------------------------------------------| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------|   0 | SELECT STATEMENT       |      |    29 |   812 |    23  (22)| 00:00:01 ||   1 |  SORT ORDER BY         |      |    29 |   812 |    23  (22)| 00:00:01 ||   2 |   VIEW                 |      |    29 |   812 |    22  (19)| 00:00:01 ||   3 |    UNION-ALL           |      |       |       |            |          ||   4 |     HASH GROUP BY      |      |    14 |   756 |     8  (25)| 00:00:01 ||*  5 |      HASH JOIN         |      |    14 |   756 |     7  (15)| 00:00:01 ||   6 |       TABLE ACCESS FULL| DEPT |     4 |    88 |     3   (0)| 00:00:01 ||   7 |       TABLE ACCESS FULL| EMP  |    14 |   448 |     3   (0)| 00:00:01 ||   8 |     HASH GROUP BY      |      |    14 |   672 |     8  (25)| 00:00:01 ||*  9 |      HASH JOIN         |      |    14 |   672 |     7  (15)| 00:00:01 ||  10 |       TABLE ACCESS FULL| DEPT |     4 |    88 |     3   (0)| 00:00:01 ||  11 |       TABLE ACCESS FULL| EMP  |    14 |   364 |     3   (0)| 00:00:01 ||  12 |     SORT AGGREGATE     |      |     1 |    39 |            |          ||* 13 |      HASH JOIN         |      |    14 |   546 |     7  (15)| 00:00:01 ||  14 |       TABLE ACCESS FULL| DEPT |     4 |    52 |     3   (0)| 00:00:01 ||  15 |       TABLE ACCESS FULL| EMP  |    14 |   364 |     3   (0)| 00:00:01 |-------------------------------------------------------------------------------

其實，如果你知道Group By的Rollup擴充的話，這種需求只是小case：

SELECT  a.dname,b.job, SUM(b.sal) sum_salFROM dept a,emp b WHERE a.deptno = b.deptnoGROUP  BY ROLLUP(a.dname,b.job);DNAME          JOB          SUM_SAL-------------- --------- ----------SALES          CLERK            950SALES          MANAGER         2850SALES          SALESMAN        5600SALES                          9400RESEARCH       CLERK           1900RESEARCH       ANALYST         6000RESEARCH       MANAGER         2975RESEARCH                      10875ACCOUNTING     CLERK           1300ACCOUNTING     MANAGER         2450ACCOUNTING     PRESIDENT       5000ACCOUNTING                     8750                              29025rollup寫法產生的執行計畫-----------------------------------------------------------------------------Plan hash value: 1037965942-----------------------------------------------------------------------------| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------|   0 | SELECT STATEMENT     |      |    14 |   756 |     8  (25)| 00:00:01 ||   1 |  SORT GROUP BY ROLLUP|      |    14 |   756 |     8  (25)| 00:00:01 ||*  2 |   HASH JOIN          |      |    14 |   756 |     7  (15)| 00:00:01 ||   3 |    TABLE ACCESS FULL | DEPT |     4 |    88 |     3   (0)| 00:00:01 ||   4 |    TABLE ACCESS FULL | EMP  |    14 |   448 |     3   (0)| 00:00:01 |-----------------------------------------------------------------------------

可以發現，這種方法不但SQL書寫方便，效能也能得到提高。

這時候，如果又有人跑過來說：除了以上資料，他還需要每個職位總的業績，你只要把rollup換成cube就可以了，如下所示：

-- CUBE分組SELECT  a.dname,b.job, SUM(b.sal) sum_salFROM dept a,emp b WHERE a.deptno = b.deptnoGROUP  BY CUBE(a.dname,b.job);DNAME          JOB          SUM_SAL-------------- --------- ----------                              29025               CLERK           4150               ANALYST         6000               MANAGER         8275               SALESMAN        5600               PRESIDENT       5000SALES                          9400SALES          CLERK            950SALES          MANAGER         2850SALES          SALESMAN        5600RESEARCH                      10875RESEARCH       CLERK           1900RESEARCH       ANALYST         6000RESEARCH       MANAGER         2975ACCOUNTING                     8750ACCOUNTING     CLERK           1300ACCOUNTING     MANAGER         2450ACCOUNTING     PRESIDENT       5000

從上面可以看出：cube比rollup的展現的粒度更細一些。

這時候，如果又有人跑過來說：他不需要那麼細的資料，只需要匯總的資料，可以使用Grouping Sets：

---GROUPING SETS分組SELECT to_char(b.hiredate,'yyyy') hire_year,a.dname,b.job, SUM(sal) sum_salFROM dept a,emp b WHERE a.deptno = b.deptnoGROUP BY GROUPING SETS(to_char(b.hiredate,'yyyy'),a.dname,b.job);HIRE DNAME          JOB          SUM_SAL---- -------------- --------- ----------1987                                41001980                                 8001982                                13001981                               22825     ACCOUNTING                     8750     RESEARCH                      10875     SALES                          9400                    CLERK           4150                    SALESMAN        5600                    PRESIDENT       5000                    MANAGER         8275                    ANALYST         6000