Hive聚合函数：Grouping set、Cube、Rollup

1、环境准备

首先，在hive上新建一张user_log的表，表示用户在某个时间点进行访问

use demo;
create table demo.user_log(
    month string, 
    day string, 
    userid string
);

准备数据如下，将其保存为user_log.txt

2015-03,2015-03-10,1
2015-03,2015-03-10,5
2015-03,2015-03-12,7
2015-04,2015-04-12,3
2015-04,2015-04-13,2
2015-04,2015-04-13,4
2015-04,2015-04-16,4
2015-03,2015-03-10,2
2015-03,2015-03-10,3
2015-04,2015-04-12,5
2015-04,2015-04-13,6
2015-04,2015-04-15,3
2015-04,2015-04-15,2
2015-04,2015-04-16,1

hive中加载数据

load data local inpath '/data/user_log.txt' into table demo.t_user;

2、需求

分别按照月（month）、天（day）、月和天（month,day）统计来访用户userid个数，并获取三者的结果集

2.1、使用union all的方式

-- 按月
select month,
       null,
       count(userid)
from demo.user_log
group by month

union all
-- 按天
select null,
       day,
       count(userid)
from demo.user_log
group by day

union all
-- 按月和天
select month,
       day,
       count(userid)
from demo.user_log
group by month,day;

查询结果如下：

执行完后，数据可以成功查询出来，但是暴露了个问题，执行的查询时间太慢了。解决方案：使用grouping set

2.2、使用grouping set的方式

使用grouping set的方式，效果与union all相同，但不同的是：
grouping set只会对表进行一次扫描，但是上述的union all写法是对表进行三次扫描
grouping sets (month,day,(month,day))相当于：按月、按天、按月和天进行查询

select 
    month,day,count(userid) 
from demo.user_log
    group by month,day 
grouping sets (month,day,(month,day));

运行结果如下：

2.3、使用cube的方式

cube函数可以实现任意维度的查询，是N个维度的组合，比如cube(month,day)，等价于grouping sets ((month,day),month,day,());

写法如下

select month,day,count(userid)
from demo.user_log
group by
cube (month, day);

结果如下：

2.4、使用rollup的方式

rollup函数可以实现按照参数内从左到右递减的多级统计的效果
如：rollup((a),(b),©)等价于grouping sets((a,b,c),(a,b),(a),())。

select month,day,count(userid)
from demo.user_log
group by
    rollup (month,day);

结果如下：