PostgreSQL查询以检测重叠的时间范围 [英] PostgreSQL query to detect overlapping time ranges

问题描述

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

I have a table in PostgreSQL 9.2 that looks like this (simplified):

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。

For each feature_id there may be multiple rows with time ranges specified by begin_time/end_time. They may overlap, but this is relatively rare. I'm looking for a fast way to find all feature_ids that have/don't have any overlaps.

我尝试使用窗口函数，如下所示：

I tried to do this using window functions, like this:

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)

。 ..但这不起作用：

... but this doesn't work:

ERROR:  window function calls cannot be nested

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

The algorithm is simple: order the rows for a given feature_id by begin_time and check if any end_time > the next begin_time (if any). I suspect there must be an easy way to do this, perhaps with tsrange functions, but can't seem to find it just now.

推荐答案

这确实可以使用范围类型来完成。

This can indeed be done using range types.

以下内容将选择所有具有重叠范围的行：

The following selects all those rows that do have overlapping ranges:

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);

将条件更改为不存在您会发现它们没有任何重叠范围。

When you change the condition to NOT EXISTS you'll find those that don't have any overlapping ranges.

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

SQLFiddle example: http://sqlfiddle.com/#!15/40b1e/1

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

tsrange(f2.begin_time, f2.end_time, '[]') creates a range that includes the upper and lower bounds. You can also create ranges that exclude either one or both.

更多详细信息可以在手册中找到：

http://www.postgresql.org/docs/current/static/rangetypes.html#RANGETYPES-INCLUSIVITY

More details can be found in the manual:
http://www.postgresql.org/docs/current/static/rangetypes.html#RANGETYPES-INCLUSIVITY

& 运算符检查两个范围是否重叠： http://www.postgresql.org/docs/current/static/functions-range.html

The && operator checks if the two ranges overlap: http://www.postgresql.org/docs/current/static/functions-range.html

^{（我只是希望甲骨文喜欢这样的东西...）}

这篇关于PostgreSQL查询以检测重叠的时间范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！