Enhanced Aggregation, Cube, Grouping and Rollup 优化聚合函数

 

 

翻译自 https://cwiki.apache.org/confluence/display/Hive/Enhanced+Aggregation%2C+Cube%2C+Grouping+and+Rollup

这篇文章是用来描述优化集合函数 group by 的， 这些函数是0.10版本以后才被添加进hive的，并且在0.11中做了优化。

GROUPING SETS clause （Grouping的用法）
GROUPING SETS子句中的GROUP BY允许我们在同一个记录集指定多个group by。

所有的分组集子句在逻辑上可以表现为多个GROUP BY的UNION连接查询。Table-1 显示了几个等价语句，这可以帮助你形成grouping by 的概念。在grouping by中的空白set()，指的是全集。

Table 1

 

 

Grouping Set	等价于
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

 

 Grouping__ID function （Grouping__ID函数）

 当需要被聚合的 列的值为null时，可能会因为null而发生冲突。

这里就需要一些方法来识别null值，Grouping__ID 函数就是用来解决这个的。

这个函数对于每一列返回一个位向量，对于每一列，如果该行被聚合了就产生"1"，否则为"0"，这可以用来区分数据中有没有null.

看下面的例子

表 2.1

 

column1 (key)	column2 (value)
1	NULL
1	1
2	2
3	3
3	NULL
4	5

 

 

 跑这个SQL会有下面的结果

SELECT key, value, GROUPING__ID, count(*) from T1 GROUP BY key, value WITH ROLLUP

表 2.2

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

 

 请注意，第三列是被选中的列的位向量。 

对于第一行，没有列被选中。 

对于第二行中，只有第一列被选择，这可以解释为2的计数。 

对于在第三行，无论列被选中（第二列恰好是空值），这可以解释为1的计数。

 

------还是解释下上面的东西

根据上面的GROUP__ID 分应该有4种情况

 

key	value	Group__ID
NULL(谁也不选中)	NULL(谁也不选中)	0(第一种情况)
任意值(选择任意一个)	NULL(谁也不选中)	1(第二种情况)
NULL(谁也不选中)	任意值(选择任意一个)	2(第三种情况)
任意值(选择任意一个)	任意值(选择任意一个)	3(第四种情况)

 

 

再来看 表2.2

第一种情况  谁也不选中  Group__ID=0   一共六行都可以谁也不选中，所以 count=6 

第二种情况  key任意值,而value 谁也不选 Group__ID=1 一共有4种情况

 

1	NULL
2	NULL
3	NULL
4	NULL

 

 

 

所以也就有了表2.2中的第二行,第五行,第七行,第10行,

具体解释下 第二行为什么count=2 是因为 1个不选(派生出来的),另外一个是本来就是NULL,同理也就解释了为什么 Group__ID=0为什么是6 而不是12,

同样的第五行 count=1 是因为 一行是派生出来,没有key=2 value=null 的列 所以count=1

第七行又是第二行的情况 所以count=2

第十行就不累述

 

第三种情况Group__ID=2 由于key没有不被选择的所以一个也没有

第四种情况大家都是任意被选中 Group__ID=3

当key=1时

有2个

1	NULL
1	1

所以coun=1 表2.2的 第三,第四行

key=2时

2

2

所以count=1 表2.2的第六行

key=3时

3	3
3	NULL

表2.2的第八,第九行

等等