如何用group_concat拆分重叠日期并合并药物生成用药方案？

问题背景 #

我有一张患者用药数据表，包含patient_id、drug_name、episode_no、episode_start、episode_end字段，单患者的样本数据如下：

PATIENT_ID DRUG EPISODE EPISODE_START        EPISODE_END
773        X    1       2013-01-22 00:00:00  2013-04-22 00:00:00
773        X    2       2013-06-02 00:00:00  2014-03-12 00:00:00
773        Y    1       2013-10-28 00:00:00  2014-01-22 00:00:00

我需要生成患者的用药方案表，按时间分段合并同期使用的所有药物，最终得到如下结构的结果：

PATIENT_ID DRUG_TAKEN REGIMEN_NO REGIMEN_START        REGIMEN_END
773        X          1          2013-01-22 00:00:00  2013-04-22 00:00:00
773        X          2          2013-06-02 00:00:00  2013-10-28 00:00:00
773        X+Y        3          2013-10-28 00:00:00  2014-01-22 00:00:00
773        X          4          2014-01-22 00:00:00  2014-03-12 00:00:00

注意：2013-04-22至2013-06-02期间患者未用药，无需纳入结果。我不清楚怎么用group_concat来拆分重叠日期并合并药物，希望得到技术帮助。

解决方案思路 #

这个问题的核心是找出所有时间分段的分界点，然后针对每个时间段统计该时段内正在使用的药物，最后合并结果并生成方案编号。以下是基于MySQL的具体实现：

WITH 
-- 步骤1：提取所有用药的开始/结束时间点（作为分段分界）
time_points AS (
    SELECT patient_id, episode_start AS point_time FROM your_table
    UNION
    SELECT patient_id, episode_end AS point_time FROM your_table
),
-- 步骤2：生成连续的时间区间（跳过无用药的间隔）
time_intervals AS (
    SELECT 
        patient_id,
        point_time AS regimen_start,
        LEAD(point_time) OVER (PARTITION BY patient_id ORDER BY point_time) AS regimen_end
    FROM time_points
    WHERE LEAD(point_time) OVER (PARTITION BY patient_id ORDER BY point_time) IS NOT NULL
),
-- 步骤3：匹配每个区间内的用药，合并同期药物
regimen_drugs AS (
    SELECT 
        ti.patient_id,
        GROUP_CONCAT(DISTINCT t.drug_name ORDER BY t.drug_name SEPARATOR '+') AS drug_taken,
        ti.regimen_start,
        ti.regimen_end
    FROM time_intervals ti
    JOIN your_table t 
        ON ti.patient_id = t.patient_id
        AND ti.regimen_start < t.episode_end
        AND ti.regimen_end > t.episode_start
    GROUP BY ti.patient_id, ti.regimen_start, ti.regimen_end
),
-- 步骤4：为每个患者的用药方案生成连续编号
final_regimens AS (
    SELECT 
        patient_id,
        drug_taken,
        ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY regimen_start) AS regimen_no,
        regimen_start,
        regimen_end
    FROM regimen_drugs
)
SELECT * FROM final_regimens ORDER BY patient_id, regimen_no;

代码细节解释 #

1. time_points：收集所有用药的开始和结束时间，用UNION去重，确保每个时间点只出现一次，这些点是划分用药时段的核心依据。
2. time_intervals：用LEAD()窗口函数把每个时间点和下一个时间点配对，形成连续的时间区间；同时过滤掉最后一个没有后续时间点的记录。
3. regimen_drugs：通过区间与用药时段的重叠判断（regimen_start < episode_end且regimen_end > episode_start），找出每个区间内正在使用的药物，再用GROUP_CONCAT()按字母顺序合并药物名称，保证X+Y的格式统一。
4. final_regimens：用ROW_NUMBER()为每个患者的用药时段生成连续的方案编号，最后按患者和编号排序输出。

注意事项 #

• 该SQL需要MySQL 8.0及以上版本（支持CTE和窗口函数），如果是低版本，可以将CTE替换为嵌套子查询。
• 如果有多个患者，代码会自动按patient_id分区处理，无需额外修改。
• 可以根据需求调整GROUP_CONCAT的分隔符，比如换成逗号或其他符号。

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