sql - 用于检测重叠时间范围的 PostgreSQL 查询

Question

我在 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 函数，但现在似乎找不到它。

Answer 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

^{(我只是希望 Oracle 有类似的东西...)}