sql - Group by repeating attribute

Question

Basically I have a table messages, with user_id field that identifies a user that created the message.

When I display a conversation(set of messages) between two users, I want to be able to group the messages by user_id, but in a tricky way:

Let's say there are some messages (sorted by created_at desc):

  id: 1, user_id: 1
  id: 2, user_id: 1
  id: 3, user_id: 2
  id: 4, user_id: 2
  id: 5, user_id: 1

I want to get 3 message groups in the below order: [1,2], [3,4], [5]

It should group by *user_id* until it sees a different one and then groups by that one.

I'm using PostgreSQL and would be happy to use something specific to it, whatever would give the best performance.

Answer 1

Try something like this:

SELECT user_id, array_agg(id)
FROM (
SELECT id, 
       user_id, 
       row_number() OVER (ORDER BY created_at)-
       row_number() OVER (PARTITION BY user_id ORDER BY created_at) conv_id
FROM table1 ) t
GROUP BY user_id, conv_id;

The expression:

row_number() OVER (ORDER BY created_at)-
row_number() OVER (PARTITION BY user_id ORDER BY created_at) conv_id

Will give you a special id for every message group (this conv_id can be repeated for other user_id, but user_id, conv_id will give you all distinct message groups)

My SQLFiddle with example.

Details: row_number(), OVER (PARTITION BY ... ORDER BY ...)