文档章节

Hive group by、groupping sets、with cube, with rollup

jeremyli90
 jeremyli90
发布于 2017/08/17 17:55
字数 1580
阅读 157
收藏 0

  数据处理过程中,我们通常需要对各个维度进行交叉分析,如果只有GROUP BY子句,那我们可以写出按各个维度或层次进行GROUP BY的查询语句,然后再通过UNION子句把结果集拼凑起来,但是这样的查询语句显得冗长、笨拙。       

为了解决HQL冗长的问题,下面我们介绍一下HIVE提供的一些语法:

  • group by 后使用 grouping sets

       因为涉及UNION操作,所以为了遵循UNION对参与合并的数据集合的要求,GROUPING SETS会把在单个GROUP BY逻辑中没有参与GROUP BY的那一列置为NULL值,使它成为常量占位列。这样聚合出来的结果,未被GROUP BY的列将显示为NULL。

group by后带grouping sets子句效果就是只返回小记记录,即只返回按单个列分组后的统计数据,不返回多个列组合分组的统计数据。

例1:Group by grouping sets(A )

产生的分组种数:1种;

第一种:group by A

返回结果集:即为以上一种分组的统计结果集。

例2:Group by grouping sets(A ,B)

产生的分组种数:2种;

第一种:group by A

第二种:group by B

返回结果集:为以上两种分组统计结果集的并集去掉重复数据。

例3:Group by grouping sets (A ,B,C)

产生的分组种数:3种;

第一种:group by A

第二种:group by B

第三种:group by C

返回结果集:为以上三种分组统计结果集的并集且未去掉重复数据。

例4:Group by A, B, C grouping sets (A ,B,C,(A ,B))

产生的分组种数:4种;

第一种:group by A

第二种:group by B

第三种:group by C

第四种:group by A, B

返回结果集:为以上四种分组统计结果集的并集且未去掉重复数据。

 

Aggregate Query with GROUPING SETS Equivalent Aggregate Query with GROUP

SELECT a, b, SUM(c) FROM tab1 

GROUP BY a, b 

GROUPING SETS ( (a,b) )  

SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b

SELECT a, b, SUM( c ) FROM tab1

GROUP BY a, b

GROUPING SETS ( (a,b), a)

SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b  

UNION  

SELECT a, null, SUM( c ) FROM tab1 GROUP BY a 

SELECT a,b, SUM( c ) FROM tab1 

GROUP BY a, b GROUPING SETS (a,b)  

SELECT a, null, SUM( c ) FROM tab1 GROUP BY a  

UNION  

SELECT null, b, SUM( c ) FROM tab1 GROUP BY b

SELECT a, b, SUM( c ) FROM tab1 

GROUP BY a, b 

GROUPING SETS ( (a, b), a, b, ( ) )

SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b  

UNION  

SELECT a, null, SUM( c ) FROM tab1 GROUP BY a, null  

UNION  

SELECT null, b, SUM( c ) FROM tab1 GROUP BY null, b  

UNION  

SELECT null, null, SUM( c ) FROM tab1 

 

  • group by后使用rollup子句总结

group by后带rollup子句的功能可以理解为:先按一定的规则产生多种分组,然后按各种分组统计数据(至于统计出的数据是求和还是最大值还是平均值等这就取决于SELECT后的聚合函数)。因此要搞懂group by后带rollup子句的用法主要是搞懂它是如何按一定的规则产生多种分组的group by后带rollup子句所返回的结果集,可以理解为各个分组所产生的结果集的并集且没有去掉重复数据。下面举例说明:

1、对比没有带rollup的goup by

例:Group by A ,B

产生的分组种数:1种;

即group by A,B

返回结果集:也就是这一种分组的结果集。

2、group by后带rollup

例1:Group by A,B with rollup

产生的分组种数:3种;

第一种:group by A,B

第二种:group by A

第三种:group by NULL

(说明:本没有group by NULL 的写法,在这里指是为了方便说明,而采用之。含义是:没有分组,也就是所有数据做一个统计。例如聚合函数是SUM的话,那就是对所有满足条件的数据进行求和。此写法的含义下同)

返回结果集:为以上三种分组统计结果集的并集且未去掉重复数据。

 

    有下面一个表数据:

  Column1 (key)  

  Column2 (value)  

  1

  NULL

  1

  1

  2

  2

  3

  3

  3

  NULL

  4

  5

 

执行下面查询语句:

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

将得到如下查询结果:

 key

 value

 GROUPING__ID

 count(*)

  NULL                  

  NULL                    

    0            

    6                      

     1

  NULL

    1

    2

     1

  NULL

    3

    1

     1

    1

    3

    1

     2

  NULL

    1

    1

     2

    2

    3

    1

     3

  NULL

    1

    2

     3

  NULL

    3

    1

     3

     3

    3

    1

     4

  NULL

    1

    1

     4

     5

    3

    1

 

例2:Group by A ,B,C with rollup

产生的分组种数:4种;

第一种:group by A,B,C

第二种:group by A,B

第三种:group by A

第四种:group by NULL

返回结果集:为以上四种分组统计结果集的并集且未去掉重复数据。

  • group by后带rollup子句与group by后带cube子句区别

group by后带rollup子句与group by后带cube子句的唯一区别就是:

带cube子句的group by会产生更多的分组统计数据。cube后的列有多少种组合(注意组合是与顺序无关的)就会有多少种分组。

例:Group by A ,B,C with cube

产生的分组种数:8种;

第一种:group by A,B,C

第二种:group by A,B

第三种:group by A,C

第四种:group by B,C

第五种:group by C

第六种:group by B

第七种:group by A

第八种:group by NULL

返回结果集:为以上八种分组统计结果集的并集且未去掉重复数据。

########################################################

Cubes and Rollups

The general syntax is WITH CUBE/ROLLUP. It is used with the GROUP BY only. CUBE creates a subtotal of all possible combinations of the set of column in its argument. Once we compute a CUBE on a set of dimension, we can get answer to all possible aggregation questions on those dimensions.

It might be also worth mentioning here that 
GROUP BY a, b, c WITH CUBE is equivalent to 
GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (b, c), (a, c), (a), (b), (c), ( )).

ROLLUP clause is used with GROUP BY to compute the aggregate at the hierarchy levels of a dimension.
GROUP BY a, b, c with ROLLUP assumes that the hierarchy is "a" drilling down to "b" drilling down to "c".

GROUP BY a, b, c, WITH ROLLUP is equivalent to GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (a), ( )).

 

其他参考:

http://blog.csdn.net/ljb522744686/article/details/

http://blog.csdn.net/mashroomxl/article/details/22578471

https://cwiki.apache.org/confluence/display/Hive/Enhanced+Aggregation,+Cube,+Grouping+and+Rollup#EnhancedAggregation,Cube,GroupingandRollup-CubesandRollups

© 著作权归作者所有

jeremyli90
粉丝 2
博文 91
码字总数 30882
作品 0
深圳
程序员
私信 提问
oracle中group by的高级用法

简单的group by用法 select c1,sum(c2) from t1 where t1<>'test' group by c1 having sum(c2)>100; rollup(多列)——上卷汇总,从最后一个汇总字段开始上卷汇总,一直到合计 select c1,c2,s......

hulubo
2012/04/09
749
0
hive 中的grouping set,cube,roll up函数

GROUPING SETS GROUPING SETS作为GROUP BY的子句,允许开发人员在GROUP BY语句后面指定多个统计选项,可以简单理解为多条group by语句通过union all把查询结果聚合起来结合起来,下面是几个实...

张欢19933
2018/09/12
221
0
GROUP BY GROUPING SETS

GROUPING SETS 子句是 SELECT 语句的 GROUP BY 子句的扩展。通过 GROUPING SETS 子句,您可采用多种方式对结果分组,而不必使用多个 SELECT 语句来实现这一目的。这就意味着,能够减少响应时...

hblt-j
2018/12/12
28
2
Hive 高级应用及分析窗口函数

Hive高级应用 1、支持复杂数据类型 array,map,struct 支持对应复杂数据类型的遍历和查询 2、支持视图 3、函数 3.1、丰富的内置函数 3.2、支持自定义Java处理类,以jar文件的方式添加至Hiv...

PeakFang-BOK
2018/11/26
449
0
高级分组group by 、 group by cube、 group by rollup 使用说明

1、分别以job和deptno分组求和 select sum(sal),job,deptno from emp group by grouping sets(job,deptno); 2、2^n-1分组(n表示列的数量,如果有括号里面的括起来的多个列算一个列 举例:cube...

love灬少
2018/06/26
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Mybatis Plus删除

/** @author beth @data 2019-10-17 00:30 */ @RunWith(SpringRunner.class) @SpringBootTest public class DeleteTest { @Autowired private UserInfoMapper userInfoMapper; /** 根据id删除......

一个yuanbeth
今天
4
0
总结

一、设计模式 简单工厂:一个简单而且比较杂的工厂,可以创建任何对象给你 复杂工厂:先创建一种基础类型的工厂接口,然后各自集成实现这个接口,但是每个工厂都是这个基础类的扩展分类,spr...

BobwithB
今天
5
0
java内存模型

前言 Java作为一种面向对象的,跨平台语言,其对象、内存等一直是比较难的知识点。而且很多概念的名称看起来又那么相似,很多人会傻傻分不清楚。比如本文我们要讨论的JVM内存结构、Java内存模...

ls_cherish
今天
4
0
友元函数强制转换

友元函数强制转换 p522

天王盖地虎626
昨天
5
0
js中实现页面跳转(返回前一页、后一页)

本文转载于:专业的前端网站➸js中实现页面跳转(返回前一页、后一页) 一:JS 重载页面,本地刷新,返回上一页 复制代码代码如下: <a href="javascript:history.go(-1)">返回上一页</a> <a h...

前端老手
昨天
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部