postgresql - 在 Postgresql 中快速加入最近邻？

Question

我有两个表 Q 和 T，都包含一列浮点数。

我想要做的是，对于 Q 中的每个数字，我想在 T 中找到一个与它的距离最小的数字。

例如，对于 T={1,7,9} 和 Q={2,6,10}，我想将 Q,T 对返回为 {(2,1),(6,7),(10,9 )}。

我应该如何用 SQL 表达这个查询？

此外，是否可以通过索引加速这种连接，例如添加一个将“FOR ORDER BY <->”与晶圆厂计算绑定的运算符类？

标签： postgresql

Answer 1

鉴于此架构：

create table t (tn float);
insert into t values (1), (7), (9);

create table q (qn float);
insert into q values (2), (6), (10);

DISTINCT ON是最直接的方法：

select distinct on (qn) qn, tn
  from q
       cross join t
 order by qn, abs(qn - tn);

根据您的数据大小，利用数值范围可能会表现得更好。如果性能是一个问题，那么您可以为 CTE 创建一个实际的临时表range_tn并在其上放置一个 gist 索引：

with all_tn as (
  select tn
    from t
   union select null
), range_tn as (
  select numrange(tn::numeric, (lead(tn) over w)::numeric, '[]') as tr
    from all_tn
  window w as (order by tn nulls first)
)
select qn,
       case 
         when lower_inf(tr) then upper(tr)
         when upper_inf(tr) then lower(tr)
         when 2 * qn - lower(tr) - upper(tr) > 0 then upper(tr)
         else lower(tr)
       end as tn
  from q
       join range_tn
         on qn::numeric <@ tr;

在这里提琴