如何编写MySQL查询统计指定时间范围内各医院每日在院患者数

实现思路 #

要实现逐天统计，首先需要生成指定时间范围内的连续日期（原表无单独日期列），再关联住院数据匹配符合条件的患者：

• 生成指定统计周期内的连续日期序列，作为统计的时间维度
• 拉取所有去重的医院名称，和连续日期做笛卡尔积，得到所有「统计日期-医院」的全量维度组合
• 关联原始住院表，筛选符合当日处于住院状态的患者：入院日期 ≤ 统计日期，且（出院日期 > 统计日期 或 出院日期为NULL）
• 按统计日期、医院分组，统计去重的PatientID数量即为当日在院人数

完整查询代码 #

-- 定义统计的时间范围，示例为2023年完整日历年
SET @start_date = '2023-01-01';
SET @end_date = '2023-12-31';

WITH RECURSIVE date_sequence AS (
    -- 递归生成连续日期序列
    SELECT @start_date AS stat_date
    UNION ALL
    SELECT DATE_ADD(stat_date, INTERVAL 1 DAY)
    FROM date_sequence
    WHERE stat_date < @end_date
),
all_hospitals AS (
    -- 拉取所有去重的医院名称
    SELECT DISTINCT HospitalName
    FROM 你的表名 -- 请替换为实际的表名
)
SELECT 
    ds.stat_date,
    ah.HospitalName,
    COUNT(DISTINCT t.PatientID) AS in_hospital_patient_count
FROM date_sequence ds
CROSS JOIN all_hospitals ah
LEFT JOIN 你的表名 t -- 请替换为实际的表名
ON ah.HospitalName = t.HospitalName
AND t.AdmissionDate <= ds.stat_date
AND IFNULL(t.DischargeDate, '9999-12-31') > ds.stat_date
GROUP BY ds.stat_date, ah.HospitalName
ORDER BY ds.stat_date, ah.HospitalName;

配置说明 #

• 替换SQL中两处你的表名为实际存储住院信息的表名即可运行
• 调整@start_date和@end_date的取值，即可自定义统计的时间范围
• 若业务规则要求出院当日仍算在院，将条件IFNULL(t.DischargeDate, '9999-12-31') > ds.stat_date修改为IFNULL(t.DischargeDate, '9999-12-31') >= ds.stat_date即可
• 该方案依赖MySQL 8.0及以上版本支持的递归CTE语法，低版本MySQL可预先维护一张日期维度表替代date_sequence部分的逻辑

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