如何在PostgreSQL中查找连续的datetime时间范围序列

需求背景 #

每行数据包含以分钟为单位的时长、开始时间与结束时间，时间精度为1分钟或更高，需要将连续的时间序列分组，统计每个连续区间的聚合信息。

表结构与测试数据 #

CREATE TABLE public."TimeSeries" (
    "Id" uuid NOT NULL,
    "BeginTime" timestamptz NOT NULL,
    "EndTime" timestamptz NOT NULL DEFAULT '0001-01-01 00:00:00' :: timestamp without time zone,
    "LengthMin" int4 NOT NULL
);

INSERT INTO public."TimeSeries" ("Id","BeginTime","LengthMin","EndTime") VALUES
     ('ecabcd8d-3129-4128-b126-a4e1abcd49c9'::uuid,'2021-11-16 11:45:00.000',1,'2021-11-16 11:46:00.000'),
     ('e5abcd70-5125-412b-9128-93ecabcdcba9'::uuid,'2021-11-16 11:46:00.000',1,'2021-11-16 11:47:00.000'),
     ('03abcdb8-8129-4124-b126-f5bbabcd7157'::uuid,'2021-11-16 11:47:00.000',1,'2021-11-16 11:48:00.000'),
     ('54abcdcc-6129-4126-912b-34b2abcde19a'::uuid,'2021-11-16 11:50:00.000',1,'2021-11-16 11:51:00.000'),
     ('77abcd28-212f-4122-912f-5060abcd2aa0'::uuid,'2021-11-16 11:51:00.000',1,'2021-11-16 11:52:00.000'),
     ('8babcdd1-f12a-4124-9128-2529abcda136'::uuid,'2021-11-16 11:52:00.000',1,'2021-11-16 11:53:00.000'),
     ('8aabcd94-9129-4121-b12c-1ebfabcd06b8'::uuid,'2021-11-16 12:35:00.000',1,'2021-11-16 12:36:00.000'),
     ('96abcd04-b12e-4122-b12b-4dc2abcdcee4'::uuid,'2021-11-16 12:40:00.000',1,'2021-11-16 12:41:00.000'),
     ('42abcd4a-f129-412c-9124-41b3abcd3ca3'::uuid,'2021-11-16 12:44:00.000',1,'2021-11-16 12:45:00.000'),
     ('beabcd8f-c12f-4126-a12b-0a37abcdd0bb'::uuid,'2021-11-16 12:49:00.000',1,'2021-11-16 12:50:00.000'),
     ('b8abcd79-d12c-4120-912f-754fabcdd220'::uuid,'2021-11-16 12:50:00.000',1,'2021-11-16 12:51:00.000'),
     ('c3abcd08-e121-4127-b125-4e70abcd5756'::uuid,'2021-11-16 12:59:00.000',1,'2021-11-16 13:00:00.000'),
     ('65abcdbe-7121-412a-a12c-1f68abcd94eb'::uuid,'2021-11-16 13:00:00.000',1,'2021-11-16 13:01:00.000'),
     ('f5abcd38-9122-412c-b12f-957fabcd79b5'::uuid,'2021-11-16 13:05:00.000',1,'2021-11-16 13:06:00.000');

现有查询问题 #

当前已经可以通过lag窗口函数判断当前行是否与上一行属于同一连续序列，查询代码如下：

SELECT
    "BeginTime" = lag("EndTime") OVER (
        ORDER BY
            "BeginTime"
    ) AS "isInSeries",
    *
FROM
    "TimeSeries"
WHERE
    "LengthMin" = 1
ORDER BY
    "BeginTime"

该查询仅能得到单行列的连续标识，无法将同属一个连续序列的所有行分组聚合。

期望输出 #

需要统计每个连续序列的起始时间、包含行数、最小开始时间、最大结束时间、总时长，格式如下：

|          SeriesBegin | cnt |                  min |                  max |            delta |
|----------------------|-----|----------------------|----------------------|------------------|
| 2021-11-16T11:45:00Z |   3 | 2021-11-16T11:45:00Z | 2021-11-16T11:48:00Z | 3 mins 0.00 secs |
| 2021-11-16T11:50:00Z |   3 | 2021-11-16T11:50:00Z | 2021-11-16T11:53:00Z | 3 mins 0.00 secs |
| 2021-11-16T12:35:00Z |   1 | 2021-11-16T12:35:00Z | 2021-11-16T12:36:00Z | 1 mins 0.00 secs |
| 2021-11-16T12:40:00Z |   1 | 2021-11-16T12:40:00Z | 2021-11-16T12:41:00Z | 1 mins 0.00 secs |
| 2021-11-16T12:44:00Z |   1 | 2021-11-16T12:44:00Z | 2021-11-16T12:45:00Z | 1 mins 0.00 secs |
| 2021-11-16T12:49:00Z |   2 | 2021-11-16T12:49:00Z | 2021-11-16T12:51:00Z | 2 mins 0.00 secs |
| 2021-11-16T12:59:00Z |   2 | 2021-11-16T12:59:00Z | 2021-11-16T13:01:00Z | 2 mins 0.00 secs |
| 2021-11-16T13:05:00Z |   1 | 2021-11-16T13:05:00Z | 2021-11-16T13:06:00Z | 1 mins 0.00 secs |

实现方案 #

通过「缺口与岛屿」经典算法实现连续序列分组，先给每个连续序列生成统一的分组ID，再做聚合计算即可，代码如下：

WITH marked_series AS (
    SELECT
        *,
        -- 遇到时间不连续的行时，分组ID+1，同一连续序列的行分组ID相同
        SUM(CASE WHEN "BeginTime" = LAG("EndTime") OVER (ORDER BY "BeginTime") THEN 0 ELSE 1 END) OVER (ORDER BY "BeginTime") AS series_group
    FROM "TimeSeries"
    WHERE "LengthMin" = 1
)
SELECT
    MIN("BeginTime") AS "SeriesBegin",
    COUNT(*) AS "cnt",
    MIN("BeginTime") AS "min",
    MAX("EndTime") AS "max",
    EXTRACT(EPOCH FROM (MAX("EndTime") - MIN("BeginTime")))/60 || ' mins 0.00 secs' AS "delta"
FROM marked_series
GROUP BY series_group
ORDER BY "SeriesBegin";

内容的提问来源于stack exchange，提问作者Fritz