如何按业务规则批量更新SQL中住院理赔记录的入出院日期

需求背景 #

需要实现住院理赔记录的入出院日期批量更新，待处理表为MOCK_DATA_CLAIM，为高度去范式化设计无主键，表结构及示例数据如下：

业务规则 #

• 同一参保人（BEN_ID）的住院记录，若前次出院当日或次日再次入院，判定为同一直接转诊链路；若出院后间隔2天及以上再次入院，判定为两次独立住院，无需合并
• 属于同一转诊链路的所有理赔记录，统一取链路内最早的入院日期作为全链路基准入院日期，取链路内最晚的出院日期作为全链路基准出院日期，更新链路内所有记录的对应字段
• 示例数据中前3条属于同一转诊链路，需将第2、3条记录入院日期更新为2018年7月10日，第1、2条记录出院日期更新为2018年8月2日；第4、5条属于另一转诊链路，按相同逻辑更新

原有方案问题 #

之前采用游标逐行更新的方案存在本质逻辑缺陷：游标仅能匹配相邻2条记录的转诊关系，无法实现转诊关系的传递性关联，当同一转诊链路包含3条及以上记录时，无法正确识别整条链路范围，最终导致日期更新错误。

最优实现方案（窗口函数版，无游标） #

该方案基于SQL窗口函数实现间断分组（Gaps and Islands）逻辑，无需依赖主键、无需逐行遍历，可正确识别任意长度的转诊链路，一次计算生成最终结果表，准确率和执行效率远高于游标方案。

实现逻辑 #

1. 按参保人维度分组，所有记录按入院日期、出院日期升序排序
2. 逐行对比当前记录入院日期和同参保人上一条记录的出院日期间隔，间隔≥2天则标记为新的住院组，同链路的记录共享同一个组ID
3. 按参保人+住院组ID分组，计算每组的基准入院日期（组内最早入院时间）、基准出院日期（组内最晚出院时间）
4. 关联基准日期直接生成最终结果表

实现代码 #

-- 直接生成最终结果表，无需中间更新步骤
DROP TABLE IF EXISTS MOCK_DATA_CLAIM_FINAL;

WITH sorted_claims AS (
    -- 同参保人记录按入院时间排序，标记连续转诊链路的分组ID
    SELECT 
        BEN_ID,
        CLAIM_ID,
        DTE_ADMISSION,
        DTE_DISCHARGE,
        SUM(
            CASE 
                -- 和上一条记录出院间隔≥2天，或者是该参保人第一条记录，则新建分组
                WHEN DATEDIFF(DAY, prev_discharge, DTE_ADMISSION) >= 2 OR prev_discharge IS NULL 
                THEN 1 ELSE 0 
            END
        ) OVER (
            PARTITION BY BEN_ID 
            ORDER BY DTE_ADMISSION, DTE_DISCHARGE 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS hospital_group_id
    FROM (
        SELECT 
            *,
            -- 取同参保人上一条记录的出院日期
            LAG(DTE_DISCHARGE) OVER (
                PARTITION BY BEN_ID 
                ORDER BY DTE_ADMISSION, DTE_DISCHARGE
            ) AS prev_discharge
        FROM MOCK_DATA_CLAIM
    ) t
),
group_base_date AS (
    -- 计算每个转诊链路的基准入出院日期
    SELECT
        BEN_ID,
        hospital_group_id,
        MIN(DTE_ADMISSION) AS base_admission,
        MAX(DTE_DISCHARGE) AS base_discharge
    FROM sorted_claims
    GROUP BY BEN_ID, hospital_group_id
)
-- 关联基准日期生成最终结果
SELECT 
    s.BEN_ID,
    s.CLAIM_ID,
    g.base_admission AS DTE_ADMISSION,
    g.base_discharge AS DTE_DISCHARGE
INTO MOCK_DATA_CLAIM_FINAL
FROM sorted_claims s
INNER JOIN group_base_date g 
    ON s.BEN_ID = g.BEN_ID 
    AND s.hospital_group_id = g.hospital_group_id
ORDER BY s.BEN_ID, g.base_admission, s.DTE_ADMISSION;

验证说明 #

针对示例数据执行上述代码，输出结果完全符合业务预期：

• 前3条记录被分到同一住院组，统一使用基准日期：入院2018-07-10、出院2018-08-02
• 第4、5条记录被分到同一住院组，统一使用基准日期：入院2018-09-27、出院2018-10-08
• 即使单条转诊链路包含上百条连续转诊记录，该分组逻辑依然可以正确识别全链路范围，不会出现游标方案的更新错误。

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