hive 高级聚合函数

5,499次阅读
没有评论

共计 6105 个字符，预计需要花费 16 分钟才能阅读完成。

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

正文完
请博主喝杯咖啡吧！
post-qrcode
hive Sql
发表至： Sql
2020-04-17
 
admin
版权声明：本站原创文章，由 admin 2020-04-17发表，共计6105字。
转载说明：除特殊说明外本站文章皆由CC-4.0协议发布，转载请注明出处。
mongodb查询之查询条件
hive动态分区读写数据遇到的问题
MySQL字符集介绍
Caused by: java.io.IOException: Malformed ORC file
评论（没有评论）
验证码