基于SSMS按月份捕获患者用药路径并按Tag分组统计

需求说明 #

• 针对每个患者ID，按月份先后顺序捕获用药路径（连续使用相同药物时仅保留一次，直到药物变更）
• 患者ID与标签（Tag）为一对一关系
• 按年份分组，再按Tag汇总各用药路径对应的患者数量
• 用药路径可包含1种或多种药物

输入数据 #

预期输出 #

T-SQL实现代码 #

WITH RankedDrugs AS (
    -- 为每个患者、年份的用药记录按月份排序，标记前一次使用的药物
    SELECT 
        Year,
        ID,
        Tag,
        Drug,
        Month,
        LAG(Drug) OVER (PARTITION BY Year, ID ORDER BY Month) AS PrevDrug
    FROM YourTableName -- 替换为实际表名
),
DistinctDrugPaths AS (
    -- 过滤连续相同药物，仅保留药物变更节点
    SELECT 
        Year,
        ID,
        Tag,
        Drug
    FROM RankedDrugs
    WHERE PrevDrug IS NULL OR PrevDrug != Drug
),
PatientDrugPaths AS (
    -- 拼接每个患者的用药路径字符串
    SELECT 
        Year,
        ID,
        Tag,
        STRING_AGG(Drug, '-->') WITHIN GROUP (ORDER BY Month) AS DrugOrder
    FROM DistinctDrugPaths
    GROUP BY Year, ID, Tag
)
-- 按年份、Tag、用药路径汇总患者数量
SELECT 
    Year,
    DrugOrder AS [用药顺序（Drug Order）],
    COUNT(ID) AS [数量（Count）],
    Tag AS [标签（Tag）]
FROM PatientDrugPaths
GROUP BY Year, Tag, DrugOrder
ORDER BY Year, Tag, DrugOrder;

代码说明 #

1. RankedDrugs：使用LAG()函数获取同一患者同一年份中前一次使用的药物，用于识别连续用药情况。
2. DistinctDrugPaths：过滤掉连续使用相同药物的重复记录，只保留药物切换的节点，确保用药路径仅展示药物变更顺序。
3. PatientDrugPaths：通过STRING_AGG()函数按月份顺序拼接每个患者的药物，生成完整用药路径。
4. 最终按年份、Tag和用药路径分组统计，得到各分组对应的患者数量。

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