为同一Class下时间范围相邻的行复制起始ID

需要将同一class中时间范围直接相邻的记录分组，把每组的首个ID填充到该组所有行的updated_id列中。例如：ID 131的结束时间为12:42，恰好是ID 132的开始时间，因此ID 132的updated_id为131。

测试表创建SQL #

create table test(ID,Start_date_time,End_date_time,class)
as values
 (131,  '5/26/2021 11:42',  '5/26/2021 12:42',  'AAA')
,(132,  '5/26/2021 12:42',  '5/26/2021 13:18',  'AAA')
,(113,  '5/26/2021 12:44',  '5/26/2021 13:19',  'AAA')
,(114,  '5/26/2021 13:19',  '5/26/2021 13:34',  'AAA')
,(115,  '5/26/2021 13:34',  '5/26/2021 13:44',  'AAA')
,(111,  '5/26/2021 16:09',  '5/26/2021 17:24',  'AAA')
,(112,  '5/26/2021 17:24',  '5/26/2021 18:09',  'AAA')
,(123,  '5/26/2021 8:08',   '5/26/2021 10:08',  'BBB')
,(124,  '5/26/2021 10:08',  '5/26/2021 11:08',  'BBB')
,(116,  '5/26/2021 11:06',  '5/26/2021 11:30',  'BBB')
,(117,  '5/26/2021 11:30',  '5/26/2021 12:18',  'BBB')
,(118,  '5/26/2021 12:18',  '5/26/2021 13:06',  'BBB')
,(128,  '5/26/2021 9:03',   '5/26/2021 9:48',   'CCC')
,(129,  '5/26/2021 9:48',   '5/26/2021 10:03',  'CCC')
,(130,  '5/26/2021 10:03',  '5/26/2021 10:13',  'CCC')
,(119,  '5/26/2021 10:22',  '5/26/2021 10:34',  'CCC')
,(120,  '5/26/2021 10:34',  '5/26/2021 10:58',  'CCC')
,(121,  '5/26/2021 10:58',  '5/26/2021 11:10',  'CCC')
,(122,  '5/26/2021 11:10',  '5/26/2021 11:22',  'CCC')
,(125,  '5/26/2021 14:47',  '5/26/2021 15:47',  'DDD')
,(126,  '5/26/2021 15:47',  '5/26/2021 16:35',  'DDD')
,(127,  '5/26/2021 16:35',  '5/26/2021 17:35',  'DDD')
;

期望结果 #

解决方案SQL #

WITH ranked_data AS (
    SELECT 
        *,
        SUM(CASE WHEN Start_date_time = LAG(End_date_time) OVER (PARTITION BY class ORDER BY Start_date_time) THEN 0 ELSE 1 END) 
            OVER (PARTITION BY class ORDER BY Start_date_time) AS group_id
    FROM test
)
SELECT 
    ID,
    Start_date_time,
    End_date_time,
    class,
    FIRST_VALUE(ID) OVER (PARTITION BY class, group_id ORDER BY Start_date_time) AS updated_id
FROM ranked_data
ORDER BY class, Start_date_time;

逻辑说明 #

1. 分组标记：通过LAG函数获取同一class中前一条记录的结束时间，对比当前记录的开始时间。如果不相等，视为新分组，累加1生成group_id。
2. 填充updated_id：在每个class和group_id的分组内，使用FIRST_VALUE获取该组的首个ID，填充到所有行的updated_id列。

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