如何使用带重置逻辑的FIRST_VALUE实现连续相同任务状态的时序数据聚合

正确实现方案 #

这个需求属于SQL典型的连续区间合并（岛屿问题），核心是先给连续相同的task_state生成统一的分组标识，再分组聚合即可。
完整可运行SQL如下：

WITH t AS
(
    SELECT
        *
    FROM
        (VALUES ('2021-10-09 23:58:34.000', '2021-10-10 00:00:00.000', 'task_completed', '00001'),
                ('2021-10-10 00:00:01.000', '2021-10-10 00:00:05.000', 'task_completed', '00001'),
                ('2021-10-10 00:00:06.000', '2021-10-10 00:00:25.000',  'task_completed', '00001'),
                ('2021-10-10 00:00:26.000', '2021-10-10 00:00:45.000', 'task_not_completed', '00001'),
                ('2021-10-10 00:00:46.000', '2021-10-10 00:01:00.000', 'task_not_completed', '00001'),
                ('2021-10-10 00:01:01.000', '2021-10-10 00:01:10.000', 'task_completed', '00001'),
                ('2021-10-10 00:01:11.000', '2021-10-10 00:01:15.000', 'task_completed', '00001')) AS t(start_time, end_time, task_state, person_id)
    ORDER BY 
        1
),
-- 第一步：获取上一行的任务状态
t1 AS (
    SELECT 
        *,
        LAG(task_state) OVER(ORDER BY start_time) AS prev_state
    FROM t
),
-- 第二步：生成分组标识，状态变化时分组号+1
t2 AS (
    SELECT 
        *,
        SUM(CASE WHEN task_state = prev_state THEN 0 ELSE 1 END) OVER(ORDER BY start_time) AS group_id
    FROM t1
)
-- 第三步：按分组聚合得到结果
SELECT 
    MIN(start_time) AS start_time,
    MAX(end_time) AS end_time,
    task_state
FROM t2
GROUP BY group_id, task_state
ORDER BY start_time;

逻辑说明 #

1. 之前的写法错误原因是没有对连续状态做分区，FIRST_VALUE没有分区约束时会返回整个结果集的首个值，自然无法做到状态切换时重置。
2. 上述方案中用LAG对比相邻行的状态，再通过累加求和生成的group_id，可以保证所有连续相邻的相同task_state行属于同一个分组，分组聚合后就能得到需要的结果，和你给出的预期输出完全一致。

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