Oracle分組函數之ROLLUP魅力

本文通過示範給出Oracle ROLLUP分組函數的用法，體驗一下Oracle在統計查詢領域中的函數魅力。ROLLUP分組函數可以理解為Group By分組函數封裝後的精簡用法，這裡同時給出ROLLUP的Group By的改寫思路。

1.初始化實驗壞境
1）建立測試表group_test
SECOOLER@ora11g> create table group_test (group_id int, job varchar2(10), name varchar2(10), salary int);

Table created.

2）初始化資料
insert into group_test values (10,'Coding',    'Bruce',1000);
insert into group_test values (10,'Programmer','Clair',1000);
insert into group_test values (10,'Architect', 'Gideon',1000);
insert into group_test values (10,'Director',  'Hill',1000);

insert into group_test values (20,'Coding',    'Jason',2000);
insert into group_test values (20,'Programmer','Joey',2000);
insert into group_test values (20,'Architect', 'Martin',2000);
insert into group_test values (20,'Director',  'Michael',2000);

insert into group_test values (30,'Coding',    'Rebecca',3000);
insert into group_test values (30,'Programmer','Rex',3000);
insert into group_test values (30,'Architect', 'Richard',3000);
insert into group_test values (30,'Director',  'Sabrina',3000);

insert into group_test values (40,'Coding',    'Samuel',4000);
insert into group_test values (40,'Programmer','Susy',4000);
insert into group_test values (40,'Architect', 'Tina',4000);
insert into group_test values (40,'Director',  'Wendy',4000);

commit;

3）初始化之後的資料情況如下：
SECOOLER@ora11g> set pages 100
SECOOLER@ora11g> select * from group_test;

  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
        30 Director   Sabrina          3000
        40 Coding     Samuel           4000
        40 Programmer Susy             4000
        40 Architect  Tina             4000
        40 Director   Wendy            4000

16 rows selected.

2.先看一下普通分組的效果：對group_id進行普通的group by操作---按照小組進行分組
SECOOLER@ora11g> select group_id,sum(salary) from group_test group by group_id;

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

3.對group_id進行普通的roolup操作---按照小組進行分組，同時求總計
SECOOLER@ora11g> select group_id,sum(salary) from group_test group by rollup(group_id);

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

  使用Group By語句翻譯一下上面的SQL語句如下（union all一個統計所有資料的行）：
SECOOLER@ora11g> select group_id,sum(salary) from group_test group by group_id
  2  union all
  3  select null, sum(salary) from group_test
  4  order by 1;

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

4.再看一個rollup兩列的情況
SECOOLER@ora11g> select group_id,job,sum(salary) from group_test 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
        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 rows selected.

上面的SQL語句該如何使用Group By進行翻譯呢？
答案如下：
SECOOLER@ora11g> select group_id,job,sum(salary) from group_test group by group_id, job
  2  union all
  3  select group_id,null,sum(salary) from group_test group by group_id
  4  union all
  5  select null,null,sum(salary) from group_test
  6  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
        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 rows selected.

5.補充一步，體驗一下GROUPING函數的效果
直接看效果就OK啦：
SECOOLER@ora11g> select group_id,job,grouping(GROUP_ID),grouping(JOB),sum(salary) from group_test 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
        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 rows selected.

  看出來什麼效果了嗎？
  有的同學還是沒有看出來，小小的解釋一下：
  如果顯示“1”表示GROUPING函數對應的列（例如JOB欄位）是由於ROLLUP函數所產生的空值對應的資訊，即對此列進行匯總計算後的結果。
  如果顯示“0”表示此行對應的這列參未與ROLLUP函數分組匯總活動。
  如果還是沒有理解清楚，請參見Oracle官方文檔中的描述內容：“Using a single column as its argument,GROUPINGreturns 1 when it encounters aNULLvalue created by aROLLUPorCUBEoperation. That is, if theNULLindicates the row is a subtotal,GROUPINGreturns a 1. Any other type of value, including a storedNULL, returns a 0.”

6.小結
  ROLLUP在資料統計和報表產生過程中帶來極大的便利，而且效率比起來Group By + Union組合方法效率高得多。這也體現了Oracle在SQL統計分析上人性化、自動化、高效率的特點。

  ROLLUP與GROUP BY的關係可以參考Oracle官方文檔中的例子，連結如下：《ROLLUP Extension to GROUP BY》http://docs.oracle.com/cd/E11882_01/server.112/e25554/aggreg.htm#DWHSG8608