Hive增强聚合函数（group sets、cube、rollup）

/*
增强聚合函数group sets
group sets是一种将多个group by逻辑写在一个sql语句中的便利写法，等价于将不同维度的group by结果集进行union all

2015-03,2015-03-10,cookie1
2015-03,2015-03-10,cookie5
2015-03,2015-03-12,cookie7
2015-04,2015-04-12,cookie3
2015-04,2015-04-13,cookie2
2015-04,2015-04-13,cookie4
2015-04,2015-04-16,cookie4
2015-03,2015-03-10,cookie2
2015-03,2015-03-10,cookie3
2015-04,2015-04-12,cookie5
2015-04,2015-04-13,cookie6
2015-04,2015-04-15,cookie3
2015-04,2015-04-15,cookie2
2015-04,2015-04-16,cookie1
*/
create table cookie_info
(
    month     string,
    day       string,
    cookie_id string
) row format delimited fields terminated by ',';

load data local inpath '/root/hivedata/cookie_info.txt' overwrite into table cookie_info;

select *
from cookie_info;

select month,
       day,
       count(distinct cookie_id) num,
       grouping__id -- 表示结果属于哪一个分组集合
from cookie_info
group by month, day
    grouping sets ( month, day)
order by grouping__id;

-- 等价于
select month,
       null as                   day,
       count(distinct cookie_id) num,
       1    as                   grouping__id
from cookie_info
group by month
union all
select null as                   month,
       day,
       count(distinct cookie_id) num,
       2    as                   grouping__id
from cookie_info
group by day;



select month,
       day,
       count(distinct cookie_id) num,
       grouping__id -- 表示结果属于哪一个分组集合
from cookie_info
group by month, day
    grouping sets ( month, day, ( month, day))
order by grouping__id;

-- 等价于
select month,
       null as                   day,
       count(distinct cookie_id) num,
       1    as                   grouping__id
from cookie_info
group by month
union all
select null as                   month,
       day,
       count(distinct cookie_id) num,
       2    as                   grouping__id
from cookie_info
group by day
union all
select month,
       day,
       count(distinct cookie_id) num,
       3 as                      grouping__id
from cookie_info
group by month, day;


/*
2.增强聚合函数cube
cube表示根据group by的维度的所有组合进行聚合
对于cube来说，如果有n个维度，则所有组合的总数为2^n
例如cube有a,b,c三个维度，则所有组合情况是：(a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),()
*/
select month,
       day,
       count(distinct cookie_id) num,
       grouping__id -- 表示结果属于哪一个分组集合
from cookie_info
group by month, day
with cube
order by grouping__id;

-- 等价于
select null, null, count(distinct cookie_id) num, 0 as grouping__id
from cookie_info
union all
select month, null, count(distinct cookie_id) num, 1 as grouping__id
from cookie_info
group by month
union all
select null, day, count(distinct cookie_id) num, 2 as grouping__id
from cookie_info
group by day
union all
select month, day, count(distinct cookie_id) num, 3 as grouping__id
from cookie_info
group by month, day

/*
rollup是cube的子集，以最左侧的维度为主，从该维度进行层级聚合
比如rollup有a,b,c三个维度，则所有组合的情况是：(a,b,c),(a,b),(a),()
*/
-- 以month维度进行层级聚合
select month,
       day,
       count(distinct cookie_id) num,
       grouping__id -- 表示结果属于哪一个分组集合
from cookie_info
group by month, day
with rollup
order by grouping__id;

-- 将month和day调换顺序，则以day维度进行聚合
select day,
       month,
       count(distinct cookie_id) num,
       grouping__id -- 表示结果属于哪一个分组集合
from cookie_info
group by day, month
with rollup
order by grouping__id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143