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

目录

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
;

执行计划：

结果：

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
;

执行计划：

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
;

执行计划：

 4.总结

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