使用SQL推导序列中事件变更的起止时间

需要计算每次事件变更对应的开始时间（START_TIME）与结束时间（END_TIME）。

示例数据 #

COL1    INSERT_TIME
A   2022-12-01 10:20:00.000
A   2022-12-01 10:30:00.000
A   2022-12-01 10:33:00.000
B   2022-12-01 10:34:00.000
B   2022-12-01 10:40:00.000
C   2022-12-01 10:41:00.000
C   2022-12-01 10:50:00.000
D   2022-12-01 10:55:00.000
A   2022-12-01 10:56:00.000
A   2022-12-01 11:57:00.000
A   2022-12-01 11:59:00.000
C   2022-12-01 12:00:00.000
C   2022-12-01 12:59:00.000

预期输出 #

COL1 START_TIME                 END_TIME
A    2022-12-01 10:20:00.000    2022-12-01 10:34:00.000
B    2022-12-01 10:34:00.000    2022-12-01 10:41:00.000
C    2022-12-01 10:41:00.000    2022-12-01 10:55:00.000
D    2022-12-01 10:55:00.000    2022-12-01 10:56:00.000
A    2022-12-01 10:56:00.000    2022-12-01 12:00:00.000
C    2022-12-01 12:00:00.000    NULL

当前尝试 #

已尝试用LEAD函数对比当前行与下一行识别变更，但不知道如何动态分组行来获取起止时间的最小/最大值。

数据准备 #

create OR REPLACE table test1
(
col1 varchar(10),
insert_time timestamp_ntz);

INSERT INTO TEST1 VALUES('A','2022-12-01 10:20:00');
INSERT INTO TEST1 VALUES('A','2022-12-01 10:30:00');
INSERT INTO TEST1 VALUES('A','2022-12-01 10:33:00');
INSERT INTO TEST1 VALUES('B','2022-12-01 10:34:00');
INSERT INTO TEST1 VALUES('B','2022-12-01 10:40:00');
INSERT INTO TEST1 VALUES('C','2022-12-01 10:41:00');
INSERT INTO TEST1 VALUES('C','2022-12-01 10:50:00');
INSERT INTO TEST1 VALUES('D','2022-12-01 10:55:00');
INSERT INTO TEST1 VALUES('A','2022-12-01 10:56:00');
INSERT INTO TEST1 VALUES('A','2022-12-01 11:57:00');
INSERT INTO TEST1 VALUES('A','2022-12-01 11:59:00');
INSERT INTO TEST1 VALUES('C','2022-12-01 12:00:00');
INSERT INTO TEST1 VALUES('C','2022-12-01 12:59:00');

;

select * from TEST1;

SELECT COL1,LEAD(COL1) OVER(ORDER BY INSERT_TIME) AS NEXT_COL1,
INSERT_TIME, LEAD(INSERT_TIME) OVER(PARTITION BY COL1 ORDER BY INSERT_TIME) AS NEXT_TIME
FROM TEST1;

解决方案 #

核心思路是先对连续相同的COL1值进行分组，再基于分组计算起止时间，具体步骤如下：

步骤1：标记连续分组 #

用LAG函数对比当前行与上一行的COL1值，当值不同时标记为新组，累加标记生成唯一分组ID：

WITH grouped_data AS (
    SELECT 
        col1,
        insert_time,
        -- 当前col1与上一行不同则记1，否则记0，累加得到分组ID
        SUM(CASE WHEN col1 = LAG(col1) OVER(ORDER BY insert_time) THEN 0 ELSE 1 END) 
            OVER(ORDER BY insert_time) AS group_id
    FROM test1
)

步骤2：计算每组起止时间 #

基于分组ID，取每组最小的insert_time作为START_TIME，再用LEAD函数获取下一组的最小insert_time作为当前组的END_TIME：

SELECT 
    col1,
    MIN(insert_time) AS START_TIME,
    LEAD(MIN(insert_time)) OVER(ORDER BY MIN(insert_time)) AS END_TIME
FROM grouped_data
GROUP BY group_id, col1
ORDER BY START_TIME;

完整SQL #

WITH grouped_data AS (
    SELECT 
        col1,
        insert_time,
        SUM(CASE WHEN col1 = LAG(col1) OVER(ORDER BY insert_time) THEN 0 ELSE 1 END) 
            OVER(ORDER BY insert_time) AS group_id
    FROM test1
)
SELECT 
    col1,
    MIN(insert_time) AS START_TIME,
    LEAD(MIN(insert_time)) OVER(ORDER BY MIN(insert_time)) AS END_TIME
FROM grouped_data
GROUP BY group_id, col1
ORDER BY START_TIME;

执行后即可得到预期输出，最后一组的END_TIME为NULL，因为没有后续事件组。

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