时间戳

首页 > 解决方案 > 查找彼此相距 x 个单位内的所有记录

sql - 查找彼此相距 x 个单位内的所有记录

问题描述

我有一张这样的桌子：

CREATE TABLE t(idx integer primary key, value integer);
INSERT INTO t(idx, value)
VALUES
    (1, 1),
    (2, 2),
    (3, 3),
    (4, 6),
    (5, 7),
    (6, 12)

我想返回值在 2 以内的所有记录组，并带有关联的组标签作为识别它们的新列。

我想也许递归查询可能是合适的......但我的 sql-fu 缺少。

标签： sqlpostgresql

解决方案

我假设您想要对行进行分组，以便每个组中的任何两个值最多只能相差 2。那么您是对的，递归查询是解决方案。在每一级递归中，新组的边界都是预先计算的。组是不相交的，因此最终将原始表与计算的组号连接起来，并按此编号进行分组。Db小提琴在这里。

with recursive r (minv,maxv,level) as (
  select min(t.value), min(t.value) + 2, 1
  from t
  union all
  select minv, maxv, level from (
    select t.value as minv, t.value + 2 as maxv, r.level + 1 as level, row_number() over (order by minv) rn
    from r
    join t on t.value > r.maxv
  ) x where x.rn = 1
)
select r.level
     , format('ids from %s to %s', min(t.idx), max(t.idx)) as id_label
     , format('values from %s to %s', min(t.value), max(t.value)) as value_label
from t join r on t.value between r.minv and r.maxv
group by r.level
order by r.level

（递归部分的内部查询只是将新添加的行数限制为一个。更简单的子句select min(t.value), min(t.value) + 2是不可能的，因为递归部分不允许聚合函数，解析函数是解决方法。）

推荐阅读