Postgres SQL GROUP BY without jumping rows?

group-by, postgresql, sql

Solution

This is a gaps and islands problem (although this article is directed at SQL-Server it describes the problem very well so still applies to Postgresql) , and can be solved using ranking functions:

SELECT  id,
        thing,
        operation,
        timestamp,
        ROW_NUMBER() OVER(ORDER BY timestamp) - 
                ROW_NUMBER() OVER(PARTITION BY id, operation ORDER BY Timestamp) AS groupingSet,
        ROW_NUMBER() OVER(ORDER BY timestamp) AS PositionInSet,
        ROW_NUMBER() OVER(PARTITION BY id, operation ORDER BY Timestamp) AS PositionInGroup
FROM    T
ORDER BY timestamp;

As you can see by taking the overall position within the set, and deducting the position in the group you can identify the islands, where each unique combination of `(id, operation, groupingset)` represents an island:

id  thing   operation   timestamp   groupingSet PositionInSet   PositionInGroup
0   foo     add         0           0           1               1
0   bar     add         1           0           2               2           
1   baz     remove      2           2           3               1
1   dim     add         3           3           4               1
0   foo     remove      4           4           5               1
0   dim     add         5           3           6               3

Then you just need to put this in a subquery, and group by the relevant fields, and use string_agg to concatenate your things:

SELECT  id, STRING_AGG(thing) AS things, operation
FROM    (   SELECT  id,
                    thing,
                    operation,
                    timestamp,
                    ROW_NUMBER() OVER(ORDER BY timestamp) - 
                            ROW_NUMBER() OVER(PARTITION BY id, operation ORDER BY Timestamp) AS groupingSet
            FROM    T
        ) AS t
GROUP BY id, operation, groupingset;

Problem

Assuming I have this data in a table: ``` id | thing | operation | timestamp ----+-------+-----------+----------- 0 | foo | add | 0 0 | bar | add | 1 1 | baz | remove | 2 1 | dim | add | 3 0 | foo | remove | 4 0 | dim | add | 5 ``` Is there any way to construct a Postgres SQL query that will group by id and operation but without grouping rows with a higher timestamp value over those with lower? I want to get this out of the query: ``` id | things | operation ----+----------+----------- 0 | foo, bar | add 1 | baz | remove 1 | dim | add 0 | foo | remove 0 | dim | add ``` Basically group by, but only over adjacent rows sorted by timestamp.

Original source