神奇的 SQL ，同时实现小计与合计，你们会如何实现

青石路
1,799 阅读13分钟

开心一刻

今天，小区有个很漂亮的姑娘出嫁
我对儿子说：你要好好学习，认真写作业，以后才能娶到这么漂亮的老婆
儿子好像听明白了，思考了一会，默默的收起了作业本
然后如释重负的跟我说到：爸，我以后还是不娶老婆了

开心一刻

环境准备

后文要讲的 重点 是标准 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);

小计与合计

关于 小计与合计 ，大家肯定不会陌生，甚至很熟悉，或多或少都实现过这样的功能，尤其是涉及到报表统计的时候， 小计与合计是绕不过去的坎，不止你们抵触，我也很抵触这样的需求

解决不了问题就解决提出问题的人

抵触归抵触，但我们不得不承认 小计与合计 是做报表分析时很重要的指标，所以该实现还是得实现；那有哪些实现方式了，我们今天就来盘一盘

  1. GROUP BY + 应用程序汇总

    先通过数据库层面的 GROUP BY 得到小计，类似如下

    group_by

    然后通过程序代码对 商品类别 的小计进行合计

    这种实现方式是不是很熟悉？我敢断定，这种方式肯定是你们用的最多的方式（没有之一！），因为我也经常这么用；如果再加个限制条件：只用 SQL

    只用 SQL，如何实现小计与合计

    此时你们会如何实现？是不是感觉在面试了？

    程序猿何苦为难程序猿

  2. GROUP BY + UNION ALL

    我相信你们第一时间想到的也是这种方式，直接看 SQL

    GROUP BY+UNION ALL

    这个 SQL ，我相信你们都能看懂，我就不做过多解释了；补充问下，用 UNION 可以吗？答案是可以的，但由于两条 SELECT 语句的聚合键不同，一定不会出现重复行，可以使用 UNION ALL， UNION ALL 和 UNION 的不同之处在于它不会对结果进行排序，所以它有更好的性能

    就从结果而言，是不是只用 SQL 实现了 小计与合计 ？

    可把我厉害坏了

    但是，这可恶的 但是 来了，执行 2 次几乎相同的 SELECT 语句，再将其结果进行连接，你们不觉得繁琐吗？在我看来不仅繁琐，效率也会因为繁琐而低下；此时面试官又会接着问了：

    在只用 SQL 的前提下，有没有比 GROUP BY + UNION ALL 效率更高的方式

    此时，你们又会如何回答？

  3. ROLLUP

    这种方式估计你们都不知道，所以我就不卖关子了，直接看 SQL

    ROLLUP

    斗胆问一句，这算实现了吗？

    可能有小伙伴会说：这不能算实现了，没看到那么明显的 Null 吗？

    如果非要较真的话，这么说也有道理，但是假若我们在展现层（比如前端）将商品类别 Null 当 合计 处理，是不是算实现了？

    为什么我不说在后端将 Null 处理成 合计 ？如果我们在后端将 Null 处理成 合计 ，为什么不直接用方式： GROUP BY + 应用程序汇总 ？

    不过话说回来，Null 看着着实不爽，关键是坑还多：神奇的 SQL 之温柔的陷阱 → 为什么是 IS NULL 而非 ＝ NULL ？，那就把它干掉，调整下 SQL

    ROLLUP_去掉NULL

    这下完美了吧，从结果上来看是完美了，但从整体上来看，我觉得还不够完美，主要有 2 点

    1. WITH ROLLUP 是 MySQL 的独有写法

      ROLLUP 的标准写法是

      GROUP BY ROLLUP(列名1,列名2,...)

      例如在 PostgreSQL 实现小计与合计

      PostgreSQL_ROLLUP

      主流的关系型数据库（ 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 其中之一来使用，所以我们针对这三个来逐一分析

  1. ROLLUP

    关于 ROLLUP ，前面已经演示了一个案例

    ROLLUP_详解

    商品类别 值 NULL 的那一行，没有聚合键，也就相当于没有 GROUP BY 子句，这时会得到全部数据的 合计行 ，该合计行记录称为 超级分组记录（super group row），虽然听上去很屌，但还是希望大家把它当做未使用 GROUP BY 的 合计行 来理解；正是因为 合计行 的 ware_category 列的键值不明确，所以会默认使用 NULL

    前面的案例只有一个聚合列，如果再加一列 registration_date，会是什么结果？

    ROLLUP_多聚合列

    就问你们懵不懵？

    愣住

    反正我有 2 点比较懵

    1. 每一行记录的含义是什么？
    2. 这么多 Null，分别表示什么

    关于第 1 点，如果大家细看的话，还是能看明白每一行记录的含义的

    ROLLUP每一行含义

    结合红色字体描述，是不是清楚每一行记录的含义了？

    此时，我相信你们对 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;
    ROLLUP_GROUPING

    这样看着是不是清晰很多？

  2. 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;
    CUBE

    与 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 的结果之中吗？

  3. 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);
    GROUPING_SETS

    提个问题：有 Null 的那一行记录表示什么？

    相比 ROLLUP 和 CUBE 相比， GROUPING SETS 的使用场景特别少，有所了解即可

总结

  1. GROUPING

    作用很明显，就是为了区分 超级分组记录 的 NULL 和 原始记录 的 NULL ，说白了，就是为了标识出 合计 记录

  2. ROLLUP

    做个等价替换，方便大家理解

    GROUP BY ROLLUP(ware_category,registration_date)

    等价于

    ROLLUP等价

    如果是 3 个聚合键了，等价情况是怎么样的？

  3. CUBE

    同样做个等价替换

    GROUP BY CUBE(ware_category,registration_date)

    等价于

    CUBE等价

    如果是 3 个聚合键了，等价情况又是怎么样的？

avatar
文章
阅读
粉丝