Hive中grouping sets与lateral view explode的用法

分手后的思念是犯贱 2022-10-13 12:34 191阅读 0赞

**概述：**

*  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][https_www.yiibai.com_sql_sql-grouping-sets.html]

**案例分析**

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

先创建这样的一张表：

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
    )

运行结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 1]

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

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

运行结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 2]  
如果维度更多，更复杂，还有一种写法可以优化代码，但是可能会导致数据爆炸，那就是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_

运行结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 3]  
也可以得到相同的结果。

这三种写法都可以实现相同的效果，具体使用哪种，根据业务需求来选择

--------------------

以上内容仅供参考学习，如有侵权请联系我删除！  
如果这篇文章对您有帮助，左下角的大拇指就是对博主最大的鼓励。  
您的鼓励就是博主最大的动力！

[https_www.yiibai.com_sql_sql-grouping-sets.html]: https://www.yiibai.com/sql/sql-grouping-sets.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70]: /images/20221005/30097a577f0c41cca055c2ce45db83ac.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 1]: /images/20221005/857b7adf8a8b4e2e95e9b1dec1a18743.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 2]: /images/20221005/daaca795acde4fe0ac479d1009961968.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 3]: /images/20221005/e296fa69b07b497aaebe05146489b0ee.png