Hive sql分组函数grouping sets

# 数据如下：
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_id两个字段分别分组
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两个字段以及以及(dep_id,group_id)组合起来分别分组
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 ： 链接

最详细参考