|
GROUPING SETS
解释GROUPING SETS
grouping sets 用于多维分析
它是group by的子句，用来简化多个实现group by条件的表的联合查询
官网这样描述
The GROUPING SETS clause in GROUP BY allows us to specify more than one GROUP BY option in the same record set. All GROUPING SET clauses can be logically expressed in terms of several GROUP BY queries connected by UNION. Table-1 shows several such equivalent statements. This is helpful in forming the idea of the GROUPING SETS clause. A blank set ( ) in the GROUPING SETS clause calculates the overall aggregate.
翻译
GROUP BY 中的 GROUPING SETS 子句允许我们在同一记录集中指定多个 GROUP BY 选项。 所有 GROUPING SET 子句都可以根据由 UNION 连接的几个 GROUP BY 查询在逻辑上表达。 表 1 显示了几个这样的等效语句。 这有助于形成 GROUPING SETS 子句的想法。 GROUPING SETS 子句中的空白集 ( ) 计算整体聚合。
表1
| Aggregate Query with GROUPING SETS | Equivalent Aggregate Query with GROUP BY |
|---|
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), 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
| 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) | SELECT a, null, SUM( c ) FROM tab1 GROUP BY a
UNION
SELECT null, b, SUM( c ) FROM tab1 GROUP BY b |
案例
需求：
查询总人数，部门人数,女生人数，部门女生人数，男生人数，部门男生人数
数据：
id,name,gender,deptid
1001,aa,male,10
1002,bb,female,10
1003,cc,male,10
1004,dd,female,20
1005,ee,male,20
1006,ff,female,20
1007,gg,male,30
1008,hh,male,30
1009,ii,female,30
建表并加载数据：
create table staff(id string, name string,gender string, deptid string)
row format delimited
fields terminated by ',';
load data local inpath '/opt/module/hive/data/staff.txt' into table staff;
测试上传是否成功：
hive (sgg)> select * from staff;
OK
staff.id staff.name staff.gender staff.deptid
1001 aa male 10
1002 bb female 10
1003 cc male 10
1004 dd female 20
1005 ee male 20
1006 ff female 20
1007 gg male 30
1008 hh male 30
1009 ii female 30
案例实现：
方法1
使用分别查出的数据进行多次union
SELECT deptid, gender, count(id) FROM staff GROUP BY deptid, gender
UNION
SELECT deptid, null, count(id) FROM staff GROUP BY deptid, null
UNION
SELECT null, gender, count(id) FROM staff GROUP BY null, gender
UNION
SELECT null, null, count(id) FROM staff;
结果
_u3.deptid _u3.gender _u3._c2
NULL NULL 9
NULL female 4
NULL male 5
10 NULL 3
10 female 1
10 male 2
20 NULL 3
20 female 2
20 male 1
30 NULL 3
30 female 1
30 male 2
Time taken: 259.516 seconds, Fetched: 12 row(s)
方法2
使用grouping sets
SELECT
deptid,
gender,
count(id)
FROM staff
GROUP BY deptid, gender
GROUPING SETS ((deptid, gender), deptid, gender, ());
结果
deptid gender _c2
NULL NULL 9
10 NULL 3
20 NULL 3
30 NULL 3
NULL female 4
10 female 1
20 female 2
30 female 1
NULL male 5
10 male 2
20 male 1
30 male 2
Time taken: 30.79 seconds, Fetched: 12 row(s)
|