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

- EM0

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有像这样的花哨东西...）}

- user330315

---

谢谢，可行！现在看来很简单 :) - EM0

太棒了！非常感谢，这节省了我好几天的时间，我刚刚开始接触PostgreSQL和其他很酷的东西！ - Guillaume OSTORERO

非常感谢，这救了我一天。 - wamae

Answer 2

这里有一个观察结果。如果一个功能存在重叠的时间段，则至少有一个时间段与之前定义的begin_time相重叠。(你也可以从另一个角度看待这个问题。如果没有这样的重叠，那么每个时间段之间都会有一个间隙，没有任何重叠。)

这导致以下查询用于确定重叠:

select f.feature_id
from (select f.feature_id,
             (case when lag(end_time) over (partition by feature_id order by begin_time) > begin_time
                   then 1 else 0
              end) as HasOverlap
      from my_features f
     ) f
group by f.feature_id
having max(HaxOverlap) = 1;

- Gordon Linoff

---

谢谢，这个可行并且基本上就是我在原帖中尝试做的。我移除了“case when”，只是在比较结果上使用了bool_or()。 - EM0