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

最新推荐文章于 2024-08-23 23:49:58 发布
最新推荐文章于 2024-08-23 23:49:58 发布
阅读量2.2k 收藏 2
点赞数
分类专栏： hive

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/confluence/display/Hive/Enhanced+Aggregation%2C+Cube%2C+Grouping+and+Rollup

幸运六叶草
关注
专栏目录
Hive cube / rollup / grouping sets/GROUPING__ID用法
Top5软件工程硕士，先后在京东、字节从事多年Java后端开发、实时和离线大数据开发
01-31 658
表结构如下： CREATE EXTERNAL TABLE `tmp.shop_detail_tb`( `id` string COMMENT '', `name` string COMMENT '', `type` string COMMENT '', `amt` string COMMENT '', `month` string COMMENT '' ) PARTITIONED BY ( `dt` string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t
Hive GROUPING SETS和GROUPING__ID\CUBE\ROLLUP
Top5软件工程硕士，先后在京东、字节从事多年Java后端开发、实时和离线大数据开发
02-25 245
GROUPING SETS和GROUPING__ID 说明 在一个GROUP BY查询中，根据不同的维度组合进行聚合，等价于将不同维度的GROUP BY结果集进行UNION ALL GROUPING__ID，表示结果属于哪一个分组集合。 查询语句： 1 2 3 4 5 6 7 8 9 select month, day, count(distinctcookieid)...
Hive分析窗口函数(五) GROUPING SETS,GROUPING__ID,CUBE,ROLLUP 操作指南
阿尼古
04-16 495
问题导读1.GROUPING SETS与另外哪种方式等价？2.根据GROUP BY的维度的所有组合进行聚合由哪个关键字完成？3.ROLLUP与ROLLUP关系是什么？ GROUPING SETS,GROUPING__ID,CUBE,ROLLUP 这几个分析函数通常用于OLAP中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如，分小时、天、月的UV数。 Hive版本为 apac...
HIVE分析窗口函数: GROUPING SETS,GROUPING__ID,CUBE,ROLLUP
Qi.zheng
10-19 907
本文转载自:lxw的大数据田地 » Hive分析窗口函数(五) GROUPING SETS,GROUPING__ID,CUBE,ROLLUP 这几个分析函数通常用于OLAP中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如，分小时、天、月的UV数。 Hive版本为 apache-hive-0.13.1 数据准备： 2015-03,2015-03-10,cookie
Groupings sets详解
最新发布
weixin_43597208的博客
08-23 602
首先需要提醒一点的是，hql中和sparksql中虽然均有grouping sets函数，可是grouping__id顺序确是相反的，一般情况我们习惯了hql中保持一致，需要脚本中增加：set spark.grouping.sets.reference.hive=true;基本使用，是在group by 维度值后，使用grouping sets ((a,b,c),(a,b),©)像这样；###等价于UNION。
【hive sql】hive中GROUPING SETS用法的GROUPING__ID的计算方法
sinat_36894556的博客
11-08 670
hive中GROUPING SETS用法的GROUPING__ID的计算方法
Hive分析窗口函数 GROUPING SETS、GROUPING__ID
菜如张学清的博客
06-26 5245
目录 1.概述 2.数据准备 2.1数据格式 2.2创表语句 3.GROUPING SETS和GROUPING__ID 3.1 GROUPING SETS例1 3.1.1查询语句 3.1.2等价于 3.1.3查询结果 3.1.4 结果说明 3.2GROUPING SETS 例2 3.2.1 查询语句 3.2.2 等价于 3.2.3 查询结果 3.3GROU...
Hive - Cube, Rollup, GroupingId 示例与详解
BITDDD小栈
05-06 2953
​上篇文章讲到了Grouping Sets 的使用方法，Grouping Sets 可以看做是将 group by 的内容进行 union 整合，这篇文章将基于同一思想进行扩展介绍两个方法 Cube 以及 Rollup，同时给出辅助函数 GroupingId 的生成方法与使用方法。...
Hive cube / rollup / grouping sets/GROUPING__ID用法详解
weixin_43542605的博客
01-06 1275
详细介绍cube / rollup / grouping sets/GROUPING__ID这几个函数的用法以及区别
Hive/Presto中函数grouping sets用法详解（踩坑总结，看到赚到）
热门推荐
LdyLly的博客
04-24 2万+
本文详细记录了函数grouping sets使用时遇到的坑，基于Hive和Presto实现。
[Hive学习]聚合增强函数解析：grouping sets、cube、rollup、grouping判断
alie_123的博客
12-13 1755
本文介绍了hive聚合增强函数：grouping sets、cube、rollup、grouping判断的使用方法及注意细节。
hive 分组取TopN
07-10
hive不直接支持分组取TopN的操作，需要自定义udf函数打成jar包添加到hive运行环境中
对于新旧hive版本中grouping_id计算方式不同造成的差异化解决方案
zwmonk的博客
10-10 733
工作中使用多维度分组时并不想写一大堆union all，为了便于代码简洁喜欢使用grouping_id这个函数，使用久了会发现hive_1.2.1版本以前的grouping_id计算方式和以后的计算方式会有所不同步。这样造成使用这个函数的时候如果存在历史数据使用时存在着矛盾，这时很多聪明人会想到的解决办法就是把不同的grouping_ID产生的数据按照时间分段开来重新计算，这样会造成许多大量重复的代码。在没有想到下面这个解决方案时，那种根据不同时间段groupingID 分段计算是一种解决方案但不是最佳方案
SQL—解决多维度随机组合查询场景：grouping sets函数
weixin_46010244的博客
08-02 2394
通过grouping sets函数，极大提高我们的开发效率，即使有更多维度组合的增加，只需要在grouping sets函数中新增即可，当然案例中姓名不具有实质性分组聚合意义，数据的展示问题需要做进一步处理，但相较于大量的union，grouping sets函数已经可以极大的对代码进行调优。
【Hive】多维函数 『grouping sets() | with cube | with rollup 』
渐渐的忘记，赶不上明天（读研ing，2024年毕业）
12-12 4195
1. 语法 2. 例子
【Hive】Hive高级聚合（grouping sets、rollup、cube）
victorzzzz的专栏
09-17 1275
Hive高级聚合（0.10开始支持） 高级聚合功能相当于group by 加强 grouping sets：多个group by 进行union all，在同一个数据集进行多重group by 该关键字可以实现同一数据集的多重group by操作。事实上GROUPING SETS是多个GROUP BY进行UNION ALL操作的简单表达，它仅仅使用一个stage完成这些操作。GRO...
rollup分析函数
Inkbamboo专栏
03-15 2万+
表的初始数据： 使用rollup进行汇总之后的数据： select t.first_name,sum(t.salary)  from t_test1 t group by rollup(t.first_name) 可以改写为： select nvl(t.first_name,'total'),sum(t.salary)  from t_test1 t group
Hive窗口函数详解与示例
"Hive窗口函数是Hive SQL中一种强大的分析工具，允许用户在特定的数据窗口上执行聚合操作，而不像传统的聚合函数那样只对整个分区或者表进行计算。窗口函数通过`OVER()`子句定义数据窗口，并可以结合`PARTITION BY`...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值