Hive-OLAP分析函数使用整理
函数主要有四个:
1、grouping sets
2、grouping__id(注意:是两个下划线)
3、cube
4、rollup
【使用注意】
打开map端聚合:set hive.map.aggr=true
grouping sets
grouping sets 等价于 多个group by语句进行union
grouping__id
函数为每种聚合数据行生成唯一的组id、某一列参与了分组,对应位就被置为1,否则为0,用来区分查询结果中的null值是属于列本身的还是聚合的结果行
【简单例子】
grouping sets (id,amount,(id,amount),())
二进制关系:例如两个字段,
0,无字段参与,结果为数据行数 () 这个
1,第一个字段 与 同一个 null 左侧字段聚合
2,第二个字段 与 同一个 null 右侧字段聚合
3,两个字段一起 一起聚合
rollup
以最左侧字段为主,逐次划分
GROUP BY a,b,c,WITH ROLLUP ==> GROUP BY a,b,c GROUPING SETS((a,b,c),(a,b),(a),())
cube
进行全部组合
GROUP BY a,b,c WITH CUBE ==> GROUP BY a,b,c GROUPING SETS((a,b,c),(a,b),(b,c), (a,c),(a),(b),(c),())
使用样例
基础数据
create table temp.temp_hiveolap_tmp0 as
select 1 as id, null as amount
union all
select 1 as id, 1 as amount
union all
select 2 as id, 2 as amount
union all
select 3 as id, 3 as amount
union all
select 3 as id, null as amount
union all
select 4 as id, 5 as amount
grouping sets 使用
select
id,
amount,
grouping__id,
count(*) cn
from temp.temp_hiveolap_tmp0
group by
id,
amount
grouping sets (id,amount,(id,amount),())
rollup使用
select
id,
amount,
grouping__id,
count(*) cn
from temp.temp_hiveolap_tmp0
group by
id,
amount
with rollup
--等价于
--goruping sets ((id,amount),id,())
cube使用
select
id,
amount,
grouping__id,
count(*) cn
from temp.temp_hiveolap_tmp0
group by
id,
amount
with cube
--等价于
--grouping sets((id,amount),id,amount,())