如何从数据库中选择重叠的值对

Question

在我的 postgres 数据库中，我有一个包含 3 列的表，如下所示：

start | end  | sorce
17    | 23   | A
150   | 188  | A
200   | 260  | A
19    | 30   | B
105   | 149  | B
199   | 220  | B
...

我想选择来自不同源 A 和 B 的区域（从开始到结束）重叠的所有行。

更新：

从 postgres 8.4 版开始，可以使用 窗口函数。它比连接或子选择方法快得多。 链接到 postgres wiki。

Answer 1

这是一种强力方法（我将您的列 range_start 和 range_end 重命名，以避免与保留字“end”冲突）：

select *
from t cross join t t2
where t2.source <> t.source
      and box(point(t2.range_start,t2.range_start),point(t2.range_end,t2.range_end))
            && box(point(t.range_start,t.range_start),point(t.range_end,t.range_end))

或者

select *
from t
where exists (select 1 from t t2
              where t2.source <> t.source and box(point(t2.range_start,t2.range_start),point(t2.range_end,t2.range_end))
                  && box(point(t.range_start,t.range_start),point(t.range_end,t.range_end)))

您应该能够使用 GiST 索引，这可能会提高效率（seq scan +索引扫描）：

create index t_range_idx on t using gist (box(point(range_start,range_start),point(range_end,range_end))

此函数可以通过清理 SQL 来帮助理解：

create function range(not_before int, not_after int) returns box
   strict immutable language sql
   as $ select box(point($1,$1),point($2,$2)) $;

使用此函数，您可以编写：

select * from t where range(range_start,range_end) && range(10,20);

并注意 框 && box 运算符表示“重叠”。

Answer 2

如果您想要所有配对，则使用经典重叠测试 INNER JOIN A 和 B

a.start < b.end and b.start < a.end

，即，

select a.start a_start, a.end a_end, b.start b_start, b.end b_end
from tbl a
inner join tbl b on a.start < b.end and b.start < a.end and b.source = 'B'
where a.source = 'A'

如果您不是指源字面上'A'和'B'，只是它们不同，您可以使用根据

select a.start a_start, a.end a_end, b.start b_start, b.end b_end
from tbl a
inner join tbl b on a.start < b.end and b.start < a.end and a.source <> b.source

您对重叠的定义，将 < 与 <= 交换（两次）

• < ： 10-20 会 < em>不重叠 20-30
• <= : 10-20 确实重叠 20-30