在Postgres中查询连续列的“运行” [英] Querying for a 'run' of consecutive columns in Postgres

问题描述

我有一个表：

create table table1 (event_id integer, event_time timestamp without time zone);
insert into table1 (event_id, event_time) values
(1, '2011-01-01 00:00:00'),
(2, '2011-01-01 00:00:15'),
(3, '2011-01-01 00:00:29'),
(4, '2011-01-01 00:00:58'),
(5, '2011-01-02 06:03:00'),
(6, '2011-01-02 06:03:09'),
(7, '2011-01-05 11:01:31'),
(8, '2011-01-05 11:02:15'),
(9, '2011-01-06 09:34:19'),
(10, '2011-01-06 09:34:41'),
(11, '2011-01-06 09:35:06');

我想构造一个语句，给定一个事件可以返回'run'的长度从该事件开始的事件。运行由以下方式定义：

I would like to construct a statement that given an event could return the length of the 'run' of events starting with that event. A run is defined by:

1. 如果两个事件之间的时间间隔在30秒之内，则两个事件将同时运行。


2. 如果A和B一起运行，而B和C一起运行，则A和C一起运行
   。

但是我的查询不需要及时倒退，因此，如果我选择事件2，则仅将事件2、3和4计为一部分以2和3开头的事件的运行次数，应作为运行的时间返回。

However my query does not need to go backwards in time, so if I select on event 2, then only events 2, 3, and 4 should be counted as part of the run of events starting with 2, and 3 should be returned as the length of the run.

有什么想法吗？我很沮丧

Any ideas? I'm stumped.

推荐答案

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

Here is the RECURSIVE CTE-solution. (islands-and-gaps problems naturally lend themselves to recursive 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 rr
WHERE 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)

这篇关于在Postgres中查询连续列的“运行”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！