Oracle中如何从连续但间断的小序列号范围生成合并范围

原始数据 #

假设表名为product_series，数据如下：

目标结果 #

按子产品合并连续的序列号范围，间断的保留独立范围：

实现SQL语句 #

WITH series_with_group AS (
    SELECT 
        sub_product,
        start_serial,
        end_serial,
        -- 生成分组标识：当前行起始序列号不等于上一行结束+1时，分组加1
        SUM(CASE WHEN start_serial = LAG(end_serial) OVER (PARTITION BY sub_product ORDER BY start_serial) + 1 THEN 0 ELSE 1 END) 
            OVER (PARTITION BY sub_product ORDER BY start_serial) AS group_id
    FROM product_series
)
SELECT 
    sub_product,
    MIN(start_serial) AS 起始序列号,
    MAX(end_serial) AS 结束序列号
FROM series_with_group
GROUP BY sub_product, group_id
ORDER BY sub_product, 起始序列号;

逻辑说明 #

1. CTE部分（series_with_group）：

   • 按sub_product分组，按start_serial排序，用LAG(end_serial)获取当前行上一行的结束序列号。
   • 判断当前行的起始序列号是否等于上一行结束序列号+1，若是则属于同一连续组（加0），否则开启新组（加1）。
   • 通过SUM() OVER()累积计算分组标识group_id，同一连续区间的行拥有相同的group_id。

2. 最终聚合：

   • 按sub_product和group_id分组，取每组最小的起始序列号和最大的结束序列号，得到合并后的连续范围。

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