基于SQL的医疗预约场景FirstOfferDateTime计算方案问询

问题概述 #

需要基于以下规则计算患者的FirstOfferDateTime：

• 核心过滤条件：仅处理EventDateTime和AppointmentDateTime均晚于ScreenDate的预约事件
• 预约变更规则：
  • 医院主动取消原预约并改至更早日期：更新FirstOfferDateTime为该更早的预约日期
  • 患者取消后改期（无论新预约日期早晚）、预约未到场（DNA）或已完成就诊：保留最初的有效预约日期

示例场景：

ScreenDate = 15/03/2023

1. 医院首次提供预约：19/06/2023 12:00
2. 医院取消该预约，改至27/05/2023 09:00
3. 医院再次取消，改至04/05/2023 09:00
   最终FirstOfferDateTime应为04/05/2023 09:00

现有递归CTE方案存在效率低下、无法支持大量连续事件（20次以上）的问题，需要替换为更高效的非递归实现。

非递归实现方案 #

以下方案通过分组筛选+窗口函数替代递归，避免深度限制且性能更优，适用于主流SQL方言（如SQL Server、PostgreSQL、MySQL 8.0+）：

步骤1：筛选有效事件 #

先过滤掉不符合核心条件的记录，并按患者分组排序事件：

WITH ValidEvents AS (
    SELECT
        PatientID,
        AppointmentDateTime,
        EventType,
        EventDateTime,
        -- 按事件发生顺序生成序号
        ROW_NUMBER() OVER (PARTITION BY PatientID ORDER BY EventDateTime ASC) AS EventSeq
    FROM
        AppointmentEvents
    WHERE
        EventDateTime > @ScreenDate -- 替换为实际ScreenDate变量或字段
        AND AppointmentDateTime > @ScreenDate
),
-- 识别医院取消且改至更早日期的事件
HospitalDowngradeEvents AS (
    SELECT
        curr.PatientID,
        curr.AppointmentDateTime
    FROM
        ValidEvents curr
    INNER JOIN ValidEvents prev
        ON curr.PatientID = prev.PatientID
        AND curr.EventSeq = prev.EventSeq + 1
    WHERE
        curr.EventType = 'Hospital_Cancel' -- 确保EventType值与业务定义一致
        AND curr.AppointmentDateTime < prev.AppointmentDateTime
)
-- 计算最终FirstOfferDateTime
SELECT
    ve.PatientID,
    -- 优先取医院改早事件中的最早日期，无则取首次有效预约
    COALESCE(hd.MinDowngradeDate, ve.InitialOffer) AS FirstOfferDateTime
FROM (
    -- 获取每个患者的首次有效预约日期
    SELECT
        PatientID,
        FIRST_VALUE(AppointmentDateTime) OVER (PARTITION BY PatientID ORDER BY EventSeq ASC) AS InitialOffer
    FROM
        ValidEvents
    GROUP BY
        PatientID, AppointmentDateTime, EventSeq
) ve
LEFT JOIN (
    -- 获取每个患者最早的医院改早日期
    SELECT
        PatientID,
        MIN(AppointmentDateTime) AS MinDowngradeDate
    FROM
        HospitalDowngradeEvents
    GROUP BY
        PatientID
) hd ON ve.PatientID = hd.PatientID;

代码说明 #

1. ValidEvents：过滤并排序所有符合核心条件的预约事件，生成事件序列编号
2. HospitalDowngradeEvents：通过自连接匹配前后事件，筛选出医院取消且新日期更早的有效改期记录
3. 最终查询：结合首次预约日期和最早的医院改早日期，得到最终的FirstOfferDateTime

优化方向 #

1. 索引优化：创建复合索引CREATE INDEX IX_AppointmentEvents_Patient_Event ON AppointmentEvents(PatientID, EventDateTime, EventType, AppointmentDateTime)，加速筛选、排序和自连接操作
2. 数据分区：若数据量极大，可按PatientID或EventDateTime进行分区，减少查询时的扫描范围
3. EventType约束：将EventType设为枚举类型或添加检查约束，避免因类型值不统一导致的逻辑错误
4. 增量计算：如果是实时场景，仅处理新增的事件记录，无需全量扫描历史数据

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