MaxCompute-多维度聚合分析（Grouping Sets、Cube、Rollup、Grouping_id）

GROUPING SETS

对于经常需要对数据进行多维度的聚合分析的场景，您既需要对A列做聚合，也要对B列做聚合，同时要对A、B两列做聚合，因此需要多次使用union all。您可以使用grouping sets快速解决此类问题。本文为您介绍如何使用grouping sets进行多维聚合。

1．功能介绍

grouping sets是对select语句中group by子句的扩展，允许您采用多种方式对结果分组，而不必使用多个select语句再union all来实现。这样能够使MaxCompute的引擎给出更有效的执行计划，从而提高执行性能。

与grouping sets相关联的语法如下。

类型	说明
cube	特殊的grouping sets，将指定列的所有可能组合作为grouping sets，也可以与grouping sets组合使用。 1、group by cube (a, b, c)   --等效于以下语句。   grouping sets ((a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),()) 2、group by cube ( (a, b), (c, d) ) --等效于以下语句。 grouping sets (     ( a, b, c, d ),     ( a, b       ),     (       c, d ),     (            ) ) 3、group by a, cube (b, c), grouping sets ((d), (e)) --等效于以下语句。 group by grouping sets (     (a, b, c, d), (a, b, c, e),     (a, b, d),    (a, b, e),     (a, c, d),    (a, c, e),     (a, d),       (a, e) )
rollup	特殊的grouping sets，以按层级聚合的方式产生grouping sets，也可以与grouping sets组合使用。 1、group by rollup (a, b, c)   --等效价于以下语句。   grouping sets ((a,b,c),(a,b),(a), ()) 2、group by rollup ( a, (b, c), d ) --等效于以下语句。 grouping sets (     ( a, b, c, d ),     ( a, b, c    ),     ( a          ),     (            ) ) 3、group by grouping sets((b), (c), rollup(a,b,c)) --等效于以下语句。 group by grouping sets (     (b), (c),     (a,b,c), (a,b), (a), ()  )
grouping	grouping sets结果中使用NULL充当占位符，导致您会无法区分占位符NULL与数据中真正的NULL。因此，MaxCompute为您提供了grouping。grouping接受一个列名作为参数，如果结果对应行使用了参数列做聚合，返回0，此时意味着NULL来自输入数据。否则返回1，此时意味着NULL是grouping sets的占位符。
grouping_id	接受一个或多个列名作为参数。结果是将参数列的grouping结果按照Bitmap的方式组成整数。
grouping__id	grouping__id不带参数，用于兼容Hive查询。此表达方式在MaxCompute中等价于grouping_id(group by参数列表)，参数与group by的顺序一致。 说明 MaxCompute和Hive 2.3.0及以上版本兼容该函数，在Hive 2.3.0以下版本中该函数输出不一致，因此并不推荐您使用此函数。

2．传统多维度聚合分析

准备数据。

--临时表
with sales as (
select * from values (1,2020,'Beijing',100),
(1,2021,'Beijing',200),
(2,2020,'Shanghai',200),
(2,2021,'Shanghai',300)
sales(item_id,year,city,sales)
)
select 
*
from sales;

对数据进行分组聚合。

--传统的多维度聚合分析
--（1）统计每个类别，每年的销售额；
--（2）统计每个城市的销售额；
--（3）统计总体的销售额；

with sales as (
select * from values (1,2020,'Beijing',100),
(1,2021,'Beijing',200),
(2,2020,'Shanghai',200),
(2,2021,'Shanghai',300)
sales(item_id,year,city,sales)
)
select item_id, year, null, sum(sales) from sales group by item_id, year 
union all 
select null, null, city, sum(sales) from sales group by city
union all 
select NULL, NULL, NULL, sum(sales) from sales ;

 

3．GROUPING SETS使用示例

--GROUPING SETS多维度聚合分析
--（1）统计每个类别，每年的销售额；
--（2）统计每个城市的销售额；
--（3）统计总体的销售额；

with sales as (
select * from values (1,2020,'Beijing',100),
(1,2021,'Beijing',200),
(2,2020,'Shanghai',200),
(2,2021,'Shanghai',300)
sales(item_id,year,city,sales)
)
select item_id, year, city, sum(sales) from sales group by grouping sets((item_id, year),(city),()) ;

 

4．CUBE使用示例

--CUBE多维度聚合分析
--（1）统计每个类别，每年,每个城市的销售额；
--（2）统计每个类别，每年的销售额；
--（3）统计每个类别，每个城市的销售额；
--（4）统计每年，每个城市的销售额；
--（5）统计每个类别的销售额；
--（6）统计每年的销售额；
--（7）统计每个城市的销售额；
--（8）统计总体的销售额

with sales as (
select * from values (1,2020,'Beijing',100),
(1,2021,'Beijing',200),
(2,2020,'Shanghai',200),
(2,2021,'Shanghai',300)
sales(item_id,year,city,sales)
)
select 
item_id,
year,
city,
sum(sales),
grouping_id(item_id,year,city) grouping_result
from sales
group by cube(item_id,year,city) ;

--相当于grouping sets((item_id,year,city),(item_id,year),(item_id,city),(year,city),(item_id),(year),(city),())

 

5．ROLLUP使用示例

--ROLLUP多维度聚合分析
--（1）统计每个类别，每年,每个城市的销售额；
--（2）统计每个类别，每年的销售额；
--（3）统计每个类别的销售额；
--（4）统计总体的销售额
with sales as (
select * from values (1,2020,'Beijing',100),
(1,2021,'Beijing',200),
(2,2020,'Shanghai',200),
(2,2021,'Shanghai',300)
sales(item_id,year,city,sales)
)
select 
item_id,
year,
city,
sum(sales),
grouping_id(item_id,year,city) grouping_results
from sales
group by rollup(item_id,year,city) ;
--相当于grouping sets((item_id,year,city),(item_id,year),(item_id),())