如何基于住院入出院日期计算再入院率？含Tableau及SQL实现需求

需在Tableau仪表盘中展示月度整体再入院率及按出院科室划分的再入院率。

• 现有数据表字段：MRN（患者唯一ID）、FIN（每次住院唯一标识，部分患者有多次住院记录）、入院日期、入院科室（NICU、CCU等）、出院日期、出院科室（入院与出院科室通常不同）
• 再入院定义：出院后30天内再次入院
• 再入院率计算公式：再入院次数/出院次数

数据样例 #

报错信息 #

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Warning: Null value is eliminated by an aggregate or other SET operation.

尝试的SQL代码 #

WITH mnths AS (
  SELECT cast(1 AS int) m 
  UNION ALL SELECT m+1 FROM mnths WHERE m<48 
), admdis AS (
  SELECT *, 12*(YEAR(reg_dt_tm)-2018)+MONTH(reg_dt_tm)a, 12*(YEAR(disch_dt_tm)-2018)+MONTH(disch_dt_tm) d,
        (SELECT 1 FROM inpatient_pop WHERE med_rec_no=t.med_rec_no AND disch_dt_tm>DATEADD(day, -30, t.reg_dt_tm) AND FIN<t.FIN) readm
  FROM inpatient_pop t
)
SELECT (m-1)/12+2018 yr, 1+(m-1)%12 mn, COUNT(med_rec_no) totl ,COUNT(readm) readm, 
       CASE WHEN COUNT(med_rec_no)>0 THEN (0.+COUNT(readm))/ COUNT(med_rec_no) ELSE 0 END rate
FROM mnths
LEFT JOIN admdis ON a=m
GROUP BY m

期望结果表 #

修正后的SQL方案 #

1. 支持整体+科室维度的月度再入院率计算

WITH discharge_summary AS (
    -- 标记每一次出院是否存在30天内的再入院
    SELECT 
        d.MRN,
        d.FIN AS discharge_FIN,
        d.Discharge_Date,
        d.Discharging_Service,
        -- 用EXISTS避免子查询返回多行的问题
        CASE 
            WHEN EXISTS (
                SELECT 1 
                FROM inpatient_pop r
                WHERE r.MRN = d.MRN
                  AND r.Admission_Date > d.Discharge_Date
                  AND r.Admission_Date <= DATEADD(day, 30, d.Discharge_Date)
                  AND r.FIN != d.FIN
            ) THEN 1 
            ELSE 0 
        END AS is_readmitted
    FROM inpatient_pop d
),
monthly_agg AS (
    -- 按年月、出院科室分组统计核心指标
    SELECT
        FORMAT(Discharge_Date, 'MMM yyyy') AS [Month/Year],
        Discharging_Service,
        COUNT(discharge_FIN) AS [Total Number of Discharges],
        SUM(is_readmitted) AS [Total Number of Readmissions]
    FROM discharge_summary
    GROUP BY FORMAT(Discharge_Date, 'MMM yyyy'), Discharging_Service
)
-- 计算最终再入院率，同时支持整体/科室维度切换
SELECT
    [Month/Year],
    -- 如果只看整体，将Discharging_Service替换为'All Departments'并去掉该字段的分组
    Discharging_Service,
    [Total Number of Readmissions],
    [Total Number of Discharges],
    CASE 
        WHEN [Total Number of Discharges] > 0 
        THEN CAST([Total Number of Readmissions] AS FLOAT) / [Total Number of Discharges] 
        ELSE 0 
    END AS [Readmission Rate]
FROM monthly_agg
ORDER BY [Month/Year], Discharging_Service;

方案说明

• 替换原SQL的子查询为EXISTS，解决了子查询返回多行的报错
• 基于出院日期月份统计，符合再入院率以出院次数为分母的逻辑
• 原生包含Discharging_Service字段，直接支持按科室划分的维度分析
• 用FORMAT生成符合期望的Month/Year格式，无需额外处理
• 处理了分母为0的边界情况，避免计算错误

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