SQL问题：多Segment日期计数差值合并查询返回Null值

需要制作以Segment和日期为筛选条件的汇总表，计算同一张表中两个日期的计数差值。当前多Segment合并查询返回列值全为Null，但单个Segment查询能得到正确结果。需求是计算每个Segment对应的2022/10/14计数减去2022/10/07计数的差值。

多Segment查询语句（返回Null）：

select
    case
        when SEGMENT='champion'THEN SUM(SEGMENTED_DATE = '2022-10-14')-SUM(SEGMENTED_DATE='2022-10-07')
    END as total_change_champion,
    case
        when SEGMENT='Hibernating'THEN SUM(SEGMENTED_DATE = '2022-10-14')-SUM(SEGMENTED_DATE='2022-10-07')
    END as  total_change_hibernate
from weekly_customer_RFM_TABLE;

单Segment查询语句（结果正确）：

select
    SUM(SEGMENTED_DATE = '2022-10-14')-
    SUM(SEGMENTED_DATE='2022-10-07')as total_changes_in_14th
From weekly_customer_RFM_TABLE
where
SEGMENT ='Hibernating'

原查询未添加GROUP BY SEGMENT，直接对全表聚合时，CASE语句仅会匹配到某一行的Segment值，其余分支均返回Null，最终聚合结果全为Null。

方案1：横向展示各Segment差值 #

先通过子查询按Segment分组计算两个日期的计数，再将结果横向展示为列：

select
    MAX(case when SEGMENT='champion' then date_14_count - date_07_count end) as total_change_champion,
    MAX(case when SEGMENT='Hibernating' then date_14_count - date_07_count end) as total_change_hibernate
from (
    select
        SEGMENT,
        SUM(SEGMENTED_DATE = '2022-10-14') as date_14_count,
        SUM(SEGMENTED_DATE = '2022-10-07') as date_07_count
    from weekly_customer_RFM_TABLE
    where SEGMENT in ('champion', 'Hibernating')
    group by SEGMENT
) t;

方案2：纵向展示各Segment差值（扩展性更强） #

若后续需新增Segment，该方案无需修改查询结构：

select
    SEGMENT,
    SUM(SEGMENTED_DATE = '2022-10-14') - SUM(SEGMENTED_DATE = '2022-10-07') as total_change
from weekly_customer_RFM_TABLE
where SEGMENT in ('champion', 'Hibernating')
group by SEGMENT;

补充说明 #

• 添加WHERE SEGMENT IN (...)可过滤无关Segment，提升查询效率；
• 方案2的结果以行形式呈现，后续新增Segment只需修改IN中的值即可，维护成本更低。

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