How to GROUP entries BY uninterrupted sequence?

Question

CREATE TABLE entries (
  id serial NOT NULL,
  title character varying,
  load_sequence integer
);

and data

INSERT INTO entries(title, load_sequence) VALUES ('A', 1);
INSERT INTO entries(title, load_sequence) VALUES ('A', 2);
INSERT INTO entries(title, load_sequence) VALUES ('A', 3);

INSERT INTO entries(title, load_sequence) VALUES ('A', 6);

INSERT INTO entries(title, load_sequence) VALUES ('B', 4);
INSERT INTO entries(title, load_sequence) VALUES ('B', 5);

INSERT INTO entries(title, load_sequence) VALUES ('B', 7);
INSERT INTO entries(title, load_sequence) VALUES ('B', 8);

Is there a way in PostgreSQL to write SQL that groups data by same title segments after ordering them by load_sequence. I mean:

=# SELECT id, title, load_sequence FROM entries ORDER BY load_sequence;
 id | title | load_sequence 
----+-------+---------------
  9 | A     |             1
 10 | A     |             2
 11 | A     |             3
 13 | B     |             4
 14 | B     |             5
 12 | A     |             6
 15 | B     |             7
 16 | B     |             8

AND I want groups:

=# SELECT title, string_agg(id::text, ',' ORDER BY id) FROM entries ???????????;

so result would be:

 title | string_agg  
-------+-------------
 A     | 9,10,11
 B     | 13,14
 A     | 12
 B     | 15,16

Unfortunately questions like this are rare that include the table creation scripts plus runnable `INSERT` statements that setup the sample data. +1 from me. — , Sep 02 '15 at 09:59

Giorgos Betsos · Accepted Answer · 2015-09-02T10:16:26.883

5

You can use the following query:

SELECT title, string_agg(id::text, ',' ORDER BY id)
FROM (
  SELECT id, title, 
         ROW_NUMBER() OVER (ORDER BY load_sequence) -
         ROW_NUMBER() OVER (PARTITION BY title 
                            ORDER BY load_sequence) AS grp
  FROM entries ) AS t
GROUP BY title, grp

Calculated grp field serves to identify slices of title records having consecutive load_sequence values. Using this field in the GROUP BY clause we can achieve the required aggregation over id values.

Demo here

edited Sep 02 '15 at 10:16

answered Sep 02 '15 at 09:57

Giorgos Betsos

71,379
9
63
98

Thank you, I got the idea. With little modifications for my specific case it works finally. – oskarae Sep 02 '15 at 13:21

score 0 · Answer 2 · answered Sep 02 '15 at 11:25

There's a trick you can use with sum as a window function running over a lagged window for this.

The idea is that when you hit an edge/discontinuity you return 1, otherwise you return 0. You detect the discontinuities using the lag window function.

SELECT title, string_agg(id::text, ', ') FROM (
  SELECT 
   id, title, load_sequence,
   sum(title_changed) OVER (ORDER BY load_sequence) AS partition_no
  FROM (
    SELECT
      id, title, load_sequence,
      CASE WHEN title = lag(title, 1) OVER (ORDER BY load_sequence) THEN 0 ELSE 1 END AS title_changed FROM entries
  ) x
) y
GROUP BY partition_no, title;

How to GROUP entries BY uninterrupted sequence?

2 Answers2