Snowflake SQL:试图计算后续行子集之间的时间差

Question

我在Snowflake数据库中有如下数据

MESSAGE_CREATED_ATtbody> <<tr>

DEVICE_SERIAL		REASON_CODE	VERSION	NEXT_REASON_CODE
BA1254862158	1	4	2022-06-23 02:06:03	4
BA1254862158	4	4	2022-06-23 02:07:07	1
BA1110001111	1	5	2022-06-16 16:19:04	4
BA1110001111	4	5	2022-06-16 17:43:04	1
BA1110001111	5	5	2022-06-20 14:37:45	4
BA1110001111	4	5	2022-06-20 17:31:12	1

with data as (
select *,
count(case when reason_code in (1, 5) then 1 end)
over (partition by device_serial order by message_created_at) as grp
/* or alternately bracket by the end code */
-- count(case when reason_code = 4 then 1 end)
-- over (partition by device_serial order by message_created_at desc) as grp
from T
)
select device_serial, min(version) as version,
datediff(second, min(message_created_at), max(message_created_at)) as delta_seconds
from data
group by device_serial, grp