MySQL with rollup的用法
当需要对数据库数据进行分类统计的时候，往往会用上groupby进行分组。
而在groupby后面还可以加入withcube和withrollup等关键字对数据进行汇总。
mysql文档
with rollup概述
with在sql语句中定义在group by之后。当需要对数据库数据进行分类统计的时候，往往会用上groupby进行分组。
而在groupby后面还可以加入withcube和withrollup等关键字对数据进行汇总。
不过这个cube在mysql中并不适用。
应用实例
现在有这样一张学生表，里面的数据如下所示。
如果想对根据学生，对科目，分数求和，可以这样写。
如果想在这个的基础上，求出学生的总分数，应该怎么做。
使用 WITH ROLLUP，此函数是对聚合函数进行求和，注意 with rollup是对 group by 后的第一个字段，进行分组求和。
ORDER BY不能在rollup中使用，两者为互斥关键字，如果使用，会抛出以下错误：Error Code:1221. Incorrect usage of CUBE/ROLLUP and ORDER BY。mysql5.7中是不支持的，在8.0以后支持。
相信大家已经知道如何使用with rollup了，这个就是在group by分组之后，再次对聚合函数进行求和。
MySQL with rollup 聚合函数类似oracle cube操作
作用
在分组统计数据的基础上再进行统计汇总
题目示例
根据题目意思需要 求每个价格区间，时间的mark=0的和/mark=1的和
最后还有求个所有价格区间 按月份的mark=0的和/mark=1的和
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT PriceBand, YearMonth, sum( CASE Mark WHEN 0 THEN VALUE END ) / sum( CASE Mark WHEN 1 THEN VALUE END ) FROM test GROUP BY YearMonth, PriceBand UNION ALL SELECT 'Alll priceBand', YearMonth, sum( CASE Mark WHEN 0 THEN VALUE END ) / sum( CASE Mark WHEN 1 THEN VALUE END ) FROM test GROUP BY YearMonth |
在不知道mysql有 这种聚合函数的情况 我所想到的只能靠这样拼接来实现
使用rollup后
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT ifnull( PriceBand, 'All priceBand' ), YearMonth, sum( CASE Mark WHEN 0 THEN VALUE END ) / sum( CASE Mark WHEN 1 THEN VALUE END ) FROM test GROUP BY YearMonth, PriceBand WITH ROLLUP HAVING YearMonth IS NOT NULL |
注意
在mysql5.6.17版本中，只定义了cube，但是不支持cube操作。
cube也是一种对数据的聚合操作。但是rollup只在层次上对数据进行聚合，而cube对所有的维度进行聚合。具有N个维度的列，cube需要2的N次方次分组操作，而rollup只需要N次分组操作。
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中 with rollup放的位置是有要求的
之前的group by 正常使用分组 加了 with rollup 在那个字段后就对所有价格区间进行统计 同时 with rollup 并不能放在 (group by a with rollup,b )中间
