SQL实现患者连续住院记录合并分组与费用求和查询

需求说明 #

在该SQL查询场景中，需基于患者维度，按住院时间连续性合并记录：同一患者相邻两条记录中，若前一条的出院日期+1天等于后一条的入院日期，即判定为同一次连续住院，需将符合规则的多行记录合并为单行，取该次连续住院的最早入院日期、最晚出院日期，累加对应记录的费用（Cost）总和。

样例参考 #

Sample Input
PatientID   AdmissionDate   DischargeDate   Cost
1009        27-07-2014      31-07-2014      1050
1009        01-08-2014      23-08-2014      1070
1009        31-08-2014      31-08-2014      1900
1009        01-09-2014      14-09-2014      1260
1009        01-12-2014      31-12-2014      2090
1024        07-06-2014      28-06-2014      1900
1024        29-06-2014      31-07-2014      2900
1024        01-08-2014      02-08-2014      1800

Expected Output
PatientId   AdmissionDate   DischargeDate   Cost
1009        27-07-2014      23-08-2014      2120
1009        31-08-2014      14-09-2014      3160
1009        01-12-2014      31-12-2014      2090
1024        07-06-2014      02-08-2014      6600

测试数据生成脚本 #

可直接执行以下语句构建测试环境用于调试：

CREATE TABLE PatientProblem
(
PatientID integer,
AdmissionDate date,
DischargeDate date,
Cost numeric(20,2)
);
 
-- 插入测试数据
INSERT INTO PatientProblem(PatientID,AdmissionDate,DischargeDate,Cost)
VALUES
(1009,'2014-07-27','2014-07-31',1050.00),
(1009,'2014-08-01','2014-08-23',1070.00),
(1009,'2014-08-31','2014-08-31',1900.00),
(1009,'2014-09-01','2014-09-14',1260.00),
(1009,'2014-12-01','2014-12-31',2090.00),
(1024,'2014-06-07','2014-06-28',1900.00),
(1024,'2014-06-29','2014-07-31',2900.00),
(1024,'2014-08-01','2014-08-02',1800.00);

实现方案 #

该问题属于典型的*间隙与岛屿（Gaps and Islands）*问题，核心思路是通过窗口函数给同一段连续住院的记录打上相同的分组标记，再按标记分组聚合即可，执行逻辑如下：

• 先按患者分区、入院日期升序排序，拿到每条记录上一条的出院日期
• 判断当前记录入院日期是否等于上一条出院日期+1，不等则标记为新住院段的起点
• 累加起点标记，生成每个连续住院段的唯一分组ID
• 按患者ID+分组ID聚合，取最小入院日期、最大出院日期、费用总和

可直接运行的SQL代码（基于PostgreSQL语法）：

WITH patient_prev AS (
    SELECT
        PatientID,
        AdmissionDate,
        DischargeDate,
        Cost,
        -- 取同患者上一条记录的出院日期
        LAG(DischargeDate, 1) OVER (PARTITION BY PatientID ORDER BY AdmissionDate) AS prev_discharge
    FROM PatientProblem
),
patient_group AS (
    SELECT
        PatientID,
        AdmissionDate,
        DischargeDate,
        Cost,
        -- 不连续则生成新分组，累加得到分组ID
        SUM(CASE WHEN prev_discharge IS NULL OR AdmissionDate <> prev_discharge + INTERVAL '1 day' THEN 1 ELSE 0 END) 
            OVER (PARTITION BY PatientID ORDER BY AdmissionDate) AS stay_group_id
    FROM patient_prev
)
SELECT
    PatientID,
    MIN(AdmissionDate) AS AdmissionDate,
    MAX(DischargeDate) AS DischargeDate,
    SUM(Cost) AS Cost
FROM patient_group
GROUP BY PatientID, stay_group_id
ORDER BY PatientID, AdmissionDate;

语法适配说明：若使用MySQL，将日期加1天的逻辑从prev_discharge + INTERVAL '1 day'改为DATE_ADD(prev_discharge, INTERVAL 1 DAY)即可；若使用SQL Server则改为DATEADD(day, 1, prev_discharge)。

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