[Oracle] Group By 語句的擴充 - Rollup、Cube和Grouping Sets

標籤：blog   os   使用   io   ar   資料   2014   art   div   

常常寫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

[Oracle] Group By 語句的擴充 - Rollup、Cube和Grouping Sets