SQL中筛选最接近Procedure_Dt的Check_In_Dt并限定时间范围求助

原始数据集 #

DROP TABLE IF EXISTS #df

CREATE TABLE #df 
(
    PTID VARCHAR(10),
    HospitalID VARCHAR(5),
    Procedure_Dt date,
    Check_In_Dt DATE,
);

INSERT INTO #df (PTID, HospitalID, Procedure_Dt, Check_In_Dt)
VALUES
('X0001', 'WY', '2021-07-25', '2021-07-23'),
('X0001', 'WY', '2021-07-25', '2021-10-24'),
('X0001', 'WY', '2021-07-25', '2021-10-27'),
('X0001', 'WY', '2021-07-25', '2021-06-24'),
('X0001', 'WY', '2021-07-25', '2022-06-10'),
('X0002', 'CA', '2022-08-25', '2022-08-26'),
('X0002', 'CA', '2022-08-25', '2022-08-27'),
('X0002', 'CA', '2022-08-25', '2022-08-29'),
('X0002', 'CA', '2022-08-25', '2022-09-22'),
('X0003', 'AL', '2023-02-02', NULL)

需求说明 #

需要筛选出每个患者（PTID）最接近手术日期（Procedure_Dt）的入院日期（Check_In_Dt），规则如下：

• 优先保留手术日期前0-40天的入院日期作为有效候选
• 若没有符合上述范围的入院日期，则选择所有记录中最接近手术日期的记录
• 保留入院日期为NULL的记录

期望结果 #

DROP TABLE IF EXISTS #df_final

CREATE TABLE #df_final 
(
    PTID VARCHAR(10),
    HospitalID VARCHAR(5),
    Procedure_Dt date,
    Check_In_Dt DATE,
    Date_Diff smallint
);

INSERT INTO #df_final (PTID, HospitalID, Procedure_Dt, Check_In_Dt, Date_Diff)
VALUES
('X0001', 'WY', '2021-07-25', '2021-07-23', 2),
('X0002', 'CA', '2022-08-25', '2022-08-26', -1),
('X0003', 'AL', '2023-02-02', NULL, NULL)

现有代码及问题 #

尝试的代码如下，但无法得到正确结果：

SELECT a.PTID, HospitalID
             , Procedure_Dt
             , Check_In_Dt
             , a.Date_Diff
FROM #df_datediff a
    JOIN (SELECT PTID, MIN(Check_In_Dt) AS Check_In_Date FROM #df_datediff GROUP BY PTID) B
        ON a.PTID = B.PTID
           AND a.Check_In_Dt = B.Check_In_Date
UNION /*Since using MAX in the above query removes Null Facesheets, we use this union to include the null facesheet accesses*/
SELECT a.PTID, HospitalID
             , Procedure_Dt
             , Check_In_Dt
             , a.Date_Diff
FROM #df_datediff a
WHERE Check_In_Dt IS NULL;

问题点：

• 对于PTID X0001，代码选中了最早的入院日期2021-06-24，而非符合有效范围且最接近手术日期的2021-07-23
• 逻辑未考虑“优先选择手术前0-40天”的规则，仅通过MIN(Check_In_Dt)筛选，不符合需求

解决方案 #

使用窗口函数ROW_NUMBER()实现优先级排序，精准筛选目标记录：

WITH ranked_records AS (
    SELECT 
        PTID,
        HospitalID,
        Procedure_Dt,
        Check_In_Dt,
        DATEDIFF(dd, Check_In_Dt, Procedure_Dt) AS Date_Diff,
        -- 排序逻辑：
        -- 1. 优先标记手术前0-40天的有效记录，排在前面
        -- 2. 按日期差的绝对值从小到大排序，确保最接近手术日期的记录优先
        -- 3. 若存在日期差绝对值相同的情况，选择较晚的入院日期
        ROW_NUMBER() OVER (
            PARTITION BY PTID 
            ORDER BY 
                CASE 
                    WHEN Check_In_Dt IS NOT NULL AND DATEDIFF(dd, Check_In_Dt, Procedure_Dt) BETWEEN 0 AND 40 
                    THEN 0 
                    ELSE 1 
                END,
                ABS(DATEDIFF(dd, Check_In_Dt, Procedure_Dt)) ASC,
                Check_In_Dt DESC
        ) AS rn
    FROM #df
)
SELECT 
    PTID,
    HospitalID,
    Procedure_Dt,
    Check_In_Dt,
    Date_Diff
FROM ranked_records
WHERE rn = 1;

代码说明 #

1. CTE部分：计算每个记录的日期差Date_Diff，并通过ROW_NUMBER()按PTID分组排序：
   • 用CASE语句区分有效候选（手术前0-40天），有效记录排序权重为0，非有效为1，确保有效记录优先被选中
   • 按日期差的绝对值排序，保证最接近手术日期的记录排在首位
   • 若出现日期差绝对值相同的情况（如手术前后各一天），选择较晚的入院日期
2. 最终筛选：取每个PTID中排序为1的记录，即为符合需求的结果

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