hive中group by的增强特性:grouping__id,grouping,groupin sets,cube,rollup


本博客来聊聊SELECT语句的GROUP BY子句的增强聚合特性。

模拟数据(分隔符为’\t’)

上海市	浦东新区	2019-01-02	2019-01-02 8:50:32	20
上海市	浦东新区	2019-01-02	2019-01-02 9:50:32	60
上海市	浦东新区	2019-01-02	2019-01-02 10:50:32	55
上海市	浦东新区	2019-01-02	2019-01-02 11:50:32	70
上海市	浦东新区	2019-01-02	2019-01-02 13:50:32	80
上海市	浦东新区	2019-01-02	2019-01-02 15:50:32	100
上海市	浦东新区	2019-01-02	2019-01-02 16:50:32	90
上海市	浦东新区	2019-01-02	2019-01-02 17:50:32	95
上海市	浦东新区	2019-01-02	2019-01-02 18:50:32	55
上海市	浦东新区	2019-01-03	2019-01-02 8:50:32	23
上海市	浦东新区	2019-01-03	2019-01-02 9:50:32	63
上海市	浦东新区	2019-01-03	2019-01-02 10:50:32	53
上海市	浦东新区	2019-01-03	2019-01-02 11:50:32	73
上海市	浦东新区	2019-01-03	2019-01-02 13:50:32	83
上海市	浦东新区	2019-01-03	2019-01-02 15:50:32	130
上海市	浦东新区	2019-01-03	2019-01-02 16:50:32	93
上海市	浦东新区	2019-01-03	2019-01-02 17:50:32	93
上海市	浦东新区	2019-01-03	2019-01-02 18:50:32	53
上海市	普陀区	2019-01-02	2019-01-02 8:50:32	26
上海市	普陀区	2019-01-02	2019-01-02 9:50:32	66
上海市	普陀区	2019-01-02	2019-01-02 10:50:32	56
上海市	普陀区	2019-01-02	2019-01-02 11:50:32	76
上海市	普陀区	2019-01-02	2019-01-02 13:50:32	86
上海市	普陀区	2019-01-02	2019-01-02 15:50:32	120
上海市	普陀区	2019-01-02	2019-01-02 16:50:32	96
上海市	普陀区	2019-01-02	2019-01-02 17:50:32	96
上海市	普陀区	2019-01-02	2019-01-02 18:50:32	56
上海市	普陀区	2019-01-03	2019-01-02 8:50:32	28
上海市	普陀区	2019-01-03	2019-01-02 9:50:32	66
上海市	普陀区	2019-01-03	2019-01-02 10:50:32	56
上海市	普陀区	2019-01-03	2019-01-02 11:50:32	76
上海市	普陀区	2019-01-03	2019-01-02 13:50:32	86
上海市	普陀区	2019-01-03	2019-01-02 15:50:32	131
上海市	普陀区	2019-01-03	2019-01-02 16:50:32	96
上海市	普陀区	2019-01-03	2019-01-02 17:50:32	96
上海市	普陀区	2019-01-03	2019-01-02 18:50:32	56
上海市	嘉定区	2019-01-02	2019-01-02 8:50:32	28
上海市	嘉定区	2019-01-02	2019-01-02 9:50:32	68
上海市	嘉定区	2019-01-02	2019-01-02 10:50:32	58
上海市	嘉定区	2019-01-02	2019-01-02 11:50:32	78
上海市	嘉定区	2019-01-02	2019-01-02 13:50:32	88
上海市	嘉定区	2019-01-02	2019-01-02 15:50:32	139
上海市	嘉定区	2019-01-02	2019-01-02 16:50:32	98
上海市	嘉定区	2019-01-02	2019-01-02 17:50:32	98
上海市	嘉定区	2019-01-02	2019-01-02 18:50:32	58
上海市	嘉定区	2019-01-03	2019-01-02 8:50:32	35
上海市	嘉定区	2019-01-03	2019-01-02 9:50:32	68
上海市	嘉定区	2019-01-03	2019-01-02 10:50:32	58
上海市	嘉定区	2019-01-03	2019-01-02 11:50:32	78
上海市	嘉定区	2019-01-03	2019-01-02 13:50:32	88
上海市	嘉定区	2019-01-03	2019-01-02 15:50:32	145
上海市	嘉定区	2019-01-03	2019-01-02 16:50:32	98
上海市	嘉定区	2019-01-03	2019-01-02 17:50:32	98
上海市	嘉定区	2019-01-03	2019-01-02 18:50:32	53
广东省	深圳市	2019-01-02	2019-01-02 8:50:32	29
广东省	深圳市	2019-01-02	2019-01-02 9:50:32	69
广东省	深圳市	2019-01-02	2019-01-02 10:50:32	59
广东省	深圳市	2019-01-02	2019-01-02 11:50:32	79
广东省	深圳市	2019-01-02	2019-01-02 13:50:32	89
广东省	深圳市	2019-01-02	2019-01-02 15:50:32	188
广东省	深圳市	2019-01-02	2019-01-02 16:50:32	99
广东省	深圳市	2019-01-02	2019-01-02 17:50:32	99
广东省	深圳市	2019-01-02	2019-01-02 18:50:32	59
广东省	深圳市	2019-01-03	2019-01-02 8:50:32	29
广东省	深圳市	2019-01-03	2019-01-02 9:50:32	69
广东省	深圳市	2019-01-03	2019-01-02 10:50:32	59
广东省	深圳市	2019-01-03	2019-01-02 11:50:32	79
广东省	深圳市	2019-01-03	2019-01-02 13:50:32	89
广东省	深圳市	2019-01-03	2019-01-02 15:50:32	166
广东省	深圳市	2019-01-03	2019-01-02 16:50:32	99
广东省	深圳市	2019-01-03	2019-01-02 17:50:32	99
广东省	深圳市	2019-01-03	2019-01-02 18:50:32	59
广东省	广州市	2019-01-02	2019-01-02 8:50:32	34
广东省	广州市	2019-01-02	2019-01-02 9:50:32	65
广东省	广州市	2019-01-02	2019-01-02 10:50:32	59
广东省	广州市	2019-01-02	2019-01-02 11:50:32	67
广东省	广州市	2019-01-02	2019-01-02 13:50:32	89
广东省	广州市	2019-01-02	2019-01-02 15:50:32	155
广东省	广州市	2019-01-02	2019-01-02 16:50:32	99
广东省	广州市	2019-01-02	2019-01-02 17:50:32	99
广东省	广州市	2019-01-02	2019-01-02 18:50:32	59
广东省	广州市	2019-01-03	2019-01-02 8:50:32	29
广东省	广州市	2019-01-03	2019-01-02 9:50:32	69
广东省	广州市	2019-01-03	2019-01-02 10:50:32	59
广东省	广州市	2019-01-03	2019-01-02 11:50:32	79
广东省	广州市	2019-01-03	2019-01-02 13:50:32	89
广东省	广州市	2019-01-03	2019-01-02 15:50:32	133
广东省	广州市	2019-01-03	2019-01-02 16:50:32	88
广东省	广州市	2019-01-03	2019-01-02 17:50:32	99
广东省	广州市	2019-01-03	2019-01-02 18:50:32	67
广东省	珠海市	2019-01-02	2019-01-02 8:50:32	29
广东省	珠海市	2019-01-02	2019-01-02 9:50:32	70
广东省	珠海市	2019-01-02	2019-01-02 10:50:32	59
广东省	珠海市	2019-01-02	2019-01-02 11:50:32	79
广东省	珠海市	2019-01-02	2019-01-02 13:50:32	88
广东省	珠海市	2019-01-02	2019-01-02 15:50:32	126
广东省	珠海市	2019-01-02	2019-01-02 16:50:32	99
广东省	珠海市	2019-01-02	2019-01-02 17:50:32	99
广东省	珠海市	2019-01-02	2019-01-02 18:50:32	59
广东省	珠海市	2019-01-03	2019-01-02 8:50:32	29
广东省	珠海市	2019-01-03	2019-01-02 9:50:32	69
广东省	珠海市	2019-01-03	2019-01-02 10:50:32	59
广东省	珠海市	2019-01-03	2019-01-02 11:50:32	69
广东省	珠海市	2019-01-03	2019-01-02 13:50:32	79
广东省	珠海市	2019-01-03	2019-01-02 15:50:32	133
广东省	珠海市	2019-01-03	2019-01-02 16:50:32	99
广东省	珠海市	2019-01-03	2019-01-02 17:50:32	85
广东省	珠海市	2019-01-03	2019-01-02 18:50:32	59

创建表;

create table xinzeng_tb (
shengfen string,
dishi string,
riqi string,
shijian string,
xinzeng int
)
row format delimited fields terminated by '\t'
location 'hdfs://bigdata-training01.fuyun.com:8020/user/hive/warehouse-3.1.1/db_window.db'

加载数据:

load data local inpath '/opt/datas/xinzeng_tb' into table xinzeng_tb

GROUPING SETS

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

按省份和地市统计新增数

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
Time taken: 128.156 seconds, Fetched: 8 row(s)

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

grouping sets语句group by语句
SELECT a, b, SUM© FROM tab1 GROUP BY a, b GROUPING SETS ( (a,b) )SELECT a, b, SUM© FROM tab1 GROUP BY a, b
SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b GROUPING SETS ( (a,b), a)SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b UNION SELECT a, null, SUM( c ) FROM tab1 GROUP BY a
SELECT a,b, SUM( c ) FROM tab1 GROUP BY a, b GROUPING SETS (a,b)SELECT a, null, SUM( c ) FROM tab1 GROUP BY a UNION SELECT null, b, SUM( c ) FROM tab1 GROUP BY b
SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b GROUPING SETS ( (a, b), a, b, ( ) )SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b UNION SELECT a, null, SUM( c ) FROM tab1 GROUP BY a, null UNION SELECT null, b, SUM( c ) FROM tab1 GROUP BY null, b UNION SELECT null, null, SUM( c ) FROM tab1

Grouping__ID(两个下划线_)

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

例1:

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)

等价于:

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)

例2:

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)

例2sql语句解释:
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), ©, ( )).

rollups
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), ( )).

rollup

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
Time taken: 75.995 seconds, Fetched: 21 row(s)

cube

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 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
Time taken: 72.189 seconds, Fetched: 45 row(s)

hive.new.job.grouping.set.cardinality设置每条数据复制的份数

以上都是group by的增强特性,是将多个group by逻辑写在一个SQL语句的便利写法。
set hive.new.job.grouping.set.cardinality=32,这条设置的意义在于告知解释器,group by之前,每条数据复制量在32份以内。

select中的字段是完整的A,B,C,但是我们知道由于group by的存在,select 字段本不应该出现非group by字段的,所以这里我们要特别说明,如果解释器发现group by A,C 但是select A,B,C 那么运行时会将所有from 表取出的结果复制一份,B都置为null,也就是在结果中,B都为null。

官方文档:https://cwiki.apache.org/confluence/display/Hive/Enhanced+Aggregation,+Cube,+Grouping+and+Rollup

### Hive SQL Grouping Sets Usage and Examples In Hive, `GROUPING SETS` allow aggregation at multiple levels simultaneously. This feature extends beyond simple GROUP BY operations to provide more flexible data summarization capabilities. For instance, consider a table named `sales_data`, which contains sales information across different regions and product categories: ```sql CREATE TABLE IF NOT EXISTS sales_data ( region STRING, category STRING, amount DOUBLE ); ``` To calculate total sales per region as well as overall totals without repeating rows manually, one can use `GROUPING SETS`. Here’s an example query demonstrating this functionality[^1]: ```sql SELECT COALESCE(region, 'Total') AS region, SUM(amount) AS total_sales FROM sales_data GROUP BY GROUPING SETS ((region), ()) ORDER BY region; ``` This will produce results showing both individual regional sums along with an additional row representing the grand total of all sales amounts. Another common scenario involves analyzing sales not only by each combination of region and category but also obtaining subtotals for just the regions or categories alone. The following code snippet illustrates how such multi-level aggregations could be achieved using `GROUPING SETS`[^2]: ```sql SELECT COALESCE(region, 'All Regions') AS region, COALESCE(category, 'All Categories') AS category, SUM(amount) AS total_sales FROM sales_data GROUP BY GROUPING SETS ((region, category), (region), (category), ()) ORDER BY region NULLS LAST, category NULLS LAST; ``` The above script generates detailed breakdowns alongside higher-level summaries within a single result set efficiently. Additionally, when working with complex queries involving multiple dimensions like time periods combined with geographical areas or other attributes, leveraging `CUBE` or `ROLLUP` operators—which internally utilize `GROUPING SETS`—can simplify syntax while enhancing readability significantly[^3].
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值