如何用SQL填充医疗数据中存在日期重叠的记录？

处理医疗数据时，常遇到患者从NURSING HOME转至医院/ICU期间，疗养院为其保留床位的情况——此时原数据中疗养院记录的Discharge_Date为MISSING，需要补全所有间隔期的疗养院记录，形成连续的患者位置时间链。

现有数据示例 #

期望输出格式 #

解决方案：CTE+窗口函数+UNION ALL组合 #

避免复杂嵌套的CASE WHEN，用以下方案提升效率并覆盖所有场景：

具体SQL代码 #

WITH ranked_records AS (
    -- 为每个患者的记录按入院日期排序，获取下一条记录的入院日期
    SELECT 
        Patient,
        Admission_Date,
        Discharge_Date,
        Location,
        LEAD(Admission_Date) OVER (PARTITION BY Patient ORDER BY Admission_Date) AS next_admission_date
    FROM your_medical_table
),
processed_nursing AS (
    -- 处理原有疗养院记录：将MISSING替换为下一条记录的入院日期（若存在）
    SELECT 
        Patient,
        Admission_Date,
        CASE 
            WHEN Discharge_Date = 'MISSING' AND next_admission_date IS NOT NULL 
            THEN next_admission_date 
            ELSE Discharge_Date 
        END AS Discharge_Date,
        Location
    FROM ranked_records
    WHERE Location = 'NURSING HOME'
),
hospital_return_records AS (
    -- 提取医院/ICU记录，并生成出院后回到疗养院的过渡记录
    SELECT 
        Patient,
        Admission_Date,
        Discharge_Date,
        Location
    FROM ranked_records
    WHERE Location != 'NURSING HOME'
    UNION ALL
    SELECT 
        Patient,
        Discharge_Date AS Admission_Date,
        next_admission_date AS Discharge_Date,
        'NURSING HOME' AS Location
    FROM ranked_records
    WHERE Location != 'NURSING HOME' AND next_admission_date IS NOT NULL
),
final_nursing_stay AS (
    -- 补全最后一次出院后回到疗养院的长期保留记录
    SELECT * FROM processed_nursing
    UNION ALL
    SELECT 
        Patient,
        MAX(Discharge_Date) AS Admission_Date,
        'MISSING' AS Discharge_Date,
        'NURSING HOME' AS Location
    FROM ranked_records
    WHERE Patient NOT IN (
        SELECT Patient FROM ranked_records 
        WHERE Location = 'NURSING HOME' AND Discharge_Date = 'MISSING'
    )
    GROUP BY Patient
)
-- 合并所有记录并按日期排序，排除无效的日期重合记录
SELECT * 
FROM (
    SELECT * FROM processed_nursing
    UNION ALL
    SELECT * FROM hospital_return_records
    UNION ALL
    SELECT * FROM final_nursing_stay
) combined
WHERE NOT (Location = 'NURSING HOME' AND Admission_Date = Discharge_Date)
ORDER BY Patient, Admission_Date;

方案优势 #

1. 效率更高：仅通过几次数据扫描完成处理，窗口函数LEAD的计算复杂度为O(n log n)，远低于嵌套CASE WHEN的多次关联查询。
2. 覆盖所有场景：自动处理初始疗养院记录补全、医院/ICU出院后的过渡疗养院记录、最后一次出院后的长期保留记录。
3. 可读性强：通过CTE拆分逻辑，每一步职责清晰，便于后续维护和调整。

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