在Postgres中查询连续列的“运行”

在Postgres中查询连续列的“运行”

这是递归CTE解决方案。（孤岛和空白问题自然会导致递归CTE）

WITH RECURSIVE runrun AS (    SELECt event_id, event_time    , event_time - ('30 sec'::interval) AS low_time    , event_time + ('30 sec'::interval) AS high_time    FROM table1    UNIOn    SELECt t1.event_id, t1.event_time    , LEAST ( rr.low_time, t1.event_time - ('30 sec'::interval) ) AS low_time    , GREATEST ( rr.high_time, t1.event_time + ('30 sec'::interval) ) AS high_time    FROM table1 t1    JOIN runrun rr ON t1.event_time >= rr.low_time       AND t1.event_time < rr.high_time    )SELECt DISTINCT ON (event_id) *FROM runrun rrWHERe rr.event_time >= '2011-01-01 00:00:15'AND rr.low_time <= '2011-01-01 00:00:15'AND rr.high_time > '2011-01-01 00:00:15'    ;

结果：

 event_id |     event_time      |      low_time       |      high_time      ----------+---------------------+---------------------+---------------------        2 | 2011-01-01 00:00:15 | 2010-12-31 23:59:45 | 2011-01-01 00:00:45        3 | 2011-01-01 00:00:29 | 2010-12-31 23:59:45 | 2011-01-01 00:01:28        4 | 2011-01-01 00:00:58 | 2010-12-31 23:59:30 | 2011-01-01 00:01:28(3 rows)