萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

SQL Server GROUP BY 進階完全指南:CUBE、ROLLUP、GROUPING SETS、GROUPING_ID 實戰

前言:從簡單分組到多維彙總

GROUP BY 不僅是「分組聚合」,配合 CUBE、ROLLUP、GROUPING SETS、GROUPING_ID,能在單查詢中產出**多維度彙總報表**,避免多次掃描表或應用層二次計算。本文以實戰範例系統講解。

一、 測試資料準備

CREATE TABLE SalesInfo (
    City NVARCHAR(50),
    OrderDate DATE,
    OrderID INT
);

INSERT INTO SalesInfo VALUES
(N'北京','2014-06-09',1001),(N'北京','2014-08-09',1002),(N'北京','2013-10-09',1009),
(N'大連','2013-08-09',4001),(N'大連','2013-10-09',4002),(N'大連','2013-05-12',4003),
(N'大連','2014-11-11',4004),(N'大連','2014-12-11',4005);

二、 基礎 GROUP BY

SELECT City, COUNT(OrderID) AS OrderCount
FROM SalesInfo
GROUP BY City;
/* 結果:
City    OrderCount
北京        3
大連        5 */

三、 WITH CUBE:全維度交叉彙總

SELECT City, YEAR(OrderDate) AS OrderYear, COUNT(OrderID) AS OrderCount
FROM SalesInfo
GROUP BY City, YEAR(OrderDate) WITH CUBE;
/* 結果包含:
1. (City, Year) 明細組合
2. (City, NULL)   → 各城市總計
3. (NULL, Year)   → 各年份總計
4. (NULL, NULL)   → 總計 */

適用場景: OLAP 多維分析、動態儀表板下鑽、需同時查看行/列/總計的報表。

四、 WITH ROLLUP:層級彙總 (巢狀)

SELECT City, YEAR(OrderDate) AS OrderYear, COUNT(OrderID) AS OrderCount
FROM SalesInfo
GROUP BY City, YEAR(OrderDate) WITH ROLLUP;
/* 結果層級:
1. (City, Year) 明細
2. (City, NULL)   → 各城市小計 (Year 彙總)
3. (NULL, NULL)   → 總計 (City 彙總)
注意:不產生 (NULL, Year) 跨城市年份總計 */

差異: CUBE = 全排列組合 (2^N 組);ROLLUP = 層級巢狀 (N+1 組)、**順序敏感** (第一欄為最高層)。

五、 GROUPING SETS:自訂彙總組合 (推薦)

-- 僅需:城市×年份明細 + 城市總計 + 總計 (無年份總計)
SELECT City, YEAR(OrderDate) AS OrderYear, COUNT(OrderID) AS OrderCount
FROM SalesInfo
GROUP BY GROUPING SETS ((City, YEAR(OrderDate)), (City), ());

-- 等價於 ROLLUP 但語義更清晰、可自訂任意組合
-- 例:只要城市總計和年份總計,不要明細
GROUP BY GROUPING SETS ((City), (YEAR(OrderDate)), ());

優勢: 效能優於 CUBE/ROLLUP (僅計算需要的組合)、語意顯式、易維護。

六、 GROUPING_ID / GROUPING:識別彙總行

結果集中無法直接區分「明細行」與「彙總行」,需輔助函數:

SELECT 
    City, 
    YEAR(OrderDate) AS OrderYear, 
    COUNT(OrderID) AS OrderCount,
    GROUPING_ID(City, YEAR(OrderDate)) AS GrpLevel, -- 位元遮罩: 01=Year彙總, 10=City彙總, 11=總計
    GROUPING(City) AS IsCityTotal,      -- 1=此行為City彙總
    GROUPING(YEAR(OrderDate)) AS IsYearTotal,
    CASE 
        WHEN GROUPING_ID(City, YEAR(OrderDate)) = 3 THEN N'總計'
        WHEN GROUPING(City) = 1 THEN City + N' 小計'
        WHEN GROUPING(YEAR(OrderDate)) = 1 THEN CAST(YEAR(OrderDate) AS NVARCHAR) + N' 年總計'
        ELSE City + N' ' + CAST(YEAR(OrderDate) AS NVARCHAR)
    END AS Label
FROM SalesInfo
GROUP BY City, YEAR(OrderDate) WITH CUBE
ORDER BY GrpLevel, City, OrderYear;

GROUPING_ID 返回整數位元遮罩,第 i 位對應第 i 個 GROUP BY 欄位,1 表示該欄位已彙總 (NULL)。

七、 GROUP BY ALL:保留空分組 (舊版相容,勿用)

-- 僅顯示大連,但北京仍出現 (Count=0)
SELECT City, COUNT(OrderID) FROM SalesInfo WHERE City = N'大連' GROUP BY ALL City;
-- ⚠️ 不支援 CUBE/ROLLUP/HAVING,2012+ 棄用,請用 LEFT JOIN 實現

八、 COMPUTE / COMPUTE BY:舊式報表輸出 (已棄用)

-- 明細 + 總計 (兩個結果集)
SELECT City, OrderDate, OrderID FROM SalesInfo COMPUTE COUNT(OrderID);

-- 分組明細 + 各組小計 (多結果集)
SELECT City, OrderDate, OrderID FROM SalesInfo ORDER BY City COMPUTE COUNT(OrderID) BY City;

現代替代:GROUP BY ROLLUP + GROUPING_ID 單一結果集,或應用層處理。

九、 綜合實戰:動態銷售儀表板查詢

DECLARE @GroupBy NVARCHAR(MAX) = N'City, YEAR(OrderDate)'; -- 可從前端傳入
DECLARE @Sql NVARCHAR(MAX) = N'
SELECT 
    City, 
    YEAR(OrderDate) AS OrderYear,
    COUNT(OrderID) AS OrderCount,
    SUM(Amount) AS TotalAmount,
    GROUPING_ID(City, YEAR(OrderDate)) AS GrpLevel
FROM SalesInfo
GROUP BY GROUPING SETS ((' + @GroupBy + N'), (City), ())
ORDER BY GrpLevel, City, OrderYear;';
EXEC sp_executesql @Sql;

配合前端下拉選單動態切換彙總維度,單一查詢滿足多種報表視圖。

十、 效能優化建議

  1. 索引支援: CREATE INDEX IX_Sales_City_Year ON SalesInfo(City, OrderDate) INCLUDE (OrderID, Amount);
  2. 避免 CUBE 爆炸: N 個分組欄位產生 2^N 組合,N>4 時極其昂貴,改用 GROUPING SETS 精確指定。
  3. 大數據預聚合: 物化視圖 / 索引視圖 / 彙總表 定時刷新,查詢直接讀預聚合結果。
  4. 並行度: MAXDOP 控制並行,避免大彙總佔用所有 CPU。

十一、 總結

GROUP BY 進階語法是關係型資料庫內建 OLAP 能力的體現。 掌握 CUBE (全維度)、ROLLUP (層級彙總)、GROUPING SETS (自訂組合)、GROUPING_ID (行類型識別),能用純 SQL 產出豐富報表數據,減少數據傳輸與應用層計算。新專案**優先使用 GROUPING SETS**,語意清晰且效能可控;維護舊系統時理解 COMPUTE/ALL 即可。

copyright © 萬盛學電腦網 all rights reserved