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

最新推荐文章于 2022-12-12 18:39:51 发布
最新推荐文章于 2022-12-12 18:39:51 发布
阅读量5.5k 收藏 16
点赞数 2
分类专栏： # hive 大数据相关 文章标签： hive group by增强特性 grouping__id grouping sets cube
版权所有，转载请注明原文链接。 文中有不妥或者错误的地方还望指出，以免误人子弟。如果觉得本文对你有所帮助不妨【推荐】一下！如果你有更好的建议，可以给我留言讨论，共同进步！ 再次感谢您耐心的读完本篇文章。
本文链接：https://blog.csdn.net/lz6363/article/details/88095541
版权

目录

本博客来聊聊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__ID位运算之谜
石榴姐yyds
03-14 178
通过纯SQL实现GROUPING__ID的逆向解析，我们解锁了以下能力：动态维度组合识别：实时解析任意维度的聚合状态智能元数据生成：自动生成可读的维度组合描述层级钻取控制：精确控制不同聚合层级的展示逻辑原生性能优势：避免UDF带来的性能损耗这种将二进制思维融入SQL操作的方法，不仅提升了多维分析的灵活性，更让我们在PB级数据场景下依然保持高效。当面对海量数据的OLAP分析需求时，掌握这项技能将使您从维度森林中精准定位数据价值。
Hive cube / rollup / grouping sets/GROUPING__ID用法
Top5软件工程硕士，先后在京东、字节从事多年Java后端开发、实时和离线大数据开发
01-31 748
表结构如下： CREATE EXTERNAL TABLE `tmp.shop_detail_tb`( `id` string COMMENT '', `name` string COMMENT '', `type` string COMMENT '', `amt` string COMMENT '', `month` string COMMENT '' ) PARTITIONED BY ( `dt` string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t
【Hive】多维函数 『grouping sets() | with cube | with rollup 』
渐渐的忘记，赶不上明天（读研ing，2024年毕业）
12-12 5116
1. 语法 2. 例子
Hive.GROUPING SETS
辰采星
03-30 2万+
如果说聚合函数（Simple UDAF / Generic UDAF）是HQL聚合数据查询或分析的中枢处理器，那GROUP BY可以说是聚合函数的神经了，GROUP BY收集和传递材料，然后交给聚合函数们去处理。这些材料的组织形式显得尤为重要，它们表达着分析者想要的观察维度或视角，管理着聚合函数们的操作对象。        而分析者经常想要在一次分析中从多个维度去获得分析数据，对包含多个维度或多
sqlserver Group by Grouping
ZhangPeng的博客
06-02 703
有的时候我们要从数据库里把数据组织成树结构再展现到页面上 像下面这样 今天我们用Group 和Grouping实现它，并总结一下它俩。 先看一下概念,再用代码一点一点去理解它们，最后我会给出完整的代码 Group By : 语句用于结合合计函数，根据一个或多个列对结果集进行分组。 Grouping :指示是否聚合 GROUP BY 列表中的指定列表达式。 在结果集中，
group by的增强
weixin_43814593的博客
04-23 304
分组函数的嵌套 目的：求部门平均工资的最大值 select max(avg(sal)) from emp group by deptno; GROUP BY 语句的增强 select deptno,job,sum(sal) from emp group by deptno,job order by deptno; select sum(sal) from emp group by deptno;...
增强group by
cml2016的博客
03-23 176
总结： 1.提示当使用group by时，未在group by部分用到的表列在select部分出现时必须使用分组函数。 2.having子句允许用户指定对一个记录组的搜索条件。而通常的where查询条件只针对单记录，不...
Hive cube / rollup / grouping sets/GROUPING__ID用法详解
weixin_43542605的博客
01-06 1365
详细介绍cube / rollup / grouping sets/GROUPING__ID这几个函数的用法以及区别
Hive分析窗口函数 GROUPING SETS、GROUPING__ID
菜如张学清的博客
06-26 5347
目录 1.概述 2.数据准备 2.1数据格式 2.2创表语句 3.GROUPING SETS和GROUPING__ID 3.1 GROUPING SETS例1 3.1.1查询语句 3.1.2等价于 3.1.3查询结果 3.1.4 结果说明 3.2GROUPING SETS 例2 3.2.1 查询语句 3.2.2 等价于 3.2.3 查询结果 3.3GROU...
HIVE中grouping__id使用场景
jiantianming2的博客
01-28 6824
grouping__id值的计算方式 例子： step01：数据准备如下 ​create table temp.temp_xw_20180127 as select '2018-01' as dt_month, null as type, 12 total union all select '2018-01' as dt_month, '收入' as type, 100 tot
对GROUP BY的增强
u011868279的博客
11-18 451
对GROUP BY的增强
Hive -- 窗口函数(五) GROUPING SETS,GROUPING__ID,CUBE,ROLLUP
认真学代码的shell
08-13 817
GROUPING SETS,GROUPING__ID,CUBE,ROLLUP 这几个分析函数通常用于OLAP中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如，分小时、天、月的UV数。 Hive版本为 apache-hive-0.13.1 0 数据准备： 2015-03,2015-03-10,cookie1 2015-03,2015-03-10,cookie5 2015-03,2015-03-12,cookie7 2015-04,2015-04-12,cookie3 2015-04,2015-04
hive学习之grouping set /cube/rollup
cclovezbf的博客
09-05 1161
就是在我们根据多个维度分组的时候，例如group by a,b,c,d 有时候我们也需要这几个维度中部分维度的聚合值 例如group by a,b。这个时候通过group by a,b,c,d grouping sets((a,b,c,d),(a,b))即可，看起来简洁，使用方便。a=1，b=null ，sum(c)=1 这个到底是根据a 分的组 b默认是null ，还是根据ab 分组，a=1 b=null。需求2，同时算出需求1也就是根据a，b分组的sum(c)，和根据a分组后的sum(c)
group by、grouping sets、rollup和cube方法详解
kw-home
11-18 7431
在平时的工作中，经常有按照不同维度筛选和统计数据的需求。拿视频会员订单数据来说吧，运营人员要查看深圳市的成功下单数或则深圳市某一种产品的成功下单数或者某一种产品的所有成功下单数时，每天的订单数又很大，现查的话按照不同的维度去查询又很慢。此时本篇文章或许会帮助到你。 group by：主要用来对查询的结果进行分组，相同组合的分组条件在结果集中只显示一行记录。可以添加聚合函数。 grouping sets：对分组集中指定的组表达式的每个子集执行group by，group by A,B grouping s
hive 0.10 0.11新增特性综述
yukangkk的技术博客
09-16 4351
我们的hive版本升迁经历了0.7.1 -> 0.8.1 -> 0.9.0，并且线上shark所依赖的hive版本也停留在0.9.0上，在这些版本上有我们自己的bug fix patch和feature enhancement。但是Hive的版本升级很快，新版本中修复了大量bug，新增了很多功能，非常令人兴奋，其中包括对未来hadoop升级为YARN的支持。所以我们准备将hive版本升级为0.11
高级sql学习--增强group by！！！
事后诸葛亮的程序人生（微信：zq9017197）
10-15 2059
<br />总结：<br />1.提示当使用group by时，未在group by部分用到的表列在select部分出现时必须使用分组函数。<br />2.having子句允许用户指定对一个记录组的搜索条件。而通常的where查询条件只针对单记录，不针对记录组。<br />3.在Group By子句中，必须采用表达式的全称，而不能够采用其别名.<br />4.采用Group语句会自动对纪录进行排序。在使用Order By排序子句对统计结果进行重新排序的时候，要慎重。<br />5.如果想把某个字段当作第一顺
Group by，Grouping，Rollup
凉风有信
08-15 443
Group By : 语句用于结合合计函数，根据一个或多个列对结果集进行分组(最基本）。Grouping :指示是否聚合 GROUP BY 列表中的指定列表达式。 在结果集中，如果 GROUPING 返回 1 则指示聚合（其实就是一个函数）； 返回 0 则指示不聚合。 如果指定了 GROUP BY，则 GROUPING 只能用在 SELECT 列表、HAVING 和 ORDER BY 子句中。
Hive之Grouping Sets函数
热门推荐
数据小白的进阶之路
03-17 3万+
最近在看一些关于Hive优化的东西，看到一个很好用的函数：Grouping Sets函数，今天就先总结一下关于这个函数的用法！ 在一个GROUP BY 查询中，根据不同的维度组合进行聚合，等价于将不同维度的GROUP BY结果进行UNION ALL操作。GROUPING SETS就是一种将多个GROUP BY逻辑UNION写在一个HIVE SQL语句中的便利写法。GROUPING SETS会把在...
Hive中数据Grouping_set和GROUPING__ID的关系整理
Yuli_li的专栏
03-22 2487
select "11+++++++++++++++++++++++++++++++++商品点击各维度聚合表++++++++++++++++++++++++++++++++h_temp_goods_hit_grouping"; select "------ 127 27941970 goods_id,create_month "; select "------ 191 13887202 goods_id,create_quarter "; select "------ .
hive grouping sets
最新发布
02-12
### 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、付费专栏及课程。

余额充值