SQL实现按月统计患者ID风险等级升降变更的解决方案

首先我们需要先给风险等级映射数值优先级，再通过窗口函数获取同个患者上次就诊的风险等级，最后按ID和月份聚合统计即可，修改后的SQL如下：

WITH RiskMapping AS (
    -- 先给风险等级映射可比较的数值，数值越高风险越高
    SELECT 
        *,
        CASE [Risk level]
            WHEN 'Low' THEN 1
            WHEN 'Medium' THEN 2
            WHEN 'High' THEN 3
        END AS RiskValue,
        DATEADD(month, DATEDIFF(month, 0, [Discharge Date]), 0) as StartOfMonth
    FROM Member_Risk
),
RiskCompare AS (
    -- 用LAG窗口函数取同个患者上一次就诊的风险等级
    SELECT 
        *,
        LAG(RiskValue) OVER (PARTITION BY ID ORDER BY [Discharge Date]) AS PrevRiskValue
    FROM RiskMapping
)
-- 按ID、会员名、月份聚合，统计当月是否存在升降
SELECT 
    ID,
    MemberName,
    StartOfMonth,
    CASE WHEN MAX(CASE WHEN RiskValue > PrevRiskValue THEN 1 ELSE 0 END) = 1 THEN 'Yes' ELSE 'No' END AS Increase_In_Risk_Level,
    CASE WHEN MAX(CASE WHEN RiskValue < PrevRiskValue THEN 1 ELSE 0 END) = 1 THEN 'Yes' ELSE 'No' END AS Decrease_In_Risk_Level
FROM RiskCompare
GROUP BY ID, MemberName, StartOfMonth
ORDER BY ID, StartOfMonth

逻辑说明 #

• 第一层CTE RiskMapping 先对文本类型的风险等级做数值映射，方便后续大小比较，同时计算每条记录对应的月份起始值
• 第二层CTE RiskCompare 用LAG窗口函数获取同个患者按出院时间排序的上一次就诊的风险等级数值
• 最终聚合时，判断当月同个患者的所有记录里，是否存在至少一次风险数值高于上一次、或者低于上一次的情况，对应给两个标识字段赋值Yes/No，完全匹配你提供的样例输出要求

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