Hive中grouping sets与lateral view explode的用法-阿里云开发者社区

Hive中grouping sets与lateral view explode的用法

2022-05-19 248

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： 笔记

概述：

GROUPING SETS函数通常用于OLAP中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如，分小时、天、月的UV数。

GROUPING SETS子句允许你指定多个GROUP BY选项。增强了GROUP BY的功能。

可以通过一条SELECT语句实现复杂繁琐的多条SELECT语句的查询。并且更加的

高效，解析存储一条SQL于语句

在一个GROUP BY查询中，根据不同的维度组合进行聚合，等价于将不同维度的GROUP BY结果集进行UNION ALL

案例分析

比如说像上面这个班级性别表，我想统计每个班级男生有多少人，女生有多少人，总共男生多少人，总共女生多少人，每个班级多少人，所有班级一共多少人，求出这样的一个结果，汇总到一个表中。

先创建这样的一张表：

create table if not exists db.test1
(
    class string,
    uid int,
    sex int
);
insert into db.test1 values('class1',001,1);
insert into db.test1 values('class1',002,0);
insert into db.test1 values('class1',002,1);
insert into db.test1 values('class2',003,0);
insert into db.test1 values('class2',004,1);
insert into db.test1 values('class2',005,0);
insert into db.test1 values('class3',006,1);
insert into db.test1 values('class3',007,0);
insert into db.test1 values('class3',008,1);
insert into db.test1 values('class3',009,0);

常规思路：

第一步：先按照class和sex分组，求count 得出每个班级男生有多少人，女生有多少人

第二步：按照class分组，求count 得出每个班级多少人

第三步：按照sex分组，求count 得出总共男生多少人，总共女生多少人

第四步：直接求count 得出所有班级一共多少人

第五步：将上面步骤全部union all

具体代码：

select 
    coalesce(class,'all') as class,
    coalesce(sex,'all') as sex,
    count
from 
(
    select class,sex,count(sex) as count from db.test1 group by class,sex
    union all
    select class,null,count(sex) as count from db.test1 group by class
    union all
    select null,sex,count(sex) as count from db.test1 group by sex
    union all
    select null,null,count(sex) as count from db.test1
)

运行结果：

这样写的弊端，如果在数仓项目中，维度更多，那需要写更多的union all来组合，这样的写法非常繁琐，那么可以grouping sets写法来简化我们的代码

select 
    class,
    sex,
    count(sex) 
from db.test1 
group by 
    class,
    sex
grouping sets(
    (class,sex),
    (class),
    (sex),
    ()
)

运行结果：

如果维度更多，更复杂，还有一种写法可以优化代码，但是可能会导致数据爆炸，那就是lateral view explode写法

lateral view explode就是将数据列转行，然后做笛卡尔积

具体代码：

select 
    class_ as class,
    sex_ as sex,
    count(sex) 
from db.test1
lateral view explode(array('all',class)) as class_
lateral view explode(array('all',sex)) as sex_
group by 
    class_,
    sex_