Oracle SQL优化：迭代式计算患者连续入住的original_entrydate

问题背景 #

给定存储患者各机构entry_date（入住日期）和exit_date（出院日期）的表，需为每条记录计算original_entrydate——即患者连续无服务间隙（前一机构exit_date等于后一机构entry_date）的最早entry_date。现有基于LEAD和嵌套CASE WHEN的方案仅支持最多4次入住，扩展性极差，无法适配任意次数的入住场景。

核心思路 #

将患者的入住记录按entry_date升序排列，判断当前记录的entry_date是否与前一条记录的exit_date匹配：

• 若不匹配，视为新的连续入住组起点
• 若匹配，归为当前连续组
  通过生成组标识把同一连续组的记录归类，最终取每组内最小的entry_date作为该组所有记录的original_entrydate。

Oracle SQL通用实现 #

WITH ranked_entries AS (
    -- 按患者分组、入住日期升序排列，获取前一条记录的出院日期并生成组标识
    SELECT 
        resident_key,
        lidda,
        facility_key,
        admit_dt_key AS entry_date,
        separation_dt_key AS exit_date,
        -- 生成组标识：有间隙则新建组
        SUM(
            CASE 
                WHEN LAG(separation_dt_key) OVER (
                    PARTITION BY resident_key 
                    ORDER BY admit_dt_key ASC
                ) = admit_dt_key 
                THEN 0 
                ELSE 1 
            END
        ) OVER (
            PARTITION BY resident_key 
            ORDER BY admit_dt_key ASC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS group_id
    FROM my_table
),
group_original_dates AS (
    -- 计算每个连续组的最早入院日期
    SELECT 
        resident_key,
        group_id,
        MIN(entry_date) AS original_entrydate
    FROM ranked_entries
    GROUP BY resident_key, group_id
)
-- 关联数据输出最终结果
SELECT 
    re.resident_key,
    re.lidda,
    re.facility_key,
    re.entry_date AS admit_dt_key,
    re.exit_date AS separation_dt_key,
    god.original_entrydate
FROM ranked_entries re
JOIN group_original_dates god 
    ON re.resident_key = god.resident_key 
    AND re.group_id = god.group_id
ORDER BY re.resident_key, re.entry_date DESC;

代码说明 #

1. ranked_entries CTE：
   • 用LAG函数获取当前记录的前序入住记录的exit_date
   • 通过SUM...OVER窗口函数生成group_id：每遇到一次服务间隙，就累加1，确保同一连续组的记录共享相同的group_id
2. group_original_dates CTE：按患者和group_id分组，提取组内最早的entry_date，即该连续组的原始入院日期
3. 最后关联两个CTE，将原始入院日期匹配到每条入住记录上

适配示例场景 #

对于示例中的患者003246：

• 记录D（2001-02-02）：无前序记录，group_id=1，original_entrydate=2001-02-02
• 记录A（2012-10-01）：与前序记录D存在间隙，group_id=2，original_entrydate=2012-10-01
• 记录B（2015-07-24）：与前序记录A无间隙，group_id=2，继承组内最小日期
• 记录C（2022-03-22）：与前序记录B无间隙，group_id=2，继承组内最小日期

完全符合示例预期结果，且支持任意次数的连续入住记录。

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