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

最新推荐文章于 2022-12-12 18:39:51 发布
最新推荐文章于 2022-12-12 18:39:51 发布
阅读量406 收藏 3
点赞数 1
本文详细介绍了Hive中的GROUPINGSETS、GROUPING__ID、CUBE和ROLLUP等高级分组函数的应用方法及场景，通过具体示例展示了如何进行多维度的数据聚合分析。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

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
  40. 

  41. 等价于
  42. select month,day,count(distinct cookieid) as uv,grouping__id
  43. from lxw1234
  44. group by month,day
  45. grouping sets((),month,day,(month,day))
  46. order by grouping__id;


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
  26. 等价于
  27. SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM lxw1234
  28. UNION ALL 
  29. SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM lxw1234 GROUP BY month 
  30. UNION ALL 
  31. SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM lxw1234 GROUP BY month, day
  32. 

  33. 等价于
  34. SELECT month,day,COUNT(DISTINCT cookieid) AS uv,GROUPING__ID  
  35. FROM lxw1234
  36. GROUP BY month,day
  37. grouping sets((),month,(month,day))
  38. ORDER BY GROUPING__ID;

  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 769
表结构如下： 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
SQL server 开窗函数及CUBE的应用（实现数据任意组合）
奋斗的阿杰的博客
07-21 792
SQL server 开窗统计函数及cube数据立方体的应用
【SQL】(task5)SQL高级处理（窗口函数 | ROLLUP）
发现问题，并解决问题，批判性思维
11-23 2393
学习总结 文章目录学习总结5.1 窗口函数5.1.1 窗口函数概念及基本的使用方法5.2 窗口函数种类5.2.1 专用窗口函数5.2.2 聚合函数在窗口函数上的使用5.3 窗口函数的的应用 - 计算移动平均5.3.1 窗口函数适用范围和注意事项5.4 GROUPING运算符5.4.1 ROLLUP - 计算合计及小计练习题5.15.25.3 5.1 窗口函数 5.1.1 窗口函数概念及基本的使用方法 窗口函数也称为OLAP函数。OLAP 是 OnLine AnalyticalProcessing 的简称，意
Hive_Hive ROLLUP, GROUPING SETS, CUBE 聚合函数 GROUPING 函数 与 GROUPING__ID 计算方式
迎难而上
04-07 1350
在 ROLLUP, GROUPING SETS , CUBE 函数中 , 我们会需要用到 GROUPING 函数 与 GROUPING__ID 。 通过 GROUPING 函数，可以方便的知道所选择的列 是否进行了聚合。 而通过 GROUPING_ID 可以结合 ROLLUP, GROUPING SETS , CUBE 列的顺序，知道用那些列 进行了...
Hive分析窗口函数(四) GROUPING SETS,GROUPING__ID,CUBE,ROLLUP
小江专栏
08-11 593
接上篇 Hive分析窗口函数(四) LAG,LEAD,FIRST_VALUE,LAST_VALUE GROUPING SETS,GROUPING__ID,CUBE,ROLLUP 这几个分析函数通常用于OLAP中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如，分小时、天、月的UV数。 Hive版本为 apache-hive-0.13.1 数据准备： 2
Hive分析窗口函数之GROUPING SETS，CUBE和ROLLUP
我是60岁程序员
03-23 751
这几个分析函数通常用于OLAP中，不能累加，而且需要根据不同维度上钻和下钻的指标统计。环境信息：Hive版本为apache-hive-0.14.0-binHadoop版本为hadoop-2.6...
hive的多维度分析函数with cube和grouping__id的理解
ZH519080的博客
04-15 2682
hive的多维度分析函数with cube和grouping__id的理解
Hive分析窗口函数 GROUPING SETS、GROUPING__ID
菜如张学清的博客
06-26 5377
目录 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 / grouping sets/GROUPING__ID用法详解
weixin_43542605的博客
01-06 1389
详细介绍cube / rollup / grouping sets/GROUPING__ID这几个函数的用法以及区别
Hive分析窗口函数(五) GROUPING SETS,GROUPING__ID,CUBE,ROLLUP 操作指南
阿尼古
04-16 543
问题导读1.GROUPING SETS与另外哪种方式等价？2.根据GROUP BY的维度的所有组合进行聚合由哪个关键字完成？3.ROLLUP与ROLLUP关系是什么？ GROUPING SETS,GROUPING__ID,CUBE,ROLLUP 这几个分析函数通常用于OLAP中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如，分小时、天、月的UV数。 Hive版本为 apac...
【Hive】多维函数 『grouping sets() | with cube | with rollup 』
渐渐的忘记，赶不上明天（读研ing，2024年毕业）
12-12 5238
1. 语法 2. 例子
Hive之grouping sets用法及grouping_id计算方法
热门推荐
HappyRocking的专栏
06-04 3万+
grouping sets 用法 在Hive中，会出现对同一个数据表进行不同粒度的汇总，这时可以有两种方案： 用多个sql，对不同粒度使用不同的 group by 方法。 用1个sql，使用 grouping sets 方法一次性得到所有粒度的汇总。 以测试数据表 tmp_student 为例: 1、生成测试数据 在 hive 环境中创建临时表： create table tmp.tmp_student ( name string, class int, sex string,
hive GROUPING SETS通过GROUPING__ID 得到聚合的字段
小菜鸟
03-23 1万+
Grouping_ID函数 当我们没有统计某一列时，它的值显示为null，这可能与列本身就有null值冲突，这就需要一种方法区分是没有统计还是值本来就是null。（写一个排列组合的算法，就马上理解了，grouping_id其实就是所统计各列二进制和） Column1 (key) Column2 (value) 1 NULL
对于新旧hive版本中grouping_id计算方式不同造成的差异化解决方案
zwmonk的博客
10-10 830
工作中使用多维度分组时并不想写一大堆union all，为了便于代码简洁喜欢使用grouping_id这个函数，使用久了会发现hive_1.2.1版本以前的grouping_id计算方式和以后的计算方式会有所不同步。这样造成使用这个函数的时候如果存在历史数据使用时存在着矛盾，这时很多聪明人会想到的解决办法就是把不同的grouping_ID产生的数据按照时间分段开来重新计算，这样会造成许多大量重复的代码。在没有想到下面这个解决方案时，那种根据不同时间段groupingID 分段计算是一种解决方案但不是最佳方案
HIVE中grouping__id使用场景
jiantianming2的博客
01-28 6841
grouping__id值的计算方式 例子： step01：数据准备如下 ​create table temp.temp_xw_20180127 as select '2018-01' as dt_month, null as type, 12 total union all select '2018-01' as dt_month, '收入' as type, 100 tot
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
无线通信基于PSO的STAR-RIS辅助NOMA系统优化：联合功率分配与智能表面参数调优（含详细代码及解释）
最新发布
08-19
内容概要：该论文探讨了一种基于粒子群优化(PSO)的STAR-RIS辅助NOMA无线通信网络优化方法。STAR-RIS作为一种新型可重构智能表面，能同时反射和传输信号，与传统仅能反射的RIS不同。结合NOMA技术，STAR-RIS可以提升覆盖范围、用户容量和频谱效率。针对STAR-RIS元素众多导致获取完整信道状态信息(CSI)开销大的问题，作者提出一种在不依赖完整CSI的情况下，联合优化功率分配、基站波束成形以及STAR-RIS的传输和反射波束成形向量的方法，以最大化总可实现速率并确保每个用户的最低速率要求。仿真结果显示，该方案优于STAR-RIS辅助的OMA系统。 适合人群：具备一定无线通信理论基础、对智能反射面技术和非正交多址接入技术感兴趣的科研人员和工程师。 使用场景及目标：①适用于希望深入了解STAR-RIS与NOMA结合的研究者；②为解决无线通信中频谱资源紧张、提高系统性能提供新的思路和技术手段；③帮助理解PSO算法在无线通信优化问题中的应用。 其他说明：文中提供了详细的Python代码实现，涵盖系统参数设置、信道建模、速率计算、目标函数定义、约束条件设定、主优化函数设计及结果可视化等环节，便于读者理解和复现实验结果。此外，文章还对比了PSO与其他优化算法（如DDPG）的区别，强调了PSO在不需要显式CSI估计方面的优势。
在自定义数据集上训练yolov3，并封装到ROS中作为一个节点
08-19
资源下载链接为： https://pan.quark.cn/s/9ccb85a2be0f 在自定义数据集上训练yolov3，并封装到ROS中作为一个节点（最新、最全版本！打开链接下载即可用！）
hive grouping sets函数怎么用
01-04
### 使用 Hive 中 `GROUPING SETS` 的方法 #### 示例与语法解释 在 Hive SQL 查询中，`GROUPING SETS` 提供了一种简洁的方式来执行多维分组聚合。这使得可以在单个查询中指定多个不同的组合来进行数据汇总。 创建...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值