Redshift/PostgreSQL分组末行条件选日期计算发票状态的SQL方案

你可以通过窗口函数标记每个发票的最新上报记录，再根据标记动态替换判断用的日期即可，Redshift/PostgreSQL都支持该语法：

WITH ranked_trans AS (
    SELECT 
        invoice_Id,
        report_date,
        due_date,
        paid_date,
        -- 按发票分组，上报日期倒序排序，rn=1即为该发票最新上报记录
        ROW_NUMBER() OVER(PARTITION BY invoice_Id ORDER BY report_date DESC) AS rn
    FROM reported_transaction
)
SELECT 
    report_date,
    due_date,
    paid_date,
    CASE
        WHEN paid_date IS NOT NULL THEN 'PAID'
        -- 最新记录用当前日期判断，非最新记录用上报日期判断
        WHEN (CASE WHEN rn = 1 THEN DATE(GETDATE()) ELSE report_date END) > due_date THEN 'PAST_DUE'
        ELSE 'PENDING'
    END AS status
FROM ranked_trans;

逻辑说明 #

• 先用CTE给每条记录打分组排序标记，同一invoice_Id下report_date最大的记录rn值为1，就是该发票的最新上报记录
• 日期判断优先级：只要paid_date非空就直接判定为已支付，不受上报时间影响
• 如果需要保留时间维度的判断，可以把DATE(GETDATE())改成GETDATE()，和你的字段类型对齐即可
• 如果存在同一发票同一天有多条上报记录的情况，可以把ROW_NUMBER换成RANK，把当天所有上报记录都标记为最新

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