[Hive系列10] 高级聚合函数grouping sets、cube、rollup

数据分析师的FIRE人生 2021-07-05

4042

点击上方「数据分析师的FIRE人生」→

点击右上角「...」→设为星标⭐

大家好，我是风影楼，一名互联网公司的数据分析师。之前我曾在CSDN编写了Hive系列的付费博客专栏，目前收获了25W次的访问，不过由于CSDN的付费设置无法取消，所以我决定在公众号重新免费分享一遍。这一篇主要介绍Hive中的三种高级聚合函数，分别是grouping sets、cube、rollup。

1、指定维度组合进行聚合 grouping sets

grouping sets用于在一个 group by 查询中，根据不同的维度组合进行聚合，等价于将不同维度的 group by 结果集进行 union all。

举例：

现有数据如下表，有平台、版本、用户id三个字段：

select * from temp_test8;


platform  version  uid
ios      1.1  1
ios      1.1  2
ios      1.2  3
android  1.1  4
android  1.1  5
android  1.2  6

此时如果想要同时求出每个平台下有多少用户，每个版本下有多少用户以及每个平台每个版本下有多少用户，需要分别对不同的维度进行group by。高级聚合函数的作用就是一次性进行不同维度的聚合，下面首先展示grouping sets的用法。

select platform
      ,version
      ,count(uid) as uv
from temp_test8
group by platform
      ,version grouping sets(platform, version,(platform, version),())
      
运行结果如下：
platform  version  uv  
ios         null   3  
android     null   3  
null        1.2    2
null        1.1    4
ios         1.2    1  
ios         1.1    2  
android     1.2    1  
android     1.1    2
null        null   6

上述代码等价于：

select platform
      ,null as version
      ,count(uid) as uv
from temp_test8
group by platform


union all


select null as platform
      ,version
      ,count(uid) as uv
from temp_test8
group by version


union all


select platform
      ,version
      ,count(uid) as uv
from temp_test8
group by platform
      ,version


union all


select null as platform
      ,null as version
      ,count(uid) as uv
from temp_test8;


运行结果如下：
platform  version  uv  
ios         null   3  
android     null   3  
null        1.2    2  
null        1.1    4  
ios         1.2    1  
ios         1.1    2  
android     1.2    1  
android     1.1    2
null        null   6

grouping sets的括号里逗号分割的每一种组合，分别对应一种聚合维度额组合，聚合时其他字段全部取NULL，NULL在这里代表整体，可以使用NVL函数直接转换成整体等字样。此时可以选取聚合字段的全排列组合后的所有维度组合，也可以根据需求需要只选取其中的部分维度组合。比如只要每个平台下有多少用户，以及共有多少用户，可以在grouping sets的括号里直选取第一个 platform和最后一个()，效果如下：

select platform
      ,count(uid) as uv
from temp_test8
group by platform grouping sets(platform,())
      
运行结果如下：
platform  uv  
ios       3
android   3
null      6

2、所有维度全排列组合进行聚合 cube

当聚合的维度很多，而且要所有维度的组合进行聚合时，最好使用cube代grouping sets，语法更加简单，堪称偷懒必备。

举例：

select platform
      ,version
      ,count(uid) as uv
from temp_test8
group by platform
      ,version with cube;
      
等价于：


select platform
      ,version
      ,count(uid) as uv
from temp_test8
group by platform
      ,version grouping sets(platform, version,(platform, version),())
      
二者运行结果相同，如下：
platform  version  uv  
ios         null   3  
android     null   3  
null        1.2    2
null        1.1    4
ios         1.2    1  
ios         1.1    2  
android     1.2    1  
android     1.1    2
null        null   6

3、最左侧的维度为主进行层级聚合 rollup

rollup是 cube 的子集，以最左侧的维度为主，从该维度进行层级聚合。这么说有些抽象，仍然是举例说明。比方说此时要计算共有多少用户，每个平台下有多少用户，每个平台每个版本下有多少用户。相当于以总->平台->平台、版本的维度不断向下聚合。如果使用grouping sets需要定义三个组合维度，此时可以直接使用rollup简便书写。

举例：

select platform
      ,version
      ,count(uid) as uv
from temp_test8
group by platform
      ,version --根据group by后的字段顺序进行下钻聚合
with rollup


等价于：
select platform
      ,version
      ,count(uid) as uv
from temp_test8
group by platform
      ,version grouping sets((),platform,(platform, version))


等价于：
select null as platform
      ,null as version
      ,count(uid) as uv
from temp_test8


union all


select platform
      ,null as version
      ,count(uid) as uv
from temp_test8
group by platform


union all


select platform
      ,version
      ,count(uid) as uv
from temp_test8
group by platform
      ,version;




结果：
platform  version  uv  
null      null    6  
ios       null    3  
android   null    3  
ios       1.2     1  
ios       1.1     2  
android   1.2     1  
android   1.1     2

三个高级函数的用法就介绍到这里，这几个偷懒加调优的好用函数你get了么？

点个在看你最好看

数据库

文章转载自数据分析师的FIRE人生，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

[Hive系列10] 高级聚合函数grouping sets、cube、rollup

1、指定维度组合进行聚合 grouping sets

2、所有维度全排列组合进行聚合 cube

3、最左侧的维度为主进行层级聚合 rollup

评论