您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle ROLLUP 進階分組統計：小計、總計、多維度彙總一條 SQL 搞定

編輯：Oracle 資料庫基礎

ROLLUP 是 GROUP BY 的擴充功能，能在單一查詢中同時產生明細、各層級小計、總計，極大簡化報表 SQL。本文以 EMP 表為例，完整展示 ROLLUP、CUBE、GROUPING SETS、GROUPING/GROUPING_ID 識別函數的實戰用法。

一、基礎範例：按 JOB、DEPTNO 彙總薪資

SELECT job, deptno, SUM(sal) AS total_sal
FROM   emp
GROUP BY ROLLUP (job, deptno);

結果集包含：

明細：每個 (JOB, DEPTNO) 組合的 SUM(SAL)
第一層小計：每個 JOB 下所有 DEPTNO 的 SUM(SAL) → DEPTNO = NULL
總計：所有 JOB、DEPTNO 的 SUM(SAL) → JOB = NULL, DEPTNO = NULL

二、ROLLUP vs CUBE vs GROUPING SETS

子句	彙總層級	適用場景
`ROLLUP (a,b,c)`	(a,b,c) → (a,b) → (a) → ()	層級式鑽取（年→季→月→日、國→省→市）
`CUBE (a,b,c)`	所有 2^n 組合	多維度交叉分析（全排列小計）
`GROUPING SETS ((a,b),(a),())`	自訂組合集合	只需特定小計，效能最優

CUBE 範例

SELECT job, deptno, SUM(sal)
FROM   emp
GROUP BY CUBE (job, deptno);
-- 產生：(job,deptno)、(job)、(deptno)、() 四組彙總

GROUPING SETS 範例

SELECT job, deptno, SUM(sal)
FROM   emp
GROUP BY GROUPING SETS ((job, deptno), (job), ());
-- 僅產生明細、job 小計、總計；不產生 deptno 小計

三、識別小計列：GROUPING / GROUPING_ID

結果集中 NULL 可能代表「真實資料為 NULL」或「彙總層級」。用識別函數區分：

SELECT job, deptno, SUM(sal) AS total_sal,
       GROUPING(job)    AS is_job_subtotal,
       GROUPING(deptno) AS is_dept_subtotal,
       GROUPING_ID(job, deptno) AS grouping_id
FROM   emp
GROUP BY ROLLUP (job, deptno);

GROUPING(col)：該列為彙總產生的 NULL 回傳 1，否則 0。
GROUPING_ID(col1, col2...)：將各 GROUPING 位元組合為整數（如 0=明細、1=deptno 小計、2=job 小計、3=總計）。

美化輸出範例

SELECT DECODE(GROUPING(job), 1, '【所有職稱】', job) AS job,
       DECODE(GROUPING(deptno), 1, '【所有部門】', TO_CHAR(deptno)) AS deptno,
       SUM(sal) AS total_sal
FROM   emp
GROUP BY ROLLUP (job, deptno)
ORDER BY GROUPING_ID(job, deptno), job, deptno;

四、進階實戰：多維度銷售報表

SELECT region, product_category, month,
       SUM(amount) AS total_amt,
       GROUPING_ID(region, product_category, month) AS gid
FROM   sales_fact
GROUP BY ROLLUP (region, product_category, month)
HAVING GROUPING_ID(region, product_category, month) IN (0, 1, 3, 7);
-- 0=明細, 1=月小計, 3=品類+月小計, 7=總計
-- 排除 region 小計 (gid=4) 和品類小計 (gid=6)

五、效能與優化提醒

ROLLUP/CUBE 會增加排序/雜湊彙總開銷，大資料量建議配合 PARALLEL。
GROUPING SETS 比 CUBE 效能好，只計算需要的組合。
識別函數 GROUPING/GROUPING_ID 可用於 HAVING、ORDER BY、應用層格式化。
12c+ APPROX_* 近似彙總函數（APPROX_COUNT_DISTINCT 等）可配合 GROUPING SETS 快速估算。

六、MySQL / PostgreSQL 對應

-- MySQL 8.0+ / PostgreSQL 9.5+ 支援標準 GROUPING SETS, ROLLUP, CUBE
SELECT job, deptno, SUM(sal)
FROM   emp
GROUP BY ROLLUP (job, deptno);
-- GROUPING() 函數同樣支援

善用 ROLLUP、CUBE、GROUPING SETS 將多層級彙總下推至資料庫層，不僅 SQL 簡潔、網路傳輸減少，更能發揮資料庫平行彙總優勢，是報表開發的必備利器。