使用Oracle SQL实现按日及日期范围统计新诊、复诊患者人数

前提约定 #

假设你表中NewReview字段用'新诊'（或数字1）标识新诊记录，'复诊'（或数字2）标识复诊记录，实际使用时请替换为你表中对应的字段取值。

1. 按日统计每日新诊、复诊人数 #

输出字段：日期、新诊人数、复诊人数

SELECT 
    TRUNC(VisitDate) AS 日期,
    COUNT(CASE WHEN day_type = '新诊' THEN 1 END) AS 新诊人数,
    COUNT(CASE WHEN day_type = '复诊' THEN 1 END) AS 复诊人数
FROM (
    -- 内层先按患者+日期分组，确定每个患者当日的归属类型
    SELECT 
        RegNo,
        TRUNC(VisitDate) AS visit_day,
        CASE 
            WHEN MAX(CASE WHEN NewReview = '新诊' THEN 1 ELSE 0 END) = 1 THEN '新诊'
            ELSE '复诊'
        END AS day_type
    FROM 你的就诊表名
    GROUP BY RegNo, TRUNC(VisitDate)
) t
GROUP BY visit_day
ORDER BY visit_day;

2. 指定日期范围统计新诊、复诊总人数 #

你之前统计错误是因为直接对全范围去重RegNo，没有遵循「新诊优先级高于复诊」的规则，可根据业务需求选择对应版本的SQL：

版本1：总人数为范围内去重统计（同一患者跨天就诊仅计1次，只要有新诊记录就归为新诊）

SELECT 
    COUNT(CASE WHEN range_type = '新诊' THEN 1 END) AS 新诊总人数,
    COUNT(CASE WHEN range_type = '复诊' THEN 1 END) AS 复诊总人数
FROM (
    -- 内层按患者分组，确定每个患者在查询范围内的归属类型
    SELECT 
        RegNo,
        CASE 
            WHEN MAX(CASE WHEN NewReview = '新诊' THEN 1 ELSE 0 END) = 1 THEN '新诊'
            ELSE '复诊'
        END AS range_type
    FROM 你的就诊表名
    WHERE TRUNC(VisitDate) BETWEEN DATE '2021-07-10' AND DATE '2021-07-12'
    GROUP BY RegNo
) t;

版本2：总人数为每日人数累加值（同一患者跨天就诊可被多次计数，遵循单日归属规则）

SELECT 
    SUM(new_cnt) AS 新诊总人数,
    SUM(review_cnt) AS 复诊总人数
FROM (
    SELECT 
        TRUNC(VisitDate) AS visit_day,
        COUNT(CASE WHEN day_type = '新诊' THEN 1 END) AS new_cnt,
        COUNT(CASE WHEN day_type = '复诊' THEN 1 END) AS review_cnt
    FROM (
        SELECT 
            RegNo,
            TRUNC(VisitDate) AS visit_day,
            CASE 
                WHEN MAX(CASE WHEN NewReview = '新诊' THEN 1 ELSE 0 END) = 1 THEN '新诊'
                ELSE '复诊'
            END AS day_type
        FROM 你的就诊表名
        WHERE TRUNC(VisitDate) BETWEEN DATE '2021-07-10' AND DATE '2021-07-12'
        GROUP BY RegNo, TRUNC(VisitDate)
    ) t
    GROUP BY visit_day
);

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