group by分組函式之rollup與cube用法

pwz1688發表於2014-01-20
函式

ROLLUP，是GROUP BY子句的一種擴充套件，可以為每個分組返回小計記錄以及為所有分組返回總計記錄。

CUBE，也是GROUP BY子句的一種擴充套件，可以返回每一個列組合的小計記錄，同時在末尾加上總計記錄。
示例：
一、初始化表及資料程式碼如下：

點選(此處)摺疊或開啟

  1. SQL> create table group_test (group_id int, job varchar2(10), name varchar2(10), salary int);

  3. 表已建立。

  5. SQL>
  6. SQL> insert into group_test values (10,'Coding', 'Bruce',1000);

  8. 已建立 1 行。

  10. SQL> insert into group_test values (10,'Programmer','Clair',1000);

  12. 已建立 1 行。

  14. SQL> insert into group_test values (10,'Architect', 'Gideon',1000);

  16. 已建立 1 行。

  18. SQL> insert into group_test values (10,'Director', 'Hill',1000);

  20. 已建立 1 行。

  22. SQL>
  23. SQL> insert into group_test values (20,'Coding', 'Jason',2000);

  25. 已建立 1 行。

  27. SQL> insert into group_test values (20,'Programmer','Joey',2000);

  29. 已建立 1 行。

  31. SQL> insert into group_test values (20,'Architect', 'Martin',2000);

  33. 已建立 1 行。

  35. SQL> insert into group_test values (20,'Director', 'Michael',2000);

  37. 已建立 1 行。

  39. SQL>
  40. SQL> insert into group_test values (30,'Coding', 'Rebecca',3000);

  42. 已建立 1 行。

  44. SQL> insert into group_test values (30,'Programmer','Rex',3000);

  46. 已建立 1 行。

  48. SQL> insert into group_test values (30,'Architect', 'Richard',3000);

  50. 已建立 1 行。

  52. SQL> insert into group_test values (30,'Director', 'Sabrina',3000);

  54. 已建立 1 行。

  56. SQL>
  57. SQL> insert into group_test values (40,'Coding', 'Samuel',4000);

  59. 已建立 1 行。

  61. SQL> insert into group_test values (40,'Programmer','Susy',4000);

  63. 已建立 1 行。

  65. SQL> insert into group_test values (40,'Architect', 'Tina',4000);

  67. 已建立 1 行。

  69. SQL> insert into group_test values (40,'Director', 'Wendy',4000);

  71. 已建立 1 行。

  73. SQL>
  74. SQL> commit;

  76. 提交完成。

  78. SQL> select * from group_test;

  80.    GROUP_ID JOB        NAME           SALARY
    ---------- ---------- ---------- ----------
            10 Coding     Bruce            1000
            10 Programmer Clair            1000
            10 Architect  Gideon           1000
            10 Director   Hill             1000
            20 Coding     Jason            2000
            20 Programmer Joey             2000
            20 Architect  Martin           2000
            20 Director   Michael          2000
            30 Coding     Rebecca          3000
            30 Programmer Rex              3000
            30 Architect  Richard          3000


      GROUP_ID JOB        NAME           SALARY
    ---------- ---------- ---------- ----------
            30 Director   Sabrina          3000
            40 Coding     Samuel           4000
            40 Programmer Susy             4000
            40 Architect  Tina             4000
            40 Director   Wendy            4000


  83. 已選擇16行。

二、group by一般分組與rollup語句區別
先看一般的group by語句

點選(此處)摺疊或開啟

  1. SQL> select group_id,sum(salary) from group_test
  2.   2 group by group_id;

  4.   GROUP_ID SUM(SALARY)
  5. ---------- -----------
  6.         30 12000
  7.         20 8000
  8.         40 16000
  9.         10 4000
使用rollup分組後如下

點選(此處)摺疊或開啟

  1. SQL> edit
  2. 已寫入 file afiedt.buf

  4.   1 select group_id,sum(salary) from group_test
  5.   2* group by rollup(group_id)
  6. SQL> /

  8.   GROUP_ID SUM(SALARY)
  9. ---------- -----------
  10.         10 4000
  11.         20 8000
  12.         30 12000
  13.         40 16000
  14.            40000
以上sql與上一sql執行結果相比，通過group_id分組查詢，使用rollup查詢結果會增加一個彙總合計列，可通過union all改寫如下：

SQL> edit
已寫入 file afiedt.buf

  1  select group_id,sum(salary) from group_test
  2  group by group_id
  3  union all
  4  select null,sum(salary) from group_test
  5* order by 1
SQL> /

  GROUP_ID SUM(SALARY)
---------- -----------
        10        4000
        20        8000
        30       12000
        40       16000
                   40000

下面檢視通過group_id,job二個欄位一般分組與rollup語句的區別

SQL> select group_id,job,sum(salary) from group_test
  2  group by rollup(group_id,job);

  GROUP_ID JOB        SUM(SALARY)
---------- ---------- -----------
        10 Coding            1000
        10 Director          1000
        10 Architect         1000
        10 Programmer        1000
        10                   4000
        20 Coding            2000
        20 Director          2000
        20 Architect         2000
        20 Programmer        2000
        20                   8000
        30 Coding            3000

  GROUP_ID JOB        SUM(SALARY)
---------- ---------- -----------
        30 Director          3000
        30 Architect         3000
        30 Programmer        3000
        30                  12000
        40 Coding            4000
        40 Director          4000
        40 Architect         4000
        40 Programmer        4000
        40                  16000
                            40000

已選擇21行。
用union all方式對上面程式碼改寫如下：

SQL> select group_id,job,sum(salary) from group_test
  2  group by group_id,job
  3  union all
  4  select group_id,null,sum(salary) from group_test
  5  group by group_id
  6  union all
  7  select null,null,sum(salary) from group_test
  8  order by 1,2;

  GROUP_ID JOB        SUM(SALARY)
---------- ---------- -----------
        10 Architect         1000
        10 Coding            1000
        10 Director          1000
        10 Programmer        1000
        10                   4000
        20 Architect         2000
        20 Coding            2000
        20 Director          2000
        20 Programmer        2000
        20                   8000
        30 Architect         3000

  GROUP_ID JOB        SUM(SALARY)
---------- ---------- -----------
        30 Coding            3000
        30 Director          3000
        30 Programmer        3000
        30                  12000
        40 Architect         4000
        40 Coding            4000
        40 Director          4000
        40 Programmer        4000
        40                  16000
                            40000

已選擇21行。

SQL> select group_id,job,grouping(group_id),grouping(job),sum(salary)
  2  from group_test
  3  group by rollup(group_id,job);

  GROUP_ID JOB        GROUPING(GROUP_ID) GROUPING(JOB) SUM(SALARY)
---------- ---------- ------------------ ------------- -----------
        10 Coding                      0             0        1000
        10 Director                    0             0        1000
        10 Architect                   0             0        1000
        10 Programmer                  0             0        1000
        10                             0             1        4000
        20 Coding                      0             0        2000
        20 Director                    0             0        2000
        20 Architect                   0             0        2000
        20 Programmer                  0             0        2000
        20                             0             1        8000
        30 Coding                      0             0        3000

  GROUP_ID JOB        GROUPING(GROUP_ID) GROUPING(JOB) SUM(SALARY)
---------- ---------- ------------------ ------------- -----------
        30 Director                    0             0        3000
        30 Architect                   0             0        3000
        30 Programmer                  0             0        3000
        30                             0             1       12000
        40 Coding                      0             0        4000
        40 Director                    0             0        4000
        40 Architect                   0             0        4000
        40 Programmer                  0             0        4000
        40                             0             1       16000
                                       1             1       40000

已選擇21行。

分析上面sql執行結果，可知grouping用法，當對某例進行了彙總合計時，grouping（此列）的值為1，否則為0。
三、group by一般分組與cube語句區別

SQL> edit
已寫入 file afiedt.buf

  1  select group_id,job,sum(salary) from group_test
  2  group by cube(group_id,job)
  3* order by 1,2
SQL> /

  GROUP_ID JOB        SUM(SALARY)
---------- ---------- -----------
        10 Architect         1000
        10 Coding            1000
        10 Director          1000
        10 Programmer        1000
        10                   4000
        20 Architect         2000
        20 Coding            2000
        20 Director          2000
        20 Programmer        2000
        20                   8000
        30 Architect         3000

  GROUP_ID JOB        SUM(SALARY)
---------- ---------- -----------
        30 Coding            3000
        30 Director          3000
        30 Programmer        3000
        30                  12000
        40 Architect         4000
        40 Coding            4000
        40 Director          4000
        40 Programmer        4000
        40                  16000
           Architect        10000
           Coding           10000

  GROUP_ID JOB        SUM(SALARY)
---------- ---------- -----------
           Director         10000
           Programmer       10000
                            40000

已選擇25行。

你會發現，cube與rollup用法不同，cube會對所有例進行分組彙總合計。
通過以上例子，可知：
如果是Group by  ROLLUP(A, B, C)的話，首先會對(A、B、C)進行GROUP BY，然後對(A、B)進行GROUP BY，然後是(A)進行GROUP BY，最後對全表進行GROUP BY操作。
如果是GROUP BY CUBE(A, B, C)，則首先會對(A、B、C)進行GROUP BY，然後依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最後對全表進行GROUP BY操作。 grouping_id()可以美化效果。除了使用GROUPING函式，還可以使用GROUPING_ID來標識GROUP BY的結果。
也可以 Group by Rollup(A,(B,C)) ，Group by A Rollup(B,C)，…… 這樣任意按自己想要的形式結合統計資料，非常方便。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21251711/viewspace-1073184/，如需轉載，請註明出處，否則將追究法律責任。

相關文章