图文精华

About云-梭伦科技»专题 › 技术学习(版主发帖区) › 大数据学习 › Hive|数据仓库 › 连载型› Hive分析窗口函数(五) GROUPING SETS,GROUPING__ID,CUBE ...
分享

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

sstutu 2015-4-30 21:54:33 发表于 连载型 [显示全部楼层] 回帖奖励 阅读模式 关闭右栏 4 108667

问题导读

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. （这里，根据天和月进行聚合，和根据天聚合结果一样，因为有父子关系，如果是其他维度组合的话，就会不一样）
复制代码

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


相关内容：


Hive分析窗口函数(一) SUM,AVG,MIN,MAX

Hive分析窗口函数(二) NTILE,ROW_NUMBER,RANK,DENSE_RANK


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


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







来源：西安 大数据 » Hive分析窗口函数(五) GROUPING SETS,GROUPING__ID,CUBE,ROLLUP


欢迎加入about云群425860289 、432264021 ，云计算爱好者群，关注about云腾讯认证空间
回复

使用道具 举报

自定义广告语

已有(4)人评论

跳转到指定楼层
正序浏览
evababy 发表于 2015-5-5 18:03:57
回复

使用道具 举报

Tomato 发表于 2015-10-12 16:50:38
太受益了，感谢楼主，请问第三章在哪儿啊？
回复

使用道具 举报

wkf46525 发表于 2016-5-5 18:40:16
grouping__id,为什么会报错，去掉这个就可以成功
回复

使用道具 举报

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

发表新帖
sstutu

实习版主

关注

  • 406

    主题

  • 948

    帖子

  • 143

    粉丝

TA的主题
关闭

推荐上一条 /2 下一条

关于我们· 联系我们· 加入我们· 小黑屋· 合作伙伴   

Copyright © 2001-2024 About云-梭伦科技 Powered by Discuz! X3.4 Licensed Discuz Team.

简书 / Smrz 京ICP备2020039040号 Wxb 简书网举报电话：021-34700000