Hive下的 GROUPING SETS语句

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