mysql cube_MySQL ROLLUP和CUBE区别

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

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

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

如果是Group by  ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。

如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP

BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。

grouping_id()可以美化效果。除了使用GROUPING函数，还可以使用GROUPING_ID来标识GROUP BY的结果。

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

CREATE TABLErollup(

orderidint NOT NULL,

orderdatedate NOT NULL,

empidint NOT NULL,

custidvarchar(10) NOT NULL,

qtyint NOT NULL,

PRIMARY KEY(orderid,orderdate));

INSERT INTOrollupSELECT1,'2010-01-02',3,'A',10;

INSERT INTOrollupSELECT2,'2010-04-02',2,'B',20;

INSERT INTOrollupSELECT3,'2010-05-02',1,'A',30;

INSERT INTOrollupSELECT4,'2010-07-02',3,'D',40;

INSERT INTOrollupSELECT5,'2011-01-02',4,'A',20;

INSERT INTOrollupSELECT6,'2011-01-02',3,'B',30;

INSERT INTOrollupSELECT7,'2011-01-02',1,'C',40;

INSERT INTOrollupSELECT8,'2009-01-02',2,'A',10;

INSERT INTOrollupSELECT9,'2009-01-02',3,'B',20;

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

SELECT YEAR(orderdate) year,

SUM(qty) sum

FROMrollup

GROUP BYYEAR(orderdate)

WITH ROLLUP;

结果为

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

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

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

(对3列进行层次的维度操作)

SELECTempid,custid,

YEAR(orderdate) year,

SUM(qty) sum

FROMrollup

GROUP BY empid,custid,YEAR(orderdate)

WITH ROLLUP;

结果为

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

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

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

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

所以上述语句等同于(但未排序)

SELECTempid,custid,YEAR(orderdate) YEAR,SUM(qty) sum FROMrollup

GROUP BY empid,custid,YEAR(orderdate)

UNION

SELECTempid,custid,NULL,SUM(qty) sum FROMrollup

GROUP BY empid,custid

UNION

SELECTempid,NULL,NULL,SUM(qty) sum FROMrollup

GROUP BY empid

UNION

SELECT NULL,NULL,NULL,SUM(qty) sum FROMrollup

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。虽然两者得到相同的结果，但是执行计划却不同，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操作：

SELECTempid,custid,YEAR(orderdate),SUM(qty)

FROMrollup

GROUP BY empid,custid,YEAR(orderdate)

WITH CUBE;

上述SQL语句会报错：

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

可以通过rollup来模拟cube：

SELECT

empid,custid, YEAR(orderdate) year, SUM(qty) sum fromrollup

GROUP BY empid,custid, YEAR(orderdate)

WITH ROLLUP

UNION

SELECT

empid,custid, YEAR(orderdate) year, SUM(qty) sum fromrollup

GROUP BY empid, YEAR(orderdate),custid

WITH ROLLUP

UNION

SELECT

empid,custid, YEAR(orderdate) year, SUM(qty) sum fromrollup

GROUP BY custid, YEAR(orderdate),empid

WITH ROLLUP

UNION

SELECT

empid,custid, YEAR(orderdate) year, SUM(qty) sum fromrollup

GROUP BY custid,empid, YEAR(orderdate)

WITH ROLLUP

UNION

SELECT

empid,custid,YEAR(orderdate) year, SUM(qty) sum fromrollup

GROUP BYYEAR(orderdate),empid,custid

WITH ROLLUP

UNION

SELECT

empid,custid,YEAR(orderdate) year, SUM(qty) sum fromrollup

GROUP BYYEAR(orderdate),custid,empid

WITH ROLLUP;产生的最终结果为：