Hive函数——OLAP函数

  |   0 评论   |   2,014 浏览

准备数据

[caixiaoyu@gw3 ~]$ cat tmp_pv.txt 
北京,北京,丰台区,1251
北京,北京,海淀区,1896
北京,北京,通州区,427
北京,北京,东城区,1310
北京,北京,西城区,1057
北京,北京,朝阳区,3099
广东,深圳,龙岗区,2484
广东,深圳,盐田区,334
广东,深圳,南山区,1195
广东,深圳,宝安区,2453
广东,深圳,龙华区,86
广东,深圳,福田区,1027
广东,深圳,罗湖区,1155
上海,上海,虹口区,419
上海,上海,长宁区,525
上海,上海,崇明区,478
上海,上海,静安区,879
上海,上海,金山区,265
上海,上海,徐汇区,990
上海,上海,嘉定区,635
hadoop fs -mkdir -p /tmp/caixiaoyu/tmp_pv
hadoop fs -put tmp_pv.txt /tmp/caixiaoyu/tmp_pv

建表

CREATE EXTERNAL TABLE `tmp_pv`(
`province_name` string, 
`city_name` string, 
`region_name` string, 
`pv` int)
ROW FORMAT SERDE 
'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
WITH SERDEPROPERTIES ( 
'field.delim'=',', 
'line.delim'='\n', 
'serialization.format'=',') 
STORED AS INPUTFORMAT 
'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
'
org.apache.hadoop.hive.ql.io
.HiveIgnoreKeyTextOutputFormat'
LOCATION
'
hdfs://ns1/tmp/caixiaoyu/tmp_pv
';

select * from tmp_pv;

 

 

tmp_pv.province_name

tmp_pv.city_name

tmp_pv.region_name

tmp_pv.pv

1北京北京丰台区1251
2北京北京海淀区1896
3北京北京通州区427
4北京北京东城区1310
5北京北京西城区1057
6北京北京朝阳区3099
7广东深圳龙岗区2484
8广东深圳盐田区334
9广东深圳南山区1195
10广东深圳宝安区2453
11广东深圳龙华区86
12广东深圳福田区1027
13广东深圳罗湖区1155
14上海上海虹口区419
15上海上海长宁区525
16上海上海崇明区478
17上海上海静安区879
18上海上海金山区265
19上海上海徐汇区990
20上海上海嘉定区635

GROUPING SETS

在一个GROUP BY查询中,根据不同的维度组合进行聚合,等价于将不同维度的GROUP BY结果集进行UNION ALL

SELECT province_name,
city_name,
sum(pv) AS total_pv,
GROUPING__ID
FROM tmp_pv
GROUP BY province_name,
city_name
GROUPING SETS (province_name,
city_name);

 

province_name

city_name

total_pv

grouping__id

1NULL上海41912
2NULL北京90402
3NULL深圳87342
4上海NULL41911
5北京NULL90401
6广东NULL87341

等价于

SELECT NULL AS province_name,
city_name,
sum(pv) AS total_pv,
2 AS GROUPING__ID
FROM tmp_pv
GROUP BY city_name
UNION
ALL
SELECT province_name,
NULL AS city_name,
sum(pv) AS total_pv,
1 AS GROUPING__ID
FROM tmp_pv
GROUP BY province_name;

 

 

_u1.province_name

_u1.city_name

_u1.total_pv

_u1.grouping__id

6广东NULL87341
5北京NULL90401
4上海NULL41911
1NULL上海41912
2NULL北京90402
3NULL深圳87342

其中的 GROUPING__ID,表示结果属于哪一个分组集合。

CUBE

SELECT province_name,
city_name,
sum(pv) AS total_pv,
GROUPING__ID
FROM tmp_pv
GROUP BY province_name, city_name
WITH CUBE;

 

province_name

city_name

total_pv

grouping__id

1NULLNULL219650
5上海NULL41911
7北京NULL90401
9广东NULL87341
2NULL上海41912
3NULL北京90402
4NULL深圳87342
6上海上海41913
8北京北京90403
10广东深圳87343

等价于

SELECT NULL AS province_name, NULL AS city_name, sum(pv) AS total_pv, 0 AS GROUPING__ID FROM tmp_pv
UNION ALL
SELECT province_name, NULL AS city_name, sum(pv) AS total_pv, 1 AS GROUPING__ID FROM tmp_pv GROUP BY province_name
UNION ALL
SELECT NULL AS province_name, city_name, sum(pv) AS total_pv, 2 AS GROUPING__ID FROM tmp_pv GROUP BY city_name
UNION ALL
SELECT province_name, city_name, sum(pv) AS total_pv, 3 AS GROUPING__ID FROM tmp_pv GROUP BY province_name, city_name;

 

_u1.province_name

_u1.city_name

_u1.total_pv

_u1.grouping__id

10NULLNULL219650
4上海NULL41911
5北京NULL90401
6广东NULL87341
7NULL上海41912
8NULL北京90402
9NULL深圳87342
1上海上海41913
2北京北京90403
3广东深圳87343

 

ROLLUP

以province_name维度进行层级聚合:

SELECT province_name,
city_name,
sum(pv) AS total_pv,
GROUPING__ID
FROM tmp_pv
GROUP BY province_name, city_name
WITH ROLLUP;

 

province_name

city_name

total_pv

grouping__id

1NULLNULL219650
2上海NULL41911
4北京NULL90401
6广东NULL87341
3上海上海41913
5北京北京90403
7广东深圳87343

可以实现这样的上钻过程:
省份级别的pv->城市级别的pv-->总pv

把province_name和city_name调换顺序,则以city_name维度进行层级聚合:

SELECT province_name,
city_name,
sum(pv) AS total_pv,
GROUPING__ID
FROM tmp_pv
GROUP BY city_name, province_name
WITH ROLLUP;

 

province_name

city_name

total_pv

grouping__id

1NULLNULL219650
2NULL上海41911
4NULL北京90401
6NULL深圳87341
3上海上海41913
5北京北京90403
7广东深圳87343

可以实现这样的上钻过程:
城市级别的pv->省份级别的pv->总pv
(这里,根据城市和省份进行聚合,和根据城市聚合结果一样,因为有父子关系,如果是其他维度组合的话,就会不一样)


读后有收获可以支付宝请作者喝咖啡