要按连续时间分组的SQL查询

Question

使用以下示例数据，我试图在给定"中断"时间的情况下将这些记录分为三组。

时间戳-120.2-120.2[/tr>-120.2-120.2[/tr>-120.2-120.2[/tr>-120.2-120.2

ID	Lat	Lng
1	80.1	2021-03-01 01:00:00
2	80.1	2021-03-01 01:00
3	80.1	2021-03-01 01:02:00
4	80.1	-120.2	2021-03-01 01:03:00
5	80.1	2021-03-01 01:04:00
6	80.1	-120.2	2021-03-01 01:15:00
7	80.1	-120.2	2021-03-01 01:16:00
8	80.1	-120.2	2021-03-01 01:17:00
9	80.1	-120.2	2021-03-01 01:18:00
10	80.1	2021-03-01 02:10:00
11	80.1	-120.2	2021-03-01 02:11:00
12	80.1	2021-03-01 02:12:00
13	80.1	2021-03-01 02:13:00
14	80.1	2021-03-01 02:14:00

您可以使用lag()和累计总和：

select t.*,
sum( case when prev_timestamp < timestamp - interval 5 minute or
prev_timestamp <> prev_timestamp_ll
then 1
else 0
end ) over (order by timestamp) as grp           
from (select t.*,
lag(timestamp) over (partition by lat, lng order by timestamp) as prev_timestamp_ll,
lag(timestamp) over (order by timestamp) as prev_timestamp
from t
) t;

这里有一个db<gt；不停摆弄