postgresql:如何添加列，以显示两行之间的时间差

Question

我有一些带有time列的表

id  | time
----|-------------------
1   | 2021-2-2 10:21:00
2   | 2021-2-2 10:24:00
3   | 2021-2-2 10:27:00
4   | 2021-2-2 10:29:00

现在，我想添加另一个列，它显示时间差，另一个列，它将根据差异添加状态。

id  | time              | diff  | status
----|----------------------------------
1   | 2021-2-2 10:21:00 | None  | None
2   | 2021-2-2 10:24:00 | 3:00  | 1
3   | 2021-2-2 10:26:00 | 2:00  | 0
4   | 2021-2-2 10:29:00 | 3:00  | 1

status = if diff >= 3 then 1 else 0 

我该怎么做呢。我必须得到过去七天的数据，然后才能得到最后的表格。

Answer 1

使用LAG

SELECT id, time,
       EXTRACT(EPOCH FROM time - LAG(time) OVER (ORDER BY time)) / 60 AS diff,
       (EXTRACT(EPOCH FROM time - LAG(time) OVER (ORDER BY time)) / 60 >= 3)::int AS status
FROM yourTable
ORDER BY time;