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

最新推荐文章于 2022-12-12 18:39:51 发布
转载 最新推荐文章于 2022-12-12 18:39:51 发布 · 406 阅读 · 3

本文详细介绍了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 5239
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_id计算方式不同造成的差异化解决方案
zwmonk的博客
10-10 830
工作中使用多维度分组时并不想写一大堆union all，为了便于代码简洁喜欢使用grouping_id这个函数，使用久了会发现hive_1.2.1版本以前的grouping_id计算方式和以后的计算方式会有所不同步。这样造成使用这个函数的时候如果存在历史数据使用时存在着矛盾，这时很多聪明人会想到的解决办法就是把不同的grouping_ID产生的数据按照时间分段开来重新计算，这样会造成许多大量重复的代码。在没有想到下面这个解决方案时，那种根据不同时间段groupingID 分段计算是一种解决方案但不是最佳方案
hive GROUPING SETS通过GROUPING__ID 得到聚合的字段
小菜鸟
03-23 1万+
Grouping_ID函数 当我们没有统计某一列时，它的值显示为null，这可能与列本身就有null值冲突，这就需要一种方法区分是没有统计还是值本来就是null。（写一个排列组合的算法，就马上理解了，grouping_id其实就是所统计各列二进制和） Column1 (key) Column2 (value) 1 NULL
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中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
spnavcfg-0.3.1-1.el8.tar.gz
最新发布
08-19
# 适用操作系统：Centos8 #Step1、解压 tar -zxvf xxx.el8.tar.gz #Step2、进入解压后的目录，执行安装 sudo rpm -ivh *.rpm
一个涵盖患者、疾病与药物关系的权威医疗 RDF 数据集
08-19
资源下载链接为： https://pan.quark.cn/s/7d4480e88650 一个涵盖患者、疾病与药物关系的权威医疗 RDF 数据集（最新、最全版本！打开链接下载即可用！）
hive grouping sets函数怎么用
01-04
### 使用 Hive 中 `GROUPING SETS` 的方法 #### 示例与语法解释 在 Hive SQL 查询中，`GROUPING SETS` 提供了一种简洁的方式来执行多维分组聚合。这使得可以在单个查询中指定多个不同的组合来进行数据汇总。 创建表并插入测试数据： ```sql CREATE TABLE IF NOT EXISTS student_scores ( class STRING, sex STRING, course STRING, score INT ); INSERT INTO student_scores VALUES ('ClassA', 'Male', 'Math', 80), ('ClassB', 'Female', 'English', 90), ('ClassA', 'Female', 'Math', 75); ``` 使用 `GROUPING SETS` 执行复杂分组操作： ```sql SELECT GROUPING__ID, -- 特殊列用于区分不同层次的分组结果 class, sex, course, AVG(score) AS average_score FROM student_scores GROUP BY class, sex, course WITH ROLLUP; -- 或者使用 CUBE 替代 ROLLUP 来获取更全面的结果集 ``` 上述例子展示了如何利用 `WITH ROLLUP` 关键字来简化某些类型的 `GROUPING SETS` 定义[^1]。对于更加复杂的场景，则可以直接定义具体的分组集合： ```sql SELECT GROUPING__ID, class, sex, course, AVG(score) AS average_score FROM student_scores GROUP BY class, sex, course GROUPING SETS( (class, course), (class, sex), (sex, course), () -- 表示全局总计行 ); ``` 此查询会返回四类不同的分组统计信息：按班级和课程、按性别和课程、仅限于特定科目以及整个表格的整体平均成绩[^3]。 注意，在输出结果集中有一个名为 `GROUPING__ID` 的特殊字段，它用来指示当前记录对应的是哪种级别的分组情况。通过检查该值可以帮助理解每一行代表的数据范围[^2]。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值