CODEWITHSUNDEEP
postgresqlgaps-in-data
v.montag
v.montag

Reputation: 91

PostgreSQL create intermediate stages by overlap

is it possible to create intermediate lines with postgresql over previous and next line ?

i have the result

rownumber   from    to  with
1   1,303   2,88    A
2   2,88    5,65    A
3   5,65    8,659   A
4   8,659   10,342  A
5   10,33   24,188  B
6   10,342  26,253  C
7   26,253  26,38   A
8   26,38   30,442  A
9   30,442  35,672  A

and i need this

rownumber   from    to  with
1   1,303   2,88    A
2   2,88    5,65    A
3   5,65    8,659   A
4   8,659   10,33   A
    10,33   10,342  A
5   10,342  24,188  B

6   24,188  26,253  C
7   26,253  26,38   A
8   26,38   30,442  A
9   30,442  35,672  A

Upvotes: 0

Views: 33

Answers (1)

Roman Tkachuk
Roman Tkachuk

Reputation: 3276

If I understand you correctly, simpliest way is :

WITH x AS (
   SELECT DISTINCT sfrom FROM stages
   UNION SELECT sto FROM stages
), y AS (
   SELECT sfrom, row_number() OVER (ORDER BY sfrom) AS i FROM x
)
SELECT y2.i,
       y2.sfrom,
       y.sfrom as sto,
       coalesce(x2.swith, x.swith) as xwith
FROM y JOIN y AS y2 ON (y.i = y2.i + 1)
LEFT JOIN stages AS x ON (x.sfrom = y2.sfrom)
LEFT JOIN stages AS x2 ON (x2.sto = y.sfrom)
ORDER BY 1
;

Upvotes: 1

Related Questions