一尘不染

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