目錄
- 一、同時得到合計行
- 二、ROLLUP——同時得出合計和小計
- 2.1 ROLLUP 的使用方法
- 2.2 將“登記日期”添加到聚合鍵當中
- 三、GROUPING 函式——讓 NULL 更加容易分辨
- 四、CUBE——用資料來搭積木
- 五、GROUPING SETS——取得期望的積木
- 請參閱
學習重點
只使用
GROUP BY子句和聚合函式是無法同時得出小計和合計的，如果想要同時得到，可以使用GROUPING運算子，理解
GROUPING運算子中CUBE的關鍵在于形成“積木搭建出的立方體”的印象，雖然
GROUPING運算子是標準 SQL 的功能，但還是有些 DBMS 尚未支持這一功能，
一、同時得到合計行
我們在 對表進行分組 中學習了 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），
KEYWORD
UNION ALL
代碼清單 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 得到之前那樣的匯總單位不同的匯總結果了，
KEYWORD
GROUPING運算子
GROUPING 運算子包含以下 3 種 [2]，
-
ROLLUP -
CUBE -
GROUPING SETS
2.1 ROLLUP 的使用方法
我們先從 ROLLUP 開始學習吧，使用 ROLLUP 就可以通過非常簡單的 SELECT 陳述句同時計算出合計行了（代碼清單 12），
KEYWORD
ROLLUP運算子
代碼清單 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，之后會為大家講解在此處插入恰當的字串的方法，
KEYWORD
- 超級分組記錄
法則 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運算子與 視窗函式 中介紹的視窗函式都是為了實作 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），
KEYWORD
GROUPING函式
代碼清單 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 一樣，都能形象地說明函式的動作，那么究竟是什么樣的動作呢？還是讓我們通過一個列子來看一看吧，
KEYWORD
CUBE運算子
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 的結果中取出部分記錄，
KEYWORD
GROUPING SETS運算子
例如，之前的 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 的機會也就很少了，
請參閱
- 視窗函式
- GROUPING 運算子
（完）
雖然也可以使用
UNION來代替UNION ALL，但由于兩條SELECT陳述句的聚合鍵不同，一定不會出現重復行，因此可以使用UNION ALL，UNION ALL和UNION的不同之處在于它不會對結果進行排序，因此比UNION的性能更好， ??目前 PostgreSQL 和 MySQL 并不支持
GROUPING運算子（MySQL 僅支持ROLLUP），具體內容請參考專欄“GROUPING運算子的支持狀況”， ??使用
ROLLUP時組合的個數是n + 1，隨著組合個數的增加，結果的行數也會增加，因此如果使用CUBE時不加以注意的話，往往會得到意想不到的巨大結果，順帶說一下，ROLLUP的結果一定包含在CUBE的結果之中， ??
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/374449.html
標籤：SQL Server
上一篇：SQL 視窗函式簡介