postgrei如何基于行数据窗口Postgre SQL行?

Question

我有一个表，它有一个时间戳列和一些数据列。给定间隔长度T(比如30分钟)，我想将表划分为"会话"。如果时间戳值的差异小于T，则两个相邻的行(按时间戳排序)在同一个'会话'中。如果差异大于T，则会话中断。例如，下面的表有两个大于T的间隔来分隔会话。如何使用SQL生成会话列?

<表类> 行 时间戳 会话 tbody><<tr>118:001218:021318:041418:301519:1026第19章20节27二十203

您可以在时间戳上使用lag()来测量差异，然后使用累积和来计算会话:

select t.*,
sum(case when prev_timestamp > timetamp - interval '30 minute' then 0 else 1 end) over
(order by timestamp) as session
from (select t.*,
lag(timestamp) over (order by timestamp) as prev_timestamp
from t
) t;

或者，您可以在Postgres中使用count()和filter:

select t.*,
1 + count(*) filter (where prev_timestamp < timestamp - interval '30 minute') over (order by timestamp) as session
from (select t.*,
lag(timestamp) over (order by timestamp) as prev_timestamp
from t
) t;