能否结合group by与partition by简化多粒度去重计数SQL查询

直接使用GROUPING SETS多粒度聚合+窗口函数回填的方式改写，无需二次聚合和多表关联，可大幅缩减代码长度，同时兼容去重计数需求：

with agg_all as (
    select 
        param1,
        yy,
        mm,
        dd,
        param2,
        param3,
        param4,
        count(distinct id1) as cnt_id1,
        count(distinct id2) as cnt_id2,
        count(distinct id3) as cnt_id3,
        cast(null as string) as param5,
        cast(null as string) as param6,
        cast(null as string) as param7,
        cast(null as string) as param8,
        param9,
        param10,
        param11,
        param12,
        param13,
        param14,
        param15,
        param16,
        param17,
        param18,
        param19,
        param20,
        param21,
        param22,
        -- 标记聚合粒度：0为日粒度，1为年粒度
        grouping(mm,dd) as agg_level
    from table
    group by 
        param1,
        yy,
        mm,
        dd,
        param2,
        param3,
        param4,
        param9,
        param10,
        param11,
        param12,
        param13,
        param14,
        param15,
        param16,
        param17,
        param18,
        param19,
        param20,
        param21,
        param22
    -- 定义两种需要的聚合粒度
    grouping sets (
        -- 日粒度：保留mm、dd维度
        (param1,yy,mm,dd,param2,param3,param4,param9,param10,param11,param12,param13,param14,param15,param16,param17,param18,param19,param20,param21,param22),
        -- 年粒度：去掉mm、dd维度
        (param1,yy,param2,param3,param4,param9,param10,param11,param12,param13,param14,param15,param16,param17,param18,param19,param20,param21,param22)
    )
)
select 
    param1,
    yy,
    mm,
    dd,
    param2,
    param3,
    param4,
    -- 取日粒度的计数
    max(case when agg_level = 0 then cnt_id1 end) as cnt_id1,
    max(case when agg_level = 0 then cnt_id2 end) as cnt_id2,
    max(case when agg_level = 0 then cnt_id3 end) as cnt_id3,
    -- 同分组下取年粒度的计数回填
    max(case when agg_level = 1 then cnt_id1 end) over(partition by param1,yy,param2,param3,param4,param9,param10,param11,param12,param13,param14,param15,param16,param17,param18,param19,param20,param21,param22) as cnt_id1_yy,
    max(case when agg_level = 1 then cnt_id2 end) over(partition by param1,yy,param2,param3,param4,param9,param10,param11,param12,param13,param14,param15,param16,param17,param18,param19,param20,param21,param22) as cnt_id2_yy,
    max(case when agg_level = 1 then cnt_id3 end) over(partition by param1,yy,param2,param3,param4,param9,param10,param11,param12,param13,param14,param15,param16,param17,param18,param19,param20,param21,param22) as cnt_id3_yy,
    param5,
    param6,
    param7,
    param8,
    param9,
    param10,
    param11,
    param12,
    param13,
    param14,
    param15,
    param16,
    param17,
    param18,
    param19,
    param20,
    param21,
    param22
from agg_all
-- 仅保留日粒度的行即可，年粒度指标已回填
qualify agg_level = 0

方案优势 #

• 仅扫描一次原表，相比原方案两次聚合+join的写法，性能提升明显
• 无需编写冗长的多表关联条件，无需重复定义大量相同字段，代码长度可缩减60%以上
• 原生支持count distinct去重计数逻辑，完全匹配业务场景

注：如果所用SQL引擎不支持qualify语法，可将外层查询改为子查询，再加where agg_level = 0过滤即可。

内容的提问来源于stack exchange，提问作者Andrey