我在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函数,但是似乎暂时找不到它。
实际上,可以使用范围类型来完成此操作。
下面将选择所有具有重叠范围的行:
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您会发现那些没有任何重叠范围的条件。
NOT EXISTS
SQLFiddle示例:http ://sqlfiddle.com/#!15/40b1e/1
tsrange(f2.begin_time, f2.end_time, '[]')创建一个包含上限和下限的范围。您还可以创建排除一个或两个范围的范围。
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
&&
(我只是希望甲骨文喜欢这样的东西…)