Oracle增強型分組函數

ROLLUP()函數是對于GROUP BY 分組統計的功能擴展，可以實現分組統計求和的效果。

下面我們開始準備我們的試驗的環境

--創建新表employee_salary，存儲數據來自用戶hr.employees

SQL>CREATE TABLE employee_salary ASSELECT E.FIRST_NAME,E.JOB_ID,E.MANAGER_ID,E.SALARY FROM HR.EMPLOYEES E WHERE E.JOB_ID='IT_PROG';
--查看新創建的表

SQL>SELECT * FROM employee_salary;

--顯示效果如下

FIRST_NAME    JOB_ID      MANAGER_ID     SALARY

-------------------- ---------- --------------------------------

Alexander       IT_PROG        102         9000.00

Bruce             IT_PROG        103          6000.00

David             IT_PROG        103          4800.00

Valli               IT_PROG        103          4800.00

Diana             IT_PROG        103         4200.00

--首先按照JOB_ID進行分組，查看salary和

SQL>SELECT sa.job_id,SUM(sa.salary) FROM employee_salary sa GROUP BY sa.job_id;

--顯示效果如下

JOB_ID      SUM(SA.SALARY)

---------- -----------------------

IT_PROG          28800

--按照MANAGER_ID進行分組，查看salary和

SELECT sa.manager_id,SUM(sa.salary) FROM employee_salary sa GROUP BY sa.manager_id;

--顯示效果如下

MANAGER_ID    SUM(SA.SALARY)

---------- ----------------------------

      102                      9000

      103                    19800

--我們使用ROLLUP函數看看是什么效果

SELECT sa.job_id,sa.manager_id,SUM(sa.salary) FROM employee_salary sa GROUP BY ROLLUP(sa.job_id,sa.manager_id);

--顯示效果如下

JOB_ID     MANAGER_ID  SUM(SA.SALARY)

---------- ---------- --------------------------------

IT_PROG       102           9000

IT_PROG       103          19800

IT_PROG                      28800

                                   28800

說明：ROLLUP解析過程，以ROLLUP(a,b)為例

ROLLUP(a,b)== GROUP(a,b) UNION ALL GROUP(a) UNIONALL GROUP()

即：解析順序是從右至左，顯示按照a,b分組，接下來是按照a分組，最后是對全表分組；

上面ROLLUP (sa.job_id,sa.manager_id)等同下面UNION ALL的集合操作

SELECT sa.job_id,sa.manager_id,SUM(sa.salary) FROM employee_salary sa

GROUP BY sa.job_id,sa.manager_id

UNION ALL

SELECT sa.job_id,NULL,SUM(sa.salary) FROM employee_salary sa

GROUP BY sa.job_id

UNION ALL

SELECT NULL,NULL,SUM(sa.salary) FROM employee_salary sa GROUP BY()

ORDER BY 1,2;

--顯示效果如下

JOB_ID     MANAGER_ID  SUM(SA.SALARY)

---------- ---------- --------------------------------

IT_PROG       102                9000

IT_PROG       103               19800

IT_PROG                            28800

                                         28800

說明：雖然最后展示的效果是相同的，但是ROLLUP()函數的執行效率要比UNION ALL的效率要高、要快。

ROLLUP(A,B,C)是在執行組合操作，無順序，組合公式是（n+1），當n=3時，組合結果就是有4個。

ROLLUP()中的參數位置不同，得出的結果可能不一樣！

在理解ROLLUP的基礎上再來理解CUBE()就比較容易，ROLLUP()在執行組合操作，CUBE()就是在執行排序動作，從左至右，排序公式是2N次方。

CUBE(A,B,C)==GROUP BY (A,B,C) UNION ALL GROUP BY (A,B) UNION ALL GROUP BY (A,C) UNION ALL GROUPBY (A) UNION ALL GROUP BY (B) UNION ALL GROUP BY (C) UNION ALL GROUP BY ()

例如：執行下列語句

SQL> SELECT sa.job_id,sa.manager_id,SUM(sa.salary) FROM employee_salary sa GROUP BY CUBE(sa.job_id,sa.manager_id);

--顯示效果如下

JOB_ID    MANAGER_ID SUM(SA.SALARY)

---------- ---------- -----------------------------

                                           28800

                     102                   9000

                     103                 19800

IT_PROG                              28800

IT_PROG        102                   9000

IT_PROG        103                  19800

6 rows selected

上面的語句等同執行下列語句

SQL>SELECT sa.job_id,sa.manager_id,SUM(sa.salary) FROM employee_salary sa GROUP BY (sa.job_id,sa.manager_id)

UNION ALL

SELECT sa.job_id,NULL,SUM(sa.salary) FROM employee_salary sa

GROUP BY (sa.job_id)

UNION ALL

SELECT NULL,sa.manager_id,SUM(sa.salary) FROM employee_salary sa 

GROUP BY (sa.manager_id)

UNION ALL

SELECT NULL,NULL,SUM(sa.salary) FROM employee_salary sa

GROUPBY ();

關于GROUPING

GROUPING(A)用于判斷對于分組后的列是否是空值NULL，返回值有0和1兩個值，1表示，該列為空——NULL，這個NULL值是因為分組時產生，否則則為0；