目录
- 总结：group by、grouping sets、rollup和cube对比
- 0 数据准备：
- 1 GROUPING SETS
- 2 CUBE
- 3 ROLLUP
- 4 Grouping__ID
- 4.1 老版本展示0或1
- 4.2 新版本呢展示数字
GROUPING SETS,GROUPING__ID,CUBE,ROLLUP
这几个分析函数
通常用于OLAP中，
不能累加，而且需要
根据不同维度上钻和下钻的指标统计，比如，分小时、天、月的UV数。
Hive版本为 apache-hive-0.13.1
总结：group by、grouping sets、rollup和cube对比
1、group by
- 主要用来对查询的结果进行分组，相同组合的分组条件在结果集中只显示一行记录。可以添加聚合函数。
2）grouping sets
- 对分组集中指定的组表达式的每个子集执行group by，
group by A,B，grouping sets(A,B)
等价于
group by A union group by B
其中A和B也可以是一个集合，比如group by A,B,C grouping sets((A,B),(A,C))。3）rollup
- 在指定表达式的每个层次级别创建分组集。实现需统计的字段从右到左多级递减，显示统计某一层次结构的聚合。按照指定的字段，进行维度组合查询，它相当于是 cube的子集，cube是所有维度，rollup是部分维度。
- 例如：统计的维度是a,b, 则组合后的维度有: (a,b), (a), ()
- group by A,B,C with rollup首先会对(A、B、C)进行group by，然后对(A、B)进行group by，然后是(A)进行group by，最后对全表进行group by操作。
4）cube
- 为指定表达式集的每个可能组合创建分组集。首先会对(A、B、C)进行group by，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，( C)，最后对全表进行group by操作。
0 数据准备：
2015-03,2015-03-10,cookie1
2015-03,2015-03-10,cookie5
2015-03,2015-03-12,cookie7
2015-04,2015-04-12,cookie3
2015-04,2015-04-13,cookie2
2015-04,2015-04-13,cookie4
2015-04,2015-04-16,cookie4
2015-03,2015-03-10,cookie2
2015-03,2015-03-10,cookie3
2015-04,2015-04-12,cookie5
2015-04,2015-04-13,cookie6
2015-04,2015-04-15,cookie3
2015-04,2015-04-15,cookie2
2015-04,2015-04-16,cookie1
CREATE EXTERNAL TABLE lxw1234 (
month STRING,
day STRING,
cookieid STRING
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
stored as textfile location '/tmp/lxw11/';
hive> select * from lxw1234;
OK
2015-03 2015-03-10 cookie1
2015-03 2015-03-10 cookie5
2015-03 2015-03-12 cookie7
2015-04 2015-04-12 cookie3
2015-04 2015-04-13 cookie2
2015-04 2015-04-13 cookie4
2015-04 2015-04-16 cookie4
2015-03 2015-03-10 cookie2
2015-03 2015-03-10 cookie3
2015-04 2015-04-12 cookie5
2015-04 2015-04-13 cookie6
2015-04 2015-04-15 cookie3
2015-04 2015-04-15 cookie2
2015-04 2015-04-16 cookie11 GROUPING SETS
- 在一个GROUP BY查询中，根据不同的维度组合进行聚合，等价于将不同维度的GROUP BY结果集进行UNION ALL
SELECT
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID
FROM lxw1234
GROUP BY month,day
GROUPING SETS (month,day)
ORDER BY GROUPING__ID;
month day uv GROUPING__ID
------------------------------------------------
2015-03 NULL 5 1
2015-04 NULL 6 1
NULL 2015-03-10 4 2
NULL 2015-03-12 1 2
NULL 2015-04-12 2 2
NULL 2015-04-13 3 2
NULL 2015-04-15 2 2
NULL 2015-04-16 2 2
等价于
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM lxw1234 GROUP BY month
UNION ALL
SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM lxw1234 GROUP BY day- 再如：
SELECT
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID
FROM lxw1234
GROUP BY month,day
GROUPING SETS (month,day,(month,day))
ORDER BY GROUPING__ID;
month day uv GROUPING__ID
------------------------------------------------
2015-03 NULL 5 1
2015-04 NULL 6 1
NULL 2015-03-10 4 2
NULL 2015-03-12 1 2
NULL 2015-04-12 2 2
NULL 2015-04-13 3 2
NULL 2015-04-15 2 2
NULL 2015-04-16 2 2
2015-03 2015-03-10 4 3
2015-03 2015-03-12 1 3
2015-04 2015-04-12 2 3
2015-04 2015-04-13 3 3
2015-04 2015-04-15 2 3
2015-04 2015-04-16 2 3
等价于
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM lxw1234 GROUP BY month
UNION ALL
SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM lxw1234 GROUP BY day
UNION ALL
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM lxw1234 GROUP BY month,day
其中的 GROUPING__ID，表示结果属于哪一个分组集合。2 CUBE
根据GROUP BY的维度的所有组合进行聚合。
SELECT
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID -- 两条下划线
FROM lxw1234
GROUP BY month,day
WITH CUBE
ORDER BY GROUPING__ID;
month day uv GROUPING__ID
--------------------------------------------
NULL NULL 7 0
2015-03 NULL 5 1
2015-04 NULL 6 1
NULL 2015-04-12 2 2
NULL 2015-04-13 3 2
NULL 2015-04-15 2 2
NULL 2015-04-16 2 2
NULL 2015-03-10 4 2
NULL 2015-03-12 1 2
2015-03 2015-03-10 4 3
2015-03 2015-03-12 1 3
2015-04 2015-04-16 2 3
2015-04 2015-04-12 2 3
2015-04 2015-04-13 3 3
2015-04 2015-04-15 2 3
等价于
SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM lxw1234
UNION ALL
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM lxw1234 GROUP BY month
UNION ALL
SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM lxw1234 GROUP BY day
UNION ALL
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM lxw1234 GROUP BY month,daySELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM lxw1234
等价于
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM lxw1234 GROUP BY month,day
GROUPING_sets(())3 ROLLUP
是CUBE的子集，以最左侧的维度为主，从该维度进行层级聚合。
- 比如，以month维度进行层级聚合：
SELECT
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID
FROM lxw1234
GROUP BY month,day
WITH ROLLUP
ORDER BY GROUPING__ID;
等价于
SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM lxw1234
UNION ALL
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM lxw1234 GROUP BY month
UNION ALL
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM lxw1234 GROUP BY month,day
month day uv GROUPING__ID
---------------------------------------------------
NULL NULL 7 0
2015-03 NULL 5 1
2015-04 NULL 6 1
2015-03 2015-03-10 4 3
2015-03 2015-03-12 1 3
2015-04 2015-04-12 2 3
2015-04 2015-04-13 3 3
2015-04 2015-04-15 2 3
2015-04 2015-04-16 2 3
可以实现这样的上钻过程：
月天的UV->月的UV->总UV- 把month和day调换顺序，则以day维度进行层级聚合：
SELECT
day,
month,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID
FROM lxw1234
GROUP BY day,month
WITH ROLLUP
ORDER BY GROUPING__ID;
day month uv GROUPING__ID
-------------------------------------------------------
NULL NULL 7 0
2015-04-13 NULL 3 1
2015-03-12 NULL 1 1
2015-04-15 NULL 2 1
2015-03-10 NULL 4 1
2015-04-16 NULL 2 1
2015-04-12 NULL 2 1
2015-04-12 2015-04 2 3
2015-03-10 2015-03 4 3
2015-03-12 2015-03 1 3
2015-04-13 2015-04 3 3
2015-04-15 2015-04 2 3
2015-04-16 2015-04 2 3
可以实现这样的上钻过程：
天月的UV->天的UV->总UV
（这里，根据天和月进行聚合，和根据天聚合结果一样，因为有父子关系，如果是其他维度组合的话，就会不一样）这种函数，需要结合实际场景和数据去使用和研究，只看说明的话，很难理解。
4 Grouping__ID
4.1 老版本展示0或1
当为列显示聚合时，其值为空。 如果列本身有一些空值，这可能会发生冲突。 需要有某种方法来识别列中的 NULL，这意味着聚合和列中的 NULL，这意味着值。 GROUPING__ID 函数就是解决这个问题的方法。
此函数返回一个与每列是否存在相对应的位向量。 对于每一列，如果该列已在该行中聚合，则为结果集中的一行生成“1”值，否则该值为“0”。 这可用于区分数据中何时存在空值。
考虑以下示例：
SELECT key, value, GROUPING__ID, count(*)
FROM T1
GROUP BY key, value WITH ROLLUP;
标记为0的，是原数据的列，而不是由于聚合导致为NULL的列。
4.2 新版本呢展示数字
select a,b,c,grouping__id
from test
group by a,b,c
grouping sets (
(a,b),
(a, c),
(a, b, c)
)1、在Hive中grouping__id的数字规则：根据group by 字段 ，从低到高，group sets中出现的字段是1没出现是0。
因此，上述SQL中 (a, b)对应 011；(a,c)对应 101
2、Spark中grouping__id的数字规则：根据group by 字段 ，从高到低，group sets中出现的字段是0没出现是1
因此，上述SQL中 (a, b)对应 001；(a,c)对应 010
注意： grouping_id函数也是计算分组级别的函数，注意如果要使用grouping_id函数那必须得有group by字句，而且group by字句的中的列与grouping_id函数的参数必须相等。比如group by A,B，那么必须使用grouping_id（A,B）。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
