CODEWITHSUNDEEP
sqlpostgresqlaggregate-functionsgaps-and-islands
Will Weld
Will Weld

Reputation: 328

Gap & Islands - POSTGRESQL

I have the following table

Name PathID PathLength PathStepNum
A      1      5          1
A      1      5          2
B      1      5          3
C      1      5          4
A      1      5          5
Y      2      3          1
Z      2      3          2
Z      2      3          3

And I need to get

PathID   PathName
1        A x2 > B x1 > C x1 > A x1
2        Y x1 > Z x2

So essentially what I need to do is: for each PathID, count the number of consecutive occurrences of Name, and stitch together Name + NumConsecOccurrences in the order of the PathStepNum.

Any thoughts? Thank you!

Upvotes: 0

Views: 427

Answers (1)

klin
klin

Reputation: 121774

A classic solution with window and aggregate functions:

select 
    pathid, 
    string_agg(format('%s x%s', name, count), ' > ' order by min) as path
from (
    select 
        pathid, name, min(pathstepnum), 
        count(*)
    from (
        select 
            pathid, name, pathstepnum, 
            sum(mark) over w as grp
        from (
            select 
                pathid, name, pathstepnum, 
                (name <> lag(name, 1, name) over w)::int as mark
            from my_table
            window w as (partition by pathid order by pathstepnum)
            ) s
        window w as (partition by pathid order by pathstepnum)
        ) s
    group by pathid, name, grp
    ) s
group by pathid

 pathid |           path            
--------+---------------------------
      1 | A x2 > B x1 > C x1 > A x1
      2 | Y x1 > Z x2
(2 rows)

DbFiddle.

It seems however, that this can be achieved in a client application (in python, java or whatever is used) in a simpler and more efficient way.

Upvotes: 1

Related Questions