SQL Server WITH ROLLUP / CUBE 多維彙總完全解析：從 GROUP BY 到報表呈現

業務場景：層級小計與總計

銷售資料需按「區域 → 店鋪 → 業務員」三層級顯示小計與總計。傳統 GROUP BY 僅能單一層級聚合，需多次查詢 UNION ALL，效能差且難維護。

ROLLUP：階層式彙總

SELECT Region, Store, SalesPerson, SUM(Amount) AS TotalAmount
FROM Sales
GROUP BY Region, Store, SalesPerson
WITH ROLLUP

結果集結構

Region  Store  SalesPerson  TotalAmount
North   A01    John         10000
North   A01    Mary         15000
North   A01    NULL         25000   -- Store A01 小計
North   A02    Tom          8000
North   A02    NULL         8000    -- Store A02 小計
North   NULL   NULL         33000   -- Region North 小計
South   B01    Lisa         12000
South   NULL   NULL         12000   -- Region South 小計
NULL    NULL   NULL         45000   -- 總計

NULL 代表該層級彙總。層級順序依 GROUP BY 欄位順序：最右欄最先彙總，最左欄最後彙總。

CUBE：全維度交叉彙總

SELECT Region, Store, SalesPerson, SUM(Amount) AS TotalAmount
FROM Sales
GROUP BY Region, Store, SalesPerson
WITH CUBE

產生所有欄位組合的彙總：
Region+Store+SalesPerson、Region+Store、Region+SalesPerson、Store+SalesPerson、Region、Store、SalesPerson、總計。
結果筆數 = (n+1) × (m+1) × (k+1) - 1，維度多時爆炸性增長。

GROUPING() / GROUPING_ID()：識別彙總列

SELECT 
    CASE WHEN GROUPING(Region) = 1 THEN '所有區域' ELSE ISNULL(Region, '未知') END AS Region,
    CASE WHEN GROUPING(Store) = 1 THEN '所有店鋪' ELSE ISNULL(Store, '未知') END AS Store,
    CASE WHEN GROUPING(SalesPerson) = 1 THEN '所有業務' ELSE ISNULL(SalesPerson, '未知') END AS SalesPerson,
    SUM(Amount) AS TotalAmount,
    GROUPING_ID(Region, Store, SalesPerson) AS GrpLevel
FROM Sales
GROUP BY Region, Store, SalesPerson
WITH ROLLUP

• GROUPING(col)：1 = 彙總列，0 = 明細列
• GROUPING_ID(col1, col2...)：位元組合，唯一識別彙總層級（0=明細，1=最右彙總，3=倒數二層彙總...）

報表呈現優化：應用層處理 NULL

資料庫只回傳標準結果集，應用層依 GROUPING_ID 渲染：

-- 偽代碼邏輯
IF GrpLevel = 7 (111b) THEN  -- 總計
    Row.Style = 'GrandTotal'; Region = '總計'; Store = ''; SalesPerson = ''
ELSE IF GrpLevel & 1 = 1 THEN  -- SalesPerson 小計
    Row.Style = 'SubTotal3'; SalesPerson = '小計'
ELSE IF GrpLevel & 3 = 3 THEN  -- Store 小計
    Row.Style = 'SubTotal2'; Store = '小計'; SalesPerson = ''
ELSE IF GrpLevel & 7 = 7 THEN  -- Region 小計
    Row.Style = 'SubTotal1'; Region = '小計'; Store = ''; SalesPerson = ''
ELSE
    Row.Style = 'Detail'

GROUPING SETS：自訂彙總組合（SQL 2008+）

-- 僅需 Region 總計 + Store 總計 + 明細，不需 Region+Store 交叉
SELECT Region, Store, SalesPerson, SUM(Amount)
FROM Sales
GROUP BY GROUPING SETS (
    (Region, Store, SalesPerson),  -- 明細
    (Region, Store),               -- Store 小計
    (Region),                      -- Region 總計
    ()                             -- 總計
)

比 CUBE 精簡、比 ROLLUP 彈性。現代首選。

效能考量

• ROLLUP/CUBE/GROUPING SETS 單掃描完成多層彙總，優於多查詢 UNION
• 大資料量需確保 GROUP BY 欄位有索引支援
• 結果集輸出量可能遠大於明細列 → 應用層分頁或串流處理
• WITH ROLLUP 不支援 ORDER BY → 外層包裝排序

完整範例：銷售儀表板查詢

SELECT 
    ISNULL(Region, '總計') AS Region,
    ISNULL(Store, CASE WHEN GROUPING(Region)=1 THEN '' ELSE '小計' END) AS Store,
    ISNULL(SalesPerson, CASE WHEN GROUPING(Store)=1 THEN '小計' ELSE '' END) AS SalesPerson,
    SUM(Amount) AS TotalAmount,
    COUNT(*) AS OrderCount,
    GROUPING_ID(Region, Store, SalesPerson) AS Level
FROM Sales
WHERE OrderDate BETWEEN @StartDate AND @EndDate
GROUP BY GROUPING SETS (
    (Region, Store, SalesPerson),
    (Region, Store),
    (Region),
    ()
)
ORDER BY 
    CASE WHEN GROUPING(Region)=1 THEN 1 ELSE 0 END,
    Region,
    CASE WHEN GROUPING(Store)=1 THEN 1 ELSE 0 END,
    Store,
    CASE WHEN GROUPING(SalesPerson)=1 THEN 1 ELSE 0 END,
    SalesPerson