本文介紹什麼是 SQL GROUPING 運算符，如何使用 SQL GROUPING 運算符。GROUPING 指示是否聚合 GROUP BY 列表中的指定列表達式。 本文重點 只使用 GROUP BY 子句和聚合函數是無法同時得出小計和合計的。如果想要同時得到，可以使用 GROUPING 運算符。 ...
目錄
本文介紹什麼是 SQL GROUPING 運算符，如何使用 SQL GROUPING 運算符。GROUPING 指示是否聚合 GROUP BY 列表中的指定列表達式。
本文重點
只使用
GROUP BY子句和聚合函數是無法同時得出小計和合計的。如果想要同時得到，可以使用GROUPING運算符。理解
GROUPING運算符中CUBE的關鍵在於形成“積木搭建出的立方體”的印象。雖然
GROUPING運算符是標準 SQL 的功能，但還是有些 DBMS 尚未支持這一功能。
一、同時得到合計行
我們在 SQL 如何對錶進行聚合和分組查詢並對查詢結果進行排序 中學習了 GROUP BY 子句和聚合函數的使用方法，可能有些讀者會想，是否有辦法能夠通過 GROUP BY 子句得到表 1 那樣的結果呢？
表 1 添加合計行
雖然這是按照商品種類計算銷售單價的總額時得到的結果，但問題在於最上面多出了 1 行合計行。使用代碼清單 10 中的 GROUP BY 子句的語法無法得到這一行。
代碼清單 10 使用 GROUP BY 無法得到合計行
SELECT product_type, SUM(sale_price)
FROM Product
GROUP BY product_type;
執行結果：
product_type | sum
--------------+------
衣服 | 5000
辦公用品 | 600
廚房用具 | 11180
因為 GROUP BY 子句是用來指定聚合鍵的場所，所以只會根據這裡指定的鍵分割數據，當然不會出現合計行。
而合計行是不指定聚合鍵時得到的彙總結果，因此與下麵的 3 行通過聚合鍵得到的結果並不相同。按照通常的思路，想一次得到這兩種結果是不可能的。
如果想要獲得那樣的結果，通常的做法是分別計算出合計行和按照商品種類進行彙總的結果，然後通過 UNION ALL [1] 連接在一起（代碼清單 11）。
代碼清單 11 分別計算出合計行和彙總結果再通過 UNION ALL 進行連接
SELECT '合計' AS product_type, SUM(sale_price)
FROM Product
UNION ALL
SELECT product_type, SUM(sale_price)
FROM Product
GROUP BY product_type;
執行結果：
product_type | sum
--------------+------
合計 | 16780
衣服 | 5000
辦公用品 | 600
廚房用具 | 11180
這樣一來，為了得到想要的結果，需要執行兩次幾乎相同的 SELECT 語句，再將其結果進行連接，不但看上去十分繁瑣，而且 DBMS 內部的處理成本也非常高，難道沒有更合適的實現方法了嗎？
二、ROLLUP——同時得出合計和小計
為了滿足用戶的需求，標準 SQL 引入了 GROUPING 運算符，我們將在本節中著重介紹。使用該運算符就能通過非常簡單的 SQL 得到之前那樣的彙總單位不同的彙總結果了。
GROUPING 運算符包含以下 3 種 [2]。
-
ROLLUP -
CUBE -
GROUPING SETS
2.1 ROLLUP 的使用方法
我們先從 ROLLUP 開始學習吧。使用 ROLLUP 就可以通過非常簡單的 SELECT 語句同時計算出合計行了（代碼清單 12）。
代碼清單 12 使用 ROLLUP 同時得出合計和小計
Oracle SQL Server DB2 PostgreSQL
SELECT product_type, SUM(sale_price) AS sum_price
FROM Product
GROUP BY ROLLUP(product_type);-----①
特定的 SQL
在 MySQL 中執行代碼清單 12 時，請將 ① 中的
GROUP BY子句改寫為“GROUP BY product_type WITH ROLLUP;”。
執行結果（在 DB2 中執行）：
product_type sum_price
-------------- ---------
16780
廚房用具 11180
辦公用品 600
衣服 5000
從語法上來說，就是將 GROUP BY 子句中的聚合鍵清單像 ROLLUP(<列 1>,<列 2>,...) 這樣使用。該運算符的作用，一言以蔽之，就是“一次計算出不同聚合鍵組合的結果”。
例如，在本例中就是一次計算出瞭如下兩種組合的彙總結果。
① GROUP BY ()
② GROUP BY (product_type)
① 中的 GROUP BY () 表示沒有聚合鍵，也就相當於沒有 GROUP BY 子句（這時會得到全部數據的合計行的記錄），該合計行記錄稱為超級分組記錄（super group row）。
雖然名字聽上去很炫，但還是希望大家把它當作未使用 GROUP BY 的合計行來理解。
超級分組記錄的 product_type 列的鍵值（對 DBMS 來說）並不明確，因此會預設使用 NULL。之後會為大家講解在此處插入恰當的字元串的方法。
法則 6
超級分組記錄預設使用
NULL作為聚合鍵。
2.2 將“登記日期”添加到聚合鍵當中
僅僅通過剛纔一個例子大家的印象可能不夠深刻，下麵讓我們再添加一個聚合鍵“登記日期（regist_date）”試試看吧。首先從不使用 ROLLUP 開始（代碼清單 13）。
代碼清單 13 在 GROUP BY 中添加“登記日期”（不使用 ROLLUP）
SELECT product_type, regist_date, SUM(sale_price) AS sum_price
FROM Product
GROUP BY product_type, regist_date;
執行結果（在 DB2 中執行）：
product_type regist_date sum_price
-------------- ------------ ----------
廚房用具 2008-04-28 880
廚房用具 2009-01-15 6800
廚房用具 2009-09-20 3500
辦公用品 2009-09-11 500
辦公用品 2009-11-11 100
衣服 2009-09-20 1000
衣服 4000
在上述 GROUP BY 子句中使用 ROLLUP 之後，結果會發生什麼變化呢（代碼清單 14）？
代碼清單 14 在 GROUP BY 中添加“登記日期”（使用 ROLLUP）
Oracle SQL Server DB2 PostgreSQL
SELECT product_type, regist_date, SUM(sale_price) AS sum_price
FROM Product
GROUP BY ROLLUP(product_type, regist_date); ------①
特定的 SQL
在 MySQL 中執行代碼清單 14 時，請將 ① 中的
GROUP BY子句改寫為“GROUP BY product_type, regist_date WITH ROLLUP;”
執行結果（在 DB2 中執行）：
product_type regist_date sum_price
-------------- ------------ ----------
16780 ←合計
廚房用具 11180 ←小計（廚房用具）
廚房用具 2008-04-28 880
廚房用具 2009-01-15 6800
廚房用具 2009-09-20 3500
辦公用品 600 ←小計（辦公用品）
辦公用品 2009-09-11 500
辦公用品 2009-11-11 100
衣服 5000 ←小計（衣服）
衣服 2009-09-20 1000
衣服 4000
將上述兩個結果進行比較後我們發現，使用 ROLLUP 時多出了最上方的合計行以及 3 條不同商品種類的小計行（也就是未使用登記日期作為聚合鍵的記錄），這 4 行就是我們所說的超級分組記錄。
也就是說，該 SELECT 語句的結果相當於使用 UNION 對如下 3 種模式的聚合級的不同結果進行連接（圖 5）。
① GROUP BY ()
② GROUP BY (product_type)
③ GROUP BY (product_type, regist_date)
圖 5 3 種模式的聚合級
如果大家覺得上述結果不容易理解的話，可以參考表 2 中按照聚合級添加縮進和說明後的內容，理解起來就很容易了。
表 2 根據聚合級添加縮進後的結果
| 合計 | 16780 | |
|---|---|---|
| 廚房用具 | 小計 | 11180 |
| 廚房用具 | 2008-04-28 | 880 |
| 廚房用具 | 2009-01-15 | 6800 |
| 廚房用具 | 2009-09-20 | 3500 |
| 辦公用品 | 小計 | 600 |
| 辦公用品 | 2009-09-11 | 500 |
| 辦公用品 | 2009-11-11 | 100 |
| 衣服 | 小計 | 5000 |
| 衣服 | 2009-09-20 | 1000 |
| 衣服 | 4000 |
ROLLUP 是“捲起”的意思，比如捲起百葉窗、窗帘捲，等等。其名稱也形象地說明瞭該操作能夠得到像從小計到合計這樣，從最小的聚合級開始，聚合單位逐漸擴大的結果。
法則 7
ROLLUP可以同時得出合計和小計，是非常方便的工具。
專欄
GROUPING 運算符的支持情況
本文介紹的
GROUPING運算符與 什麼是 SQL 視窗函數 介紹的視窗函數都是為了實現 OLAP 用途而添加的功能，是比較新的功能（是 SQL：1999 的標準 SQL 中添加的新功能）。因此，還有一些 DBMS 尚未支持這些功能。截止到 2016 年 5 月，Oracle、SQL Server、DB2、PostgreSQL 的最新版本都已經支持這些功能了，但 MySQL 的最新版本 5.7 還是不支持這些功能。
想要在不支持
GROUPING運算符的 DBMS 中獲得包含合計和小計的結果時，只能像本文一開始介紹的那樣，使用UNION將多條SELECT語句連接起來。此外，使用 MySQL 時的情況更加複雜一些，只有一個不合規則的
ROLLUP能夠使用。這裡所說的“不合規則”指的是需要使用特定的語法。-- MySQL專用 SELECT product_type, regist_date, SUM(sale_price) AS sum_price FROM Product GROUP BY product_type, regist_date WITH ROLLUP;遺憾的是，MySQL 5.7 並不支持
CUBE和GROUPING SETS。希望之後的版本能夠提供對它們的支持。
三、GROUPING 函數——讓 NULL 更加容易分辨
可能有些讀者會註意到，之前使用 ROLLUP 所得到的結果（代碼清單 14 的執行結果）有些蹊蹺，問題就出在“衣服”的分組之中，有兩條記錄的 regist_date 列為 NULL，但其原因卻並不相同。
sum_price 為 4000 元的記錄，因為商品表中 運動 T 恤 的註冊日期為 NULL，所以就把 NULL 作為聚合鍵了，這在之前的示例中我們也曾見到過。
相反，sum_price 為 5000 元的記錄，毫無疑問就是超級分組記錄的 NULL 了（具體為 1000 元 + 4000 元 = 5000 元）。但兩者看上去都是“NULL”，實在是難以分辨。
product_type regist_date sum_price
-------------- ------------ ----------
衣服 5000 ←因為是超級分組記錄，所以登記日期為NULL
衣服 2009-09-20 1000
衣服 4000 ←僅僅因為“運動T恤”的登記日期為NULL
為了避免混淆，SQL 提供了一個用來判斷超級分組記錄的 NULL 的特定函數——GROUPING 函數。
該函數在其參數列的值為超級分組記錄所產生的 NULL 時返回 1，其他情況返回 0（代碼清單 15）。
代碼清單 15 使用 GROUPING 函數來判斷 NULL
Oracle SQL Server DB2 PostgreSQL
SELECT GROUPING(product_type) AS product_type,
GROUPING(regist_date) AS regist_date, SUM(sale_price) AS sum_price
FROM Product
GROUP BY ROLLUP(product_type, regist_date);
執行結果（在 DB2 中執行）：
product_type regist_date sum_price
-------------- ------------ ----------
1 1 16780
0 1 11180
0 0 880
0 0 6800
0 0 3500
0 1 600
0 0 500
0 0 100
0 1 5000 ←碰到超級分組記錄中的NULL時返回1
0 0 1000
0 0 4000 ←原始數據為NULL時返回0
這樣就能分辨超級分組記錄中的 NULL 和原始數據本身的 NULL 了。
使用 GROUPING 函數還能在超級分組記錄的鍵值中插入字元串。
也就是說，當 GROUPING 函數的返回值為 1 時，指定“合計”或者“小計”等字元串，其他情況返回通常的列的值（代碼清單 16）。
代碼清單 16 在超級分組記錄的鍵值中插入恰當的字元串
Oracle SQL Server DB2 PostgreSQL
SELECT CASE WHEN GROUPING(product_type) = 1
THEN '商品種類 合計'
ELSE product_type END AS product_type,
CASE WHEN GROUPING(regist_date) = 1
THEN '登記日期 合計'
ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,
SUM(sale_price) AS sum_price
FROM Product
GROUP BY ROLLUP(product_type, regist_date);
執行結果（在 DB2 中執行）：
product_type regist_date sum_price
-------------- ------------ ----------
商品種類 合計 登記日期 合計 16780
廚房用具 登記日期 合計 11180
廚房用具 2008-04-28 880
廚房用具 2009-01-15 6800
廚房用具 2009-09-20 3500
辦公用品 登記日期 合計 600
辦公用品 2009-09-11 500
辦公用品 2009-11-11 100
衣服 登記日期 合計 5000 ←將超級分組記錄中的NULL替換為“登記日期 合計”
衣服 2009-09-20 1000
衣服 4000 ←原始數據中的NULL保持不變
在實際業務中需要獲取包含合計或者小計的彙總結果（這種情況是最多的）時，就可以使用 ROLLUP 和 GROUPING 函數來實現了。
CAST(regist_date AS VARCHAR(16))
那為什麼還要將 SELECT 子句中的 regist_date 列轉換為 CAST(regist_date AS VARCHAR)16)) 形式的字元串呢？
這是為了滿足 CASE 表達式所有分支的返回值必須一致的條件。如果不這樣的話，那麼各個分支會分別返回日期類型和字元串類型的值，執行時就會發生語法錯誤。
法則 8
使用
GROUPING函數能夠簡單地分辨出原始數據中的NULL和超級分組記錄中的NULL。
四、CUBE——用數據來搭積木
ROLLUP 之後我們來介紹另一個常用的 GROUPING 運算符——CUBE。CUBE 是“立方體”的意思，這個名字和 ROLLUP 一樣，都能形象地說明函數的動作。
那麼究竟是什麼樣的動作呢？還是讓我們通過一個列子來看一看吧。
CUBE 的語法和 ROLLUP 相同，只需要將 ROLLUP 替換為 CUBE 就可以了。
下麵我們就把代碼清單 16 中的 SELECT 語句替換為 CUBE 試試看吧（代碼清單 17）。
代碼清單 17 使用 CUBE 取得全部組合的結果
Oracle SQL Server DB2 PostgreSQL
SELECT CASE WHEN GROUPING(product_type) = 1
THEN '商品種類 合計'
ELSE product_type END AS product_type,
CASE WHEN GROUPING(regist_date) = 1
THEN '登記日期 合計'
ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,
SUM(sale_price) AS sum_price
FROM Product
GROUP BY CUBE(product_type, regist_date);
執行結果（在 DB2 中執行）：
product_type regist_date sum_price
-------------- ------------ ----------
商品種類 合計 登記日期 合計 16780
商品種類 合計 2008-04-28 880 ←追加
商品種類 合計 2009-01-15 6800 ←追加
商品種類 合計 2009-09-11 500 ←追加
商品種類 合計 2009-09-20 4500 ←追加
商品種類 合計 2009-11-11 100 ←追加
商品種類 合計 4000 ←追加
廚房用具 登記日期 合計 11180
廚房用具 2008-04-28 880
廚房用具 2009-01-15 6800
廚房用具 2009-09-20 3500
辦公用品 登記日期 合計 600
辦公用品 2009-09-11 500
辦公用品 2009-11-11 100
衣服 登記日期 合計 5000
衣服 2009-09-20 1000
衣服 4000
與 ROLLUP 的結果相比，CUBE 的結果中多出了幾行記錄。大家看一下應該就明白了，多出來的記錄就是只把 regist_date 作為聚合鍵所得到的彙總結果。
① GROUP BY ()
② GROUP BY (product_type)
③ GROUP BY (regist_date) ← 添加的組合
④ GROUP BY (product_type, regist_date)
所謂 CUBE，就是將 GROUP BY 子句中聚合鍵的“所有可能的組合”的彙總結果集中到一個結果中。因此，組合的個數就是 2n（n 是聚合鍵的個數）。
本例中聚合鍵有 2 個，所以 22 = 4。如果再添加 1 個變為 3 個聚合鍵的話，就是 23 = 8 [3]。
讀到這裡，可能很多讀者都會覺得奇怪，究竟 CUBE 運算符和立方體有什麼關係呢？
眾所周知，立方體由長、寬、高 3 個軸構成。對於 CUBE 來說，一個聚合鍵就相當於其中的一個軸，而結果就是將數據像積木那樣堆積起來（圖 6）。
圖 6 CUBE 的執行圖示
由於本例中只有商品種類（product_type）和登記日期（regist_date）2 個軸，所以我們看到的其實是一個正方形，請大家把它看作缺了 1 個軸的立方體。
通過 CUBE 當然也可以指定 4 個以上的軸，但那已經屬於 4 維空間的範疇了，是無法用圖形來表示的。
法則 9
可以把
CUBE理解為將使用聚合鍵進行切割的模塊堆積成一個立方體。
五、GROUPING SETS——取得期望的積木
最後要介紹給大家的 GROUPING 運算符是 GROUPING SETS。該運算符可以用於從 ROLLUP 或者 CUBE 的結果中取出部分記錄。
例如，之前的 CUBE 的結果就是根據聚合鍵的所有可能的組合計算而來的。
如果希望從中選取出將“商品種類”和“登記日期”各自作為聚合鍵的結果，或者不想得到“合計記錄和使用 2 個聚合鍵的記錄”時，可以使用 GROUPING SETS（代碼清單 18）。
代碼清單 18 使用 GROUPING SETS 取得部分組合的結果
Oracle SQL Server DB2 PostgreSQL
SELECT CASE WHEN GROUPING(product_type) = 1
THEN '商品種類 合計'
ELSE product_type END AS product_type,
CASE WHEN GROUPING(regist_date) = 1
THEN '登記日期 合計'
ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,
SUM(sale_price) AS sum_price
FROM Product
GROUP BY GROUPING SETS (product_type, regist_date);
執行結果（在 DB2 中執行）：
product_type regist_date sum_price
-------------- ------------ ----------
商品種類 合計 2008-04-28 880
商品種類 合計 2009-01-15 6800
商品種類 合計 2009-09-11 500
商品種類 合計 2009-09-20 4500
商品種類 合計 2009-11-11 100
商品種類 合計 4000
廚房用具 登記日期 合計 11180
辦公用品 登記日期 合計 600
衣服 登記日期 合計 5000
上述結果中也沒有全體的合計行（16780 元）。與 ROLLUP 或者 CUBE 能夠得到規定的結果相對，GROUPING SETS 用於從中取出個別條件對應的不固定的結果。
然而，由於期望獲得不固定結果的情況少之又少，因此與 ROLLUP 或者 CUBE 比起來，使用 GROUPING SETS 的機會也就很少了。
原文鏈接：https://www.developerastrid.com/sql/sql-grouping/
（完）
雖然也可以使用
UNION來代替UNION ALL，但由於兩條SELECT語句的聚合鍵不同，一定不會出現重覆行，因此可以使用UNION ALL。UNION ALL和UNION的不同之處在於它不會對結果進行排序，因此比UNION的性能更好。 ↩︎目前 PostgreSQL 和 MySQL 並不支持
GROUPING運算符（MySQL 僅支持ROLLUP）。具體內容請參考專欄“GROUPING運算符的支持狀況”。 ↩︎使用
ROLLUP時組合的個數是n + 1。隨著組合個數的增加，結果的行數也會增加，因此如果使用CUBE時不加以註意的話，往往會得到意想不到的巨大結果。順帶說一下，ROLLUP的結果一定包含在CUBE的結果之中。 ↩︎
