SQL- GROUP BY 、CUBE 、ROLLUP

Compare GROUP BY 、CUBE 、ROLLUP

First, we created a table DEPART as below:

CREATE TABLE DEPART (dept char(10),
                     employee char(6),
                     salary int);
INSERT INTO DEPART VALUES ('A','ZHANG',100);
INSERT INTO DEPART VALUES ('A','LI',200);
INSERT INTO DEPART VALUES ('A','WANG',300);
INSERT INTO DEPART VALUES ('A','ZHAO',400);
INSERT INTO DEPART VALUES ('A','DUAN',500);
INSERT INTO DEPART VALUES ('B','DUAN',600);
INSERT INTO DEPART VALUES ('B','DUAN',700);

Table DEPART

1. GROUP BY

SELECT dept,employee,SUM(salary)
AS TOTAL 
FROM DEPART
GROUP BY dept,employee;

2. ROLLUP

SELECT dept,employee,SUM(salary) AS TOTAL 
FROM DEPART
GROUP BY dept,employee WITH ROLLUP

There are three more records in the ROLLUP result: the total salary of department A, the total salary of department B, and the total salary of A and B. It equals to:

SELECT dept,employee,SUM(salary) AS TOTAL 
FROM DEPART
GROUP BY dept,employee 
union 
SELECT dept,’NULL’,SUM(salary) AS TOTAL 
FROM DEPART 
GROUP BY dept 
union 
SELECT ‘NULL’,’NULL’,SUM(salary) AS TOTAL 
FROM DEPART

3. CUBE

SELECT dept,employee,SUM(salary)AS TOTAL 
FROM DEPART
GROUP BY dept,employee WITH CUBE;
#In MySQL 5.6.17 version, CUBE is defined, but CUBE operation is not supported.

The result of CUBE is based on the ROLLUP result set with 5 more rows. These 5 rows are equivalent to the result of using the employee (ie CUBE) as the GROUP BY on the union on the ROLLUP result:

SELECT dept,employee,SUM(salary) AS TOTAL 
FROM DEPART
GROUP BY dept,employee WITH ROLLUP
union
SELECT 'NULL',employee,SUM(salary) AS TOTAL 
FROM DEPART 
GROUP BY employee

The difference between ROLLUP and CUBE

1）Rollup is a special case of CUBE. The result generated by CUBE shows the aggregation of all combinations of values in the selected column. While the result generated by ROLLUP shows a certain hierarchical aggregation of the values in the selected column.

2）Assuming there are n dimensions,

• ROLLUP will have n aggregations:

ROLLUP(a,b) contains: (a,b), (a), ()

ROLLUP(a,b,c) statistical column contains: (a,b,c), (a,b), (a), ()

…And so on…

• CUBE will have 2^n aggregations：

CUBE(a,b) The statistics column contains: (a,b), (a), (b), ()

CUBE(a,b,c) The statistical columns include: (a,b,c), (a,b), (a,c), (b,c), (a), (b), (c), ()

…And so on…