如何按EYE变量获取INJECTION事件的最近前置关联事件？

问题分析 #

核心需求是按ID+EYE分组，保留每个组内所有INJECTION事件，以及每个INJECTION发生前对应事件（OCT/VA）的最近一次记录，同时排除无对应INJECTION的孤立事件（如原表中RIGHT眼2022-06-01的记录）。

通用SQL方案 #

以下SQL适配多ID场景，通过窗口函数精准关联INJECTION与前置事件：

WITH injection_dates AS (
    -- 提取所有INJECTION事件的日期，按ID+EYE分组
    SELECT ID, EYE, DATE AS injection_date
    FROM MY_TABLE
    WHERE EVENT = 'INJECTION'
),
record_next_injection AS (
    -- 为每条记录匹配其所属ID+EYE下，最近的后续INJECTION日期
    SELECT 
        t.ID,
        t.DATE,
        t.EVENT,
        t.EYE,
        MIN(i.injection_date) OVER (
            PARTITION BY t.ID, t.EYE 
            ORDER BY t.DATE DESC 
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        ) AS next_injection_date
    FROM MY_TABLE t
    LEFT JOIN injection_dates i 
        ON t.ID = i.ID 
        AND t.EYE = i.EYE 
        AND t.DATE <= i.injection_date
),
latest_pre_injection_records AS (
    -- 筛选每个ID+EYE+EVENT下，离对应INJECTION最近的记录
    SELECT 
        ID, DATE, EVENT, EYE
    FROM record_next_injection
    WHERE next_injection_date IS NOT NULL -- 排除无对应INJECTION的孤立记录
    QUALIFY ROW_NUMBER() OVER (
        PARTITION BY ID, EYE, EVENT, next_injection_date 
        ORDER BY DATE DESC
    ) = 1
)
-- 合并前置记录与所有INJECTION记录，按规则排序
SELECT ID, DATE, EVENT, EYE
FROM latest_pre_injection_records
UNION ALL
SELECT ID, DATE, EVENT, EYE
FROM MY_TABLE
WHERE EVENT = 'INJECTION'
ORDER BY ID, EYE, DATE, EVENT;

方案说明 #

1. injection_dates：单独提取所有INJECTION事件的日期，作为后续关联的基准。
2. record_next_injection：通过窗口函数MIN(...) OVER(...)为每条记录找到所属ID+EYE下最近的后续INJECTION日期，无对应INJECTION的记录会被标记为NULL。
3. latest_pre_injection_records：用QUALIFY+ROW_NUMBER()筛选出每个事件类型（OCT/VA）离对应INJECTION最近的记录。
4. 最终合并：将筛选后的前置记录与所有INJECTION记录合并，按ID、EYE、DATE、EVENT排序，得到目标结果。

原SQL问题说明 #

你之前的SQL逻辑存在两个关键缺陷：

• 分区仅按ID+DATE，未关联EYE，无法实现按眼分组的需求；
• 未关联INJECTION事件的逻辑，仅通过行号筛选，无法精准匹配INJECTION的前置事件，导致多ID场景失效。

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