技术问询：如何按唯一ID匹配X类型记录获取对应最近Y类型记录？

核心需求 #

对于每个唯一记录ID，仅当存在更新的X类型记录时，返回该X记录时间点之前最近的Y类型记录。记录按EventDate降序排列（最新记录在顶部）。

典型场景案例 #

• 案例1：ID=1的记录中，X类型记录日期为7月29日，需返回日期为2月23日的Y类型记录（该Y是X之前最近的Y）。
• 案例2：ID=2的记录中有两条X类型记录（11月2日、7月2日），需分别返回对应的最近Y：10月31日的Y（对应11月2日的X）、2月23日的Y（对应7月2日的X）。
• 案例3：ID=3的记录中有两条X类型记录（7月2日、1月5日），需返回2月23日的Y类型记录（该Y是7月2日X之前最近的Y，且晚于1月5日的X）。
• 案例4：ID=4的Y记录（10月15日）晚于X记录（7月2日），不返回；ID=5的X记录（2月23日），需返回1月5日的Y类型记录（该Y是X之前最近的Y）。

现有SQL的缺陷 #

当前使用的SQL语句如下：

SELECT
    *
FROM
    (
        SELECT
            *,
            ROW_NUMBER() OVER ( PARTITION BY A.ID ORDER BY EventDate DESC ) AS pc

        FROM
            SOMETABLE AS "A"
            INNER JOIN
            (
                SELECT
                    ID AS 'BID',
                    MIN(EventDate) AS 'OldestDate'
                FROM
                    SOMETABLE
                WHERE
                    TYPE = 'X' 
                GROUP BY
                    ID
            ) AS "B" ON A.ID = B.BID

    WHERE
        EventDate < OldestDate
        AND
        Type = 'Y'

    ) AS "FINAL"

该语句的问题在于：它通过MIN(EventDate)获取每个ID的最早X记录日期，然后只保留早于这个日期的Y记录。这会过滤掉所有在最早X记录之后、后续X记录之前的Y记录，无法满足案例2、3这类需要为多条X记录匹配对应Y记录的场景。

修正后的SQL实现 #

以下是可以满足所有需求的SQL方案：

WITH AllRecords AS (
    SELECT
        ID,
        Type,
        EventDate,
        -- 获取当前记录之后（更新的）最近的X记录日期
        LEAD(CASE WHEN Type = 'X' THEN EventDate END) OVER (
            PARTITION BY ID 
            ORDER BY EventDate DESC
        ) AS NextXDate,
        -- 标记当前记录如果是X的话的日期
        CASE WHEN Type = 'X' THEN EventDate END AS XEventDate
    FROM SOMETABLE
),
XMatchedY AS (
    SELECT
        x.ID,
        x.XEventDate AS X_EventDate,
        y.EventDate AS Y_EventDate,
        y.*, -- 按需选择Y记录的字段
        -- 为每个X记录筛选出最近的前置Y
        ROW_NUMBER() OVER (
            PARTITION BY x.ID, x.XEventDate 
            ORDER BY y.EventDate DESC
        ) AS rn
    FROM AllRecords x
    JOIN AllRecords y 
        ON x.ID = y.ID
        AND y.Type = 'Y'
        AND y.EventDate < x.XEventDate
        -- 确保Y记录位于当前X和下一个更新的X之间（如果存在下一个X）
        AND (x.NextXDate IS NULL OR y.EventDate > x.NextXDate)
    WHERE x.Type = 'X'
)
SELECT *
FROM XMatchedY
WHERE rn = 1;

逻辑说明 #

1. AllRecords CTE：对每个ID的所有记录，标记X记录的日期，并通过LEAD窗口函数找到当前X记录之后（更新的）最近的另一条X记录的日期，以此划分Y记录的有效范围。
2. XMatchedY CTE：将每个X记录与ID相同、日期在当前X和下一个X（如果存在）之间的Y记录关联，然后按X记录分组，取每个X对应的最近Y记录（rn=1）。
3. 最终结果会为每个X记录匹配到符合要求的最近前置Y记录，完全覆盖所有案例场景。

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