Hive 多分组函数GROUPING SETS,GROUPING__ID,CUBE,ROLLUP

前言

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,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

GROUPING SETS

在一个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

再如:

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;

j结果:

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, 表示结果属于哪一个分组集合。

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

ROLLUP

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

比如,以month维度进行层级聚合:

SELECT 
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID  
FROM lxw1234 
GROUP BY month,day
WITH ROLLUP 
ORDER BY GROUPING__ID;

结果:

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 --把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 这里,根据天和月进行聚合,和根据天聚合结果一样, 因为有父子关系,如果是其他维度组合的话,就会不一样

官网的介绍: https://cwiki.apache.org/confluence/display/Hive/Enhanced+Aggregation%2C+Cube%2C+Grouping+and+Rollup

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏深圳java培训

深圳Web前端学习:js中的模块化--【千锋】

我们知道最常见的模块化方案有CommonJS、AMD、CMD、ES6,AMD规范一般用于浏览器,异步的,因为模块加载是异步的,js解释是同步的,所以有时候导致依...

9830
来自专栏蚂蚁开源社区

支持animate.css动画的jquery弹出层特效

jquery.popup.js是一款支持animate.css动画效果的弹出层模态窗口插件。你可以在初始化插件时,配置模态窗口打开和关闭时的CSS3,使用非常炫...

19550
来自专栏数据猿

什么将会替代 JavaScript 呢?

JavaScript 正在蓬勃发展。但由于 WebAssembly 的出现,它的衰落可能只是一个时间问题。

11520
来自专栏一Li小麦

JavaScript设计模式之策略模式

为达到一个目的做事情的方法有很多种,比如做工资表,需要计算码农,美工,需求三个人的工资。这时候如果是一个靠谱的人事,一定会有这样一个表:

7810
来自专栏绿盟科技安全情报

【漏洞预警】Weblogic反序列化漏洞(绕过CVE-2019-2725)0day预警通告

近日,绿盟科技安全团队发现针对Oracle Weblogic的在野漏洞利用,攻击特征与CVE-2019-2725类似。此攻击可以绕过Oracle官方在4月份发布...

10420
来自专栏JAVA杂谈

Java日志Log4j或者Logback的NDC和MDC功能

Java中使用的日志的实现框架有很多种,常用的log4j和logback以及java.util.logging,而log4j是apache实现的一个开源日志组件...

15220
来自专栏绿盟科技安全情报

【漏洞预警】Weblogic wls9-async反序列化远程代码执行漏洞预警通告V2.0

4月17日,国家信息安全漏洞共享平台(CNVD)公开了Weblogic反序列化远程代码执行漏洞(CNVD-C-2019-48814),此漏洞存在于weblogi...

9230
来自专栏Java3y

网络协议常见面试题

答:Http协议运行在TCP之上,明文传输,客户端与服务器端都无法验证对方的身份;Https是身披SSL(Secure Socket Layer)外壳的Http...

15570
来自专栏成猿之路

SpringBoot+Mybatis+Swagger2环境搭建

SpringBoot相对于传统的SSM框架的优点是提供了默认的样板化配置,简化了Spring应用的初始搭建过程,如果你不想被众多的xml配置文件困扰,可以考虑使...

12710
来自专栏绿盟科技安全情报

【漏洞预警】Weblogic反序列化远程代码执行漏洞预警通告

4月17日,国家信息安全漏洞共享平台(CNVD)公开了Weblogic反序列化远程代码执行漏洞,此漏洞存在于weblogic自带的wls9_async_resp...

10610

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励