Oracle SQL分组内日期重叠排查及有效记录筛选求助

针对Oracle SQL Developer 21.4.3中的需求，我们可以通过识别重叠区间组 + 组内优先级筛选的方式实现目标，以下是具体SQL实现及步骤说明：

假设表结构 #

假设你的表名为PRODUCT_DATA，字段包括：KEY, TYPE, DATE_FROM, DATE_TO, VALID_FROM, VALID_TO

核心SQL代码 #

WITH overlapping_groups AS (
    -- 第一步：将同KEY下重叠/连续的有效期记录归为同一组
    SELECT 
        key,
        type,
        valid_from,
        valid_to,
        date_from,
        date_to,
        SUM(
            CASE 
                WHEN valid_from > LAG(valid_to) OVER (PARTITION BY key ORDER BY valid_from) THEN 1 
                ELSE 0 
            END
        ) OVER (PARTITION BY key ORDER BY valid_from) AS group_id
    FROM product_data
),
ranked_groups AS (
    -- 第二步：在每个重叠组内按TYPE优先级排序（A > B > C，可按需调整）
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY key, group_id 
            ORDER BY CASE type WHEN 'A' THEN 1 WHEN 'B' THEN 2 WHEN 'C' THEN 3 END
        ) AS rn
    FROM overlapping_groups
)
-- 第三步：筛选每个重叠组内优先级最高的记录，得到无重叠的连续区间
SELECT key, type, valid_from, valid_to, date_from, date_to
FROM ranked_groups
WHERE rn = 1
ORDER BY key, valid_from;

代码说明 #

1. 重叠区间分组（overlapping_groups）

   • 用LAG(valid_to)窗口函数获取同KEY组内上一条记录的有效期结束时间
   • 通过SUM(CASE...)标记新的区间：当前记录的VALID_FROM大于上一条的VALID_TO时，视为新区间，group_id递增；否则归为同一组
   • 此步骤将所有重叠或连续的记录划分为同一个group_id

2. 组内优先级排序（ranked_groups）

   • 在KEY + group_id的分组内，按TYPE优先级（示例中A优先级最高，B次之，C最低）排序，给每条记录分配行号rn
   • 优先级最高的记录rn=1，会被最终保留

3. 筛选结果

   • 只保留rn=1的记录，即每个重叠区间内优先级最高的记录，最终得到的记录有效期无重叠且形成连续区间

自定义调整 #

• 如果TYPE优先级规则不同，直接修改ORDER BY CASE type...中的顺序即可
• 若需按其他规则选择保留记录（比如DATE_FROM更早的），可将ORDER BY后的条件替换为date_from等字段

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