Hive分析窗口函数(五) GROUPING SETS,GROUPING__ID,CUBE,ROLLUP

问题导读

1.GROUPING SETS与另外哪种方式等价？
2.根据GROUP BY的维度的所有组合进行聚合由哪个关键字完成？
3.ROLLUP与ROLLUP关系是什么？





接上篇
Hive分析窗口函数(四) LAG,LEAD,FIRST_VALUE,LAST_VALUE

GROUPING SETS,GROUPING__ID,CUBE,ROLLUP

这几个分析函数通常用于OLAP中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如，分小时、天、月的UV数。

Hive版本为 apache-hive-0.13.1

数据准备：

1. 2015-03,2015-03-10,cookie1
   
2. 2015-03,2015-03-10,cookie5
   
3. 2015-03,2015-03-12,cookie7
   
4. 2015-04,2015-04-12,cookie3
   
5. 2015-04,2015-04-13,cookie2
   
6. 2015-04,2015-04-13,cookie4
   
7. 2015-04,2015-04-16,cookie4
   
8. 2015-03,2015-03-10,cookie2
   
9. 2015-03,2015-03-10,cookie3
   
10. 2015-04,2015-04-12,cookie5
    
11. 2015-04,2015-04-13,cookie6
    
12. 2015-04,2015-04-15,cookie3
    
13. 2015-04,2015-04-15,cookie2
    
14. 2015-04,2015-04-16,cookie1
    
15. 
    
16. CREATE EXTERNAL TABLE lxw1234 (
    
17. month STRING,
    
18. day STRING, 
    
19. cookieid STRING 
    
20. ) ROW FORMAT DELIMITED 
    
21. FIELDS TERMINATED BY ',' 
    
22. stored as textfile location '/tmp/lxw11/';
    
23. 
    
24. 
    
25. hive> select * from lxw1234;
    
26. OK
    
27. 2015-03 2015-03-10      cookie1
    
28. 2015-03 2015-03-10      cookie5
    
29. 2015-03 2015-03-12      cookie7
    
30. 2015-04 2015-04-12      cookie3
    
31. 2015-04 2015-04-13      cookie2
    
32. 2015-04 2015-04-13      cookie4
    
33. 2015-04 2015-04-16      cookie4
    
34. 2015-03 2015-03-10      cookie2
    
35. 2015-03 2015-03-10      cookie3
    
36. 2015-04 2015-04-12      cookie5
    
37. 2015-04 2015-04-13      cookie6
    
38. 2015-04 2015-04-15      cookie3
    
39. 2015-04 2015-04-15      cookie2
    
40. 2015-04 2015-04-16      cookie1

复制代码

GROUPING SETS

在一个GROUP BY查询中，根据不同的维度组合进行聚合，等价于将不同维度的GROUP BY结果集进行UNION ALL

1. SELECT 
   
2. month,
   
3. day,
   
4. COUNT(DISTINCT cookieid) AS uv,
   
5. GROUPING__ID 
   
6. FROM lxw1234 
   
7. GROUP BY month,day 
   
8. GROUPING SETS (month,day) 
   
9. ORDER BY GROUPING__ID;
   
10. 
    
11. month      day            uv      GROUPING__ID
    
12. ------------------------------------------------
    
13. 2015-03    NULL            5       1
    
14. 2015-04    NULL            6       1
    
15. NULL       2015-03-10      4       2
    
16. NULL       2015-03-12      1       2
    
17. NULL       2015-04-12      2       2
    
18. NULL       2015-04-13      3       2
    
19. NULL       2015-04-15      2       2
    
20. NULL       2015-04-16      2       2
    
21. 
    
22. 
    
23. 等价于 
    
24. SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM lxw1234 GROUP BY month 
    
25. UNION ALL 
    
26. SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM lxw1234 GROUP BY day

复制代码

再如：

1. SELECT 
   
2. month,
   
3. day,
   
4. COUNT(DISTINCT cookieid) AS uv,
   
5. GROUPING__ID 
   
6. FROM lxw1234 
   
7. GROUP BY month,day 
   
8. GROUPING SETS (month,day,(month,day)) 
   
9. ORDER BY GROUPING__ID;
   
10. 
    
11. month         day             uv      GROUPING__ID
    
12. ------------------------------------------------
    
13. 2015-03       NULL            5       1
    
14. 2015-04       NULL            6       1
    
15. NULL          2015-03-10      4       2
    
16. NULL          2015-03-12      1       2
    
17. NULL          2015-04-12      2       2
    
18. NULL          2015-04-13      3       2
    
19. NULL          2015-04-15      2       2
    
20. NULL          2015-04-16      2       2
    
21. 2015-03       2015-03-10      4       3
    
22. 2015-03       2015-03-12      1       3
    
23. 2015-04       2015-04-12      2       3
    
24. 2015-04       2015-04-13      3       3
    
25. 2015-04       2015-04-15      2       3
    
26. 2015-04       2015-04-16      2       3
    
27. 
    
28. 
    
29. 等价于
    
30. SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM lxw1234 GROUP BY month 
    
31. UNION ALL 
    
32. SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM lxw1234 GROUP BY day
    
33. UNION ALL 
    
34. SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM lxw1234 GROUP BY month,day

复制代码

其中的 GROUPING__ID，表示结果属于哪一个分组集合。

CUBE

根据GROUP BY的维度的所有组合进行聚合。

1. SELECT 
   
2. month,
   
3. day,
   
4. COUNT(DISTINCT cookieid) AS uv,
   
5. GROUPING__ID 
   
6. FROM lxw1234 
   
7. GROUP BY month,day 
   
8. WITH CUBE 
   
9. ORDER BY GROUPING__ID;
   
10. 
    
11. 
    
12. month                              day             uv     GROUPING__ID
    
13. --------------------------------------------
    
14. NULL            NULL            7       0
    
15. 2015-03         NULL            5       1
    
16. 2015-04         NULL            6       1
    
17. NULL            2015-04-12      2       2
    
18. NULL            2015-04-13      3       2
    
19. NULL            2015-04-15      2       2
    
20. NULL            2015-04-16      2       2
    
21. NULL            2015-03-10      4       2
    
22. NULL            2015-03-12      1       2
    
23. 2015-03         2015-03-10      4       3
    
24. 2015-03         2015-03-12      1       3
    
25. 2015-04         2015-04-16      2       3
    
26. 2015-04         2015-04-12      2       3
    
27. 2015-04         2015-04-13      3       3
    
28. 2015-04         2015-04-15      2       3
    
29. 
    
30. 
    
31. 
    
32. 等价于
    
33. SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM lxw1234
    
34. UNION ALL 
    
35. SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM lxw1234 GROUP BY month 
    
36. UNION ALL 
    
37. SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM lxw1234 GROUP BY day
    
38. UNION ALL 
    
39. SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM lxw1234 GROUP BY month,day

复制代码

ROLLUP

是CUBE的子集，以最左侧的维度为主，从该维度进行层级聚合。

1. 比如，以month维度进行层级聚合：
   
2. SELECT 
   
3. month,
   
4. day,
   
5. COUNT(DISTINCT cookieid) AS uv,
   
6. GROUPING__ID  
   
7. FROM lxw1234 
   
8. GROUP BY month,day
   
9. WITH ROLLUP 
   
10. ORDER BY GROUPING__ID;
    
11. 
    
12. month                              day             uv     GROUPING__ID
    
13. ---------------------------------------------------
    
14. NULL             NULL            7       0
    
15. 2015-03          NULL            5       1
    
16. 2015-04          NULL            6       1
    
17. 2015-03          2015-03-10      4       3
    
18. 2015-03          2015-03-12      1       3
    
19. 2015-04          2015-04-12      2       3
    
20. 2015-04          2015-04-13      3       3
    
21. 2015-04          2015-04-15      2       3
    
22. 2015-04          2015-04-16      2       3
    
23. 
    
24. 可以实现这样的上钻过程：
    
25. 月天的UV->月的UV->总UV

复制代码

1. --把month和day调换顺序，则以day维度进行层级聚合：
   
2. 
   
3. SELECT 
   
4. day,
   
5. month,
   
6. COUNT(DISTINCT cookieid) AS uv,
   
7. GROUPING__ID  
   
8. FROM lxw1234 
   
9. GROUP BY day,month 
   
10. WITH ROLLUP 
    
11. ORDER BY GROUPING__ID;
    
12. 
    
13. 
    
14. day                                month              uv     GROUPING__ID
    
15. -------------------------------------------------------
    
16. NULL            NULL               7       0
    
17. 2015-04-13      NULL               3       1
    
18. 2015-03-12      NULL               1       1
    
19. 2015-04-15      NULL               2       1
    
20. 2015-03-10      NULL               4       1
    
21. 2015-04-16      NULL               2       1
    
22. 2015-04-12      NULL               2       1
    
23. 2015-04-12      2015-04            2       3
    
24. 2015-03-10      2015-03            4       3
    
25. 2015-03-12      2015-03            1       3
    
26. 2015-04-13      2015-04            3       3
    
27. 2015-04-15      2015-04            2       3
    
28. 2015-04-16      2015-04            2       3
    
29. 
    
30. 可以实现这样的上钻过程：
    
31. 天月的UV->天的UV->总UV
    
32. （这里，根据天和月进行聚合，和根据天聚合结果一样，因为有父子关系，如果是其他维度组合的话，就会不一样）

复制代码

这种函数，需要结合实际场景和数据去使用和研究，只看说明的话，很难理解。

官网的介绍：  https://cwiki.apache.org/conflue ... Grouping+and+Rollup