hive 多组group by hive中group by用法

转载

幸福的地图 2024-08-24 07:14:15

文章标签 hive 多组group by hive group by增强特性 grouping__id grouping sets 文章分类 Hive 大数据

上海市	浦东新区	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。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：android studio modules 本地插件使用 android studio 本地gradle

下一篇：ios 图片库 iphoto图库

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯