Hive sql grouping function grouping sets

Nanhu Fisherman's Song 2022-11-24 22:34:59 阅读数:19

hivesqlgroupingfunctiongrouping
# 数据如下:
user_id,dep_id,group_id,salary
10001,a,101,13000
10002,a,101,17000
10003,a,101,9000
10004,a,101,11000
10005,a,101,18000
10006,a,102,16000
10007,a,102,10000
10008,b,103,18000
10009,b,103,11000
10010,b,103,16000
10011,b,103,12000
10012,c,104,16000
10013,c,105,18000
10014,c,105,18000
10015,c,106,12000
10016,c,106,14000
10017,c,106,14000
10018,c,106,9000
10019,c,106,8000
10020,c,106,9000
10021,c,106,10000
-- 建表:
create external table test.salaryinfo(
user_id string,
dep_id string,
group_id string,
salary int
)
row format delimited
fields terminated by ','
stored as textfile
location '/test'
tblproperties("skip.header.line.count"="1")
;
-- 按照dep_id,group_idThe two fields are grouped separately
select dep_id,group_id,count(1)
from salaryinfo
group by dep_id,group_id
grouping sets (dep_id,group_id)
;
-- 按照dep_id,group_idTwo fields as well as (dep_id,group_id)Combine and group separately
select dep_id,group_id,count(1)
from salaryinfo
group by dep_id,group_id
grouping sets (dep_id,group_id,(dep_id,group_id))
;

在这里插入图片描述

grouping sets:

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

cube:

在这里插入图片描述

相关:https://www.cnblogs.com/Allen-rg/p/10648231.html
CUBE : 链接

The most detailed reference

在这里插入图片描述

版权声明:本文为[Nanhu Fisherman's Song]所创,转载请带上原文链接,感谢。 https://javamana.com/2022/328/202211242233198499.html