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

本文详细介绍了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. select month,day,count(distinct cookieid) as uv,grouping__id
  42. from lxw1234
  43. group by month,day
  44. grouping sets((),month,day,(month,day))
  45. 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. SELECT month,day,COUNT(DISTINCT cookieid) AS uv,GROUPING__ID  
  34. FROM lxw1234
  35. GROUP BY month,day
  36. grouping sets((),month,(month,day))
  37. ORDER BY GROUPING__ID;
  1. --把monthday调换顺序,则以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 中 `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、付费专栏及课程。

余额充值