sql - 如何根据 PostgreSQL 中的行差异有条件地合并行？

Question

我已经完成了一些相关的合并问题，但我的问题与现有问题略有不同。我的 PostgreSQL 9.5 数据库中有一个表，其中包含四列，即 Segment(唯一组)、height(数字)、lower_limit(数字)和 upper_limit(数字)。示例数据如下:

Segment height  lower_limit upper_limit
A       19.3    112         142
A       19.3    142         172
A       20.3    172         202
A       20.3    202         232
A       19.3    232         262
A       19.3    262         292
B       22.1    203         233
B       22.1    233         263
B       22.1    263         293
B       22.1    293         323
B       22.1    323         353
B       22.1    353         383
C       18.9    136         166
C       18.9    166         196
C       18.9    196         226
C       27.1    286         316
C       27.1    316         346
C       6.5     346         376
C       6.5     376         406

我需要根据高度值的差异有条件地合并行。我会尝试分步骤解释:

• 从第一个高度开始，检查前后行之间的差异是否小于或等于1
• 如果条件满足，合并第一行下限和合并行上限的行
• 如果所有行都合并到一个组中，则选择最常见的高度，下限为第一行和最后合并行的上限

• 对其他组重复此操作

  基于以上，期望的输出可能是这样的:

  段高度 lower_limit upper_limit
19.3 112 292
乙 22.1 203 383
C 18.9 136 226
C 27.1 286 346
C 6.5 346 406

有人可以帮我根据高度差值有条件地合并行吗？

Answer 1

假设 lower_limit 列可以用于排序，你可以使用

select segment,mode() within group(order by height),min(lower_limit),max(upper_limit) 
from (select t.*
      ,sum(case when abs(height-prev) <= 1 then 0 else 1 end) over(partition by segment order by lower_limit) as grp
      from (select t.*
            ,lag(height) over(partition by segment order by lower_limit) as prev
            from tbl t
           ) t
    ) t
group by segment,grp