Hive分析函数详解：GROUPING SETS/CUBE/ROLLUP

Hive分析函数详解：GROUPING SETS/CUBE/ROLLUP

测试数据准备

drop table if exists travel_data;
create table if not exists travel_data(
	province_name string,
	city_name string,
	attraction_name string,
	star_level int, 
	Price double,
	sales int,
	sale_date string
);
-- 数据为随便写的数据
insert into travel_data select '河南省','郑州市','方特',4,312.22,15789,'2019-02-03';
insert into travel_data select '河南省','郑州市','二七广场',4,0,5942,'2019-02-03';
insert into travel_data select '河南省','郑州市','河南省博物馆',4,1.22,943,'2019-02-03';
insert into travel_data select '河南省','洛阳市','白云山',4,324.44,16843,'2019-02-03';
insert into travel_data select '河南省','洛阳市','白马寺',4,23.45,2567,'2019-02-03';
insert into travel_data select '河南省','洛阳市','龙门石窟',4,45,15784,'2019-02-03';
insert into travel_data select '广东省','深圳市','东部华侨城',4,86,9523,'2019-02-03';
insert into travel_data select '广东省','深圳市','欢乐谷',4,54,2573,'2019-02-03';
insert into travel_data select '广东省','深圳市','世界之窗',4,34,5644,'2019-02-03';
insert into travel_data select '广东省','广州市','长隆',4,46,25673,'2019-02-03';
insert into travel_data select '广东省','广州市','广州塔',4,35,9735,'2019-02-03'; 



select * from travel_data;

1. 分析函数

1. GROUPING SETS

作用：GROUPING SETS是多维聚合场景下使用的，可以根据GROUPING的字段进行多维的自动聚合计算。

GROUPING_ID是粒度的区分，根据不通的粒度计算，计算规则：将group by中字段倒序排序，如果字段出现在当前粒度中，则将改字段位置赋值为0，否则为1，然后将得到的二进制数转为十进制数就是GROUPING_ID。第四部分会详细介绍计算规则。

语法：

1. grouping SETS要用在group by后面，并且在grouping SETS中的字段也要在group by中出现。
2. 出现在group by中但是没有在grouping SETS中的字段将会被赋值为null；
3. GROUPING_ID用来区分不同的粒度。
4. 可以使用grouping来处理空值。

-- 计算省市两个维度的销售数量
select
	td.province_name ,
	td.city_name,
	sum(sales) as sales,
	grouping__id
from
	travel_data td
group by
	td.province_name ,
	td.city_name
grouping SETS (
	td.province_name ,
	td.city_name) 
	order by grouping__id

上面一种场景是以单一维度进行汇总的，如果需要多维度进行汇总，则下面的写法可以完成：
grouping SETS ((td.province_name ,td.city_name),(td.province_name,td.sale_date),td.province_name )

select
	td.province_name ,
	td.city_name,
	sum(sales) as sales,
	td.sale_date ,
	grouping__id
from
	travel_data td
group by
	td.province_name ,
	td.city_name,
	td.sale_date
grouping SETS (
	(td.province_name ,
	td.city_name),
	(td.province_name,
	td.sale_date)
	,
	td.province_name )
order by
	grouping__id

使用grouping进行空值处理，有值为0，空值为1，可以使用if来判断是否有值并进行赋值

-- 计算省市两个维度的销售数量
select
	td.province_name ,
	IF(grouping(td.city_name) = 0,td.city_name,'城市') city_name, -- 进行空值判断
	sum(sales) as sales,
	td.sale_date ,
	grouping__id
from
	travel_data td
group by
	td.province_name ,
	td.city_name,
	td.sale_date
grouping SETS (
	td.province_name ,
	td.city_name)
order by
	grouping__id

2. CUBE

cube也是多维分析的一种函数，能根据cube中的字段进行多维聚合分析，CUBE会根据指定列进行所有组合，rollup会根据顺序从左到右进行组合

用法如下：

1. cube跟在group by后面，也就是将group by中的字段用cube函数封装
2. 依然可以使用grouping__id进行排序和粒度划分
3. 依然可以使用grouping来判断是否有值

select
	td.province_name,
	td.city_name,
	sum(sales) as sales,
	grouping__id
from
	travel_data td
group by
	cube(td.province_name ,
	td.city_name)
order by
	grouping__id

3. ROLLUP

rollup为上卷的意思。类似于cube，不同的是rollup以左侧维度进行聚合,从下面的示例中可以看出，是以最左侧维度依次递减进行统计的。

select
	td.province_name,
	td.city_name,
	td.sale_date,
	sum(sales) as sales,
	grouping__id
from
	travel_data td
group by
	rollup(td.province_name ,
	td.city_name,td.sale_date)
order by
	grouping__id

4. grouping__id计算规则

从上面的各种示例可以看出来使用grouping__id可以进行排序，也可以识别出聚合的层级区分，但是这个顺序并不是连续的，这里就总结下grouping__id的生成策略。

代码：

select
	td.province_name,
	td.city_name,
	td.sale_date,
	sum(sales) as sales,
	grouping__id
from
	travel_data td
group by
	rollup(td.province_name ,
	td.city_name,td.sale_date)
order by
	grouping__id

分析：

1. 上面的代码从三个维度去rollup的，分别是省份，城市和日期，根据规则，字段在当前维度范围内标记为0，否则标记为1，前4条数据包含了三个字段，那么得到的二进制就是000，转换成十进制就是0
2. 第4条到第8条细分了省份和城市维度的，省份维度标记为0，城市维度标记为0，日期维度不在范围内，标记为1，那么二进制就是001，转换成十进制就是1
3. 第9条到第10条是只用了省份进行计算，那么省份为0，城市和日期为1，得到的二进制就是011，转换成十进制就是3
4. 第11条三个维度都没有计算，那么就都是1，得到的二进制就是111，转换成十进制就是7

5. 总结

1. cube和rollup的区别是CUBE会根据指定列进行所有组合，rollup会根据顺序从左到右进行组合
2. 可以使用grouping进行空值判断
3. 可以使用grouping__id进行粒度区分