SQL Server ROLLUP 與 CUBE 進階分組統計：從小計到總計的完整實戰

為什麼需要 ROLLUP / CUBE？

傳統 GROUP BY 僅能產生單一層級彙總。報表常需「分類小計 → 大類小計 → 總計」多層次統計。ROLLUP 與 CUBE 讓單一查詢即可產生階層式彙總，避免多次查詢或應用層運算。

核心差異：ROLLUP vs CUBE

範例資料準備

DECLARE @Sales TABLE (
    Region NVARCHAR(10),   -- 大區
    Shop   NVARCHAR(10),   -- 店點
    Product NVARCHAR(10),  -- 產品
    Qty    INT             -- 銷量
);

INSERT @Sales VALUES
('北區','台北','筆電',10), ('北區','台北','滑鼠',20),
('北區','新竹','筆電',15), ('南區','高雄','筆電',30),
('南區','台南','鍵盤',25);

1. 基礎 ROLLUP 用法

SELECT Region, Shop, Product, SUM(Qty) AS TotalQty
FROM @Sales
GROUP BY Region, Shop, Product WITH ROLLUP;

結果解讀：

• 一般列：三欄皆有值 → 明細列
• Product = NULL, Shop 有值 → 該店點小計
• Shop = NULL, Region 有值 → 該大區小計
• Region = NULL → 總計

2. GROUPING() 識別彙總列

GROUPING(col) 回傳 1 表示該列為彙總產生的 NULL，0 為實際資料 NULL。

SELECT 
    CASE WHEN GROUPING(Region)=1 THEN '總計' ELSE Region END AS Region,
    CASE WHEN GROUPING(Shop)=1 THEN '小計' ELSE Shop END AS Shop,
    CASE WHEN GROUPING(Product)=1 THEN '明計' ELSE Product END AS Product,
    SUM(Qty) AS TotalQty,
    GROUPING(Region) AS IsRegionTotal,
    GROUPING(Shop) AS IsShopSubtotal
FROM @Sales
GROUP BY Region, Shop, Product WITH ROLLUP;

3. 進階：自訂彙總標籤與排序

SELECT 
    Region = CASE 
        WHEN GROUPING(Region)=1 THEN '【總計】'
        WHEN GROUPING(Shop)=1 THEN Region + ' 小計'
        ELSE Region END,
    Shop = CASE 
        WHEN GROUPING(Shop)=1 AND GROUPING(Region)=0 THEN '小計'
        WHEN GROUPING(Region)=1 THEN ''
        ELSE Shop END,
    Product = CASE 
        WHEN GROUPING(Product)=1 THEN '小計'
        ELSE Product END,
    TotalQty = SUM(Qty)
FROM @Sales
GROUP BY Region, Shop, Product WITH ROLLUP
ORDER BY 
    GROUPING(Region), 
    CASE WHEN GROUPING(Region)=0 THEN Region END,
    GROUPING(Shop),
    CASE WHEN GROUPING(Shop)=0 THEN Shop END,
    GROUPING(Product),
    CASE WHEN GROUPING(Product)=0 THEN Product END;

關鍵排序邏輯：先按 GROUPING 排 (0 明細在前，1 彙總在後)，組內再按實際值排序，確保「明細 → 小計 → 總計」視覺順序正確。

4. CUBE 完整交叉彙總

SELECT Region, Shop, Product, SUM(Qty) AS TotalQty
FROM @Sales
GROUP BY Region, Shop, Product WITH CUBE;

會額外產生：(Region, Product)、(Shop, Product)、(Product) 等非階層組合。適合「不固定鑽取路徑」的分析報表。

5. GROUPING_ID() 簡化多層判斷 (SQL 2008+)

SELECT 
    Region, Shop, Product, SUM(Qty) AS TotalQty,
    GROUPING_ID(Region, Shop, Product) AS GrpLevel
    -- 0=明細, 1=Product總計, 2=Shop小計, 3=Shop+Product, 4=Region小計, 7=總計
FROM @Sales
GROUP BY Region, Shop, Product WITH ROLLUP;

效能與最佳實踐

• 索引支援：建立覆蓋索引 CREATE INDEX IX_Rollup ON Sales(Region, Shop, Product) INCLUDE (Qty) 可大幅加速。
• 資料量大時：考慮先彙總到暫存表/資料表值函數，再套用 ROLLUP，避免單一巨大排序運算。
• NULL 值語意衝突：若業務資料本身含 NULL，務必用 GROUPING() 或 GROUPING_ID() 區分，勿直接 ISNULL(col, '總計')。
• 相容性：WITH ROLLUP/CUBE 為 ISO 標準，SQL Server、Oracle、PostgreSQL、MySQL 8.0+ 均支援。

總結

ROLLUP 以 O(n) 列數產生階層報表；CUBE 以 O(2^n) 列數支援全維度分析。善用 GROUPING()、GROUPING_ID() 與 ORDER BY GROUPING... 可輸出專業級報表，直接對接 Excel/報表工具無需二次處理。