PostgreSQL为每个id查找时间差超过5秒的所有行

Question

我有一个由以下数据组成的表格：

id       status       timestamp
1        REGISTERED   1000
2        REGISTERED   1030
2        COMPLETED    1031
1        PROCESSING   1035
3        REGISTERED   1040
3        COMPLETED    1041
1        COMPLETED    1048
4        REGISTERED   1049
5        REGISTERED   1050
5        PROCESSING   1056
6        REGISTERED   1060
6        PROCESSING   1062
6        COMPLETED    1080

我需要一个查询，该查询查找所有已注册状态的行，该查询的下一行将在稍后发生>=5时间戳单元，或者直接丢失。对于上述数据，将返回以下行：

id       status       timestamp
1        REGISTERED   1000
4        REGISTERED   1049
5        REGISTERED   1050

Answer 1

您可以使用lead

select id, status, timestamp
from (
    select id, status, timestamp
       , lead(timestamp) over(partition by id order by timestamp) nxt
    from mytable
) t 
where status = 'REGISTERED' and (nxt is null or timestamp < nxt - 5)