2.3.11 hadoop体系之离线计算-Hive数据仓库-MaxCompute SQL多维聚合求PV和UV

「爱情、让人受尽委屈。」 2022-09-01 14:57 88阅读 0赞

**目录**

1.写在前面

2.数据构造

3.多维聚合

3.1 多次GROUP BY，再UNION ALL

3.2  GROUPING SETS或者CUBE实现

3.3 Lateral View实现

4.总结

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

# 1.写在前面 #

最近遇到一个问题，需要对多个维度的多个组合聚合查询。比如我们的日志流量中有用户id，商铺id，用户和商铺又可以属于不同的类别，当需要统计，不同类别人群在不同类别商铺中的消费情况的时候，就需要用到这个知识。

# 2.数据构造 #

我们模拟构造一个cube\_demo表，表中有三个字段，id，国家，性别，我们想要统计不同国家，不同性别的PV和UV。

CREATE TABLE IF NOT EXISTS cube_demo LIFECYCLE 3 AS
    SELECT user_id, country, sex
    FROM 
    VALUES 
        ('u1','中国', '男'), 
        ('u2','美国', '男'), 
        ('u3','美国', '女'), 
        ('u4','中国', '男'), 
        ('u5','中国', '男'), 
        ('u5','中国', '男')  as a(user_id, country, sex)
    ;

# 3.多维聚合 #

## 3.1 多次GROUP BY，再UNION ALL ##

SELECT  country
            ,'ALL' AS sex
            ,COUNT(1) pv
            ,COUNT(DISTINCT user_id) uv
    FROM    cube_demo
    GROUP BY country
    UNION ALL
    SELECT  'ALL' AS country
            ,sex
            ,COUNT(1) pv
            ,COUNT(DISTINCT user_id) uv
    FROM    cube_demo
    GROUP BY sex
    UNION ALL
    SELECT  country
            ,sex
            ,COUNT(1) pv
            ,COUNT(DISTINCT user_id) uv
    FROM    cube_demo
    GROUP BY country
             ,sex
    UNION ALL
    SELECT  'ALL' AS country
            ,'ALL' AS sex
            ,COUNT(1) pv
            ,COUNT(DISTINCT user_id) uv
    FROM    cube_demo
    ;

执行计划：

![image.png][]

结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU_size_16_color_FFFFFF_t_70][]

## 3.2  GROUPING SETS或者CUBE实现 ##

如果需要同时对多维度组合的聚合分析，需要写很多group by 然后union all，使用GROUPING SETS多维聚合可简化SQL，能够使引擎给出更有效的执行计划，从而提高执行性能。  
        用法：GROUP BY GROUPING SETS((c1), (c2), (), (c1,c2))

SELECT
        IF(GROUPING(country)=1, 'ALL', country) country
        ,IF(GROUPING(sex)=1, 'ALL', sex) sex
        ,COUNT(1) pv
        ,COUNT(DISTINCT user_id) uv
    FROM cube_demo
    -- GROUP BY GROUPING SETS((country), (sex), (), (country,sex))
    GROUP BY CUBE(country,sex)--或者使用CUBE
    ;

执行计划：

![image.png][image.png 1]

## 3.3 Lateral View实现 ##

*  Lateral view通常和split, explode等UDTF一起封装使用
 *  将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合

SELECT  lat1.country country
            ,lat2.sex sex
            ,COUNT(1) pv
            ,COUNT(DISTINCT user_id) uv
    FROM    cube_demo
    LATERAL VIEW EXPLODE(ARRAY(country, 'ALL')) lat1 AS country
    LATERAL VIEW EXPLODE(ARRAY(sex, 'ALL')) lat2 AS sex
    GROUP BY lat1.country
             ,lat2.sex
    ;

执行计划：

![image.png][image.png 2]

#  4.总结 #

*  多次GROUP BY再UNION ALL的方法最原始，执行计划复杂，效率最低。
 *  GROUPING SETS实现和Lateral View实现执行效率目测相同，但GROUPING SETS语义上更好理解，推荐GROUPING SETS方式。

[image.png]: /images/20220829/d26ee9d51f614ef69a86b6077a5b1edd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU_size_16_color_FFFFFF_t_70]: /images/20220829/cb017ca020c141f3b7aeeabe980797e8.png
[image.png 1]: /images/20220829/af6d9a14d35e42828af6d12cf1e07d6f.png
[image.png 2]: /images/20220829/9d1d53bfb6fb44d993f4cc9362a72198.png