如何在SQL中按指定条件计算datediff日期差值

你的原有代码存在两个核心问题：

• 未覆盖规则4的边界场景：没有判断deadline_date为/N、三个状态日期均为2999-12-31的情况，导致这两类场景返回null而非要求的0
• 分组逻辑错误：last_status是聚合计算结果，不能放到group by中，否则会按用户+最终状态分组，导致单个用户出现多条记录

完整可运行代码 #

select 
    user,
    (array_agg(STATUS) within group(order by UPDATED_AT_DATETIME desc)[0])::varchar as last_status,
    coalesce(max(case when STATUS = 'clicked' THEN UPDATED_AT_DATETIME END),'2999-12-31'::datetime) as clicked_date,
    coalesce(max(case when STATUS = 'claimed' THEN UPDATED_AT_DATETIME END),'2999-12-31'::datetime) as claimed_date,
    coalesce(max(case when STATUS = 'bought' THEN ACCOUNT_REWARD_UPDATED_AT_DATETIME END),'2999-12-31'::datetime) as bought_date,
    case 
        -- 优先匹配规则4的返回0场景
        when DEADLINE_DATETIME = '/N' 
            or (clicked_date = '2999-12-31'::datetime and claimed_date = '2999-12-31'::datetime and bought_date = '2999-12-31'::datetime)
        then 0
        -- 按优先级匹配计算规则
        when clicked_date <> '2999-12-31'::datetime then DATEDIFF('days', clicked_date, TRY_TO_TIMESTAMP(DEADLINE_DATETIME))
        when claimed_date <> '2999-12-31'::datetime then DATEDIFF('days', claimed_date, TRY_TO_TIMESTAMP(DEADLINE_DATETIME))
        else DATEDIFF('days', bought_date, TRY_TO_TIMESTAMP(DEADLINE_DATETIME))
    end as number_days
from TBL_A a
group by user

逻辑说明 #

• 先判断规则4的两个场景，命中直接返回0
• 再按clicked > claimed > bought的优先级选择要计算的日期，避免缺漏
• 去掉了group by中的聚合字段，保证按用户维度聚合，每个用户仅返回一条记录

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