诊所预约槽重订率计算及取消与重订数据统计需求

我来帮你一步步解决这个问题哈！你需要计算预约槽的重订率，还要统计被取消的预约槽数量、取消间隔天数，以及这些槽是否后续被重订和对应的重订日期。下面是具体的实现思路和SQL示例：

前提假设 #

首先默认你的activity表是日志式状态记录表：同一个scheduleID（预约槽唯一标识）会有多条记录，对应不同的状态变更（比如从初始预约到取消，再到重订），并且表中包含状态变更时间字段（这里命名为change_time，如果你的字段名不同，替换成实际字段即可）。如果是单条记录存储当前状态的结构，那需要历史数据支撑才能统计重订情况，这种情况可以补充表结构细节再调整方案。

第一步：提取取消记录并关联后续重订信息 #

我们用窗口函数LEAD()来高效获取每个取消记录之后的第一条状态记录，快速判断是否存在重订：

WITH cancelled_slots AS (
    SELECT
        scheduleID,
        clinic_slot,
        appointment_start,
        appointment_end,
        change_time AS cancel_datetime,
        -- 计算取消日期与原预约开始时间的天数差，若要和诊所时段对比，替换appointment_start为clinic_slot即可
        DATEDIFF(DAY, appointment_start, change_time) AS days_until_cancel,
        -- 获取当前预约槽取消后的下一个状态及变更时间
        LEAD(status) OVER (PARTITION BY scheduleID ORDER BY change_time) AS next_status,
        LEAD(change_time) OVER (PARTITION BY scheduleID ORDER BY change_time) AS next_status_time
    FROM activity
    WHERE status = 'cancelled' -- 先筛选所有取消状态的记录
),
rebooking_details AS (
    SELECT
        scheduleID,
        clinic_slot,
        cancel_datetime,
        days_until_cancel,
        -- 判断是否被重订：后续状态为有效预约（attend/DNA，可根据业务调整）
        CASE WHEN next_status IN ('attend', 'DNA') THEN '是' ELSE '否' END AS is_rebooked,
        -- 仅当重订时显示重订日期，否则为NULL
        CASE WHEN next_status IN ('attend', 'DNA') THEN next_status_time ELSE NULL END AS rebook_datetime
    FROM cancelled_slots
)

第二步：计算核心统计指标 #

基于上面的临时表，我们可以得到整体统计数据，也可以查看每个取消预约槽的详细信息：

1. 整体重订率及取消数量统计 #

SELECT
    COUNT(*) AS total_cancelled_slots, -- 被取消的预约槽总数
    SUM(CASE WHEN is_rebooked = '是' THEN 1 ELSE 0 END) AS rebooked_slot_count, -- 被重订的数量
    -- 计算重订率，保留两位小数
    ROUND(
        SUM(CASE WHEN is_rebooked = '是' THEN 1 ELSE 0 END) * 100.0 / COUNT(*),
        2
    ) AS rebook_rate_percent
FROM rebooking_details;

2. 单个取消预约槽的详细信息 #

如果需要查看每一条取消记录的具体情况，直接查询临时表即可：

SELECT * FROM rebooking_details;

关键逻辑说明 #

• 取消间隔天数：这里用DATEDIFF(DAY, appointment_start, change_time)计算从原预约开始到取消的天数差，如果你需要的是和诊所时段的间隔，把appointment_start替换成clinic_slot字段就行（前提是clinic_slot是日期类型）。
• 重订判断规则：默认取消后出现attend或DNA状态就算重订，如果你业务上认为只有attend才算有效重订，直接修改CASE语句里的状态条件即可。
• 窗口函数作用：LEAD()函数会按change_time排序，为每个scheduleID的取消记录匹配后续第一条状态变更记录，避免了低效的自连接操作。

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