Oralce 進階 sql 之 rollup 與 cube，oralcerollup

Oralce 進階 sql 之 rollup 與 cube，oralcerollup
在 oracle SQL 查詢中，oracle 提供了一些進階功能能夠簡化 SQL 查詢語句以及提高查詢效能。下面將介紹 rollup，cube的用法與區別。

1. rollup

rollup 能夠在 select 語句中計算指定分組欄位的多個層次的小計以及合計. rollup 非常容易使用並且非常高效。
rollup 從右至左掃描分組欄位, 逐步建立更進階別的小計. 最後再建立一行總計. rollup 將建立 n + 1 個層次(層級)的小計, n 指的是 rollup 中的欄位數. 

1.1 什麼時候使用 rollup

•  對於一些層次維度小計(統計某些層次欄位的小計及總計)
•  對於資料倉儲中的統計匯總表, rollup 能夠簡化統計匯總表並且提高查詢統計匯總表的速度

1.2 rollup 例子

首先構造兩個基本表 emp(員工表) 與 dept(部門表)：

SQL> create table emp as select * from scott.emp;Table created.SQL> create table dept as select * from scott.dept;Table created.SQL> select * from emp;     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ----------      7369 SMITH      CLERK           7902 17-DEC-80        800                    20      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30      7566 JONES      MANAGER         7839 02-APR-81       2975                    20      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20      7839 KING       PRESIDENT            17-NOV-81       5000                    10      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20      7900 JAMES      CLERK           7698 03-DEC-81        950                    30      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20      7934 MILLER     CLERK           7782 23-JAN-82       1300                    1014 rows selected.SQL> select * from dept;    DEPTNO DNAME          LOC---------- -------------- -------------        10 ACCOUNTING     NEW YORK        20 RESEARCH       DALLAS        30 SALES          CHICAGO        40 OPERATIONS     BOSTON

如果想統計每個部門每個職位的總薪水，sql 語句如下：

SQL> select b.dname, a.job, sum(a.sal)  2  from emp a, dept b  3  where a.deptno = b.deptno  4  group by b.dname, a.job;DNAME          JOB       SUM(A.SAL)-------------- --------- ----------SALES          MANAGER         2850SALES          CLERK            950ACCOUNTING     MANAGER         2450ACCOUNTING     PRESIDENT       5000ACCOUNTING     CLERK           1300SALES          SALESMAN        5600RESEARCH       MANAGER         2975RESEARCH       ANALYST         6000RESEARCH       CLERK           19009 rows selected.

如果想用一條 sql 語句統計 每個部門每個職位的總薪水， 每個部門的總薪水以及 所有部門的總薪水的話，勢必要再用一個 group by 語句統計每個部門的總薪水然後再與上面的結果 union 起來才能得到最後的結果，但是這樣寫的話除了書寫會複雜一點外，另外肯定還要多掃描幾次 emp 與 dept 表，現在 oracle 提供了 rollup 子句，我們可以先來看看 rollup 子句的結果：

SQL> select b.dname, a.job, sum(a.sal)  2  from emp a, dept b  3  where a.deptno = b.deptno  4  group by rollup(b.dname, a.job);DNAME          JOB       SUM(A.SAL)-------------- --------- ----------SALES          CLERK            950SALES          MANAGER         2850SALES          SALESMAN        5600SALES                          9400RESEARCH       CLERK           1900RESEARCH       ANALYST         6000RESEARCH       MANAGER         2975RESEARCH                      10875ACCOUNTING     CLERK           1300ACCOUNTING     MANAGER         2450ACCOUNTING     PRESIDENT       5000ACCOUNTING                     8750                              29025

從上面的結果中可以看出，rollup 子句會為每個部門增加一行小計以及為所有部門增加一行總計，即統計了每個部門的總薪水以及所有部門的總薪水。

通常來說，rollup 往往同 group by 語句一起使用，它是 group by 語句的一種擴充。

• 如果語句為 group by rollup(a, b)，oracle 將會從右至左先對欄位 a 和 b 先進行 group by，然後對欄位 A 進行 group by，最後對全表進行 group by。
• 如果語句為 group by rollup(a, b, c), oracle 將從右至左先會對欄位 a 和 b 和 c 先進行 group by, 然後對欄位 a 和 b 進行 group by，然後對欄位 a 進行 group by，最後對全表進行 group by。

下面我們將示範一個 rollup 三個欄位的例子：

SQL> select b.dname, a.job, to_char(hiredate, 'yyyy'), sum(sal)  2  from emp a, dept b  3  where a.deptno = b.deptno  4  group by b.dname, a.job, to_char(hiredate, 'yyyy')  5  order by 1, 2, 3;DNAME          JOB       TO_C   SUM(SAL)-------------- --------- ---- ----------ACCOUNTING     CLERK     1982       1300ACCOUNTING     MANAGER   1981       2450ACCOUNTING     PRESIDENT 1981       5000RESEARCH       ANALYST   1981       3000RESEARCH       ANALYST   1987       3000RESEARCH       CLERK     1980        800RESEARCH       CLERK     1987       1100RESEARCH       MANAGER   1981       2975SALES          CLERK     1981        950SALES          MANAGER   1981       2850SALES          SALESMAN  1981       560011 rows selected.SQL> select b.dname, a.job, to_char(hiredate, 'yyyy'), sum(sal)  2  from emp a, dept b  3  where a.deptno = b.deptno  4  group by rollup(b.dname, a.job, to_char(hiredate, 'yyyy'));DNAME          JOB       TO_C   SUM(SAL)-------------- --------- ---- ----------SALES          CLERK     1981        950SALES          CLERK                 950SALES          MANAGER   1981       2850SALES          MANAGER              2850SALES          SALESMAN  1981       5600SALES          SALESMAN             5600SALES                               9400RESEARCH       CLERK     1980        800RESEARCH       CLERK     1987       1100RESEARCH       CLERK                1900RESEARCH       ANALYST   1981       3000RESEARCH       ANALYST   1987       3000RESEARCH       ANALYST              6000RESEARCH       MANAGER   1981       2975RESEARCH       MANAGER              2975RESEARCH                           10875ACCOUNTING     CLERK     1982       1300ACCOUNTING     CLERK                1300ACCOUNTING     MANAGER   1981       2450ACCOUNTING     MANAGER              2450ACCOUNTING     PRESIDENT 1981       5000ACCOUNTING     PRESIDENT            5000ACCOUNTING                          8750                                   2902524 rows selected.

1.3 部分 rollup(Partial rollup)

當你只想統計部分欄位時, 可以使用部分 rollup. 例如, group by a, rollup(b, c), 這條語句將建立三個(2 + 1)層級的小計. 分別為層級 (a, b, c), 層級 (a, b) 以及層級 (a).

SQL> select b.dname, a.job, to_char(hiredate, 'yyyy'), sum(sal)  2  from emp a, dept b  3  where a.deptno = b.deptno  4  group by b.dname, rollup(a.job, to_char(hiredate, 'yyyy'));DNAME          JOB       TO_C   SUM(SAL)-------------- --------- ---- ----------SALES          CLERK     1981        950SALES          CLERK                 950SALES          MANAGER   1981       2850SALES          MANAGER              2850SALES          SALESMAN  1981       5600SALES          SALESMAN             5600SALES                               9400RESEARCH       CLERK     1980        800RESEARCH       CLERK     1987       1100RESEARCH       CLERK                1900RESEARCH       ANALYST   1981       3000RESEARCH       ANALYST   1987       3000RESEARCH       ANALYST              6000RESEARCH       MANAGER   1981       2975RESEARCH       MANAGER              2975RESEARCH                           10875ACCOUNTING     CLERK     1982       1300ACCOUNTING     CLERK                1300ACCOUNTING     MANAGER   1981       2450ACCOUNTING     MANAGER              2450ACCOUNTING     PRESIDENT 1981       5000ACCOUNTING     PRESIDENT            5000ACCOUNTING                          875023 rows selected.

從上面的結果中可以看出，部分 rollup 產生的結果:

• 普通的合計列是由 group by 產生而不是 rollup
• 不會產生總計

2. cube

cube 可以為指定的列建立各種不同組合的小計. 如果指定的列的數量為 n, group by cube 將建立 2 * n 個層次的小計. cube 是一種比 rollup 更細粒度的分組統計語句。先看看 cube 語句的結果：

2.1 什麼時候使用 cube

• 當需求中有類似 cross-tabular report (交叉報表)時
• 對於資料倉儲中的統計匯總表, rollup 能夠簡化統計匯總表並且提高查詢統計匯總表的速度

2.2 cube 例子

SQL> select b.dname, a.job, sum(a.sal)  2  from emp a, dept b  3  where a.deptno = b.deptno  4  group by cube(b.dname, a.job);DNAME          JOB       SUM(A.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       500018 rows selected.

從上面的結果可以看出，group by cube(b.dname, a.job) 語句首先統計所有部門的總薪水，然後統計每個職位(a.job)的總薪水，然後統計每個部門(b.dname)的總薪水,最後統計每個部門每個職位(b.dname, a.job)的總薪水。

• 如果語句為 group by cube(a, b)，oracle 首先對欄位 a 和 b 進行 group by，然後對欄位 a 進行 group by，然後對欄位 b 進行 group by，最後對全表進行 group by。
• 如果語句為 group by cube(a, b, c)，oracle 進行分組的欄位分別為 (a, b, c)，(a, b)，(a, c)，(b, c)，(a)，(b)，(c)，最後對全表的總計

下面示範 cube 三個欄位的例子：

SQL> select b.dname, a.job, to_char(hiredate, 'yyyy'), sum(sal)  2  from emp a, dept b  3  where a.deptno = b.deptno  4  group by cube(b.dname, a.job, to_char(hiredate, 'yyyy'));DNAME          JOB       TO_C   SUM(SAL)-------------- --------- ---- ----------                                   29025                         1980        800                         1981      22825                         1982       1300                         1987       4100               CLERK                4150               CLERK     1980        800               CLERK     1981        950               CLERK     1982       1300               CLERK     1987       1100               ANALYST              6000               ANALYST   1981       3000               ANALYST   1987       3000               MANAGER              8275               MANAGER   1981       8275               SALESMAN             5600               SALESMAN  1981       5600               PRESIDENT            5000               PRESIDENT 1981       5000SALES                               9400SALES                    1981       9400SALES          CLERK                 950SALES          CLERK     1981        950SALES          MANAGER              2850SALES          MANAGER   1981       2850SALES          SALESMAN             5600SALES          SALESMAN  1981       5600RESEARCH                           10875RESEARCH                 1980        800RESEARCH                 1981       5975RESEARCH                 1987       4100RESEARCH       CLERK                1900RESEARCH       CLERK     1980        800RESEARCH       CLERK     1987       1100RESEARCH       ANALYST              6000RESEARCH       ANALYST   1981       3000RESEARCH       ANALYST   1987       3000RESEARCH       MANAGER              2975RESEARCH       MANAGER   1981       2975ACCOUNTING                          8750ACCOUNTING               1981       7450ACCOUNTING               1982       1300ACCOUNTING     CLERK                1300ACCOUNTING     CLERK     1982       1300ACCOUNTING     MANAGER              2450ACCOUNTING     MANAGER   1981       2450ACCOUNTING     PRESIDENT            5000ACCOUNTING     PRESIDENT 1981       500048 rows selected.

2.3 部分 cube (partial cube)
部分 cube 類似於部分 rollup, 把列放在 cube 操作符的外面能夠限制產生列組合的小計. 例如, group by a, cube(b, c), 這條語句將產生 4 (2 * 2) 個層次的小計, 分別為層次 (a, b, c), 層次 (a, b), 層次 (a, c), 層次 (a). 

SQL> select b.dname, a.job, to_char(hiredate, 'yyyy'), sum(sal)  2  from emp a, dept b  3  where a.deptno = b.deptno  4  group by b.dname, cube(a.job, to_char(hiredate, 'yyyy'));DNAME          JOB       TO_C   SUM(SAL)-------------- --------- ---- ----------SALES                               9400SALES                    1981       9400SALES          CLERK                 950SALES          CLERK     1981        950SALES          MANAGER              2850SALES          MANAGER   1981       2850SALES          SALESMAN             5600SALES          SALESMAN  1981       5600RESEARCH                           10875RESEARCH                 1980        800RESEARCH                 1981       5975RESEARCH                 1987       4100RESEARCH       CLERK                1900RESEARCH       CLERK     1980        800RESEARCH       CLERK     1987       1100RESEARCH       ANALYST              6000RESEARCH       ANALYST   1981       3000RESEARCH       ANALYST   1987       3000RESEARCH       MANAGER              2975RESEARCH       MANAGER   1981       2975ACCOUNTING                          8750ACCOUNTING               1981       7450ACCOUNTING               1982       1300ACCOUNTING     CLERK                1300ACCOUNTING     CLERK     1982       1300ACCOUNTING     MANAGER              2450ACCOUNTING     MANAGER   1981       2450ACCOUNTING     PRESIDENT            5000ACCOUNTING     PRESIDENT 1981       500029 rows selected.