Oracle SQL合并连续分配行获取分配起止日期方案

可以通过分析函数+分组聚合实现，核心是先识别连续的分配时段，再合并每组的起止日期，纯查询语句即可完成，无需表操作：

步骤说明 #

1. 用LAG()函数获取同一ACCOUNT_ID、STAFF_ID下上一条记录的END_DT
2. 判断当前记录的STRT_DT是否与上一条的END_DT连续（这里假设连续定义为：当前STRT_DT = 上一条END_DT + 1天，可根据实际业务调整判断逻辑）
3. 基于连续标记生成分组ID，将连续的记录归为同一组
4. 按分组ID、ACCOUNT_ID、STAFF_ID分组，取每组的最早STRT_DT、最晚END_DT，以及对应RANK（若同一组内RANK相同可直接保留，不同则按需取最值）

完整查询语句 #

WITH consecutive_groups AS (
    SELECT 
        ACCOUNT_ID,
        STAFF_ID,
        STRT_DT,
        END_DT,
        RANK,
        -- 生成分组标识：如果当前记录与上一条不连续，则分组ID+1
        SUM(CASE 
            WHEN STRT_DT = LAG(END_DT) OVER (PARTITION BY ACCOUNT_ID, STAFF_ID ORDER BY STRT_DT) + INTERVAL '1' DAY 
            THEN 0 
            ELSE 1 
        END) OVER (PARTITION BY ACCOUNT_ID, STAFF_ID ORDER BY STRT_DT) AS group_id
    FROM 
        -- 替换成你当前的查询语句，比如原始查询是SELECT ... FROM your_table
        your_current_query
)
SELECT 
    ACCOUNT_ID,
    STAFF_ID,
    MIN(STRT_DT) AS merged_strt_dt,
    MAX(END_DT) AS merged_end_dt,
    -- 若同一分组内RANK值相同，取MIN/MAX均可；若不同，需根据业务确定保留逻辑
    MIN(RANK) AS merged_rank
FROM 
    consecutive_groups
GROUP BY 
    ACCOUNT_ID, STAFF_ID, group_id
ORDER BY 
    ACCOUNT_ID, STAFF_ID, merged_strt_dt;

关键逻辑解释 #

• PARTITION BY ACCOUNT_ID, STAFF_ID：确保只在同一账户同一员工的范围内比较记录
• LAG(END_DT) OVER (...)：获取上一条记录的结束日期，用于判断连续性
• SUM(...) OVER (...)：通过累加非连续标记生成分组ID，每遇到一次不连续的情况，分组ID就递增，连续的记录会被分到同一个组
• 最后分组聚合时，取每组的最小开始日期和最大结束日期，实现合并

调整说明 #

如果你的“连续”定义不是“次日衔接”，可以修改CASE里的判断条件：

• 若END_DT为NULL表示当前分配有效：WHEN STRT_DT = LAG(END_DT) OVER (...) + INTERVAL '1' DAY OR LAG(END_DT) OVER (...) IS NULL THEN 0 ELSE 1
• 若允许特定间隔：比如间隔不超过3天，则WHEN STRT_DT - LAG(END_DT) OVER (...) <= 3 THEN 0 ELSE 1

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