Oracle 11g中基于eff_date和end_date合并连续相同val记录的实现

需求：针对Oracle 11g数据库中的数据，需按id分组，将连续拥有相同val值的记录合并，保留该组中最早的eff_date（生效日期）和最晚的end_date（结束日期）。

示例输入 #

期望输出 #

解决方案 #

Oracle 11g支持窗口函数，可以通过生成分组标识来区分连续的相同val组，再对分组进行聚合操作。具体步骤如下：

1. 生成分组键：使用两个ROW_NUMBER()窗口函数的差值作为分组标识。当val发生变化时，差值会改变，从而将连续相同的val划分为同一组。
2. 聚合分组数据：按id、val和分组键分组，取每组的最小eff_date和最大end_date。

完整SQL代码 #

WITH sample_data AS (
    SELECT 10 AS id, 100 AS val, TO_DATE('01-Jan-21', 'DD-Mon-RR') AS eff_date, TO_DATE('04-Jan-21', 'DD-Mon-RR') AS end_date FROM DUAL
    UNION ALL
    SELECT 10 AS id, 105 AS val, TO_DATE('05-Jan-21', 'DD-Mon-RR') AS eff_date, TO_DATE('07-Jan-21', 'DD-Mon-RR') AS end_date FROM DUAL
    UNION ALL
    SELECT 10 AS id, 100 AS val, TO_DATE('08-Jan-21', 'DD-Mon-RR') AS eff_date, TO_DATE('10-Jan-21', 'DD-Mon-RR') AS end_date FROM DUAL
    UNION ALL
    SELECT 10 AS id, 100 AS val, TO_DATE('11-Jan-21', 'DD-Mon-RR') AS eff_date, TO_DATE('17-Jan-21', 'DD-Mon-RR') AS end_date FROM DUAL
    UNION ALL
    SELECT 10 AS id, 100 AS val, TO_DATE('18-Jan-21', 'DD-Mon-RR') AS eff_date, TO_DATE('21-Jan-21', 'DD-Mon-RR') AS end_date FROM DUAL
    UNION ALL
    SELECT 10 AS id, 110 AS val, TO_DATE('22-Jan-21', 'DD-Mon-RR') AS eff_date, NULL AS end_date FROM DUAL
),
grouped_data AS (
    SELECT 
        id,
        val,
        eff_date,
        end_date,
        -- 生成分组键：连续相同val的记录会得到相同的group_key
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY eff_date) 
        - ROW_NUMBER() OVER (PARTITION BY id, val ORDER BY eff_date) AS group_key
    FROM sample_data
)
SELECT 
    id,
    val,
    MIN(eff_date) AS eff_date,
    MAX(end_date) AS end_date
FROM grouped_data
GROUP BY id, val, group_key
ORDER BY eff_date;

代码说明 #

• sample_data：模拟输入的测试数据，实际使用时替换为你的业务表名。
• group_key：通过两个行号的差值，将同一id下连续相同的val归为一组。例如，第3-5条记录的val都是100，它们的group_key相同，会被合并。
• 聚合阶段：对每个分组取最小的生效日期和最大的结束日期，MAX(end_date)会自动保留null值（如果组内存在null）。

执行上述SQL后，输出结果将与期望输出一致。

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