神奇的 SQL ，同時實現小計與合計，閣下該如何應對

開心一刻

　　今天，小區有個很漂亮的姑娘出嫁

　　我對兒子說：你要好好學習，認真寫作業，以後才能娶到這麼漂亮的老婆

　　兒子好像聽明白了，思考了一會，默默的收起了作業本

　　然後如釋重負的跟我說到：爸，我以後還是不娶老婆了

 環境準備

　　後文要講的重點是標準 SQL ，與具體的資料庫沒關係，所以理論上來講，所有的關係型資料庫都應該支援

　　但理論是理論，事實是事實，大家需要結合當下的實際情況來看問題

　　關係型資料庫很多，後文主要基於 MySQL 8.0.30 來講解，偶爾會插入 PostgreSQL 14.1 ，沒有特殊說明的情況下，都是基於 MySQL 8.0.30 

　　 MySQL 建表 tbl_ware ，並初始化資料

CREATE TABLE `tbl_ware` (
  `ware_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '商品id',
  `ware_name` VARCHAR(100) NOT NULL COMMENT '商品名稱',
  `ware_category` VARCHAR(100) NOT NULL COMMENT '商品類別',
  `sale_unit_price` INT COMMENT '銷售單價',
  `purchase_unit_price` INT COMMENT '進貨單價',
  `registration_date` DATE COMMENT '登記日期',
  PRIMARY KEY (`ware_id`) USING BTREE
) ENGINE=InnoDB COMMENT='產品';

　　 PostgreSQL 建表 tbl_ware ，並初始化資料

CREATE TABLE tbl_ware (
  ware_id INT PRIMARY KEY,
  ware_name VARCHAR(100) NOT NULL,
  ware_category VARCHAR(100) NOT NULL,
  sale_unit_price INT,
  purchase_unit_price INT,
  registration_date DATE
);

INSERT INTO tbl_ware VALUES 
(1, 'T恤衫', '衣服', 100, 50, '2023-12-11'),
(2, '打孔器', '辦公用品', 25, 10, '2023-12-13'),
(3, '運動T恤', '衣服', 150, 50, '2023-12-10'),
(4, '菜刀', '廚房用具', 75, 30, '2023-12-15'),
(5, '高壓鍋', '廚房用具', 600, 200, '2023-12-15'),
(6, '叉子', '廚房用具', 7, 3, NULL),
(7, '菜板', '廚房用具', 98, 30, '2023-12-12'),
(8, '圓珠筆', '辦公用品', 5, 2, '2023-12-15'),
(9, '帶帽衛衣', '衣服', 150, 90, NULL),
(10, '砍骨刀', '廚房用具', 150, 69, '2023-12-13'),
(11, '羽絨服', '衣服', 800, 200, NULL);

小計與合計

　　關於 小計與合計 ，大家肯定不會陌生，甚至很熟悉

　　或多或少都實現過這樣的功能，尤其是涉及到報表統計的時候， 小計與合計 是繞不過去的坎

　　那有哪些實現方式了，我們今天就來盤一盤

　　GROUP BY + 應用程式彙總

　　先透過資料庫層面的 GROUP BY 得到小計，類似如下

　　然後透過程式程式碼對 商品類別 的小計進行一個合計

　　我敢斷定，這種方式肯定是大家用的最多的方式，因為我就是這麼用的！

　　但是，如果加個限制條件：只用 SQL 

　　此時如何實現小計和合計，各位該如何應對？

　　是不是有面試內味了？

　　GROUP BY + UNION ALL

　　直接上 SQL 

　　這個 SQL ，大家都能看懂，我就不做過多解釋了

　　補充問下，用 UNION 可以嗎

　　答案是可以的，但由於兩條 SELECT 語句的聚合鍵不同，一定不會出現重複行，可以使用 UNION ALL 

　　 UNION ALL 和 UNION 的不同之處在於它不會對結果進行排序，所以它有更好的效能

　　就從結果而言，是不是隻用 SQL 實現了 小計與合計 ？

　　但是，這可惡的 但是 來了

　　執行 2 次幾乎相同的 SELECT 語句，再將其結果進行連線，你們不覺得繁瑣嗎？

　　在我看來不僅繁瑣，效率也會因為繁瑣而低下

　　面試官又會接著問了：在只用 SQL 的前提下，有沒有更合適的實現方法？

　　此時，各位又該如何應對？

　　ROLLUP

　　我就不賣關子了，直接上絕招

　　斗膽問一句，這算實現了嗎？

　　可能有小夥伴會說：這不能算實現了，沒看到那麼明顯的 Null 嗎？

　　如果非要較真的話，這麼說也有道理，但是假若我們在展現層（比如前端）將 Null 當 合計 處理了？

　　為什麼我不說在後端將 Null 處理成 合計 ？

　　如果我們在後端將 Null 處理成 合計 ，為什麼不直接用方式： GROUP BY + 應用程式彙總 ？

　　不過， Null 看著著實不爽，關鍵是坑還多：神奇的 SQL 之溫柔的陷阱 → 三值邏輯 與 NULL ！

　　那就把它幹掉，調整下 SQL 

　　這下完美了吧，從結果上來看是完美了

　　但從整體上來看，我覺得還不夠完美，主要有 2 點

　　1、 WITH ROLLUP 是 MySQL 的獨有寫法

　　　　 ROLLUP 的標準寫法是 GROUP BY ROLLUP(列名1,列名2,...) ，例如在 PostgreSQL 實現小計與合計

　　　　主流的關係型資料庫（ Oracle 、 SQL Server 、 DB2 、 PostgreSQL ）都是按 SQL 標準來實現的

　　　　唯獨 MySQL 沒有按標準來，她發揮了她的小任性，用 WITH ROLLUP 堅持了自己的個性

　　2、 GROUPING 、 ROLLUP ，你認識嗎

　　　　這是本文的重點（呼應開頭了），請繼續往下看

　　　　你們不要懷疑我是不是在套娃，請把懷疑去掉，我就是在套娃！

GROUPING

　　考慮到 MySQL 8.0.30 不支援 CUBE 和 GROUPING SETS ，所以後面的 SQL 都基於 PostgreSQL 14.1 

　　 GROUPING 不會單獨使用，往往會結合 ROLLUP 、 CUBE 和 GROUPING SETS 其中之一來使用

　　ROLLUP

　　關於 ROLLUP ，前面已經演示了一個案例

　　

　　 商品類別 值 NULL 的那一行，沒有聚合鍵，也就相當於沒有 GROUP BY 子句，這時會得到全部資料的 合計行 

　　該合計行記錄稱為 超級分組記錄（super group row） ，雖然聽上去很屌，但還是希望大家把它當做未使用 GROUP BY 的 合計行 來理解

　　正是因為 合計行 的 ware_category 列的鍵值不明確，所以會預設使用 NULL 

　　前面的案例只有一個聚合列，如果再加一列 registration_date ，會是什麼結果？

 

　　就問你們看的懵不懵？

　　反正我有 2 點比較懵：

　　　　1、每一行記錄的含義是什麼？

　　　　2、這麼多 Null ，分別表示什麼

　　關於懵點 1，如果大家細看的話，還是能看明白每一行記錄的含義的

　　至此，相信大家對 ROLLUP 的作用有一定感覺了

　　總結下， ROLLUP 作用就如其名一樣，能夠得到像從小計到合計，從最小的聚合級開始，聚合單位逐漸擴大的結果

　　GROUP BY ROLLUP(ware_category) 時，那麼結果就是以 ware_category 歸類的 小計 加上這些 小計 的 合計 ，一共 3 + 1 = 4 條記錄

　　GROUP BY ROLLUP(ware_category,registration_date) 時，那麼結果就是以 ware_category,registration_date 歸類的 小計 加上 GROUP BY ROLLUP(ware_category) 的結果，一共 9 + 4 = 13 條記錄

　　如果聚合列有 3 列，大家還能明白每一行記錄的含義嗎

　　關於懵點 2， Null 看著確實難受，關鍵是難以區分：到底是值是 Null ，還是超級分組記錄的 Null 

　　所以為了避免混淆， SQL 標準就規定用 GROUPING 函式來判斷超級分組記錄的 NULL 

　　如果 GROUPING 函式的值是 1，則表示是超級分組記錄，0 則表示其他情況

　　我們調整下 SQL 

SELECT 
    CASE WHEN GROUPING(ware_category) = 1
        THEN '商品類別 合計'
        ELSE ware_category
    END AS ware_category, 
    CASE WHEN GROUPING(registration_date) = 1
        THEN '登記日期 合計'
        ELSE TO_CHAR(registration_date, 'YYYY-MM-DD')
    END    AS registration_date,
    SUM(purchase_unit_price) AS purchase_unit_prices
FROM tbl_ware
GROUP BY ROLLUP(ware_category,registration_date)
ORDER BY ware_category DESC, registration_date;

　　這樣看著是不是清晰很多？

　　CUBE

　　語法和 ROLLUP 一樣，我們直接看案例

SELECT 
    CASE WHEN GROUPING(ware_category) = 1
        THEN '商品類別 合計'
        ELSE ware_category
    END AS ware_category, 
    CASE WHEN GROUPING(registration_date) = 1
        THEN '登記日期 合計'
        ELSE TO_CHAR(registration_date, 'YYYY-MM-DD')
    END    AS registration_date,
    SUM(purchase_unit_price) AS purchase_unit_prices
FROM tbl_ware
GROUP BY CUBE(ware_category,registration_date)
ORDER BY ware_category DESC, registration_date;

　　與 ROLLUP 的結果相比， CUBE 結果多了幾行記錄，而這幾行記錄就是 GROUP BY(registration_date) 的聚合記錄

　　所謂 CUBE ，就是將 GROUP BY 子句中的聚合鍵的 所有可能組合 的聚合結果集中到一個結果集中的功能

　　因此，組合的個數就 2 的 n 次方（n 是聚合鍵的個數）

　　本例中，聚合鍵有 2 個（ ware_category,registration_date ），所以組合個數就是 2 的 2 次方，即 4 個

　　如果再新增 1 個變為 3 個聚合鍵的話，那麼組合的個數就是 2 的 3 次方，即 8 個

　　反觀 ROLLUP ，組合個數就是 n + 1

　　提個疑問， ROLLUP 的結果一定包含在 CUBE 的結果之中嗎？

　　GROUPING SETS

　　該運算子主要用於從 ROLLUP 或者 CUBE 的結果中取出部分記錄

　　例如，如果希望從 GROUP BY CUBE(ware_category,registration_date) 的結果中選出 商品類別 和 登記日期 各自作為聚合鍵的結果

　　可以這麼實現

SELECT 
    CASE WHEN GROUPING(ware_category) = 1
        THEN '商品類別 合計'
        ELSE ware_category
    END AS ware_category, 
    CASE WHEN GROUPING(registration_date) = 1
        THEN '登記日期 合計'
        ELSE TO_CHAR(registration_date, 'YYYY-MM-DD')
    END    AS registration_date,
    SUM(purchase_unit_price) AS purchase_unit_prices
FROM tbl_ware
GROUP BY GROUPING SETS (ware_category,registration_date);

　　提個問題，有 Null 的哪一行記錄表示什麼？

　　相比 ROLLUP 和 CUBE 相比， GROUPING SETS 的使用場景特別少，有所瞭解即可

總結

　　GROUPING

　　作用很明顯，就是為了區分 超級分組記錄 的 NULL 和原始資料 NULL 

　　說白了，就是為了標識出 合計 記錄

　　ROLLUP

　　做個等價替換，方便大家理解

　　 GROUP BY ROLLUP(ware_category,registration_date) 等價於

　　如果是 3 個聚合鍵了，等價情況是怎麼樣的？

　　CUBE

　　同樣做個等價替換

　　 GROUP BY CUBE(ware_category,registration_date) 等價於

　　如果是 3 個聚合鍵了，等價情況又是怎麼樣的？

參考

　　《SQL基礎教程》