如何在SQL中一次性计算多组患者预约缺席率？

架构老林

2026-6-13

患者就诊预约缺席率统计问题

我有一个SQL表patient_visits，存储了每位患者的多次就诊信息：

CREATE TABLE patient_visits 
(
    patient_id INT,
    hospital_visit_date DATE,
    visit_num INT,
    appointment_result VARCHAR(3),
    PRIMARY KEY (patient_id, visit_num)
);

INSERT INTO patient_visits (patient_id, hospital_visit_date, visit_num, appointment_result) 
VALUES
(1001, '2023-01-15', 1, 'yes'),
(1001, '2023-02-20', 2, 'no'),
(1001, '2023-04-10', 3, 'yes'),
(1001, '2023-07-05', 4, 'yes'),
(1001, '2023-11-22', 5, 'no');

INSERT INTO patient_visits (patient_id, hospital_visit_date, visit_num, appointment_result) 
VALUES
(1002, '2023-03-08', 1, 'yes'),
(1002, '2023-06-12', 2, 'yes'),
(1002, '2023-10-30', 3, 'no');

INSERT INTO patient_visits (patient_id, hospital_visit_date, visit_num, appointment_result) 
VALUES
(1003, '2023-02-05', 1, 'yes'),
(1003, '2023-08-18', 2, 'no');

INSERT INTO patient_visits (patient_id, hospital_visit_date, visit_num, appointment_result) 
VALUES
(1004, '2023-01-10', 1, 'no'),
(1004, '2023-03-25', 2, 'yes'),
(1004, '2023-05-14', 3, 'yes'),
(1004, '2023-09-07', 4, 'no');

INSERT INTO patient_visits (patient_id, hospital_visit_date, visit_num, appointment_result) 
VALUES
(1005, '2023-07-20', 1, 'yes');

INSERT INTO patient_visits (patient_id, hospital_visit_date, visit_num, appointment_result) 
VALUES
(1006, '2023-01-05', 1, 'yes'),
(1006, '2023-02-15', 2, 'yes'),
(1006, '2023-05-22', 3, 'no'),
(1006, '2023-08-30', 4, 'yes'),
(1006, '2023-12-15', 5, 'yes');

该表数据展示如下：

patient_id hospital_visit_date visit_num appointment_result
  -----------------------------------------------------------
       1001          2023-01-15         1                yes
       1001          2023-02-20         2                 no
       1001          2023-04-10         3                yes
       1001          2023-07-05         4                yes
       1001          2023-11-22         5                 no
       1002          2023-03-08         1                yes
       1002          2023-06-12         2                yes
       1002          2023-10-30         3                 no
       1003          2023-02-05         1                yes
       1003          2023-08-18         2                 no
       1004          2023-01-10         1                 no
       1004          2023-03-25         2                yes
       1004          2023-05-14         3                yes
       1004          2023-09-07         4                 no
       1005          2023-07-20         1                yes
       1006          2023-01-05         1                yes
       1006          2023-02-15         2                yes
       1006          2023-05-22         3                 no
       1006          2023-08-30         4                yes
       1006          2023-12-15         5                yes

基于此表，需要统计以下内容：

仅预约1次的患者：首次预约的缺席率是多少？
仅预约2次的患者：首次预约的缺席率是多少？第二次预约的缺席率是多少？
仅预约3次的患者：首次、第二次、第三次预约的缺席率分别是多少？
预约超过3次的患者：首次、第二次、第三次预约的缺席率分别是多少？其余所有预约合并后的缺席率是多少？

目前我通过手动分步实现，例如：

WITH ThreeVisitPatients AS  
(
    SELECT patient_id
    FROM myt
    GROUP BY patient_id
    HAVING COUNT(*) = 3
)
SELECT 
    visit_num AS appointment_num,
    SUM(CASE WHEN appointment_result = 'yes' THEN 1 ELSE 0 END) AS yes_count,
    COUNT(*) AS total_count,
    ROUND(SUM(CASE WHEN appointment_result = 'yes' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS percentage_yes,
    3 AS total_appointments
FROM 
    myt
WHERE 
    patient_id IN (SELECT patient_id FROM ThreeVisitPatients)
GROUP BY 
    visit_num
ORDER BY 
    visit_num;

WITH TwoVisitPatients AS 
(
    SELECT patient_id
    FROM myt
    GROUP BY patient_id
    HAVING COUNT(*) = 2
),
ThreeVisitPatients AS 
(
    SELECT patient_id
    FROM myt
    GROUP BY patient_id
    HAVING COUNT(*) = 3
)
-- 预约2次患者的统计结果
SELECT 
    visit_num AS appointment_num,
    SUM(CASE WHEN appointment_result = 'yes' THEN 1 ELSE 0 END) AS yes_count,
    COUNT(*) AS total_count,
    ROUND(SUM(CASE WHEN appointment_result = 'yes' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS percentage_yes,
    2 AS total_appointments
FROM 
    myt
WHERE 
    patient_id IN (SELECT patient_id FROM TwoVisitPatients)
GROUP BY 
    visit_num

UNION ALL

-- 预约3次患者的统计结果
SELECT 
    visit_num AS appointment_num,
    SUM(CASE WHEN appointment_result = 'yes' THEN 1 ELSE 0 END) AS yes_count,
    COUNT(*) AS total_count,
    ROUND(SUM(CASE WHEN appointment_result = 'yes' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS percentage_yes,
    3 AS total_appointments
FROM 
    myt
WHERE 
    patient_id IN (SELECT patient_id FROM ThreeVisitPatients)
GROUP BY 
    visit_num;

是否有办法一次性完成所有统计？

一种概念验证思路：

WITH patient_appointment_counts AS 
(
    -- 获取每位患者的总预约次数
    SELECT 
        patient_id, 
        COUNT(*) as total_appointments
    FROM 
        patient_visits
    GROUP BY 
        patient_id
),
patient_appointment_categories AS 
(
    -- 根据总预约次数对患者分类
    -- 1 = 仅1次预约
    -- 2 = 仅2次预约
    -- 3 = 仅3次预约
    -- 999 = 预约超过3次
    SELECT 
        patient_id,
        CASE 
            WHEN total_appointments = 1 THEN 1
            WHEN total_appointments = 2 THEN 2
            WHEN total_appointments = 3 THEN 3
            WHEN total_appointments > 3 THEN 999
        END AS appointment_category
    FROM 
        patient_appointment_counts
),
normalized_visits AS 
(
    -- 标准化预约序号，对预约超过3次的患者，将第4次及以后的预约合并为一组
    SELECT 
        pv.patient_id,
        pv.visit_num,
        pv.appointment_result,
        pac.appointment_category,
        CASE 
            WHEN pac.appointment_category = 999 AND pv.visit_num > 3 THEN 4 -- 合并第4次及以后的所有预约
            ELSE pv.visit_num 
        END AS normalized_visit_num
    FROM 
        patient_visits pv
    JOIN 
        patient_appointment_categories pac ON pv.patient_id = pac.patient_id
),
miss_counts AS 
(
    -- 计算每个分类和预约序号的缺席次数及总次数
    SELECT 
        appointment_category,
        normalized_visit_num,
        COUNT(*) AS total_count,
        SUM(CASE WHEN appointment_result = 'no' THEN 1 ELSE 0 END) AS miss_count
    FROM 
        normalized_visits
    GROUP BY 
        appointment_category, normalized_visit_num
)
SELECT 
    CASE 
        WHEN appointment_category = 1 THEN '仅预约1次的患者'
        WHEN appointment_category = 2 THEN '仅预约2次的患者'
        WHEN appointment_category = 3 THEN '仅预约3次的患者'
        WHEN appointment_category = 999 THEN '预约超过3次的患者'
    END AS patient_group,
    CASE 
        WHEN normalized_visit_num = 1 THEN '第1次预约'
        WHEN normalized_visit_num = 2 THEN '第2次预约'
        WHEN normalized_visit_num = 3 THEN '第3次预约'
        WHEN normalized_visit_num = 4 THEN '第4次及以后预约（合并）'
    END AS appointment_number,
    total_count,
    miss_count,
    ROUND((miss_count * 100.0 / total_count), 2) AS miss_rate_percentage
FROM 
    miss_counts
ORDER BY 
    CASE 
        WHEN appointment_category = 1 THEN 1
        WHEN appointment_category = 2 THEN 2
        WHEN appointment_category = 3 THEN 3
        WHEN appointment_category = 999 THEN 4
    END, 
    normalized_visit_num;

手动识别4次及以上预约的方式：

WITH PatientVisitCounts AS 
(
    -- 获取每位患者的总就诊次数
    SELECT 
        patient_id,
        MAX(visit_num) AS total_visits
    FROM myt
    GROUP BY patient_id
),
VisitCategories AS (
    -- 对每次就诊分类，将第4次及以后的就诊归为同一类别
    SELECT 
        m.patient_id,
        m.visit_num,
        m.appointment_result,
        pvc.total_visits,
        CASE 
            WHEN m.visit_num <= 3 THEN m.visit_num
            ELSE 4 -- 将第4次及以后的就诊归为类别4
        END AS visit_category
    FROM myt m
    JOIN PatientVisitCounts pvc ON m.patient_id = pvc.patient_id
    WHERE pvc.total_visits >= 4 -- 仅包含预约4次及以上的患者
)

-- 计算每个就诊类别的统计数据
SELECT 
    '4+' AS total_appointments,
    CASE 
        WHEN visit_category < 4 THEN CAST(visit_category AS VARCHAR(255))
        ELSE '4+'
    END AS appointment_num,
    SUM(CASE WHEN appointment_result = 'yes' THEN 1 ELSE 0 END) AS yes_count,
    COUNT(*) AS total_count,
    ROUND(SUM(CASE WHEN appointment_result = 'yes' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS percentage_yes
FROM VisitCategories
GROUP BY visit_category
ORDER BY visit_category

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