Hive中grouping sets与lateral view explode的用法_lateral view 和grouping sets-CSDN博客

本文链接：https://blog.csdn.net/weixin_45366499/article/details/118530418

概述：

GROUPING SETS函数通常用于OLAP中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如，分小时、天、月的UV数。
GROUPING SETS子句允许你指定多个GROUP BY选项。增强了GROUP BY的功能。
可以通过一条SELECT语句实现复杂繁琐的多条SELECT语句的查询。并且更加的
高效，解析存储一条SQL于语句
在一个GROUP BY查询中，根据不同的维度组合进行聚合，等价于将不同维度的GROUP BY结果集进行UNION ALL

关于grouping sets可以参考这篇文章：
https://www.yiibai.com/sql/sql-grouping-sets.html

案例分析

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

先创建这样的一张表：

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_