如何在SQL中按优先级基于多日期列计算距离截止日期的天数差

原有代码错误原因 #

• 同层SELECT引用别名不生效：SQL执行顺序中，SELECT子句定义的别名无法在同一层SELECT的其他表达式中直接调用，你在CASE中直接使用clicked_date、claimed_date等别名会触发字段不存在的报错。
• 分组规则错误：原代码GROUP BY 1,2中，第二位对应的last_status是聚合函数计算的结果，不属于分组维度，分组维度只有user，会触发分组逻辑错误。
• 规则覆盖不全：原CASE逻辑没有覆盖「三个状态日期全为2999-12-31」、「deadline_date值为/N」的场景，这两类情况不会返回0，会得到NULL值。
• 类型适配问题：deadline_date如果是字符串类型的'/N'，直接调用TRY_TO_TIMESTAMP会返回NULL，需要提前做判断。

正确实现代码 #

采用CTE先计算聚合后的各状态日期，再在外层按规则计算天数差：

WITH user_agg_data AS (
    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,
        -- 如果单用户对应多个截止日期，可根据业务调整为MAX(DEADLINE_DATETIME)等聚合逻辑
        DEADLINE_DATETIME
    FROM TBL_A a
    GROUP BY user, DEADLINE_DATETIME
)
SELECT 
    *,
    CASE
        -- 优先处理返回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 user_agg_data

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