神奇的 SQL ，同时实现小计与合计，阁下该如何应对

Posted on by hackdl

开心一刻

　　今天，小区有个很漂亮的姑娘出嫁

　　我对儿子说：你要好好学习，认真写作业，以后才能娶到这么漂亮的老婆

　　儿子好像听明白了，思考了一会，默默的收起了作业本

　　然后如释重负的跟我说到：爸，我以后还是不娶老婆了

环境准备

　　后文要讲的重点是标准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='产品';

View Code

　　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);

View Code

小计与合计

　　关于小计与合计，大家肯定不会陌生，甚至很熟悉

　　或多或少都实现过这样的功能，尤其是涉及到报表统计的时候，小计与合计是绕不过去的坎

　　那有哪些实现方式了，我们今天就来盘一盘

　　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;

View Code

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

　　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;

View Code

　　与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);

View Code

　　提个问题，有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基础教程》

服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
机房租用，北京机房租用，IDC机房托管， http://www.fwqtg.net

相关推荐: Linux下PCI设备驱动开发详解（五）

Linux下PCI设备驱动开发详解（五） 本章及其以后的几章，我们将从用户态软件、内核态驱动、FPGA逻辑介绍一个通过PCI Express总线实现CPU和FPGA数据通信的简单框架。 这个框架就是开源界非常有名的RIFFA（reuseable integra…

服务器托管，北京服务器托管，服务器租用，机房机柜带宽租用

服务器托管

咨询：董先生

电话13051898268 QQ/微信93663045！

上一篇:
下一篇: