SQL实现累计求和与累计计数 统计患者治疗疗程

背景 #

我正在搭建患者治疗疗程划分规则，目前已经通过Python完成计算输出，需要将同等逻辑迁移到SQL环境实现。

样例数据 #

*说明：diff列中的1.1是NULL值的占位符，无实际业务含义。

现有Python实现逻辑 #

实现代码如下：

sessions['Coursenum']=(sessions.courses.eq('start')).cumsum()
sessions['course_count']=sessions.groupby(['Coursenum']).cumcount()+1
sessions['TotalSessions']=sessions.groupby('Coursenum')['course_count'].transform('max')

核心计算逻辑：

• 识别courses列值为start的疗程起始记录，通过累计求和生成疗程编号Coursenum
• 按Coursenum分组，对组内记录按顺序累计计数，生成单疗程内的会话序号course_Count
• 按Coursenum分组取组内会话序号最大值，得到单疗程总会话数Total Session

SQL实现方案 #

基于标准ANSI窗口函数编写，兼容MySQL 8.0+、PostgreSQL、SQL Server、BigQuery等支持窗口函数的数据库引擎：

WITH step1 AS (
    SELECT
        *,
        -- 对齐Python cumsum逻辑，累计start标记生成疗程编号
        SUM(CASE WHEN courses = 'start' THEN 1 ELSE 0 END) OVER (
            ORDER BY PatientID, SessionDate
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS Coursenum
    FROM sessions
),
step2 AS (
    SELECT
        *,
        -- 对齐分组cumcount+1逻辑，生成疗程内会话序号
        ROW_NUMBER() OVER (
            PARTITION BY PatientID, Coursenum
            ORDER BY SessionDate
        ) AS course_Count
    FROM step1
)
SELECT
    *,
    -- 对齐分组取max逻辑，计算单疗程总会话数
    MAX(course_Count) OVER (PARTITION BY PatientID, Coursenum) AS `Total Session`
FROM step2;

注意事项 #

• 涉及多患者数据时，所有窗口计算必须携带PatientID作为分区字段，避免跨患者数据串算
• 若同一患者同一天存在多条就诊记录，建议在窗口的ORDER BY子句中补充唯一排序字段（如就诊记录自增ID、精确到时分秒的就诊时间），保证记录排序和Python处理时的顺序完全一致，避免序号错位

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