窗口函数中CASE表达式全返回空值问题排查求助

核心错误原因 #

• 逻辑矛盾：任意一行的time_key只能对应单个日期，不可能同时等于2021-05-01和2021-06-01，所有CASE的WHEN判断条件永远不成立，自然全部返回空值。
• 窗口函数逻辑错误：你需要对比同一个用户（subs_key+ban_key）两个不同月份行的active_group值，不能直接判断当前行的两个不同时间值，需要先通过窗口函数拉取同用户其他月份的状态值再做判断。
• 语法错误：CASE表达式后直接加as migration_status的写法错误，别名应放在窗口函数结束的外侧，当前的括号包裹逻辑也不符合窗口函数的语法规范。

修复方案 #

方案1：使用LAG/LEAD窗口函数（保留原表行结构） #

select 
    subs_key || ban_key as user_unique_key,
    TIME_KEY,  
    PRICE_PLAN_CHANGE_DATE,  
    active_group,
    case
        -- 给6月的行标记迁移状态
        when time_key = to_date('01.06.21','dd.mm.yy') and lag(active_group,1) over user_window = '1' and active_group = '0' then 'out paid migr'
        when time_key = to_date('01.06.21','dd.mm.yy') and lag(active_group,1) over user_window = '0' and active_group = '1' then 'in paid migr'
        when time_key = to_date('01.06.21','dd.mm.yy') and lag(active_group,1) over user_window = '1' and active_group = '1' then 'core base'
        -- 也可以给5月的行同步标记状态，不需要可以删掉下面三行
        when time_key = to_date('01.05.21','dd.mm.yy') and lead(active_group,1) over user_window = '0' and active_group = '1' then 'out paid migr'
        when time_key = to_date('01.05.21','dd.mm.yy') and lead(active_group,1) over user_window = '1' and active_group = '0' then 'in paid migr'
        when time_key = to_date('01.05.21','dd.mm.yy') and lead(active_group,1) over user_window = '1' and active_group = '1' then 'core base'
    end as migration_status
from s
window user_window as (partition by subs_key || ban_key order by time_key)

方案2：行转列聚合（每个用户仅返回一行结果，更直观） #

select 
    user_unique_key,
    may_active,
    jun_active,
    case
        when may_active = '1' and jun_active = '0' then 'out paid migr'
        when may_active = '0' and jun_active = '1' then 'in paid migr'
        when may_active = '1' and jun_active = '1' then 'core base'
    end as migration_status
from (
    select 
        subs_key || ban_key as user_unique_key,
        max(case when time_key = to_date('01.05.21','dd.mm.yy') then active_group end) as may_active,
        max(case when time_key = to_date('01.06.21','dd.mm.yy') then active_group end) as jun_active
    from s
    group by subs_key || ban_key
) t

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