MySQL WITH ROLLUP 進階聚合：多維度小計與總計一條 SQL 搞定

MySQL WITH ROLLUP 進階聚合：多維度小計與總計一條 SQL 搞定

WITH ROLLUP 是 MySQL 擴展的 GROUP BY 修飾符，在分組統計基礎上自動生成上層匯總（小計、總計），類似 Oracle 的 GROUPING SETS/ROLLUP，極大簡化報表查詢。

基本語法

SELECT grouping_columns, aggregate_functions
FROM table
GROUP BY grouping_columns WITH ROLLUP;

單維度演示：司機行駛里程統計

-- 數據準備
CREATE TABLE driver_log (name VARCHAR(20), miles INT);
INSERT INTO driver_log VALUES 
('Ben', 100), ('Ben', 120), ('Ben', 142),
('Henry', 200), ('Henry', 300), ('Henry', 411),
('Suzi', 300), ('Suzi', 293), ('Suzi', 300);

-- 1. 各司機總里程 + 總計
SELECT name, SUM(miles) AS total_miles
FROM driver_log
GROUP BY name WITH ROLLUP;
-- 結果：
-- Ben     362
-- Henry   911
-- Suzi    893
-- NULL    2166   ← 總計行 (name 為 NULL 表示匯總)

-- 2. 各司機平均里程 + 總體平均
SELECT name, AVG(miles) AS avg_miles
FROM driver_log
GROUP BY name WITH ROLLUP;
-- 結果：
-- Ben     120.6667
-- Henry   182.2000
-- Suzi    446.5000
-- NULL    216.6000   ← 總體平均

-- 3. 同時輸出 SUM 和 AVG
SELECT name, SUM(miles) AS total_miles, AVG(miles) AS avg_miles
FROM driver_log
GROUP BY name WITH ROLLUP;

多維度演示：郵件發送統計

-- 數據：srcuser(發件人), dstuser(收件人)
CREATE TABLE mail (srcuser VARCHAR(20), dstuser VARCHAR(20));
INSERT INTO mail VALUES
('barb','barb'), ('barb','tricia'), ('barb','tricia'),
('gene','barb'), ('gene','barb'), ('gene','gene'), ('gene','gene'), ('gene','gene'), ('gene','tricia'),
('phil','barb'), ('phil','phil'), ('phil','phil'), ('phil','tricia'), ('phil','tricia'),
('tricia','gene'), ('tricia','phil');

-- 不用 ROLLUP
SELECT srcuser, dstuser, COUNT(*) AS cnt
FROM mail
GROUP BY srcuser, dstuser;
-- 10 行明細

-- 用 ROLLUP
SELECT srcuser, dstuser, COUNT(*) AS cnt
FROM mail
GROUP BY srcuser, dstuser WITH ROLLUP;
-- 15 行：明細 + 每發件人小計 + 總計
-- barb    barb       1
-- barb    tricia     2
-- barb    NULL       3   ← barb 小計
-- gene    barb       2
-- gene    gene       3
-- gene    tricia     1
-- gene    NULL       6   ← gene 小計
-- phil    barb       1
-- phil    phil       2
-- phil    tricia     2
-- phil    NULL       5   ← phil 小計
-- tricia  gene       1
-- tricia  phil       1
-- tricia  NULL       2   ← tricia 小計
-- NULL    NULL       16  ← 總計

進階技巧

1. 識別匯總行：GROUPING() 函數

SELECT 
    srcuser, 
    dstuser, 
    COUNT(*) AS cnt,
    GROUPING(srcuser) AS is_src_total,
    GROUPING(dstuser) AS is_dst_total
FROM mail
GROUP BY srcuser, dstuser WITH ROLLUP;
-- is_src_total=1 表示該行是 srcuser 維度的匯總 (總計行)
-- is_dst_total=1 表示該行是 dstuser 維度的匯總 (小計行)

2. 替換 NULL 為可讀標籤

SELECT 
    COALESCE(srcuser, '【總計】') AS srcuser,
    COALESCE(dstuser, CONCAT('【', srcuser, '小計】')) AS dstuser,
    COUNT(*) AS cnt
FROM mail
GROUP BY srcuser, dstuser WITH ROLLUP;

3. HAVING 過濾匯總行

-- 僅保留小計和總計
SELECT srcuser, dstuser, COUNT(*) AS cnt
FROM mail
GROUP BY srcuser, dstuser WITH ROLLUP
HAVING dstuser IS NULL;  -- dstuser 為 NULL 即小計/總計行

效能與限制

• 效能： 比應用層多次查詢 + UNION ALL 效能高，單次掃描完成所有聚合
• 限制： 不支援 ORDER BY（結果順序固定：分組順序 + 小計 + 總計），需外層包裹排序
• 兼容性： MySQL 專有，遷移 PostgreSQL/Oracle 需改寫為 GROUP BY ROLLUP(...)
• 索引： 同普通 GROUP BY，分組列有索引可避免 filesort

實戰報表模板

-- 銷售報表：按區域、產品、月份三維匯總
SELECT 
    COALESCE(region, '全區') AS region,
    COALESCE(product, CONCAT(region, '小計')) AS product,
    COALESCE(DATE_FORMAT(sale_date, '%Y-%m'), '月度小計') AS month,
    SUM(amount) AS total_amount,
    COUNT(*) AS order_count,
    GROUPING(region) AS g_region,
    GROUPING(product) AS g_product,
    GROUPING(sale_date) AS g_month
FROM sales
WHERE sale_date >= '2024-01-01'
GROUP BY region, product, DATE_FORMAT(sale_date, '%Y-%m') WITH ROLLUP
ORDER BY g_region, g_product, g_month, region, product, month;