PostgreSQL 合并重叠的时间间隔，如何实现

在本文中，我们将介绍如何使用 PostgreSQL 数据库来合并重叠的时间间隔。合并重叠的时间间隔是在处理时间相关数据时常见的需求，特别是在日程安排、会议时间表或时间段预订等领域。通过合并重叠的时间间隔，我们可以简化数据的处理和分析，减少重叠的冲突。

阅读更多：PostgreSQL 教程

创建示例表格

为了演示合并重叠的时间间隔的示例，我们首先创建一个名为”intervals”的表格，并添加一些包含时间间隔的数据。

CREATE TABLE intervals (
    id serial PRIMARY KEY,
    start_time timestamp,
    end_time timestamp
);

INSERT INTO intervals (start_time, end_time) VALUES
    ('2022-01-01 08:00:00', '2022-01-01 09:00:00'),
    ('2022-01-01 09:00:00', '2022-01-01 10:00:00'),
    ('2022-01-01 10:00:00', '2022-01-01 11:00:00'),
    ('2022-01-01 10:30:00', '2022-01-01 11:30:00'),
    ('2022-01-01 11:00:00', '2022-01-01 12:00:00');

以上代码创建了一个包含五个时间间隔的示例表格。每个时间间隔具有“开始时间”和“结束时间”两个列。

合并重叠的时间间隔

要合并重叠的时间间隔，我们可以使用 PostgreSQL 的窗口函数和递归查询来实现。以下是一个使用递归查询的示例。

WITH RECURSIVE merged_intervals AS (
  SELECT
    start_time,
    end_time,
    1 AS level,
    ARRAY[id] AS ids
  FROM
    intervals
  WHERE
    id = (SELECT MIN(id) FROM intervals)
  UNION ALL
  SELECT
    CASE
      WHEN intervals.start_time <= mt.end_time THEN mt.start_time
      ELSE intervals.start_time
    END,
    CASE
      WHEN intervals.end_time >= mt.end_time THEN intervals.end_time
      ELSE mt.end_time
    END,
    mt.level + 1,
    mt.ids || intervals.id AS ids
  FROM
    merged_intervals mt
    JOIN intervals ON intervals.start_time <= mt.end_time
        AND intervals.end_time >= mt.start_time
        AND intervals.id NOT IN (SELECT UNNEST(mt.ids))
)
SELECT
  start_time,
  end_time
FROM
  merged_intervals
WHERE
  level = (SELECT MAX(level) FROM merged_intervals);

以上代码使用了递归查询来合并重叠的时间间隔。通过递归查询，我们为每个时间间隔分配了一个级别，并用一个数组追踪已合并的时间间隔。在每次迭代时，我们将重叠的时间间隔合并为一个新的时间间隔，并更新级别和已合并的时间间隔数组。最后，我们选择级别为最大值的时间间隔作为结果。

运行以上代码后，将获得合并后的时间间隔结果：

 start_time          | end_time
---------------------+---------------------
 2022-01-01 08:00:00 | 2022-01-01 09:00:00
 2022-01-01 09:00:00 | 2022-01-01 12:00:00

以上结果展示了两个合并后的时间间隔，分别为’2022-01-01 08:00:00’至’2022-01-01 09:00:00’和’2022-01-01 09:00:00’至’2022-01-01 12:00:00’。