获取每个用户倒数第二个DEF_ENDING为X后的首个BEG_PERIOD日期

需求说明 #

获取每个USER_ID对应的倒数第二条DEF_ENDING值为X的记录之后的第一条BEG_PERIOD日期。

现有PERIODS表数据 #

尝试的SQL（仅能获取最新日期） #

SELECT
    p.USER_ID,
    p.BEG_PERIOD
FROM
    PERIODS p
    INNER JOIN PERIODS p2 ON
        p.USER_ID = p2.USER_ID
        AND
        p.BEG_PERIOD = (
            SELECT
                MAX( BEG_PERIOD )
            FROM
                PERIODS
            WHERE
                PERIODS.USER_ID = p.USER_ID
        )
WHERE
    p.USER_ID > 10

正确解决方案 #

要实现需求，需要分两步：先定位每个用户的倒数第二条X记录，再找到该记录之后的第一条记录的BEG_PERIOD。以下是基于窗口函数的SQL实现：

WITH all_records AS (
    SELECT 
        USER_ID,
        BEG_PERIOD,
        END_PERIOD,
        DEF_ENDING,
        -- 给每个用户的X记录按时间升序编号
        COUNT(CASE WHEN DEF_ENDING = 'X' THEN 1 END) OVER (PARTITION BY USER_ID ORDER BY BEG_PERIOD) AS x_seq,
        -- 统计每个用户的X记录总数
        COUNT(CASE WHEN DEF_ENDING = 'X' THEN 1 END) OVER (PARTITION BY USER_ID) AS total_x
    FROM PERIODS
),
second_last_x AS (
    SELECT 
        USER_ID,
        END_PERIOD AS x_end_date
    FROM all_records
    WHERE DEF_ENDING = 'X' AND x_seq = total_x - 1
)
SELECT 
    slx.USER_ID,
    MIN(p.BEG_PERIOD) AS target_beg_period
FROM second_last_x slx
JOIN PERIODS p ON slx.USER_ID = p.USER_ID
WHERE p.BEG_PERIOD > slx.x_end_date
GROUP BY slx.USER_ID;

逻辑说明 #

1. all_records CTE：给每个用户的所有记录计算两个值：x_seq是该用户截至当前记录的X记录累计数，total_x是该用户的X记录总数量。
2. second_last_x CTE：筛选出每个用户的倒数第二条X记录（即x_seq = total_x - 1的X记录），并保留其结束日期。
3. 最后关联原表，找到该用户中所有开始日期晚于倒数第二条X记录结束日期的记录，取最小的开始日期即为目标日期。

结果验证 #

执行上述SQL后，将得到以下结果：

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