MySQL 中with rollup的用法,对分组再汇总

2022-09-05 11:17:31

mysql的rollup 主要作用:对group by分组的结果再进行汇总


举例:

创建测试表

-- ------------------------------ Table structure for test1-- ----------------------------DROPTABLEIFEXISTS`test1`;CREATETABLE`test1`(`id`int(11)NOTNULL,`name`varchar(255)CHARACTERSET utf8COLLATE utf8_general_ciNULLDEFAULTNULL,`sex`varchar(255)CHARACTERSET utf8COLLATE utf8_general_ciNULLDEFAULTNULL,`money`int(11)NULLDEFAULTNULL,PRIMARYKEY(`id`)USINGBTREE)ENGINE=InnoDBCHARACTERSET= utf8COLLATE= utf8_general_ci ROW_FORMAT= Dynamic;-- ------------------------------ Records of test1-- ----------------------------INSERTINTO`test1`VALUES(1,'靓仔','1',10);INSERTINTO`test1`VALUES(2,'靓妹','1',2);INSERTINTO`test1`VALUES(3,'靓仔','1',30);INSERTINTO`test1`VALUES(4,'test','2',7);INSERTINTO`test1`VALUES(5,'靓妹','2',6);INSERTINTO`test1`VALUES(6,'靓仔','2',5);INSERTINTO`test1`VALUES(7,'靓妹','2',5);

在这里插入图片描述

运行不带with rollup

mysql>SELECT name,sex,sum(money)from test1groupby`name`,sex;+-------+-----+------------+| name| sex|sum(money)|+-------+-----+------------+| test|2|7|| 靓仔|1|40|| 靓仔|2|5|| 靓妹|1|2|| 靓妹|2|11|+-------+-----+------------+5rowsinset(0.07 sec)

运行带with rollup

mysql>SELECT name,sex,sum(money)from test1groupby`name`,sexwith rollup;+-------+------+------------+| name| sex|sum(money)|+-------+------+------------+| test|2|7|| test|NULL|7|| 靓仔|1|40|| 靓仔|2|5|| 靓仔|NULL|45|| 靓妹|1|2|| 靓妹|2|11|| 靓妹|NULL|13||NULL|NULL|65|+-------+------+------------+9rowsinset(0.07 sec)

可以看到每个分组最后会多一条结果,就是对每个分组聚合结果的汇总,然后最最后还有个汇总的汇总,额……有点绕,也就是小计和合计
看图:
![在这里插入图片描述](https://img-blog.csdnimg.cn/98a1fc1959c9422595aa73590ebeec4f.png在这里插入图片描述

等价于如下代码:

SELECT name,sex,sum(money)from test1groupby`name`,sexunionSELECT name,null,sum(money)from test1groupby`name`unionSELECTnull,null,sum(money)from test1;
+-------+------+------------+
| name | sex  | sum(money) |
+-------+------+------------+
| test   | 2    | 7          |
| 靓仔  | 1    | 40         |
| 靓仔  | 2    | 5          |
| 靓妹  | 1    | 2          |
| 靓妹  | 2    | 11         |
| test   | NULL | 7          |
| 靓仔  | NULL | 45         |
| 靓妹  | NULL | 13         |
| NULL  | NULL | 65         |
+-------+------+------------+
9 rows in set (0.09 sec)

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。虽然两者得到相同的结果,但是执行计划却不同,rollup只需要一次表扫描操作就能得到全部结果,因此查询效率在此得到了极大的提升。

本以为MySQL也有像hive一样的cube来做多维查询,结果它不支持
在mysql5.6.17版本中,
只定义了cube,但是不支持cube操作。

二、cube:

rollup是cube的一种特殊情况,和rollup一样,cube也是一种对数据的聚合操作。但是rollup只在层次上对数据进行聚合,而cube对所有的维度进行聚合。具有N个维度的列,cube需要2的N次方次分组操作,而rollup只需要N次分组操作。

在mysql5.6.17版本中,只定义了cube,但是不支持cube操作。

2、rollup和cube的区别:

1)假设有n个维度,rollup会有n个聚合:

rollup(a,b) 统计列包含:(a,b)、(a)、()

rollup(a,b,c)统计列包含:(a,b,c)、(a,b)、(a)、()

……以此类推……

2)假设有n个纬度,cube会有2的n次方个聚合

cube(a,b) 统计列包含:(a,b)、(a)、(b)、()

cube(a,b,c) 统计列包含:(a,b,c)、(a,b)、(a,c)、(b,c)、(a)、(b)、©、()

参考链接:mysql之rollup(…)和cube(…)

  • 作者:不想做靓仔
  • 原文链接:https://blog.csdn.net/dz77dz/article/details/126391459
    更新时间:2022-09-05 11:17:31