多表全外连接后消除重复YEAR_MONTH列问题求助

背景与需求 #

我需要从同一张表中按不同条件统计多组月度数据，最终合并成一张包含所有年月、各统计值的汇总表，缺失的统计值用NULL填充。

原始单组统计查询 #

单组统计的SQL模板如下：

select 
    TO_VARCHAR(CREATE_TIME, 'yyyy-MM') as YEAR_MONTH,
    COUNT(1) as DESIRED_VALUE
from
    MY_TABLE
where 
    FIELD = 'DESIRED_VALUE'
group by 1;

单组查询结果示例 #

• 统计值1的结果：

YEAR_MONTH  DESIRED_VALUE1
2022-09     52
2022-10     117
2022-11     95
2023-01     73

• 统计值2的结果：

YEAR_MONTH  DESIRED_VALUE2
2022-11     35
2022-12     30
2023-01     29

期望的汇总表格式 #

希望合并后得到如下结构的汇总表：

YEAR_MONTH  DESIRED_VALUE1  DESIRED_VALUE2
2022-09     52              NULL
2022-10     117             NULL
2022-11     95              35
2022-12     53              30 
2023-01     73              29

尝试的解决方案及问题 #

两表合并的可行方案 #

因为无法预知各查询的日期范围，我采用全外连接合并两表，并用COALESCE处理重复的年月列，SQL如下：

with result_1 as 
    (
        select 
            TO_VARCHAR(CREATE_TIME, 'yyyy-MM') as YEAR_MONTH,
            COUNT(1) as DESIRED_VALUE1
        from
            MY_TABLE
        where 
            STATUS = 'DESIRED_VALUE1'
        group by 1
    ),
result_2 as 
    (
        select 
            TO_VARCHAR(CREATE_TIME, 'yyyy-MM') as YEAR_MONTH,
            COUNT(1) as DESIRED_VALUE2
        from
            MY_TABLE
        where 
            STATUS = 'DESIRED_VALUE2'
        group by 1
    ) 
select 
    COALESCE(result_1.YEAR_MONTH, result_2.YEAR_MONTH) as YEAR_MONTH,
    DESIRED_VALUE1,
    DESIRED_VALUE2
from 
    result_1 
full outer join result_2
on  
    result_1.YEAR_MONTH = result_2.YEAR_MONTH
order by YEAR_MONTH desc;

这个方案处理两表时正常，但加入第三张表后出现问题。

三表合并的问题 #

加入第三组统计后，我扩展了全外连接的SQL，但结果出现了重复的YEAR_MONTH行：

with result_1 as 
    (
        select 
            TO_VARCHAR(CREATE_TIME, 'yyyy-MM') as YEAR_MONTH,
            COUNT(1) as DESIRED_VALUE1
        from
            MY_TABLE
        where 
            STATUS = 'DESIRED_VALUE1'
        group by 1
    ),
result_2 as 
    (
        select 
            TO_VARCHAR(CREATE_TIME, 'yyyy-MM') as YEAR_MONTH,
            COUNT(1) as DESIRED_VALUE2
        from
            MY_TABLE
        where 
            STATUS = 'DESIRED_VALUE2'
        group by 1
    ), 
result_3 as 
    (
        select 
            TO_VARCHAR(CREATE_TIME, 'yyyy-MM') as YEAR_MONTH,
            COUNT(1) as DESIRED_VALUE3
        from
            MY_TABLE
        where 
            STATUS = 'DESIRED_VALUE3'
            and CONDITION = 'CONDITION'
        group by 1
    )
select 
    COALESCE(result_1.YEAR_MONTH, result_2.YEAR_MONTH, result_3.YEAR_MONTH) as YEAR_MONTH,
    DESIRED_VALUE1,
    DESIRED_VALUE2,
    DESIRED_VALUE3
from 
    result_1 
full outer join 
    result_2
on  
    result_1.YEAR_MONTH = result_2.YEAR_MONTH
full outer join 
    result_3
on
    result_2.YEAR_MONTH = result_3.YEAR_MONTH
order by YEAR_MONTH desc; 

得到的错误结果：

YEAR_MONTH  DESIRED_VALUE1  DESIRED_VALUE2  DESIRED_VALUE3
2023-01     73              29              83
2022-12     53              30              57
2022-11     95              35              71
2022-10     NULL            39              NULL        
2022-10     117             NULL            NULL    
2022-09     18              NULL            NULL    
2022-09     52              NULL            NULL    

可行的解决方案 #

方案1：使用条件聚合（推荐，性能更优） #

不需要多次子查询和全外连接，直接通过一次扫描表，用CASE语句按条件统计：

select
    TO_VARCHAR(CREATE_TIME, 'yyyy-MM') as YEAR_MONTH,
    COUNT(CASE WHEN STATUS = 'DESIRED_VALUE1' THEN 1 END) as DESIRED_VALUE1,
    COUNT(CASE WHEN STATUS = 'DESIRED_VALUE2' THEN 1 END) as DESIRED_VALUE2,
    COUNT(CASE WHEN STATUS = 'DESIRED_VALUE3' AND CONDITION = 'CONDITION' THEN 1 END) as DESIRED_VALUE3
from MY_TABLE
group by TO_VARCHAR(CREATE_TIME, 'yyyy-MM')
order by YEAR_MONTH desc;

• 原理：CASE语句只在满足条件时返回1，COUNT会忽略NULL，从而实现分组统计不同条件的数量。
• 优势：只需要扫描一次表，性能远优于多次子查询+全外连接，且不会出现重复行问题。

方案2：修复全外连接的关联逻辑 #

如果必须使用全外连接，需要调整第三表的关联条件，关联到合并后的年月而非仅第二表的年月：

with result_1 as 
    (
        select 
            TO_VARCHAR(CREATE_TIME, 'yyyy-MM') as YEAR_MONTH,
            COUNT(1) as DESIRED_VALUE1
        from
            MY_TABLE
        where 
            STATUS = 'DESIRED_VALUE1'
        group by 1
    ),
result_2 as 
    (
        select 
            TO_VARCHAR(CREATE_TIME, 'yyyy-MM') as YEAR_MONTH,
            COUNT(1) as DESIRED_VALUE2
        from
            MY_TABLE
        where 
            STATUS = 'DESIRED_VALUE2'
        group by 1
    ), 
result_3 as 
    (
        select 
            TO_VARCHAR(CREATE_TIME, 'yyyy-MM') as YEAR_MONTH,
            COUNT(1) as DESIRED_VALUE3
        from
            MY_TABLE
        where 
            STATUS = 'DESIRED_VALUE3'
            and CONDITION = 'CONDITION'
        group by 1
    )
select 
    COALESCE(t1.YEAR_MONTH, t2.YEAR_MONTH, t3.YEAR_MONTH) as YEAR_MONTH,
    t1.DESIRED_VALUE1,
    t2.DESIRED_VALUE2,
    t3.DESIRED_VALUE3
from 
    result_1 t1
full outer join 
    result_2 t2 on t1.YEAR_MONTH = t2.YEAR_MONTH
full outer join 
    result_3 t3 on COALESCE(t1.YEAR_MONTH, t2.YEAR_MONTH) = t3.YEAR_MONTH
order by YEAR_MONTH desc;

• 原理：第三表的关联条件使用COALESCE(t1.YEAR_MONTH, t2.YEAR_MONTH)，确保不管前两表哪一个有值，都能和第三表正确关联，避免出现不匹配导致的重复行。

方案3：先收集所有唯一年月，再左连接各统计结果 #

先通过子查询获取所有可能的年月，再分别左连接各组统计结果：

with all_months as (
    select distinct TO_VARCHAR(CREATE_TIME, 'yyyy-MM') as YEAR_MONTH
    from MY_TABLE
    -- 如果需要包含无数据的年月，可根据实际需求生成连续年月
),
result_1 as 
    (
        select 
            TO_VARCHAR(CREATE_TIME, 'yyyy-MM') as YEAR_MONTH,
            COUNT(1) as DESIRED_VALUE1
        from
            MY_TABLE
        where 
            STATUS = 'DESIRED_VALUE1'
        group by 1
    ),
result_2 as 
    (
        select 
            TO_VARCHAR(CREATE_TIME, 'yyyy-MM') as YEAR_MONTH,
            COUNT(1) as DESIRED_VALUE2
        from
            MY_TABLE
        where 
            STATUS = 'DESIRED_VALUE2'
        group by 1
    ), 
result_3 as 
    (
        select 
            TO_VARCHAR(CREATE_TIME, 'yyyy-MM') as YEAR_MONTH,
            COUNT(1) as DESIRED_VALUE3
        from
            MY_TABLE
        where 
            STATUS = 'DESIRED_VALUE3'
            and CONDITION = 'CONDITION'
        group by 1
    )
select 
    am.YEAR_MONTH,
    r1.DESIRED_VALUE1,
    r2.DESIRED_VALUE2,
    r3.DESIRED_VALUE3
from all_months am
left join result_1 r1 on am.YEAR_MONTH = r1.YEAR_MONTH
left join result_2 r2 on am.YEAR_MONTH = r2.YEAR_MONTH
left join result_3 r3 on am.YEAR_MONTH = r3.YEAR_MONTH
order by am.YEAR_MONTH desc;

• 原理：先获取所有存在数据的年月（如果需要连续年月，可扩展生成逻辑），再通过左连接把各组统计值匹配到对应的年月，天然避免重复行。

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