如何计算存在重复预约与空值的门诊时段容量？

需求回顾 #

现有数据集包含CLINIC、APPTDATETIME、PATIENT_ID、NEW_FOLLOWUP_FLAG字段，存在以下场景/异常：

• 同一时段存在多条患者预约记录
• 无患者预约的空时段
• 同一时段同时存在患者记录与空值记录（数据质量错误）

需按诊所+日期统计三个核心指标：

• N Capacity：新患者可用容量（对应标记为新患者的有效时段数）
• F Capacity：随访患者可用容量（对应标记为随访的有效时段数）
• Unfilled Capacity：未填充容量（仅存在空值的时段数）

核心规则：同一时段同时有患者和空值时，仅保留患者记录；仅空值的时段计入未填充容量。

优雅实现思路（SQL） #

通过分层CTE结合窗口函数，无需额外关联操作即可完成数据清洗与统计，逻辑清晰且性能更优：

1. 标记时段有效性：用窗口函数判断每个时段是否存在有效患者记录，一次性处理数据质量错误
2. 过滤有效行：保留有患者的记录，或无患者的空时段记录
3. 时段归类：对每个唯一时段标记类型（新患者/随访/未填充）
4. 聚合统计：按诊所和日期汇总三个指标

代码示例 #

WITH processed_slots AS (
    -- 标记每个时段是否存在有效患者，同时提取日期和时段维度
    SELECT 
        CLINIC,
        DATE(APPTDATETIME) AS APPTDATE,
        DATE_TRUNC('hour', APPTDATETIME) AS APPTSLOT, -- 可根据实际时段粒度调整（如半小时）
        NEW_FOLLOWUP_FLAG,
        PATIENT_ID,
        -- 窗口函数：判断当前时段是否有非空患者ID的记录
        MAX(CASE WHEN PATIENT_ID IS NOT NULL THEN 1 ELSE 0 END) 
            OVER (PARTITION BY CLINIC, DATE_TRUNC('hour', APPTDATETIME)) AS HAS_VALID_PATIENT
    FROM your_dataset
),
valid_slots AS (
    -- 过滤无效行：仅保留有患者的记录，或无患者的空时段记录
    SELECT 
        CLINIC,
        APPTDATE,
        APPTSLOT,
        NEW_FOLLOWUP_FLAG
    FROM processed_slots
    WHERE 
        (HAS_VALID_PATIENT = 1 AND PATIENT_ID IS NOT NULL)
        OR HAS_VALID_PATIENT = 0
),
slot_category AS (
    -- 对每个唯一时段归类，避免同一时段多患者重复统计
    SELECT 
        CLINIC,
        APPTDATE,
        APPTSLOT,
        CASE 
            WHEN MAX(NEW_FOLLOWUP_FLAG) = 'NEW' THEN 'NEW' -- 假设NEW_FOLLOWUP_FLAG取值为'NEW'/'FOLLOWUP'
            WHEN MAX(NEW_FOLLOWUP_FLAG) = 'FOLLOWUP' THEN 'FOLLOWUP'
            ELSE 'UNFILLED'
        END AS SLOT_TYPE
    FROM valid_slots
    GROUP BY CLINIC, APPTDATE, APPTSLOT
)
-- 最终按诊所+日期聚合统计
SELECT 
    CLINIC,
    APPTDATE,
    COUNT(CASE WHEN SLOT_TYPE = 'NEW' THEN 1 END) AS "N Capacity",
    COUNT(CASE WHEN SLOT_TYPE = 'FOLLOWUP' THEN 1 END) AS "F Capacity",
    COUNT(CASE WHEN SLOT_TYPE = 'UNFILLED' THEN 1 END) AS "Unfilled Capacity"
FROM slot_category
GROUP BY CLINIC, APPTDATE
ORDER BY CLINIC, APPTDATE;

方案优势 #

• 无需额外关联操作，通过窗口函数一次性完成时段有效性判断，性能更高效
• 分层CTE结构清晰，每一步逻辑独立，便于后续调整时段粒度或规则
• 自动处理同一时段多患者、数据质量错误的情况，无需额外分支判断
• 统计逻辑直接通过CASE语句聚合，简洁易懂

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