Oracle 進階 SQL：ROLLUP 與 CUBE 多維度彙總分析完整指南

ROLLUP 與 CUBE 核心差異

基礎資料準備

GROUP BY ROLLUP (col1, col2, ..., coln)

執行邏輯 (由右至左)

1. GROUP BY col1, col2, ..., coln (最細粒度)
2. GROUP BY col1, col2, ..., col(n-1)
3. ...
4. GROUP BY col1
5. GROUP BY () — 總計 (Grand Total)

範例 1：部門 x 職位 薪資彙總

SELECT d.dname, e.job, TO_CHAR(e.hiredate, 'YYYY') hire_year, SUM(e.sal) total_sal
FROM emp e JOIN dept d ON e.deptno = d.deptno
GROUP BY ROLLUP (d.dname, e.job, TO_CHAR(e.hiredate, 'YYYY'))
ORDER BY d.dname, e.job, hire_year;

彙總層級 (3+1=4 層)：

1. 部門 x 職位 x 年份 (最細)
2. 部門 x 職位 (跨年份小計)
3. 部門 (跨職位小計)
4. 總計

部分 ROLLUP

僅對部分欄位做階層彙總，其餘維度保持分組。

GROUP BY CUBE (col1, col2, ..., coln)

執行邏輯 (2^n 組合)

對 n 個欄位的所有子集合執行 GROUP BY：

• n=2: (col1,col2), (col1), (col2), ()
• n=3: (col1,col2,col3), (col1,col2), (col1,col3), (col2,col3), (col1), (col2), (col3), ()

範例：部門 x 職位 CUBE

GROUP BY CUBE (d.dname, e.job, TO_CHAR(e.hiredate, 'YYYY'))

產生：(d,j,y), (d,j), (d,y), (j,y), (d), (j), (y), () — 8 組彙總。

部分 CUBE

SELECT 
  d.dname, 
  e.job,
  GROUPING(d.dname) AS is_dname_total,
  GROUPING(e.job) AS is_job_total,
  GROUPING_ID(d.dname, e.job) AS grouping_id,
  SUM(e.sal) total_sal
FROM emp e JOIN dept d ON e.deptno = d.deptno
GROUP BY CUBE (d.dname, e.job);

美化輸出範例

SELECT * FROM (
  SELECT deptno, job, sal FROM emp
)
PIVOT (
  SUM(sal) FOR job IN (
    'CLERK' AS clerk_sal,
    'MANAGER' AS mgr_sal,
    'ANALYST' AS analyst_sal,
    'SALESMAN' AS sales_sal,
    'PRESIDENT' AS pres_sal
  )
)
ORDER BY deptno;

多重彙總函數 PIVOT

SELECT * FROM (
  SELECT deptno, job, sal FROM emp
)
PIVOT XML (
  SUM(sal) FOR job IN (ANY)
)
ORDER BY deptno;

UNPIVOT：列轉行 (逆向操作)

建議說明
優先使用 ROLLUP/CUBE單次掃描完成多層彙總，避免多次 UNION ALL 掃描
配合 GROUPING_ID應用層易於識別彙總層級，生成階層式報表 UI
大表考慮物化視圖預計算 ROLLUP/CUBE 結果，查詢時直接讀取
PIVOT 替代 CASE 聚合語法更簡潔，優化器內部同樣單次掃描
注意 NULL 語意彙總列維度為 NULL，GROUPING 函數必須區分


總結
ROLLUP 適合階層式報表 (年→季→月)，CUBE 適合多維度交叉分析 (OLAP 切片塊)。兩者皆為 GROUP BY 擴展，單次掃描產生多層彙總，配合 GROUPING/GROUPING_ID 識別彙總列，再搭配 PIVOT/UNPIVOT 完成行列轉換，構成 Oracle 進階分析 SQL 的核心工具箱。