He pasado por algunas preguntas de fusión relevantes, pero mi problema es ligeramente diferente de las existentes. Tengo una tabla en mi base de datos PostgreSQL 9.5 que contiene cuatro columnas, es decir, Segmento (grupo único), altura (numérico), límite inferior (numérico) y límite superior (numérico). Los datos de muestra son los siguientes:

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

Necesito fusionar filas condicionalmente en función de la diferencia de valores de altura. Intentaría explicar en pasos:

  • Comenzando desde la primera altura, verifique si la diferencia entre la fila anterior y la siguiente es menor o igual a 1
  • Si se cumple la condición, combine esas filas con el límite inferior de la primera fila y el límite superior de la fila fusionada
  • Si todas las filas se fusionan en un grupo, seleccione la altura más común con el límite inferior del primer y el límite superior de la última fila fusionada
  • repita esto para otros grupos

    Según lo anterior, el resultado deseado podría ser:

    Altura del segmento lower_limit upper_limit A 19.3 112 292 B 22,1 203 383 C 18,9 136 226 C 27.1 286 346 C 6.5 346 406

¿Alguien puede ayudarme para que pueda fusionar condicionalmente las filas en función de los valores de diferencia de altura?

0
khajlk 10 jun. 2017 a las 16:53

2 respuestas

La mejor respuesta

Suponiendo que la columna lower_limit se puede usar para ordenar, puede usar

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
1
Vamsi Prabhala 10 jun. 2017 a las 15:21
  -- setting reset points
  with  b as
  (
      select segment, height, lower_limit, upper_limit, 
             case when lag(height) over (partition by segment order by segment, height) is null
                       or abs(height - lag(height) over (partition by segment order by segment, height)) > 1
                  then 1 end as is_reset
      from   foo
  )
     -- setting groups
     , c as
     (
         select segment, height, lower_limit, upper_limit,
                sum(is_reset) over (order by segment, height) as grp
         from b
     )
       -- finding most common height
           select segment, mode() within group (order by height),
                  min(lower_limit) as lower_limit, 
                  max(upper_limit) as upper_limit
           from c
           group by segment, grp
segment |  mode | lower_limit | upper_limit
:------ | ----: | ----------: | ----------:
A       | 19.30 |         112 |         292
B       | 22.10 |         203 |         383
C       |  6.50 |         346 |         406
C       | 18.90 |         136 |         226
C       | 27.10 |         286 |         346

dbfiddle aquí

1
Community 20 jun. 2020 a las 09:12