基于日期区间合并处方记录：连续用药时长统计SQL需求

需求说明 #

通过SQL合并患者的处方记录，统计同一种药物/医嘱类型的连续用药时长，核心规则如下：

• 对于相同Generic_Name、Route且cadance_value为NULL的记录，直接保留原有状态
• 若同组（相同Generic_Name、Route）记录中存在cadance_value不为NULL的条目，该药物的用药时长需从前一订单的e_date延长cadance_value天
• 针对**Generic_Name、Route相同且Route为'R'**的记录：若当前记录的s_date≤前序记录的扩展日期，则合并为单条；若s_date超出最大扩展日期，则单独分为一组

示例数据 #

CREATE TABLE prescriptions (
    patient_id INT,
    Generic_Name VARCHAR(50),
    Route VARCHAR(10),
    s_date DATE,
    e_date DATE,
    cadance_value INT
);

INSERT INTO prescriptions VALUES
(1, 'Amoxicillin', 'R', '2023-01-01', '2023-01-10', NULL),
(1, 'Amoxicillin', 'R', '2023-01-08', '2023-01-18', 5),
(1, 'Lisinopril', 'O', '2023-02-01', '2023-02-28', NULL),
(1, 'Amoxicillin', 'R', '2023-01-20', '2023-01-30', NULL);

预期结果 #

实现SQL代码 #

WITH ranked_prescriptions AS (
    -- 按患者、药物、路径分组，按起始日期排序并生成行号
    SELECT 
        patient_id,
        Generic_Name,
        Route,
        s_date,
        e_date,
        cadance_value,
        ROW_NUMBER() OVER (PARTITION BY patient_id, Generic_Name, Route ORDER BY s_date) AS rn
    FROM prescriptions
),
extended_dates AS (
    -- 计算每条记录的扩展结束日期：前一条的结束日期 + 当前的cadance_value（若存在）
    SELECT 
        *,
        CASE 
            WHEN rn = 1 THEN e_date
            ELSE LAG(e_date) OVER (PARTITION BY patient_id, Generic_Name, Route ORDER BY rn) + COALESCE(cadance_value, 0)
        END AS extended_end
    FROM ranked_prescriptions
),
grouped_records AS (
    -- 处理Route为'R'的记录，根据s_date与扩展日期的关系分组
    SELECT 
        *,
        SUM(
            CASE 
                WHEN s_date > LAG(extended_end, 1, '1900-01-01') OVER (PARTITION BY patient_id, Generic_Name, Route ORDER BY rn) 
                THEN 1 ELSE 0 
            END
        ) OVER (PARTITION BY patient_id, Generic_Name, Route ORDER BY rn) AS group_id
    FROM extended_dates
    WHERE Route = 'R'
    
    UNION ALL
    
    -- 保留cadance_value为NULL且Route非'R'的原始记录，单独分组
    SELECT 
        patient_id,
        Generic_Name,
        Route,
        s_date,
        e_date,
        cadance_value,
        rn,
        e_date AS extended_end,
        rn AS group_id
    FROM ranked_prescriptions
    WHERE cadance_value IS NULL AND Route != 'R'
)
-- 按分组聚合，得到合并后的起始/结束日期
SELECT 
    patient_id,
    Generic_Name,
    Route,
    MIN(s_date) AS start_date,
    MAX(CASE WHEN cadance_value IS NOT NULL THEN extended_end ELSE e_date END) AS end_date
FROM grouped_records
GROUP BY patient_id, Generic_Name, Route, group_id
ORDER BY patient_id, Generic_Name, start_date;

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