sql-浅谈groupingsets cube rollup

–建表插入数据
CREATE TABLE IF NOT EXISTS test_data
(
year_month STRING
,date STRING
,pv INT
);
INSERT into test_data VALUES (‘2015-04’,‘2015-04-12’,10),(‘2015-03’,‘2015-03-10’,2),(‘2015-03’,‘2015-03-10’,9),
(‘2015-04’,‘2015-04-12’,4),(‘2015-04’,‘2015-04-13’,5),(‘2015-04’,‘2015-04-13’,6),(‘2015-04’,‘2015-04-16’,7),(‘2015-03’,‘2015-03-12’,3);
SELECT * from test_data;
–1 grouping sets 实现同一数据集的多重group by操作.事实上GROUPING SETS是多个GROUP BY进行UNION ALL操作的简单表达。
SELECT
year_month,
date,
SUM(pv) AS pv
FROM test_data
GROUP BY year_month,date;
– year_month date pv
– 2015-03 2015-03-10 11
– 2015-03 2015-03-12 3
– 2015-04 2015-04-12 14
– 2015-04 2015-04-13 11
– 2015-04 2015-04-16 7
SELECT
year_month,
date,
SUM(pv) AS pv
FROM test_data
GROUP BY year_month,date
GROUPING SETS ((year_month,date));
– year_month date pv
– 2015-03 2015-03-10 11
– 2015-03 2015-03-12 3
– 2015-04 2015-04-12 14
– 2015-04 2015-04-13 11
– 2015-04 2015-04-16 7
SELECT
year_month,
date,
SUM(pv) AS pv
FROM test_data
GROUP BY year_month,date
GROUPING SETS (year_month,date) ;
– year_month date pv
– \N 2015-03-10 11
– \N 2015-03-12 3
– \N 2015-04-12 14
– \N 2015-04-13 11
– \N 2015-04-16 7
– 2015-03 \N 14
– 2015-04 \N 32
SELECT
year_month,
date,
SUM(pv) AS pv
FROM test_data
GROUP BY year_month,date
GROUPING SETS ((year_month,date),year_month,date);
– year_month date pv
– \N 2015-03-10 11
– \N 2015-03-12 3
– \N 2015-04-12 14
– \N 2015-04-13 11
– \N 2015-04-16 7
– 2015-03 \N 14
– 2015-03 2015-03-10 11
– 2015-03 2015-03-12 3
– 2015-04 \N 32
– 2015-04 2015-04-12 14
– 2015-04 2015-04-13 11
– 2015-04 2015-04-16 7
–2 cube 根据GROUP BY的维度的所有组合进行聚合。
SELECT
year_month,
date,
SUM(pv) AS pv
FROM test_data
GROUP BY year_month,date
WITH CUBE;
– year_month date pv
– \N 2015-03-10 11
– \N 2015-03-12 3
– \N 2015-04-12 14
– \N 2015-04-13 11
– \N 2015-04-16 7
– 2015-03 \N 14
– 2015-03 2015-03-10 11
– 2015-03 2015-03-12 3
– 2015-04 \N 32
– 2015-04 2015-04-12 14
– 2015-04 2015-04-13 11
– 2015-04 2015-04-16 7
–3 rollup CUBE的子集，以最左侧的维度为主，从该维度进行层级聚合 可以实现这样的上钻过程：月天的PV -> 月的PV -> 总PV。
SELECT
year_month,
date,
SUM(pv) AS pv
FROM test_data
GROUP BY year_month,date
WITH ROLLUP;
– year_month date pv
– \N \N 46
– 2015-03 \N 14
– 2015-03 2015-03-10 11
– 2015-03 2015-03-12 3
– 2015-04 \N 32
– 2015-04 2015-04-12 14
– 2015-04 2015-04-13 11
– 2015-04 2015-04-16 7
–把year_month和date调换顺序，则以date维度进行层级聚合：
SELECT
date,
year_month,
SUM(pv) AS pv
FROM test_data
GROUP BY date,year_month
WITH ROLLUP;
– date year_month pv
– \N \N 46
– 2015-03-10 \N 11
– 2015-03-12 \N 3
– 2015-04-12 \N 14
– 2015-04-13 \N 11
– 2015-04-16 \N 7
– 2015-03-10 2015-03 11
– 2015-03-12 2015-03 3
– 2015-04-12 2015-04 14
– 2015-04-13 2015-04 11
– 2015-04-16 2015-04 7