GROUPING SETS 子句是 SELECT 语句的 GROUP BY 子句的扩展。通过 GROUPING SETS 子句,您可采用多种方式对结果分组,而不必使用多个 SELECT 语句来实现这一目的。这就意味着,能够减少响应时间并提高性能。
例如,以下两条查询语句在语义上是等效的。不过,第二个查询通过使用 GROUP BY GROUPING SETS 子句能够更有效地定义分组条件。
使用多个 SELECT 语句的多个分组:
SELECT NULL, NULL, NULL, COUNT( * ) AS Cnt FROM Customers WHERE State IN ( 'MB' , 'KS' ) UNION ALL SELECT City, State, NULL, COUNT( * ) AS Cnt FROM Customers WHERE State IN ( 'MB' , 'KS' ) GROUP BY City, State UNION ALL SELECT NULL, NULL, CompanyName, COUNT( * ) AS Cnt FROM Customers WHERE State IN ( 'MB' , 'KS' ) GROUP BY CompanyName; |
使用 GROUPING SETS 的多个分组:
SELECT City, State, CompanyName, COUNT( * ) AS Cnt FROM Customers WHERE State IN ( 'MB' , 'KS' ) GROUP BY GROUPING SETS( ( City, State ), ( CompanyName ) , ( ) ); |
两种方法均产生相同的结果,如下所示:
City | State | CompanyName | Cnt | |
---|---|---|---|---|
1 | (NULL) | (NULL) | (NULL) | 8 |
2 | (NULL) | (NULL) | Cooper Inc. | 1 |
3 | (NULL) | (NULL) | Westend Dealers | 1 |
4 | (NULL) | (NULL) | Toto's Active Wear | 1 |
5 | (NULL) | (NULL) | North Land Trading | 1 |
6 | (NULL) | (NULL) | The Ultimate | 1 |
7 | (NULL) | (NULL) | Molly's | 1 |
8 | (NULL) | (NULL) | Overland Army Navy | 1 |
9 | (NULL) | (NULL) | Out of Town Sports | 1 |
10 | 'Pembroke' | 'MB' | (NULL) | 4 |
11 | 'Petersburg' | 'KS' | (NULL) | 1 |
12 | 'Drayton' | 'KS' | (NULL) | 3 |
第 2-9 行是按照 CompanyName 分组生成的行,第 10-12 行是按照 City 和 State 的组合进行分组所生成的行,第 1 行是空分组集所表示的总计,它是使用一对成对的圆括号 () 指定的。空分组集表示 GROUP BY 输入中所有行的单个分区。
请注意 NULL 值如何在分组集中不使用的表达式中充当占位符,因为这些结果集必须可以组合。例如,第 2-9 行由查询 (CompanyName) 中的第二个分组集得到。因为分组集未将 City 或 State 作为表达式包含在内,所以对于第 2-9 行,City 和 State 的值中会含有占位符 NULL,而 CompanyName 中的值将含有在 CompanyName 中找到的明确值。
因为 NULL 用作占位符,所以很容易将占位符 NULL 与数据中找到的真正的 NULL 相混淆。为有助于将占位符 NULL 与 NULL 数据区分开来,请使用 GROUPING 函数。
下面的示例说明了如何使用 GROUPING SETS 定制从查询返回的结果,以及如何使用 ORDER BY 子句更好地组织这些结果。以下查询将按各年份 (Year) 中的季度 (Quarter) 返回订单总数以及各年份 (Year) 的总数。先按年份 (Year) 排序,再按季度 (Quarter) 排序可使结果更易于理解:
SELECT Year( OrderDate ) AS Year, Quarter( OrderDate ) AS Quarter, COUNT (*) AS Orders FROM SalesOrders GROUP BY GROUPING SETS ( ( Year, Quarter ), ( Year ) ) ORDER BY Year, Quarter; |
此查询会返回以下结果:
Year | Quarter | Orders | |
---|---|---|---|
1 | 2000 | (NULL) | 380 |
2 | 2000 | 1 | 87 |
3 | 2000 | 2 | 77 |
4 | 2000 | 3 | 91 |
5 | 2000 | 4 | 125 |
6 | 2001 | (NULL) | 268 |
7 | 2001 | 1 | 139 |
8 | 2001 | 2 | 119 |
9 | 2001 | 3 | 10 |
第 1 行和第 6 行分别是 2000 年和 2001 年的订单数小计。第 2-5 行和第 7-9 行是小计行的详细信息行。也就是说,它们按年、按季度显示订单总数。
结果集中没有所有年份中所有季度的总计。要实现此目的,查询必须在 GROUPING SETS 说明中包括空分组说明 '()'。
如果在 GROUP BY 子句中使用空 GROUPING SETS 说明 '()',则会产生一个总计行,对结果中的所有项进行总计。使用总计行时,所有分组表达式的所有值均会包含占位符 NULL。可使用 GROUPING 函数将占位符 NULL 与计算行底层数据中的值后产生的实际 NULL 值区分开来。
可在 GROUPING SETS 子句中指定重复分组说明。此时,SELECT 语句的结果将包含相同的行。
以下查询包括重复分组:
SELECT City, COUNT( * ) AS Cnt FROM Customers WHERE State IN ( 'MB' , 'KS' ) GROUP BY GROUPING SETS( ( City ), ( City ) ); |
此查询会返回以下结果。请注意,由于重复分组的原因,第 1-3 行与第 4-6 行相同:
City | Cnt | |
---|---|---|
1 | 'Drayton' | 3 |
2 | 'Petersburg' | 1 |
3 | 'Pembroke' | 4 |
4 | 'Drayton' | 3 |
5 | 'Petersburg' | 1 |
6 | 'Pembroke' | 4 |
GROUP BY GROUPING SETS 子句的分组语法的解释方式不同于简单的 GROUP BY 子句。例如,GROUP BY (X, Y) 返回按 X 和 Y 值的唯一组合进行分组的结果。而 GROUP BY GROUPING SETS (X, Y) 将指定两个单独的分组集,且这两个分组的结果会结合在一起。也就是说,结果将按 (X) 分组,然后合并到按 (Y) 分组的相同结果中。
为使格式良好,并在表达式复杂的情况下避免造成任何歧义,请在有可能出错的时候为说明的每个单独分组集括上括号。例如,尽管以下两条语句都是正确的,且语义上等效,但建议采用第二种格式:
SELECT * FROM t GROUP BY GROUPING SETS ( X, Y ); SELECT * FROM t GROUP BY GROUPING SETS( ( X ), ( Y ) ); |
如果说聚合函数(Simple UDAF / Generic UDAF)是HQL聚合数据查询或分析的中枢处理器,那GROUP BY可以说是聚合函数的神经了,GROUP BY收集和传递材料,然后交给聚合函数们去处理。这些材料的组织形式显得尤为重要,它们表达着分析者想要的观察维度或视角,管理着聚合函数们的操作对象。
而分析者经常想要在一次分析中从多个维度去获得分析数据,对包含多个维度或多级层次的分析,上卷(roll up)或下钻(drill down)一类就很有分析价值。
我们有时候可以从最细、最多的粒度去做一个查询,然后把结果集导入Excel这个数据分析利器,用数据透视图标进行“上卷”分析;但有时候也行不通,比如说UV这种需要去重的数据,在Excel里用汇总方式进行上卷,就不是纯粹的UV概念了。
所以,对这种情形,在查询过程中,我们就需要获得已经下钻和上卷的数据;如果只有GROUP BY子句,那我们可以写出按各个维度或层次进行GROUP BY的查询语句,然后再通过UNION子句把结果集拼凑起来,但是这样的查询语句显得冗长、笨拙。
为此,HQL像其它很多SQL实现一样,为我们提供了GROUPINGSETS子句来简化查询语句的编写,以下官方CWiki文档很清晰地表达了GROUPING SETS的功能:
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) )
SELECT a, b, SUM(c) 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
因为涉及UNION操作,所以为了遵循UNION对参与合并的数据集合的要求,GROUPING SETS会把在单个GROUP BY逻辑中没有参与GROUP BY的那一列置为NULL值,使它成为常量占位列。这样聚合出来的结果,未被GROUP BY的列将显示为NULL。
但是这样的处理也会引起一个歧义性问题,如果我们分析的表有一些列没有NOT NULL约束,那原始数据中,未被GROUP BY的列可能原本就会出现一些NULL值,这样,GROUPING SETS出来的结果,我们没有办法去区分该列显示的NULL值是原始数据出现的NULL值聚合的结果,还是你因为这列没有参与GROUP BY而被置为NULL值的结果。
为了解决这个歧义问题,HQL又为我们提供了一个Grouping__ID函数(请注意函数名中的下划线是两个!);这个函数没有参数,在有GROUPING SETS子句的情况下,把它直接放在SELECT子句中,像其它列一样,独占一列。它返回的结果是一个看起来像整形数值类型,其实是字符串的值,这个值使用了位图策略(bitvector,位向量),即它的二进制形式中的每1位标示着对应列是否参与GROUP BY,如果某一列参与了GROUP BY,对应位就被置为1,否则为0,根据这个位向量值和对应列是否显示为NULL,我们就可以解决上面提到的歧义问题了。
这样一来,Grouping__ID函数返回值的范围由查询的字段数(除去聚合函数产生的列)决定,如果比如有3列,那位向量为3位,最大值为7。CWiki文档提供了下面的示例:
有下面一个表数据:
Column1 (key)
Column2 (value)
1
NULL
1
1
2
2
3
3
3
NULL
4
5
我们用这样的查询语句去执行查询:
SELECT key, value, GROUPING__ID, count(*) from T1 GROUP BY key,value WITH ROLLUP
将得到如下查询结果:
NULL
NULL
0
6
1
NULL
1
2
1
NULL
3
1
1
1
3
1
2
NULL
1
1
2
2
3
1
3
NULL
1
2
3
NULL
3
1
3
3
3
1
4
NULL
1
1
4
5
3
1
官方文档没有明确说明这个位向量和各列的高低位对应关系,但是从示例我们可以看到,这个位向量的低位对应SELECT子句中的第1列(非聚合列),高位对应最后1列(非聚合列)。
上面的查询用到了WITH ROLLUP子句,它对应SQL中的上卷操作,其实它就是GROUPINGSETS的特例,对应上面第一个表格中的第4种情形;根据官方的CWiki文档解释,GROUP BY 子句加上ROLLUP 子句可用于计算从一个维度进行层级聚合的操作:
GROUP BY a, b, c with ROLLUP assumes that the hierarchy is"a" drilling down to "b" drilling down to "c".
类似地还有WITH CUBE子句,对应SQL中的CUBE操作,它完成对字段列中的所有可能组合(全序集?)进行GROUP BY的功能,正如官方CWiki文档的解释:
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), (c), ( ))
GROUPING SETS增强了GROUP BY的查询表达能力,ROLLUP和CUBE又增强了GROUPING SETS的查询表达能力,这样一来,GROUP BY的形态也变得多样化了,让我们能够在查询阶段就实现更多的分析角度。
还需留意的是:Hive从0.10.0版本才开始有GROUPING SETS的。
---------------------
- 2012/04/01
GROUPING SETS 等效项
使用 GROUPING SETS 的 GROUP BY 子句可以生成一个等效于由多个简单 GROUP BY 子句的 UNION ALL 生成的结果集。GROUPING SETS 可以生成等效于由简单 GROUP BY、ROLLUP 或 CUBE 操作生成的结果。GROUPING SETS、ROLLUP 或 CUBE 的不同组合可以生成等效的结果集。
本主题提供了 GROUPING SETS 等效项的示例。这些示例中使用了以下缩写:
-
Agg():任何聚合函数
-
(arg):参数
UNION ALL 的 GROUPING SETS 等效项
指定 GROUPING SETS (<分组集> [,...n]) 作为 GROUP BY 列表等效于查询的 UNION ALL,其中每个查询将其中一个分组集作为其 GROUP BY 列表。浮点数的聚合返回的结果可能会略有不同。
以下语句是等效的:
复制 | 复制 |
简单 GROUP BY 的 GROUPING SETS 等效项
以下子句可返回相同的总计:
复制 | 复制 |
以下子句可返回相同的单个结果集:
复制 | 复制 |
GROUPING SETS ROLLUP 等效项
输入列表为 n 维的 GROUP BY ROLLUP (<组合元素列表>) 等效于这样的 GROUPING SETS,其中使用其输入列表的所有前缀 (n+1) 作为其 GROUPING SETS。
以下子句是等效的:
复制 | 复制 |
GROUPING SETS CUBE 等效项
输入列表为 n 维的 GROUP BY CUBE (<组合元素列表>) 等效于这样的 GROUPING SETS,其中使用其输入列表的全集(输入列表中维度的 2n 个组合)作为其 GROUPING SETS。
以下子句是等效的:
复制 | 复制 |
以下子句是等效的:
复制 | 复制 |
内部包含分组集的 ROLLUP 中的组合列
以下子句是等效的:
复制 | 复制 | 复制 |
内部包含分组集的 CUBE 中的组合列
以下子句是等效的:
复制 | 复制 | 复制 |
包含 GROUPING SETS、ROLLUP 或 CUBE 的 GROUP BY
以下子句是等效的:
复制 | 复制 |
以下子句是等效的:
复制 | 复制 |
以下子句是等效的:
复制 | 复制 |
包含在 GROUPING SETS 列表中的 ROLLUP
以下子句是等效的:
复制 | 复制 |
包含在分组集中的 ROLLUP
以下子句是等效的:
复制 | 复制 |
请参阅