我们知道Select语句能访问存储在Hive表中的数据,做聚合查询时总是会使用到GroupBy子句,常见的聚合函数有count(),sum(),max()等。其实,GroupBy子句不仅包括基本的聚合作用,还可以做OLAP查询,比如cube,rollup,grouping sets等,统计根据不同维度上卷或下钻的指标。

0x00 基本GroupBy聚合查询

需求场景:统计每个每个客户的消费次数,消费金额,最大一次消费金额,最小一次消费金额,平均消费金额。

1
2
3
4
5
6
7
8
9
select order_date, user_name
,count(*) as total_cnt
,sum(amount) as total_amt
,max(amount) as max_amt
,min(amount) as min_amt
,avg(amount) as avg_amt
from order_window
group by order_date, user_name
;

0x01 高级GroupBy分析查询

官方支持的高级GroupBy功能有:

  • CUBE子句:实现任意维度组合的分组聚合
  • ROLLUP子句:实现从右到左递减多级的分组聚合
  • GROUPING SETS子句:依据指定分组进行聚合
  • Grouping__ID函数:表示聚合结果属于哪一个分组集合
  • Grouping()函数:表示当前行是否参与了该分组的聚合

需求场景:统计每天每个客户的消费金额的数据魔方,满足上卷与下钻。

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
select order_date, user_name
,grouping__id
,sum(amount) as total_amt
from order_window
group by order_date, user_name
with cube
;

select order_date, user_name
,grouping__id
,sum(amount) as total_amt
from order_window
group by order_date, user_name
with rollup
;

select order_date, user_name
,grouping__id
,sum(amount) as total_amt
from order_window
group by order_date, user_name
grouping sets(
(order_date, user_name)
,(order_date)
)
;

0x02 GroupBy子句的参数调优

1
2
3
4
---MAP端聚合
set hive.map.aggr=true;
---发生倾斜时进行负载均衡
set hive.groupby.skewindata=true;

参考文献

Enhanced Aggregation, Cube, Grouping and Rollup
Hive.GROUPING SETS
Hive group by操作