SQL - 在 PostgreSQL 中以组的形式查找连续时间序列中的记录

Question

我有一个时间序列数据，其中我试图在特定时间间隔内按顺序查找连续的记录，并按每个系列的连续记录进行分组。例如，如果每个集合(组)的记录之间的时间间隔为 5 分钟，则 5 分钟内的任何下一条记录都将在该集合中，而任何超过 5 分钟的记录将结束该集合(组)。一个新的集合(组)将在 5 分钟内从接下来的两个记录中出现。

**表中没有分，是在查询中计算的

|    |                     |                                   |          |                                                            | 
|----|---------------------|-----------------------------------|----------|------------------------------------------------------------| 
| id | timestamp           | minute separation (Calculated **) | group    | notes                                                      | 
| 1  | 2018-02-13T01:18:00 | 0                                 | Group 1  |                                                            | 
| 2  | 2018-02-13T01:22:00 | 4                                 | Group 1  |                                                            | 
| 3  | 2018-02-13T01:25:00 | 3                                 | Group 1  |                                                            | 
| 4  | 2018-02-13T01:31:00 | 6                                 | No Group | breaks group 1                                             | 
| 5  | 2018-02-13T01:38:00 | 7                                 | No Group | not within interval on either side                         | 
| 6  | 2018-02-13T01:44:00 | 6                                 | Group 2  | Start of group 2                                           | 
| 7  | 2018-02-13T01:47:00 | 3                                 | Group 2  |                                                            | 
| 8  | 2018-02-13T01:48:00 | 1                                 | Group 2  |                                                            | 
| 9  | 2018-02-13T01:49:00 | 1                                 | Group 2  |                                                            | 
| 10 | 2018-02-13T01:51:00 | 2                                 | Group 2  |                                                            | 
| 11 | 2018-02-13T01:57:00 | 6                                 | Group 3  | Breaks Group 2, included in next group as start of group 3 | 
| 12 | 2018-02-13T01:59:00 | 2                                 | Group 3  |                                                            | 
| 13 | 2018-02-13T02:01:00 | 2                                 | Group 3  |                                                            | 
| 14 | 2018-02-13T02:02:00 | 1                                 | Group 3  |                                                            | 
| 15 | 2018-02-13T02:08:00 | 6                                 | No Group | Breaks group 3                                             | 
| 16 | 2018-02-13T02:15:00 | 7                                 | No Group |                                                            | 
| 17 | 2018-02-13T02:22:00 | 7                                 | No Group |                                                            | 

我可以按顺序找到 2 行之间的差异并提取它们，但我不确定如何提取一系列 n+1 行。

我的工作样本在这里:http://sqlfiddle.com/#!17/e9fa1/7

示例数据 SQL 插入:

CREATE TABLE time_series (
  id SERIAL UNIQUE, 
  name TEXT,
  timestamp TIMESTAMPTZ
);

INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:18:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:22:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:25:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:31:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:38:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:44:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:47:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:48:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:49:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:51:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:57:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:59:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T02:01:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T02:02:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T02:08:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T02:15:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T02:22:00');

工作查询:

WITH my_series AS
(
  SELECT *, ROW_NUMBER() OVER (ORDER BY timestamp) AS rn
    FROM time_series 
)
SELECT o1.id AS id1, o1.timestamp AS date1, o2.id AS id2, o2.timestamp  AS date2, ABS(EXTRACT(EPOCH FROM (o1.timestamp - o2.timestamp))) AS diff

  FROM my_series as o1 JOIN my_series  as o2
    ON o1.rn + 1 = o2.rn
  WHERE ABS(EXTRACT(EPOCH FROM (o1.timestamp - o2.timestamp))) < 300;

我需要将每个组作为一组返回(这将起作用，因为我应该能够重复查询，注意查询结束以开始下一个查询的时间戳，尽管这看起来不是很有效)，或者返回所有标记为组的组，如下所示:

期望的结果:

|    |                     |                                   |          |                                                            | 
|----|---------------------|-----------------------------------|----------|------------------------------------------------------------| 
| id | timestamp           | minute separation (Calculated **) | group    | notes                                                      | 
| 1  | 2018-02-13T01:18:00 | 0                                 | 1        |                                                            | 
| 2  | 2018-02-13T01:22:00 | 4                                 | 1        |                                                            | 
| 3  | 2018-02-13T01:25:00 | 3                                 | 1        |                                                            | 
| 6  | 2018-02-13T01:44:00 | 6                                 | 2        |                                                            | 
| 7  | 2018-02-13T01:47:00 | 3                                 | 2        |                                                            | 
| 8  | 2018-02-13T01:48:00 | 1                                 | 2        |                                                            | 
| 9  | 2018-02-13T01:49:00 | 1                                 | 2        |                                                            | 
| 10 | 2018-02-13T01:51:00 | 2                                 | 2        |                                                            | 
| 11 | 2018-02-13T01:57:00 | 6                                 | 3        |                                                            | 
| 12 | 2018-02-13T01:59:00 | 2                                 | 3        |                                                            | 
| 13 | 2018-02-13T02:01:00 | 2                                 | 3        |                                                            | 
| 14 | 2018-02-13T02:02:00 | 1                                 | 3        |                                                            | 

Answer 1

您想使用 lag() 和 lead()。我会枚举所有组，甚至是只有一行的组:

select s.*,
       sum( (timestamp >= prev_timestamp + interval '5 minute' or prev_timestamp is null)::int ) over (order by timestamp) as grp
from (select s.*,
             lag(timestamp) over (order by timestamp) as prev_timestamp,
             lead(timestamp) over (order by timestamp) as next_timestamp
      from my_series s
     ) s;

对于您的实际问题，逻辑有点复杂。我认为这可以解决问题:

select s.*,
       (case when timestamp > prev_timestamp + interval '5 minute' and
                  timestamp < next_timestamp - interval '5 minute' and
             then NULL
             else sum( (timestamp >= prev_timestamp + interval '5 minute' and next_timestamp < timestamp + interval '5 minute')::int ) over (order by timestamp) 
        end) as grp
from (select s.*,
             lag(timestamp) over (order by timestamp) as prev_timestamp,
             lead(timestamp) over (order by timestamp) as next_timestamp
      from my_series s
     ) s;