如何使用first_value与reset

Question

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
)
SELECT * 
FROM t

我试图获得一个聚合的结果，看起来像这样:

<表类>start_timeend_timetask_statetbody><<tr>"2021-10-09 23:58:34.0002021-10-10 00:00:25.000">task_completed"2021-10-10 00:00:26.0002021-10-10 00:01:00.000">task_not_completed"2021-10-10 00:01:01.0002021-10-10 00:01:15.000">task_completed

这是gaps & island的问题:

SELECT person_id, task_state, MIN(start_time) start_time, MAX(end_time) end_time  
from (
SELECT * 
, ROW_NUMBER() OVER (PARTITION BY t.person_id ORDER BY start_time)
- ROW_NUMBER() OVER (PARTITION BY t.person_id, t.task_state ORDER BY start_time) AS taskgroups
FROM t
) tt GROUP BY taskgroups, person_id, task_state

db<此处小提琴>