Contiguous Group By

Question

I have the following table:

SELECT * FROM mytable ORDER BY id; id name code time 1 A 111 1 2 A 111 2 3 A 888 3 4 A 888 4 5 A 888 5 6 A 888 6 7 A 888 7 8 A 111 8 9 A 111 9 10 A 111 10

I need to get a result like this:

name code times_between A 111 1,2 A 888 3,7 A 111 8,10

Is it possible to group by "chunks"? I need to make a distinction based on time, so I can't just group by name,code and get the first and last element only.

"the order of the table" --- there is no such thing. The data in the table is stored unordered and is not guaranteed to be the same unless you use ORDER BY in your queries explicitly. — zerkms
– zerkms, Commented Jun 16, 2017 at 9:22
postgresql.org/message-id/… And a lot of other relevant stuff when you google with "postgresql group consecutive" request. — zerkms
– zerkms, Commented Jun 16, 2017 at 9:31

Oto Shavadze · Accepted Answer · 2017-06-16 09:52:30Z

One way is this:

with the_table(id, name , code , time) as( select 1, 'A',111 , 1 union all select 2, 'A',111 , 2 union all select 3, 'A',888 , 3 union all select 4, 'A',888 , 4 union all select 5, 'A',888 , 5 union all select 6, 'A',888 , 6 union all select 7, 'A',888 , 7 union all select 8, 'A',111 , 8 union all select 9, 'A',111 , 9 union all select 10, 'A',111 , 10 ) select name, code, min(time) ||','|| max(time) from ( select name, code, time, id, row_number() over(order by id) - row_number() over(partition by name , code order by id) as grp from the_table ) t group by name, code, grp order by min(id)

(I forgot and just can't find/remember the name of technique, which creates groups grp)

You should use OVER (ORDER BY time), if I interpret the question correctly.
@LaurenzAlbe - if I correctly understand, order of "gaps and islands" depends on id column

Collectives™ on Stack Overflow

Contiguous Group By

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related