如何在Oracle中使用LEAD函数合并产品与有效期

需求描述 #

查询device_id、valid_from、valid_to、product字段，当连续记录的产品相同时，合并其有效期（取该组最早的valid_from和最晚的valid_to），且保留原有记录的顺序。

原始数据示例 #

DEVICE_ID| VALID_FROM | VALID_TO | PRODUCT
Y0001     01.01.2012   10.01.2012    Y
Y0001     10.01.2012   20.01.2012    Y
Y0001     10.01.2013   20.01.2013    Z
Y0001     21.01.2013   30.01.2013    Y
Y0001     12.02.2013   21.02.2013    X

预期结果 #

DEVICE_ID| VALID_FROM | VALID_TO | PRODUCT
Y0001      01.01.2012   20.01.2012    Y    -- 合并连续Y的有效期
Y0001      10.01.2013   20.01.2013    Z
Y0001      21.01.2013   30.01.2013    Y
Y0001      12.02.2013   21.02.2013    X

现有初始代码 #

Select device_id
       ,valid_from
       ,valid_to
       ,LEAD(product,1,0) OVER (order by 1) as next_row
from test

解决方案 #

要实现连续相同产品的合并，核心是给连续相同的产品记录打上同一个分组标识，再按分组聚合。这里用LAG窗口函数对比当前行与上一行的产品，生成分组ID，具体SQL如下：

WITH grouped_data AS (
    SELECT 
        device_id,
        valid_from,
        valid_to,
        product,
        -- 生成分组ID：当前产品与上一行不同时，分组ID累加1
        SUM(CASE WHEN product = LAG(product) OVER (PARTITION BY device_id ORDER BY valid_from) THEN 0 ELSE 1 END) 
            OVER (PARTITION BY device_id ORDER BY valid_from) AS group_id
    FROM test
)
SELECT 
    device_id,
    MIN(valid_from) AS valid_from,
    MAX(valid_to) AS valid_to,
    product
FROM grouped_data
GROUP BY device_id, group_id, product
ORDER BY device_id, valid_from;

逻辑说明 #

1. 分组标识生成：通过LAG(product)获取当前设备上一条记录的产品，若当前产品与上一行不同，则标记为新分组，用SUM累加得到连续相同产品的分组ID。
2. 分组聚合：按device_id、group_id、product分组，取组内最早的valid_from和最晚的valid_to。
3. 排序保留顺序：最后按device_id和valid_from排序，保证结果顺序与原始记录一致。

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