sql - postgres 选择聚合时间跨度

Question

我有一个具有以下结构的表:

timstamp-start, timestamp-stop
1,5
6,10
25,30
31,35
...

我只对连续的时间跨度感兴趣，例如一个时间戳结束和下一个时间戳开始之间的间隔小于 3。

我怎样才能获得汇总的涵盖时间跨度:

timestamp-start,timestamp-stop
1,10
25,35

我考虑这个的原因是因为用户可能会请求一个需要返回几千行的时间跨度。然而，大多数记录是连续的，使用上述方法可能会将数千行减少到十几行。还是增加的计算量不值得节省带宽和延迟？

Answer 1

您可以分三步对时间戳进行分组:

1. 添加一个标志以确定新周期的开始位置(即大于 3 的间隙)。
2. 对标志进行累计求和以分配分组。
3. 重新聚合新的分组。

代码如下:

select min(ts_start) as ts_start, max(ts_end) as ts_end
from (select t.*,
             sum(flag) over (order by ts_start) as grouping
      from (select t.*,
                   (coalesce(ts_start - lag(ts_end) over (order by ts_start),0) > 3)::int as flag
            from t
           ) t
     ) t
group by grouping;