SQL Server 2008 GROUPING SETS：多維彙總的靈活新語法

背景：CUBE/ROLLUP 的侷限

SQL Server 2005 的 GROUP BY WITH CUBE/ROLLUP 雖支援多維彙總，但產生的組合固定（全維度交叉或層級向上彙總），無法自訂「只要這幾組維度組合」。

GROUPING SETS 核心語法

SELECT ProductKey, OrderDateKey, CustomerKey, PromotionKey,
       SUM(UnitPrice) AS SumUnitPrice,
       SUM(OrderQuantity) AS SumOrderQuantity
FROM dbo.FactInternetSales
GROUP BY GROUPING SETS (
    (ProductKey, OrderDateKey, CustomerKey, PromotionKey), -- 明細層
    (ProductKey, OrderDateKey, CustomerKey),               -- 少一維
    ()                                                     -- 總計
);

執行結果語義

• 第一組：四維明細彙總
• 第二組：三維彙總（PromotionKey 為 NULL）
• 第三組：全表總計（所有維度鍵為 NULL）

未出現在某 GROUPING SET 中的欄位，結果集自動填充 NULL。

等價改寫對比

傳統寫法需 UNION ALL 多次掃描：

SELECT ..., 'Detail' AS Level FROM ... GROUP BY ProductKey, OrderDateKey, CustomerKey, PromotionKey
UNION ALL
SELECT ..., 'SubTotal' FROM ... GROUP BY ProductKey, OrderDateKey, CustomerKey
UNION ALL
SELECT ..., 'GrandTotal' FROM ... GROUP BY ();

GROUPING SETS 單次掃描完成所有彙總，I/O 與 CPU 成本大幅降低。

進階組合：GROUPING SETS + ROLLUP/CUBE

GROUP BY GROUPING SETS (
    ROLLUP (ProductKey, OrderDateKey),  -- 階層向上
    CUBE (CustomerKey, PromotionKey),   -- 全交叉
    ()                                  -- 總計
);

輔助函數：GROUPING() 與 GROUPING_ID()

• GROUPING(col)：該欄位是否為彙總產生的 NULL（1=是，0=否）
• GROUPING_ID(col1, col2...)：多欄位組合位圖，便於區分彙總層級

SELECT CASE WHEN GROUPING(ProductKey)=1 THEN 'All Products' ELSE ProductKey END AS Product,
       SUM(UnitPrice)
FROM ...
GROUP BY GROUPING SETS ((ProductKey), ())
ORDER BY GROUPING_ID(ProductKey);

適用場景

• OLAP 報表：同一查詢產出明細、小計、總計多層次結果
• 儀表板資料源：一次查詢滿足多個視覺化元件
• ETL 階層彙總：替代多階段暫存表

總結

GROUPING SETS 將「彙總組合自定義」與「單次掃描高效執行」完美結合，是 SQL Server 2008+ 進行多維分析的首選語法。配合 GROUPING_ID 可輕鬆構建階層報表導航。