• 为了保证你在浏览本网站时有着更好的体验,建议使用类似Chrome、Firefox之类的浏览器~~
    • 如果你喜欢本站的内容何不Ctrl+D收藏一下呢,与大家一起分享各种编程知识~
    • 本网站研究机器学习、计算机视觉、模式识别~当然不局限于此,生命在于折腾,何不年轻时多折腾一下

hive 高级聚合函数

Sql admin 7天前 18次浏览 0个评论 扫描二维码

HQL里着实还有很多未发掘的东东,平时写的SQL都感觉很简单。直到最近做些统计报表发现一些新大陆。

grouping sets

grouping sets相当于多个group by分组统计后再union的逻辑,

那么你想统计多个维度的聚合就需要写多个子查询,使用grouping sets 可以轻松帮忙实现。

按省份和地市统计新增数

select
shengfen,
dishi,
sum(xinzeng) as sum_xinzeng
from xinzeng_tb
group by shengfen, dishi
grouping sets (shengfen, dishi)
-- cluster by sum_xinzeng 
distribute by sum_xinzeng sort by sum_xinzeng desc;

OK
shengfen	dishi	sum_xinzeng
广东省	NULL	4345
上海市	NULL	4092
NULL	深圳市	1518
NULL	广州市	1438
NULL	嘉定区	1434
NULL	珠海市	1389
NULL	普陀区	1369
NULL	浦东新区	1289
Time taken: 83.518 seconds, Fetched: 8 row(s)

等价于

select
shengfen,
null,
sum(xinzeng) as sum_xinzeng
from xinzeng_tb
group by shengfen
union all
select
null,
dishi,
sum(xinzeng) as sum_xinzeng
from xinzeng_tb
group by dishi
distribute by sum_xinzeng sort by sum_xinzeng desc;

OK
_u2.shengfen	_u2._c1	_u2.sum_xinzeng
广东省	NULL	4345
上海市	NULL	4092
NULL	深圳市	1518
NULL	广州市	1438
NULL	嘉定区	1434
NULL	珠海市	1389
NULL	普陀区	1369
NULL	浦东新区	1289
Time taken: 124.22 seconds, Fetched: 8 row(s)

上面的例子实际上就是先以省份为分组字段,然后以市级作为分组字段,然后将结果合并。可以看做事依次遍历完成分局聚合操作。

上面的方法按地市分组时省份那列为null,可以优化为:

select
shengfen,
dishi,
sum(xinzeng) as sum_xinzeng
from xinzeng_tb
group by shengfen, dishi
grouping sets (shengfen, (shengfen, dishi))
-- cluster by sum_xinzeng 
distribute by sum_xinzeng sort by sum_xinzeng desc;

OK
shengfen	dishi	sum_xinzeng
广东省	NULL	4345
上海市	NULL	4092
广东省	深圳市	1518
广东省	广州市	1438
上海市	嘉定区	1434
广东省	珠海市	1389
上海市	普陀区	1369
上海市	浦东新区	1289
Time taken: 77.62 seconds, Fetched: 8 row(s)

等价于:

select
shengfen,
null,
sum(xinzeng) as sum_xinzeng
from xinzeng_tb
group by shengfen
union all
select
shengfen,
dishi,
sum(xinzeng) as sum_xinzeng
from xinzeng_tb
group by shengfen, dishi
distribute by sum_xinzeng sort by sum_xinzeng desc;

OK
_u2.shengfen	_u2._c1	_u2.sum_xinzeng
广东省	NULL	4345
上海市	NULL	4092
广东省	深圳市	1518
广东省	广州市	1438
上海市	嘉定区	1434
广东省	珠海市	1389
上海市	普陀区	1369
上海市	浦东新区	1289

按grouping sets查询和group by查询再union的等价关系。

上面的例子优化了在以市级聚合时省份为NULL现象,市级作为省级下面更小的单位,也就是说省份肯定是有的,结果为NULL不太合理,所以通过更改grouping sets  以省市联合作为分组字段就可以解决上述问题。

grouping__id(两个下划线_)

这个函数返回一个位向量,该位向量对应于每一列是否存在。对于每一列,如果结果集中的某一行已经聚合了该列,则结果集中的某一行的值为“1”,否则该值为“0”。这可以用于在数据中有空值时进行区分。

select
shengfen,
dishi,
sum(xinzeng) as sum_xinzeng,
grouping__id
from xinzeng_tb
group by shengfen, dishi
grouping sets (shengfen, (shengfen, dishi))
-- cluster by grouping__id
distribute by grouping__id sort by grouping__id, sum_xinzeng desc;

OK
shengfen	dishi	sum_xinzeng	grouping__id
广东省	深圳市	1518	0
广东省	广州市	1438	0
上海市	嘉定区	1434	0
广东省	珠海市	1389	0
上海市	普陀区	1369	0
上海市	浦东新区	1289	0
广东省	NULL	4345	1
上海市	NULL	4092	1
Time taken: 74.346 seconds, Fetched: 8 row(s)

上面最后倒数两行,分别只有一个字段是有效的聚合字段,所以 grouping__id 为1 ,有一个不存在 就记为 1,按照二级制编码的方式得到 grouping__id。

等价于

select
shengfen,
null,
sum(xinzeng) as sum_xinzeng,
1 as grouping__id
from xinzeng_tb
group by shengfen
union all
select
shengfen,
dishi,
sum(xinzeng) as sum_xinzeng,
0 as grouping__id
from xinzeng_tb
group by shengfen, dishi
distribute by grouping__id sort by grouping__id, sum_xinzeng desc;

OK
_u2.shengfen	_u2._c1	_u2.sum_xinzeng	_u2.grouping__id
广东省	深圳市	1518	0
广东省	广州市	1438	0
上海市	嘉定区	1434	0
广东省	珠海市	1389	0
上海市	普陀区	1369	0
上海市	浦东新区	1289	0
广东省	NULL	4345	1
上海市	NULL	4092	1
Time taken: 117.174 seconds, Fetched: 8 row(s)
select
shengfen,
dishi,
sum(xinzeng) as sum_xinzeng,
grouping__id
from xinzeng_tb
group by shengfen, dishi
with rollup
-- cluster by grouping__id
distribute by grouping__id sort by grouping__id, sum_xinzeng desc;

OK
shengfen	dishi	sum_xinzeng	grouping__id
广东省	深圳市	1518	0
广东省	广州市	1438	0
上海市	嘉定区	1434	0
广东省	珠海市	1389	0
上海市	普陀区	1369	0
上海市	浦东新区	1289	0
广东省	NULL	4345	1
上海市	NULL	4092	1
NULL	NULL	8437	3
Time taken: 69.779 seconds, Fetched: 9 row(s)

group_id是为了区分每条输出结果是属于哪一个group by的数据。它是根据group by后面声明的顺序字段是否存在于当前group by中的一个二进制位组合数据。
grouping__id为0的是group by中所有列都被选中了,二进制00,所以标识为0
grouping__id为1的是group by中只有一列被选中了,二进制01,所以标识为1
grouping__id为3的是group by中没有一列被选中,二进制11,所以标识为3

Grouping

分组函数指示GROUP BY子句中的表达式是否对给定行进行聚合。值0表示属于分组集的列,而值1表示不属于分组集的列。

select
shengfen,
dishi,
sum(xinzeng) as sum_xinzeng,
grouping(shengfen, dishi) grouping_two,
grouping(shengfen) grouping_s,
grouping(dishi) grouping_d,
grouping__id
from xinzeng_tb
group by shengfen, dishi
with rollup
-- cluster by grouping__id
distribute by grouping__id sort by grouping__id, sum_xinzeng desc;

OK
shengfen	dishi	sum_xinzeng	grouping_two	grouping_s	grouping_d	grouping__id
广东省	深圳市	1518	0	0	0	0
广东省	广州市	1438	0	0	0	0
上海市	嘉定区	1434	0	0	0	0
广东省	珠海市	1389	0	0	0	0
上海市	普陀区	1369	0	0	0	0
上海市	浦东新区	1289	0	0	0	0
广东省	NULL	4345	1	0	1	1
上海市	NULL	4092	1	0	1	1
NULL	NULL	8437	3	1	1	3
Time taken: 73.224 seconds, Fetched: 9 row(s)

cube and rollup

CUBE/ROLLUP必须与GROUP BY一起使用。

cube可以得到group by这些维度上所有可能的聚合问题的答案。
例如:
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), ©, ( )).

rollup

ROLLUP子句用于与GROUP BY一起计算维度层次结构级别上的聚合。
使用ROLLUP将a、b、c分组,假设层次结构是“a”向下钻取到“b”,钻取到“c”。
GROUP BY a, b, c, WITH ROLLUP 等价于 GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (a), ( )).

select
shengfen,
dishi,
riqi,
sum(xinzeng) as sum_xinzeng,
grouping__id
from xinzeng_tb
group by shengfen, dishi, riqi
--grouping sets (shengfen, dishi, riqi)
with rollup
--cluster by grouping__id
distribute by grouping__id sort by grouping__id desc, sum_xinzeng desc;

OK
shengfen	dishi	riqi	sum_xinzeng	grouping__id
NULL	NULL	NULL	8437	7
广东省	NULL	NULL	4345	3
上海市	NULL	NULL	4092	3
广东省	深圳市	NULL	1518	1
广东省	广州市	NULL	1438	1
上海市	嘉定区	NULL	1434	1
广东省	珠海市	NULL	1389	1
上海市	普陀区	NULL	1369	1
上海市	浦东新区	NULL	1289	1
广东省	深圳市	2019-01-02	770	0
广东省	深圳市	2019-01-03	748	0
广东省	广州市	2019-01-02	726	0
上海市	嘉定区	2019-01-03	721	0
上海市	嘉定区	2019-01-02	713	0
广东省	广州市	2019-01-03	712	0
广东省	珠海市	2019-01-02	708	0
上海市	普陀区	2019-01-03	691	0
广东省	珠海市	2019-01-03	681	0
上海市	普陀区	2019-01-02	678	0
上海市	浦东新区	2019-01-03	664	0
上海市	浦东新区	2019-01-02	625	0

cube

elect
shengfen,
dishi,
riqi,
sum(xinzeng) as sum_xinzeng,
grouping__id
from xinzeng_tb
group by shengfen, dishi, riqi
--grouping sets (shengfen, dishi, riqi)
with cube
--cluster by grouping__id
distribute by grouping__id sort by grouping__id desc, sum_xinzeng desc;

OK
shengfen	dishi	riqi	sum_xinzeng	grouping__id
NULL	NULL	NULL	8437	7
NULL	NULL	2019-01-02	4220	6
NULL	NULL	2019-01-03	4217	6
NULL	深圳市	NULL	1518	5
NULL	广州市	NULL	1438	5
NULL	嘉定区	NULL	1434	5
NULL	珠海市	NULL	1389	5
NULL	普陀区	NULL	1369	5
NULL	浦东新区	NULL	1289	5
NULL	深圳市	2019-01-02	770	4
NULL	深圳市	2019-01-03	748	4
NULL	广州市	2019-01-02	726	4
NULL	嘉定区	2019-01-03	721	4
NULL	嘉定区	2019-01-02	713	4
NULL	广州市	2019-01-03	712	4
NULL	珠海市	2019-01-02	708	4
NULL	普陀区	2019-01-03	691	4
NULL	珠海市	2019-01-03	681	4
NULL	普陀区	2019-01-02	678	4
NULL	浦东新区	2019-01-03	664	4
NULL	浦东新区	2019-01-02	625	4
广东省	NULL	NULL	4345	3
上海市	NULL	NULL	4092	3
广东省	NULL	2019-01-02	2204	2
广东省	NULL	2019-01-03	2141	2
上海市	NULL	2019-01-03	2076	2
上海市	NULL	2019-01-02	2016	2
广东省	深圳市	NULL	1518	1
广东省	广州市	NULL	1438	1
上海市	嘉定区	NULL	1434	1
广东省	珠海市	NULL	1389	1
上海市	普陀区	NULL	1369	1
上海市	浦东新区	NULL	1289	1
广东省	深圳市	2019-01-02	770	0
广东省	深圳市	2019-01-03	748	0
广东省	广州市	2019-01-02	726	0
上海市	嘉定区	2019-01-03	721	0
上海市	嘉定区	2019-01-02	713	0
广东省	广州市	2019-01-03	712	0
广东省	珠海市	2019-01-02	708	0
上海市	普陀区	2019-01-03	691	0
广东省	珠海市	2019-01-03	681	0
上海市	普陀区	2019-01-02	678	0
上海市	浦东新区	2019-01-03	664	0
上海市	浦东新区	2019-01-02	625	0

上述语句需要的数据文件

文件下载

  文件名称:测试文件  文件大小:3kb
  下载声明:本站文件大多来自于网络,仅供学习和研究使用,不得用于商业用途,如有版权问题,请联系博猪!
  下载地址:test.txt


Deeplearn, 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明hive 高级聚合函数
喜欢 (1)
[@lc@]
分享 (0)
admin
关于作者:
互联网行业码农一枚/业余铲屎官/数码影音爱好者/二次元

您必须 登录 才能发表评论!