获得N行之间的距离

Suppose I have a table such as:

假设我有一个表，例如：

ID,visit, Concentration
1, 1, 4.0
2, 2, 2.0
3, 3, 1.5
4, 4, 5.0
5, 1, 7.0
6, 2, 1.0
7, 3, 5.0
8, 4, 12.0

The table has 2 subgroups of 4 concentrations (the partition is made by id, visit). Within each group, I'd like to iterate over all 4 concentrations and return the two id,visit that are closest together based on the concentration.

该表有2个4个浓度的子组（分区由id，访问）。在每组中，我想迭代所有4个浓度并返回两个id，根据浓度访问最接近的一起。

So the query would return a result such as this:

所以查询会返回如下结果：

ID, visit, concentration
2, 2, 2.0
3, 3, 1.5
5, 1, 7.0
7, 3, 5.0

Thanks

谢谢

1 个解决方案

#1

You can assign the groups in various ways. Assuming that all are the same sizes, you can just enumerate the visits, in the order of ids. Then, I think it is easiest to return one row for each pair:

您可以通过各种方式分配组。假设所有大小都相同，您可以按ID的顺序枚举访问次数。然后，我认为每对返回一行最简单：

with g as (
      select t.*, row_number() over (partition by visit order by id) as grp
      from t
     )
select *
from (select g1.id as id1, g1.visit as visit1, g1.concentration as concentration1,
             g2.id as id2, g2.visit as visit2, g2.concentration as concentration2
             row_number() over (partition by g1.grp
                                order by abs(g1.concentration - g2.concentration)
                               ) as seqnum
      from g g1 join
           g g2
           on g1.grp = g2.grp and g1.id < g2.id
     ) g
where seqnum = 1;