Wetts's blog

Stay Hungry, Stay Foolish.

0%

MySQL-语法-ROLLUP和CUBE.md

发表于 分类于

rollup

rollup 是根据维度在数据结果集中进行的聚合操作。

假设用户需要对 N 个唯独进行聚合查询操作，普通的 group by 语句需要 N 个查询和 N 次 group by 操作。

而 rollup 的有点是一次可以去的 N 次 group by 的结果，这样可以提高查询效率，同时大大减少网络的传输流量。

（注，此表的表结构和数据与格式化聚合表 formatting 一致）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE rollup(
  orderid int NOT NULL,
  orderdate date NOT NULL,
  empid int NOT NULL,
  custid varchar(10) NOT NULL,
  qty int NOT NULL,
  PRIMARY KEY(orderid,orderdate));

INSERT INTO rollup SELECT 1,'2010-01-02',3,'A',10;
INSERT INTO rollup SELECT 2,'2010-04-02',2,'B',20;
INSERT INTO rollup SELECT 3,'2010-05-02',1,'A',30;
INSERT INTO rollup SELECT 4,'2010-07-02',3,'D',40;
INSERT INTO rollup SELECT 5,'2011-01-02',4,'A',20;
INSERT INTO rollup SELECT 6,'2011-01-02',3,'B',30;
INSERT INTO rollup SELECT 7,'2011-01-02',1,'C',40;
INSERT INTO rollup SELECT 8,'2009-01-02',2,'A',10;
INSERT INTO rollup SELECT 9,'2009-01-02',3,'B',20;

首先做一个简单的一维聚合

1
2
3
4
SELECT YEAR(orderdate) year, SUM(qty) sum 
FROM rollup
GROUP BY YEAR(orderdate)
WITH ROLLUP;

结果为

1

和普通的 group by 差别不大，只是多了一个 (null,220)，表示对所有的 year 再做一次聚合，即订单数量总和。

对单个唯独进行 rollup 操作只是可以在最后得到聚合的数据，对比 group by 语句并没有非常大的优势。

对多个维度进行 rollup 才能体现出 rollup 的优势：

（对 3 列进行层次的维度操作）

1
2
3
4
SELECT empid, custid, YEAR(orderdate) year, SUM(qty) sum
FROM rollup
GROUP BY empid,custid,YEAR(orderdate)
WITH ROLLUP;

结果为

2

其中 (null,null,null) 表示最后的聚合

(empid,custid,year) 表示对这 3 列进行分组的聚合结果

(empid,custid,null) 表示对 (empid,custid) 两列进行分组的聚合结果

(empid,null,null) 表示仅对 (empid) 一列进行分组的聚合结果

所以上述语句等同于（但未排序）

1
2
3
4
5
6
7
8
9
10
SELECT empid, custid, YEAR(orderdate) YEAR, SUM(qty) sum FROM rollup
GROUP BY empid, custid, YEAR(orderdate)
UNION
SELECT empid, custid, NULL, SUM(qty) sum FROM rollup
GROUP BY empid, custid
UNION
SELECT empid, NULL, NULL, SUM(qty) sum FROM rollup
GROUP BY empid
UNION
SELECT NULL, NULL, NULL, SUM(qty) sum FROM rollup

虽然两者得到相同的结果，但是执行计划却不同

rollup 只需要一次表扫描操作就能得到全部结果，因此查询效率在此得到了极大的提升。

P.S.在使用 rollup 需要注意以下几方面

  1. ORDER BY 不能在 rollup 中使用，两者为互斥关键字，如果使用，会抛出以下错误：Error Code:1221. Incorrect usage of CUBE/ROLLUP and ORDER BY
  2. 可以使用 LIMIT，但是因为不能使用 order by，所以阅读性下降，故大多数情况下无实际意义。
  3. 如果分组的列包含 NULL 值，那么 rollup 的结果可能不正确

因为在 rollup 中进行的分组统计时，null 具有特殊意义

因此在进行 rollup 时可以先将 null 转换成一个不可能存在的值，或者没有特别含义的值，比如：IFNULL(xxx,0)

cube

rollup 是 cube 的一种特殊情况，和 rollup 一样，cube 也是一种对数据的聚合操作

但是 rollup 只在层次上对数据进行聚合，而 cube 对所有的维度进行聚合

具有 N 个维度的列，cube 需要 2 的 N 次方次分组操作，而 rollup 只需要 N 次分组操作

在 mysql 5.6.17 版本中，只定义了 cube，但是不支持 cube 操作：

1
2
3
4
SELECT empid, custid, YEAR(orderdate), SUM(qty)
FROM rollup
GROUP BY empid, custid, YEAR(orderdate)
WITH CUBE;

上述 SQL 语句会报错：

1
-- ERROR 1235 (42000): This version of MySQL doesn't yet support 'CUBE'

可以通过 rollup 来模拟 cube：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SELECT 
    empid, custid, YEAR(orderdate) year, SUM(qty) sum from rollup
GROUP BY empid, custid, YEAR(orderdate)
WITH ROLLUP
UNION
SELECT 
    empid, custid, YEAR(orderdate) year, SUM(qty) sum from rollup
GROUP BY empid, YEAR(orderdate), custid
WITH ROLLUP
UNION
SELECT 
    empid, custid, YEAR(orderdate) year, SUM(qty) sum from rollup
GROUP BY custid, YEAR(orderdate),empid
WITH ROLLUP
UNION
SELECT 
    empid, custid, YEAR(orderdate) year, SUM(qty) sum from rollup
GROUP BY custid, empid, YEAR(orderdate)
WITH ROLLUP
UNION
SELECT 
    empid,custid,YEAR(orderdate) year, SUM(qty) sum from rollup
GROUP BY YEAR(orderdate), empid, custid
WITH ROLLUP
UNION
SELECT 
    empid,custid,YEAR(orderdate) year, SUM(qty) sum from rollup
GROUP BY YEAR(orderdate), custid, empid 
WITH ROLLUP;

产生的最终结果为：

3