如何在Amazon Redshift中获取ant_date后的下一个状态及对应日期

数据表结构 #

awb    manifest_date  out_branch_date  in_branch_date  ant_date    dlv_date      cc_date
ab123   2023-07-01      2023-07-02      2023-07-03    2023-07-04   2023-07-05    2023-07-04
ab124   2023-07-03      2023-07-03      2023-07-04    2023-07-05   2023-07-06    2023-07-08
ab125   2023-07-04      2023-07-09      2023-07-05    2023-07-06   2023-07-08    2023-07-09
ab127   2023-07-04      2023-07-05      2023-07-06    2023-07-07   2023-07-08    2023-07-05

需求说明 #

需要从所有日期列中找出ant_date之后紧接着的下一个状态及其对应日期。

原查询的问题 #

此前使用CASE WHEN编写的查询逻辑存在错误：原查询按固定顺序判断日期是否大于ant_date，一旦匹配就返回结果，忽略了该日期是否是所有符合条件的日期中最小的。比如ab124和ab125的cc_date虽大于ant_date，但dlv_date更早，正确状态应为dlv而非cc。

原查询代码：

select awb,
       case when manifest_date > ant_date then 'manifest'
            when out_branch_date > ant_date then 'out branch'
            when in_branch_date > ant_date then 'in branch'
            when dlv_date > ant_date then 'dlv'
            when cc_date > ant_date then 'cc' else null end as status_after_ant,
       case when manifest_date > ant_date then manifest_date
            when out_branch_date > ant_date then out_branch_date
            when in_branch_date > ant_date then in_branch_date
            when dlv_date > ant_date then dlv_date
            when cc_date > ant_date then cc_date else null end as date_status_after_ant
from data

修正后的查询方案 #

方案一：UNION ALL转成行+窗口函数排序 #

先将各状态列转换为行数据，筛选出大于ant_date的记录，再通过窗口函数取每个awb对应的最小日期状态：

WITH status_dates AS (
    SELECT awb, 'manifest' AS status, manifest_date AS status_date, ant_date FROM data WHERE manifest_date > ant_date
    UNION ALL
    SELECT awb, 'out branch' AS status, out_branch_date AS status_date, ant_date FROM data WHERE out_branch_date > ant_date
    UNION ALL
    SELECT awb, 'in branch' AS status, in_branch_date AS status_date, ant_date FROM data WHERE in_branch_date > ant_date
    UNION ALL
    SELECT awb, 'dlv' AS status, dlv_date AS status_date, ant_date FROM data WHERE dlv_date > ant_date
    UNION ALL
    SELECT awb, 'cc' AS status, cc_date AS status_date, ant_date FROM data WHERE cc_date > ant_date
),
ranked_statuses AS (
    SELECT 
        awb, status, status_date,
        ROW_NUMBER() OVER (PARTITION BY awb ORDER BY status_date ASC) AS rn
    FROM status_dates
)
SELECT 
    awb,
    status AS status_after_ant,
    status_date AS date_status_after_ant
FROM ranked_statuses
WHERE rn = 1
ORDER BY awb;

方案二：LATERAL连接+VALUES构造行（更简洁） #

利用Redshift支持的LATERAL连接，将各状态列构造为临时行，直接筛选并取最小日期的状态：

SELECT 
    d.awb,
    rs.status AS status_after_ant,
    rs.status_date AS date_status_after_ant
FROM data d
LEFT JOIN LATERAL (
    SELECT status, status_date
    FROM (
        VALUES
            ('manifest', d.manifest_date),
            ('out branch', d.out_branch_date),
            ('in branch', d.in_branch_date),
            ('dlv', d.dlv_date),
            ('cc', d.cc_date)
    ) AS s(status, status_date)
    WHERE s.status_date > d.ant_date
    ORDER BY s.status_date ASC
    LIMIT 1
) rs ON TRUE
ORDER BY d.awb;

两种方案均能准确找出ant_date之后最早的状态日期，解决原查询的逻辑缺陷。

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