PostgreSQL查询以检测重叠的时间范围

一尘不染

PostgreSQL查询以检测重叠的时间范围

sql

我在PostgreSQL 9.2中有一张表，看起来像这样（简化）：

CREATE TABLE my_features
(
  id integer NOT NULL,
  feature_id integer NOT NULL,
  begin_time timestamp NOT NULL,
  end_time timestamp
)

对于每个feature_id，可能会有多行，其时间范围由begin_time /
end_time指定。它们可能重叠，但这是相对罕见的。我正在寻找一种快速的方法来查找所有具有/不具有任何重叠的feature_id 。

我尝试使用窗口函数来执行此操作，如下所示：

SELECT feature_id, bool_or(end_time > lead(begin_time) OVER ts_win) OVER ts_win AS overlaps_any
FROM my_features
WINDOW ts_win AS (PARTITION BY feature_id ORDER BY begin_time)

…但这是行不通的：

ERROR:  window function calls cannot be nested

该算法很简单：按begin_time排序给定feature_id的行，并检查是否有end_time>下一个begin_time（如果有）。我怀疑必须有一种简单的方法可以执行此操作，也许使用tsrange函数，但是似乎暂时找不到它。

阅读 166

2021-05-05

共1个答案

一尘不染

实际上，可以使用范围类型来完成此操作。

下面将选择所有具有重叠范围的行：

select f1.*
from my_features f1
where exists (select 1
              from my_features f2
              where tsrange(f2.begin_time, f2.end_time, '[]') && tsrange(f1.begin_time, f1.end_time, '[]')
                and f2.feature_id = f1.feature_id
                and f2.id <> f1.id);

当您将条件更改为时，NOT EXISTS您会发现那些没有任何重叠范围的条件。

SQLFiddle示例：http
://sqlfiddle.com/#!15/40b1e/1

tsrange(f2.begin_time, f2.end_time, '[]')创建一个包含上限和下限的范围。您还可以创建排除一个或两个范围的范围。

可以在手册中找到更多详细信息：http :
//www.postgresql.org/docs/current/static/rangetypes.html#RANGETYPES-
INCLUSIVITY

的&&操作者检查是否两个范围重叠：http://www.postgresql.org/docs/current/static/functions-
range.html

（我只是希望甲骨文喜欢这样的东西…）

2021-05-05