SQL技术问题：如何计算上次就诊以来的取消/错过预约事件数

核心实现思路 #

要完成两个统计目标，核心是利用窗口函数追踪每条记录的关联历史数据，并计算时间区间内的事件数：

• 用LAG()窗口函数定位每条记录的上次取消就诊时间和上次任意就诊时间
• 用关联子查询或范围窗口函数统计指定时间区间内的目标事件数量

假设表结构 #

假设你的就诊表（visits）包含以下字段：

visit_id INT,          -- 就诊记录ID
patient_id INT,        -- 患者ID
visit_date DATETIME,   -- 就诊/事件发生时间
visit_status VARCHAR   -- 状态：'COMPLETED'(完成)、'CANCELED'(取消)、'MISSED'(错过)

修正后的SQL语句 #

WITH visit_history AS (
    SELECT
        *,
        -- 获取当前患者上次取消就诊的时间
        LAG(CASE WHEN visit_status = 'CANCELED' THEN visit_date END)
            OVER (PARTITION BY patient_id ORDER BY visit_date) AS prev_cancel_dt,
        -- 获取当前患者上次就诊（无论状态）的时间
        LAG(visit_date)
            OVER (PARTITION BY patient_id ORDER BY visit_date) AS prev_visit_dt
    FROM visits
)
SELECT
    visit_id,
    patient_id,
    visit_date,
    visit_status,
    -- 计算上次取消到当前就诊之间的取消数量
    (SELECT COUNT(*)
     FROM visits v2
     WHERE v2.patient_id = v1.patient_id
       AND v2.visit_status = 'CANCELED'
       AND v2.visit_date > v1.prev_cancel_dt
       AND v2.visit_date <= v1.visit_date) AS 上次取消到当前就诊间的取消数量,
    -- 计算自上次就诊以来的错过预约事件数
    (SELECT COUNT(*)
     FROM visits v2
     WHERE v2.patient_id = v1.patient_id
       AND v2.visit_status = 'MISSED'
       AND v2.visit_date > v1.prev_visit_dt
       AND v2.visit_date <= v1.visit_date) AS 自上次就诊以来错过的预约事件数
FROM visit_history v1
ORDER BY patient_id, visit_date;

关键逻辑说明 #

1. CTE visit_history：
   • 通过LAG()函数为每条记录绑定同一患者的历史关键时间点，确保统计范围仅限当前患者的就诊记录
   • 针对取消就诊的时间做了条件过滤，仅当历史记录为取消状态时才返回时间值
2. 子查询统计事件数：
   • 针对每条记录，用关联子查询精准筛选出时间在「上次目标事件时间」到「当前就诊时间」之间的目标状态记录，直接计数
   • 这种写法兼容绝大多数数据库（MySQL、PostgreSQL、SQL Server等），避免部分数据库不支持范围窗口语法的问题

适配调整建议 #

• 如果你的"错过预约"定义是指取消的预约，只需将第二个子查询中的visit_status = 'MISSED'改为visit_status = 'CANCELED'
• 若存在多条同一时间的记录，可调整时间条件为>=或<以适配具体业务规则
• 若需提升大表查询性能，可建立patient_id + visit_date + visit_status的复合索引

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