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

已于 2024-05-22 08:43:06 修改
阅读量108 收藏
点赞数
分类专栏： hive窗口函数 文章标签： hive hadoop 数据仓库
于 2022-08-13 20:38:43 首次发布
原文链接：http://lxw1234.com/archives/2015/04/193.htm
版权

目录


GROUPING SETS,GROUPING__ID,CUBE,ROLLUP
这几个分析函数 通常用于OLAP中， 不能累加，而且需要 根据不同维度上钻和下钻的指标统计，比如，分小时、天、月的UV数。
Hive版本为 apache-hive-0.13.1

官网的介绍

总结：group by、grouping sets、rollup和cube对比

1、group by

  • 主要用来对查询的结果进行分组，相同组合的分组条件在结果集中只显示一行记录。可以添加聚合函数。

2）grouping sets

  • 对分组集中指定的组表达式的每个子集执行group by，
group by A,B，grouping sets(A,B)
等价于
group by A union group by B
其中A和B也可以是一个集合，比如group by A,B,C grouping sets((A,B),(A,C))。

3）rollup

  • 在指定表达式的每个层次级别创建分组集。实现需统计的字段从右到左多级递减，显示统计某一层次结构的聚合。按照指定的字段，进行维度组合查询，它相当于是 cube的子集，cube是所有维度，rollup是部分维度。
  • 例如：统计的维度是a,b, 则组合后的维度有: (a,b), (a), ()
  • group by A,B,C with rollup首先会对(A、B、C)进行group by，然后对(A、B)进行group by，然后是(A)进行group by，最后对全表进行group by操作。

4）cube

  • 为指定表达式集的每个可能组合创建分组集。首先会对(A、B、C)进行group by，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，( C)，最后对全表进行group by操作。

0 数据准备：

2015-03,2015-03-10,cookie1
2015-03,2015-03-10,cookie5
2015-03,2015-03-12,cookie7
2015-04,2015-04-12,cookie3
2015-04,2015-04-13,cookie2
2015-04,2015-04-13,cookie4
2015-04,2015-04-16,cookie4
2015-03,2015-03-10,cookie2
2015-03,2015-03-10,cookie3
2015-04,2015-04-12,cookie5
2015-04,2015-04-13,cookie6
2015-04,2015-04-15,cookie3
2015-04,2015-04-15,cookie2
2015-04,2015-04-16,cookie1
 
CREATE EXTERNAL TABLE lxw1234 (
month STRING,
day STRING, 
cookieid STRING 
) ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
stored as textfile location '/tmp/lxw11/';
 
 
hive> select * from lxw1234;
OK
2015-03 2015-03-10      cookie1
2015-03 2015-03-10      cookie5
2015-03 2015-03-12      cookie7
2015-04 2015-04-12      cookie3
2015-04 2015-04-13      cookie2
2015-04 2015-04-13      cookie4
2015-04 2015-04-16      cookie4
2015-03 2015-03-10      cookie2
2015-03 2015-03-10      cookie3
2015-04 2015-04-12      cookie5
2015-04 2015-04-13      cookie6
2015-04 2015-04-15      cookie3
2015-04 2015-04-15      cookie2
2015-04 2015-04-16      cookie1

1 GROUPING SETS

  1. 在一个GROUP BY查询中，根据不同的维度组合进行聚合，等价于将不同维度的GROUP BY结果集进行UNION ALL
SELECT 
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID 
FROM lxw1234 
GROUP BY month,day 
GROUPING SETS (month,day) 
ORDER BY GROUPING__ID;
 
month      day            uv      GROUPING__ID
------------------------------------------------
2015-03    NULL            5       1
2015-04    NULL            6       1
NULL       2015-03-10      4       2
NULL       2015-03-12      1       2
NULL       2015-04-12      2       2
NULL       2015-04-13      3       2
NULL       2015-04-15      2       2
NULL       2015-04-16      2       2
 
 
等价于 
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM lxw1234 GROUP BY month 
UNION ALL 
SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM lxw1234 GROUP BY day
  1. 再如：
SELECT 
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID 
FROM lxw1234 
GROUP BY month,day 
GROUPING SETS (month,day,(month,day)) 
ORDER BY GROUPING__ID;
 
month         day             uv      GROUPING__ID
------------------------------------------------
2015-03       NULL            5       1
2015-04       NULL            6       1
NULL          2015-03-10      4       2
NULL          2015-03-12      1       2
NULL          2015-04-12      2       2
NULL          2015-04-13      3       2
NULL          2015-04-15      2       2
NULL          2015-04-16      2       2
2015-03       2015-03-10      4       3
2015-03       2015-03-12      1       3
2015-04       2015-04-12      2       3
2015-04       2015-04-13      3       3
2015-04       2015-04-15      2       3
2015-04       2015-04-16      2       3
 等价于
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM lxw1234 GROUP BY month 
UNION ALL 
SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM lxw1234 GROUP BY day
UNION ALL 
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM lxw1234 GROUP BY month,day
其中的 GROUPING__ID，表示结果属于哪一个分组集合。

2 CUBE

根据GROUP BY的维度的所有组合进行聚合。

SELECT 
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID -- 两条下划线
FROM lxw1234 
GROUP BY month,day 
WITH CUBE 
ORDER BY GROUPING__ID;
  
month  			    day             uv     GROUPING__ID
--------------------------------------------
NULL            NULL            7       0
2015-03         NULL            5       1
2015-04         NULL            6       1
NULL            2015-04-12      2       2
NULL            2015-04-13      3       2
NULL            2015-04-15      2       2
NULL            2015-04-16      2       2
NULL            2015-03-10      4       2
NULL            2015-03-12      1       2
2015-03         2015-03-10      4       3
2015-03         2015-03-12      1       3
2015-04         2015-04-16      2       3
2015-04         2015-04-12      2       3
2015-04         2015-04-13      3       3
2015-04         2015-04-15      2       3
 
等价于
SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM lxw1234
UNION ALL 
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM lxw1234 GROUP BY month 
UNION ALL 
SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM lxw1234 GROUP BY day
UNION ALL 
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM lxw1234 GROUP BY month,day

SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM lxw1234
等价于
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM lxw1234 GROUP BY month,day
GROUPING_sets(())

3 ROLLUP

是CUBE的子集，以最左侧的维度为主，从该维度进行层级聚合。

  1. 比如，以month维度进行层级聚合：
SELECT 
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID  
FROM lxw1234 
GROUP BY month,day
WITH ROLLUP 
ORDER BY GROUPING__ID;

等价于
SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM lxw1234
UNION ALL 
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM lxw1234 GROUP BY month 
UNION ALL 
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM lxw1234 GROUP BY month,day
 
month  			    day             uv     GROUPING__ID
---------------------------------------------------
NULL             NULL            7       0
2015-03          NULL            5       1
2015-04          NULL            6       1
2015-03          2015-03-10      4       3
2015-03          2015-03-12      1       3
2015-04          2015-04-12      2       3
2015-04          2015-04-13      3       3
2015-04          2015-04-15      2       3
2015-04          2015-04-16      2       3
可以实现这样的上钻过程：
月天的UV->月的UV->总UV
  1. 把month和day调换顺序，则以day维度进行层级聚合：
SELECT 
day,
month,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID  
FROM lxw1234 
GROUP BY day,month 
WITH ROLLUP 
ORDER BY GROUPING__ID;
 
 
day  			      month              uv     GROUPING__ID
-------------------------------------------------------
NULL            NULL               7       0
2015-04-13      NULL               3       1
2015-03-12      NULL               1       1
2015-04-15      NULL               2       1
2015-03-10      NULL               4       1
2015-04-16      NULL               2       1
2015-04-12      NULL               2       1
2015-04-12      2015-04            2       3
2015-03-10      2015-03            4       3
2015-03-12      2015-03            1       3
2015-04-13      2015-04            3       3
2015-04-15      2015-04            2       3
2015-04-16      2015-04            2       3
 可以实现这样的上钻过程：
天月的UV->天的UV->总UV
（这里，根据天和月进行聚合，和根据天聚合结果一样，因为有父子关系，如果是其他维度组合的话，就会不一样）

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

4 Grouping__ID

4.1 老版本展示0或1

当为列显示聚合时，其值为空。 如果列本身有一些空值，这可能会发生冲突。 需要有某种方法来识别列中的 NULL，这意味着聚合和列中的 NULL，这意味着值。 GROUPING__ID 函数就是解决这个问题的方法。
此函数返回一个与每列是否存在相对应的位向量。 对于每一列，如果该列已在该行中聚合，则为结果集中的一行生成“1”值，否则该值为“0”。 这可用于区分数据中何时存在空值。
考虑以下示例：
在这里插入图片描述

SELECT key, value, GROUPING__ID, count(*)
FROM T1
GROUP BY key, value WITH ROLLUP;

在这里插入图片描述
标记为0的，是原数据的列，而不是由于聚合导致为NULL的列。

4.2 新版本呢展示数字

select a,b,c,grouping__id 
from test
	 group by a,b,c
grouping sets (
	(a,b),
	(a, c),
	(a, b, c)
)

1、在Hive中grouping__id的数字规则：根据group by 字段 ，从低到高，group sets中出现的字段是1没出现是0。
因此，上述SQL中 (a, b)对应 011；(a,c)对应 101
2、Spark中grouping__id的数字规则：根据group by 字段 ，从高到低，group sets中出现的字段是0没出现是1
因此，上述SQL中 (a, b)对应 001；(a,c)对应 010
注意：　grouping_id函数也是计算分组级别的函数，注意如果要使用grouping_id函数那必须得有group by字句，而且group by字句的中的列与grouping_id函数的参数必须相等。比如group by A,B，那么必须使用grouping_id（A,B）。

大数据知识搬运工
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
Hive分析窗口函数 GROUPING SETS、GROUPING__ID、CUBE和ROLLUP
大数据ING的博客
08-28 199
概述 GROUPING SETS,GROUPING__ID,CUBE,ROLLUP 这几个分析函数通常用于OLAP中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如，分小时、天、月的UV数。 数据准备 2015-03,2015-03-10,cookie1 2015-03,2015-03-10,cookie5 2015-03,2015-03-12,cookie7 2015-04,2...
Hive分析窗口函数 GROUPING SETS、GROUPING__ID
菜如张学清的博客
06-26 5099
目录 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分析窗口函数 GROUPING SETS、GROUPING__ID、CUBE、ROLLUP使用说明和示例
helloxiaozhe的博客
04-16 3971
Hive分析窗口函数 GROUPING SETS、GROUPING__ID、CUBE、ROLLUP使用说明和示例 0、概述 GROUPING SETS：根据不同的维度组合进行聚合，等价于将不同维度的GROUP BY结果集进行UNION ALL GROUPING__ID：表示结果属于哪一个分组集合，属于虚字段 CUBE：根据GROUP BY的维度的所有组合进行聚合。 ROLLUP：为CUBE的子集，以最左侧的维度为主，从该维度进行层级聚合。 这几个分析函数通常用于OLAP中，不能累加，而且需要根据不
Hive之grouping sets用法及grouping_id计算方法
热门推荐
HappyRocking的专栏
06-04 2万+
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,
grouping_id&grouping sets
weixin_46680124的博客
09-05 713
grouping sets 用法在Hive中，会出现对同一个数据表进行不同粒度的汇总，这时可以有两种方案：用多个sql，对不同粒度使用不同的 group by 方法。
spark-hive-thriftserver_2.11-2.1.3-SNAPSHOT-123456.jar
04-01
spark-hive-thriftserver_2.11-2.1.spark-hive-thrift
hive-jdbc-1.2.1-standalone.jar
10-21
dbeaver-cli.exe客户端连接hive所需要的其中一个jar包 hive-jdbc-1.2.1-standalone.jar
02.hive内置函数--窗口分析函数--row_number_over.mp4
04-29
02.hive内置函数--窗口分析函数--row_number_over.mp4
flink-sql-connector-hive-2.3.6_2.11-1.11.0.jar
11-16
flink-sql-connector-hive-2.3.6_2.11-1.11.0.jar
hive-hbase-handler-1.2.1.jar
09-04
被编译的hive-hbase-handler-1.2.1.jar，用于在Hive中创建关联HBase表的jar，解决创建Hive关联HBase时报FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. org.apache.hadoop....
Hive grouping sets、cube 、rollup
很吵请安静
12-06 935
背景 假设有如下数据 with query1 as ( select stack (5, 'A', 'x', '1', 1, 'B', 'x', '1', 2, 'C', 'x', '0', 3, 'D', 'y', '0', 4, 'E', 'y', '0', 5 ) as (a,b,c,d) ) 假设a、b、c、d是数据中的四种属性 现在想通过group by 操作统计出这四种属性不同组合的结
hive GROUPING SETS通过GROUPING__ID 得到聚合的字段
小菜鸟
03-23 1万+
Grouping_ID函数 当我们没有统计某一列时，它的值显示为null，这可能与列本身就有null值冲突，这就需要一种方法区分是没有统计还是值本来就是null。（写一个排列组合的算法，就马上理解了，grouping_id其实就是所统计各列二进制和） Column1 (key) Column2 (value) 1 NULL
Hive分析窗口函数(五) GROUPING SETS,GROUPING__ID,CUBE,ROLLUP
刘光华的专栏
09-13 2814
转载地址：    GROUPING SETS,GROUPING__ID,CUBE,ROLLUP 这几个分析函数通常用于OLAP中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如，分小时、天、月的UV数。 Hive版本为 apache-hive-0.13.1 数据准备： 2015-03,2015-03-10,cookie1 2015-03,2015-03-10,
Hive高级聚合之GROUPING SETS/ROLLUP/CUBE/Grouping_ID
遐想者
05-22 1641
1、GROUPING SETS该关键字可以实现同一数据集的多重group by操作。事实上GROUPING SETS是多个GROUP BY进行UNION ALL操作的简单表达，它仅仅使用一个stage完成这些操作。GROUPING SETS的子句中如果包含()数据集，则表示整体聚合。示例：select name, work_space[0] as main_place, count(employe...
Hive.GROUPING SETS的“陷阱”
辰采星
04-06 8558
HQL查询中，对GROUPING SETS出来的数据与其它数据进行连接操作时，需要小心处理数据过滤的问题。而且，需要注意的还有，grouping__id这个函数的返回值不是整型，是字符串。
hive 中的grouping set，cube，roll up函数
weixin_34352449的博客
09-12 2207
2019独角兽企业重金招聘Python工程师标准>>> ...
计算机毕业设计Hadoop+Hive地震预测系统 地震数据分析可视化 地震爬虫 大数据毕业设计 Spark 机器学习 深度学习 Flink 大数据
最新发布
从业计算机毕业设计10年，打算用一年时间分享10年毕设经验！
06-13 461
计算机毕业设计Hadoop+Hive地震预测系统 地震数据分析可视化 地震爬虫 大数据毕业设计 Spark 机器学习 深度学习 Flink 大数据
软件1班20240607
2301_80189168的博客
06-07 2156
【代码】软件1班20240607。
Ansible——unarchive模块
Lzcsfg的博客
06-07 1079
src描述：要解压缩的文件路径，可以是本地路径或远程 URL。类型：字符串必需：是dest描述：解压缩文件的目标路径。类型：字符串默认值：当前工作目录remote_src描述：如果为yes，则将src参数指定的文件视为远程文件。如果为no，则将其视为本地文件。类型：布尔值no描述：如果为yes，则将dest参数指定的路径视为远程路径。如果为no，则将其视为本地路径。类型：布尔值noextra_opts描述：额外的解压缩选项，作为字符串传递。类型：字符串。
$HIVE_HOME/bin/hive --service hiveserv补全命令
05-10
$HIVE_HOME/bin/hive --service hiveserver是启动HiveServer2的命令。在启动HiveServer2时，可以添加一些参数来控制其行为，这些参数的完整列表可以通过在命令行中输入$HIVE_HOME/bin/hive --service hiveserver2 --help来查看。其中一些常用的参数包括： - -p 或 --port 指定HiveServer2监听的端口号，默认为10000 - -H 或 --hostname 指定HiveServer2绑定的主机名，默认为0.0.0.0 - --hiveconf key=value 设置Hive配置属性，例如--hiveconf hive.server2.authentication=LDAP可以启用LDAP认证 - --hivevar key=value 设置Hive变量，例如--hivevar database=my_db可以在HQL语句中引用${hivevar:database}来代替my_db 同时，还可以通过添加--hiveconf hive.server2.thrift.port=<port_number>来启动HiveServer1。在这种情况下，端口号需要设置为9083。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值