Oracle SQL实现：基于上一行START_DATETIME更新END_DATETIME空值

需求说明 #

表temp_t中部分END_DATETIME字段为NULL，需完成以下更新：

• 非最新行（同rule_id下START_DATETIME不是最大的行）的END_DATETIME空值，更新为上一行（时间更晚的行）的START_DATETIME减1秒
• 最新行（START_DATETIME最大的行）的END_DATETIME空值保持不变

表结构与测试数据 #

create table temp_t 
(
    rule_id number, 
    start_datetime timestamp, 
    end_datetime timestamp,
    last_update timestamp 
);

insert into temp_t values (2345,TO_TIMESTAMP('2023-10-02 15:22:37','YYYY-MM-DD HH24:MI:SS'),null,null);
insert into temp_t values (2345,TO_TIMESTAMP('2023-10-02 15:18:36','YYYY-MM-DD HH24:MI:SS'),TO_TIMESTAMP('2023-10-02 15:22:36','YYYY-MM-DD HH24:MI:SS'),null);
insert into temp_t values (2345,TO_TIMESTAMP('2023-10-02 15:15:18','YYYY-MM-DD HH24:MI:SS'),null,null);
insert into temp_t values (2345,TO_TIMESTAMP('2023-10-02 15:11:02','YYYY-MM-DD HH24:MI:SS'),TO_TIMESTAMP('2023-10-02 15:12:10','YYYY-MM-DD HH24:MI:SS'),null);
insert into temp_t values (2345,TO_TIMESTAMP('2023-10-02 14:03:02','YYYY-MM-DD HH24:MI:SS'),null,null);

预期结果 #

RULE_ID | START_DATETIME                 | END_DATETIME                  |
-----------------------------------------------------------------------
2345    |02-OCT-23 03.22.37.000000000 PM |  NULL
2345    |02-OCT-23 03.18.36.000000000 PM |  02-OCT-23 03.22.36.000000000 PM
2345    |02-OCT-23 03.15.18.000000000 PM |  02-OCT-23 03.18.35.000000000 PM
2345    |02-OCT-23 03.11.02.000000000 PM |  02-OCT-23 03.12.10.000000000 PM
2345    |02-OCT-23 02.03.02.000000000 PM |  02-OCT-23 03.11.01.000000000 PM

实现代码 #

方法1：使用MERGE语句（推荐） #

MERGE INTO temp_t t
USING (
    SELECT 
        rule_id,
        start_datetime,
        end_datetime,
        -- 按start_datetime降序，获取同规则下时间更晚的记录的start_datetime
        LEAD(start_datetime) OVER (PARTITION BY rule_id ORDER BY start_datetime DESC) AS next_start
    FROM temp_t
) src
ON (t.rule_id = src.rule_id AND t.start_datetime = src.start_datetime)
WHEN MATCHED THEN
    UPDATE SET t.end_datetime = CASE 
        -- 仅更新END_DATETIME为空且存在后续时间记录的行
        WHEN t.end_datetime IS NULL AND src.next_start IS NOT NULL 
        THEN src.next_start - INTERVAL '1' SECOND 
        ELSE t.end_datetime 
    END;

方法2：使用UPDATE语句 #

UPDATE temp_t t
SET end_datetime = (
    SELECT next_start - INTERVAL '1' SECOND
    FROM (
        SELECT 
            rule_id,
            start_datetime,
            LEAD(start_datetime) OVER (PARTITION BY rule_id ORDER BY start_datetime DESC) AS next_start
        FROM temp_t
    ) src
    WHERE src.rule_id = t.rule_id AND src.start_datetime = t.start_datetime
)
WHERE t.end_datetime IS NULL 
-- 排除最新行（无后续时间记录的行）
AND EXISTS (
    SELECT 1
    FROM (
        SELECT 
            rule_id,
            start_datetime,
            LEAD(start_datetime) OVER (PARTITION BY rule_id ORDER BY start_datetime DESC) AS next_start
        FROM temp_t
    ) src
    WHERE src.rule_id = t.rule_id AND src.start_datetime = t.start_datetime
    AND src.next_start IS NOT NULL
);

验证结果 #

执行更新后，运行以下查询查看结果：

SELECT 
    rule_id,
    TO_CHAR(start_datetime, 'DD-MON-RR HH.MI.SS.FF9 AM') AS start_datetime,
    TO_CHAR(end_datetime, 'DD-MON-RR HH.MI.SS.FF9 AM') AS end_datetime
FROM temp_t
ORDER BY start_datetime DESC;

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