1. with cube() 函数

cube() 函数表示根据GROUP BY的维度的所有组合进行聚合。

对于cube来说,如果有n个维度,则所有组合的总个数是：

2^{n}

$2^{n}$

比如cube有a,b,c 3个维度，则所有组合情况是： (a,b,c),(a,b),(b,c),(a,c),(a),(b),©,()

SELECT
	month,
	day,
	COUNT(DISTINCT cookieid) AS nums,
	GROUPING__ID
FROM cookie_info
GROUP BY month,day
WITH CUBE
ORDER BY GROUPING__ID;


--等价于
select NULL, NULL, nums, GROUPING__ID from (SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS nums,0 AS GROUPING__ID FROM cookie_info)
UNION ALL
select month, NULL, nums, GROUPING__ID  from (SELECT month,NULL,COUNT(DISTINCT cookieid) AS nums,1 AS GROUPING__ID FROM cookie_info GROUP BY month)
UNION ALL
select NULL, day, nums, GROUPING__ID  from (SELECT NULL,day,COUNT(DISTINCT cookieid) AS nums,2 AS GROUPING__ID FROM cookie_info GROUP BY day)
UNION ALL
select month, day, nums, GROUPING__ID from (SELECT month,day,COUNT(DISTINCT cookieid) AS nums,3 AS GROUPING__ID FROM cookie_info GROUP BY month,day;)

2. with rollup() 函数

rollup() 函数是cube的子集，以最左侧的维度为主。

比如ROLLUP有a,b,c3个维度，则所有组合情况是：(a,b,c),(a,b),(a),()

SELECT
	month,
	day,
	COUNT(DISTINCT cookieid) AS nums,
	GROUPING__ID
FROM cookie_info
GROUP BY month,day
WITH ROLLUP
ORDER BY GROUPING__ID;


-- 等价于
select month, day, nums, GROUPING__ID from (SELECT month,day,COUNT(DISTINCT cookieid) AS nums,3 AS GROUPING__ID FROM cookie_info GROUP BY month,day;)
UNION ALL
select month, NULL, nums, GROUPING__ID  from (SELECT month,NULL,COUNT(DISTINCT cookieid) AS nums,1 AS GROUPING__ID FROM cookie_info GROUP BY month)
UNION ALL
select NULL, NULL, nums, GROUPING__ID from (SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS nums,0 AS GROUPING__ID FROM cookie_info)