Oracle GROUP BY 進階：ROLLUP 與 CUBE 多維彙總實戰詳解

Oracle GROUP BY 進階：ROLLUP 與 CUBE 多維彙總實戰詳解

GROUP BY 單純分組已無法滿足多層次小計需求。Oracle 提供 ROLLUP（層次彙總）與 CUBE（全維度交叉彙總）擴展，單一查詢即可產生報表所需的各層級小計與總計。本文以實測資料對比兩者差異與適用場景。

一、核心差異速覽

二、建立測試資料

CREATE TABLE department (department CHAR(10), employee CHAR(6), salary INT);

BEGIN
  INSERT INTO department VALUES ('A','ZHANG',100);
  INSERT INTO department VALUES ('A','LI',200);
  INSERT INTO department VALUES ('A','WANG',300);
  INSERT INTO department VALUES ('A','ZHAO',400);
  INSERT INTO department VALUES ('A','DUAN',500);
  INSERT INTO department VALUES ('B','DUAN',600);
  INSERT INTO department VALUES ('B','DUAN',700);
  COMMIT;
END;

資料分布：部門 A 5 人，部門 B 2 人（皆為 DUAN）。

三、ROLLUP 實測

SELECT department, employee, SUM(salary)
FROM department
GROUP BY ROLLUP(department, employee);

結果：

• 明細行：A-ZHANG 100、A-LI 200、A-WANG 300、A-ZHAO 400、A-DUAN 500、B-DUAN 600、B-DUAN 700
• 部門 A 小計：1500
• 部門 B 小計：1300
• 總計：2800

ROLLUP 依 department → employee 層次產生小計，不會產生「跨部門的員工小計」（如所有 DUAN 的總和）。

四、CUBE 實測

SELECT department, employee, SUM(salary)
FROM department
GROUP BY CUBE(department, employee);

結果： 在 ROLLUP 基礎上新增 5 行：

• 員工 ZHANG 總計、LI 總計、WANG 總計、ZHAO 總計、DUAN 總計（跨部門彙總）

CUBE 相當於 ROLLUP(department, employee) UNION ALL ROLLUP(employee, department) 去重。

五、GROUPING 函數輔助識別彙總行

SELECT
  CASE WHEN GROUPING(department)=1 THEN 'ALL_DEPT' ELSE department END AS dept,
  CASE WHEN GROUPING(employee)=1 THEN 'ALL_EMP' ELSE employee END AS emp,
  SUM(salary) AS total_sal
FROM department
GROUP BY CUBE(department, employee);

GROUPING(col) 回傳 1 表示該行為該欄位的彙總行，0 為明細行。配合 GROUPING_ID 可生成位元組合編碼，便於前端渲染層級。

六、效能與選擇建議

• 報表固定層次（年-月-日、國-省-市）→ ROLLUP，結果集小、計劃優。
• 探索性多維分析、需任意維度交叉 → CUBE，但注意結果集指數級增長，大維度慎用。
• 兩者皆可搭配 HAVING 過濾彙總行，或用 GROUPING SETS 自訂組合（12c+ 推薦）。