海量多阶段案件数据中，如何将交易日期匹配对应案件主阶段？

有两张表的查询语句如下：

案件阶段查询语句 #

SELECT 
    s.case_id, 
    s.start_date,
    s.group_phase_code,
    l.main_phase,
    l.detailed_phase,
    ROW_NUMBER () OVER (PARTITION BY s.case_id ORDER BY s.start_date) AS row_num
FROM system3020.group_case_phase AS s
LEFT JOIN lookup.case_phase as l
ON s.group_phase_code = l.code
WHERE s.case_id = '1002389';

该查询展示案件阶段随时间的变化，start_date代表阶段开始时间，下一个阶段的start_date即为上一阶段的结束时间。

交易记录查询语句 #

SELECT case_id, transaction_date, (-1 * amount) AS amount
FROM system3020.transactions
WHERE case_id = '1002389' AND payment_cost_ind = 'P' AND orig_cost_type != 'IJ'

核心需求 #

根据交易发生的时间段，将第一张查询结果中的main_phase匹配到第二张查询的每一条交易记录的transaction_date上。例如：交易日期为2010-12-16时对应main_phase为legal，2008-09-14时对应amicable。

由于数据量庞大，每个case_id的阶段数量和类型各不相同，因此不能固定case_id或阶段数量。

要实现动态匹配每个交易对应的案件阶段，可以通过以下步骤完成：

1. 为案件阶段生成结束时间：用窗口函数LEAD()获取每个阶段的下一个开始时间，作为当前阶段的结束时间；如果是最后一个阶段，结束时间设为'9999-12-31'，确保后续所有交易都能匹配到最后一个阶段。
2. 关联交易与阶段数据：通过case_id关联两张表，同时让交易的transaction_date落在对应阶段的时间区间内。

完整SQL语句如下：

WITH case_phases AS (
    SELECT 
        s.case_id,
        s.start_date,
        -- 获取下一个阶段的开始时间作为当前阶段的结束时间
        LEAD(s.start_date) OVER (PARTITION BY s.case_id ORDER BY s.start_date) AS end_date,
        l.main_phase,
        l.detailed_phase
    FROM system3020.group_case_phase AS s
    LEFT JOIN lookup.case_phase AS l
        ON s.group_phase_code = l.code
),
transactions_data AS (
    SELECT 
        case_id,
        transaction_date,
        (-1 * amount) AS amount
    FROM system3020.transactions
    WHERE payment_cost_ind = 'P' AND orig_cost_type != 'IJ'
)
SELECT 
    t.case_id,
    t.transaction_date,
    t.amount,
    p.main_phase,
    p.detailed_phase
FROM transactions_data t
LEFT JOIN case_phases p
    ON t.case_id = p.case_id
    -- 交易日期落在当前阶段的时间区间内
    AND t.transaction_date >= p.start_date
    AND t.transaction_date < COALESCE(p.end_date, '9999-12-31');

关键说明 #

• LEAD()窗口函数自动处理阶段顺序，无需固定阶段数量，适配所有case_id的阶段变化。
• COALESCE(p.end_date, '9999-12-31')解决最后一个阶段无后续阶段的边界问题，避免遗漏交易匹配。
• 用CTE（公共表表达式）拆分逻辑，让代码结构清晰，便于后续维护和调整。

内容的提问来源于stack exchange，提问作者Mikas Jankeliūnas