SQL Server 數據分類匯總完全指南：ROLLUP、CUBE 與 GROUPING SETS 進階應用

業務場景

-- 源數據
Code  Warehouse  Qty
01    A          6
01    B          7
02    A          8
02    B          9

-- 期望輸出
Code  Warehouse  Qty
01    A          6
01    B          7
01    NULL       13   -- 小計
02    A          8
02    B          9
02    NULL       17   -- 小計
NULL  NULL       30   -- 總計

核心語法：GROUP BY 擴展

ROLLUP：層次化聚合

SELECT Code, Warehouse, SUM(Qty) AS TotalQty
FROM TableA
GROUP BY ROLLUP (Code, Warehouse)
-- 產生: (Code,Warehouse), (Code), () 三級聚合

適用於：有明確層次 的維度 (年→季→月、地區→省→市、產品線→品類→SKU)

CUBE：全維度交叉聚合

SELECT Code, Warehouse, SUM(Qty) AS TotalQty
FROM TableA
GROUP BY CUBE (Code, Warehouse)
-- 產生: (Code,Warehouse), (Code), (Warehouse), () 四組合

適用於：無層次、需全排列分析 的維度 (品牌×顏色×尺碼、渠道×活動×地區)

GROUPING SETS：自定義聚合組合

SELECT Code, Warehouse, SUM(Qty) AS TotalQty
FROM TableA
GROUP BY GROUPING SETS ((Code, Warehouse), (Code), ())
-- 等價於 ROLLUP(Code, Warehouse)

適用於：精確控制 需要哪些聚合層級，避免 CUBE/ROLLUP 產生冗餘行。

輔助函數：識別聚合行

SELECT
  Code,
  Warehouse,
  SUM(Qty) AS TotalQty,
  GROUPING(Code)     AS IsCodeTotal,      -- 1=該列為聚合行
  GROUPING(Warehouse) AS IsWhTotal,
  GROUPING_ID(Code, Warehouse) AS GroupLevel  -- 位圖: 00=明細, 01=Warehouse小計, 10=Code小計, 11=總計
FROM TableA
GROUP BY ROLLUP (Code, Warehouse)

實戰模板：生成帶層級標識的報表

WITH Aggregated AS (
  SELECT
    Code,
    Warehouse,
    SUM(Qty) AS TotalQty,
    GROUPING_ID(Code, Warehouse) AS GrpLevel
  FROM TableA
  GROUP BY ROLLUP (Code, Warehouse)
)
SELECT
  CASE GrpLevel
    WHEN 0 THEN Code
    WHEN 1 THEN Code
    WHEN 2 THEN Code
    WHEN 3 THEN '總計'
  END AS Code,
  CASE GrpLevel
    WHEN 0 THEN Warehouse
    WHEN 1 THEN '小計'
    WHEN 2 THEN NULL
    WHEN 3 THEN NULL
  END AS Warehouse,
  TotalQty
FROM Aggregated
ORDER BY Code, GrpLevel

性能與優化提示

• 聚合前盡早過濾 (WHERE 而非 HAVING)
• 聚合列建立索引支援排序/哈希聚合
• 大數據集考慮分區表 + 並行聚合
• 避免在 OLTP 核心表上實時跑 CUBE/ROLLUP，改用物化視圖/預聚合表