介紹一種非常好用彙總數據的方式GROUPING SETS

介紹

　　對於任何人而言，用T-SQL語句來寫聚會查詢都是工作中重要的一環。我們大家也都很熟悉GROUP BY子句來實現聚合表達式，但是如果打算在一個結果集中包含多種不同的彙總結果，可能會比較麻煩。我將舉例展示給大家使用GROUPING SETS操作符來完成這個“混合的結果集”。

　　或許當我們在打算分析較大規模的數據集時，不知道從何下手，此時處理這種情況最好的方式就是彙總數據，快速的得到一個數據預覽。

在T-SQL中，使用GROUP BY子句在一個聚合查詢中來彙總需要的數據。這個子句由一組表達式定義的分組構成。結果集中每一行返回GROUP BY 子句中表達式的唯一值或者組合，並且聚合函數，像COUNT或者SUM等可以對查詢中的任何行進行聚合。但是，如果你想要多種不同組合的聚合時，一般有兩種方式：

　　1.將不懂組合聚合的結果集UNIONALL在一起。

　　2.使用 GROUPING SETS操作符，結合GROUP BY一起在一個語句中實現。

本文中，我會展示如何使用GROUPING SETS來實現這一目的。

準備數據集

本文中所有的查詢都使用AdventureWorks2012 資料庫中的數據，這裡提供一個下載地址方便使用（AdventureWorks2012）

實例: Data Analyst at Adventure Works

比如你是一個數據分析師，對於公司今年的收入很感興趣。這意味著你需要分組彙總公司的每一年的收入，查詢語句如下:

Query 1. 彙總每年收入

USE AdventureWorks2012;
GO
 
SELECT
YEAR(OrderDate) AS OrderYear,
SUM(SubTotal) AS Income
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
ORDER BY OrderYear;
GO

Query 1 返回結果集:

根據這個結果集，可知該公2005到2008年的收入情況。這類數據信息對於商業分析來說很常見。

但是，如果你想要更多關於收入的信息，比如其他彙總條件，你必須要重新運行一個GROUP BY子句。比如查詢返回公司每個月的收入情況。查詢語句如下：

Query 2. 公司每個月的收入

SELECT
YEAR(OrderDate) AS OrderYear,
MONTH(OrderDate) AS OrderMonth,
SUM(SubTotal) AS Income
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY OrderYear, OrderMonth;
GO

結果集如下:

這個結果集要比之前的更詳細一點。可以得到具體某個月的收入彙總。顯然GROUP BY 後面的列越多其越詳細，結果一般也越多（除非有傳遞依賴鍵）。

如果你仔細觀察兩個查詢，你會發現他們都是根據個子的分組表達式進行分組彙總的。前面的是按照年，後面的是按照年和月。

假如我想查看兩種彙總結果在一個結果集中應該怎麼處理那？為了實現這個目標，我們前面說了兩個方案，方案1就是使用UNION ALL,代碼如下： 

Query 3. 公司收入（每年|每月）

SELECT
YEAR(OrderDate) AS OrderYear,
NULL AS OrderMonth, --Dummy Column
SUM(SubTotal) AS Incomes
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
UNION ALL
SELECT
YEAR(OrderDate) AS OrderYear,
MONTH(OrderDate) AS OrderMonth,
SUM(SubTotal) AS Incomes
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY OrderYear, OrderMonth;
GO

結果集如下圖所示：

其中紅色框內為按照年的彙總數據。藍色框內為按照年和月的分組彙總。

如圖所示兩個結果集被合併在一起了。註意。此時NULL出現在裡面，使用NULL作為假列來標識order year分組的結果。因為按年分組沒有這個列。

儘管你已經獲得了想要的結果，但是這樣需要完成兩次的語句，接下來我們嘗試一下grouping set，方案2。因為我們都是懶人嗎，所以這個方式一定要更加簡單。目的就是“更少代碼，相同結果”。接下來我們詳細看一下：

Query 4.使用 GROUPING SETS實現相同結果 

SELECT
YEAR(OrderDate) AS OrderYear,
MONTH(OrderDate) AS OrderMonth,
SUM(SubTotal) AS Incomes
FROM Sales.SalesOrderHeader
GROUP BY
GROUPING SETS
(
YEAR(OrderDate), --1st grouping set
(YEAR(OrderDate),MONTH(OrderDate)) --2nd grouping set
);
GO

結果集跟之前的一模一樣。但是新的代碼要少很多。GROUPING SETS 操作符要和GROUP  BY 子句在一起使用。並且允許我們可以做一個多分組的查詢。儘管如此，我們要仔細檢查指定的分組集。例如假如一個分組包含兩個列，假設列A和B，兩個列都需要包含在括弧內：(column A, column B)。如果沒有括弧，這個子句將會被定義為獨立的分組，結果就不同了。

上面語句的結果如下：

順便說一下，如果我們打算聚合整個結果集（不分組聚合所有數據），只需要添加有一個空的括弧在分組集裡面即可。查詢語句如下:

Query 5. 加入總體彙總結果

SELECT
YEAR(OrderDate) AS OrderYear,
MONTH(OrderDate) AS OrderMonth,
SUM(SubTotal) AS Incomes
FROM Sales.SalesOrderHeader
GROUP BY
GROUPING SETS
(
YEAR(OrderDate), --1st grouping set
(YEAR(OrderDate),MONTH(OrderDate)), --2nd grouping set
() --3rd grouping set (grand total)
);
GO

結果如圖：

註意最下方的42行，年月都為null，這個查詢彙總了鄭鐵的所有收入，因為沒有進行任何分組。

註意，需要強調一個十強，一定要確保分組列欄位部位NULL，因此NULLS不能被用作分組列在GROUPING SETS中使用。如果非要那個為空欄位，需要使用 GROUPING 或者 GROUPING_ID 函數判斷是否NULL來自GROUPING SETS 操作符。

總結

本篇文章中，主要介紹如何使用另一種聚合查詢方式來實現多種分組聚合結果的合併。熟悉後你會發現這種方式對於總結彙總數據非常有幫助，大大提高了我們代碼的效率。

原文地址