I can’t write a request with grouping as in the social news feed.

For example, there is a table of all news (which is connected by a polymorphic link with tables in which a detailed description of the news) columns: id, user_id, message_id, message_type;

id: 1, user_id: 1, message_id: 4, message_type: 'Warning' id: 2, user_id: 1, message_id: 5, message_type: 'Warning' id: 3, user_id: 1, message_id: 6, message_type: 'Warning' id: 4, user_id: 2, message_id: 4, message_type: 'Error' id: 5, user_id: 2, message_id: 1, message_type: 'Exception' id: 6, user_id: 1, message_id: 2, message_type: 'Exception' id: 7, user_id: 1, message_id: 3, message_type: 'Exception' id: 8, user_id: 2, message_id: 4, message_type: 'Exception'

It is impossible to write a request that will give the latest news grouped by the same successive message_type for a user.

For example:

 id: 8, user_id: 2, message_id: 4, message_type: 'Exception' id: {6,7} user_id: 1, message_id: {2,3}, message_type: 'Exception' id: 5, user_id: 2, message_id: 1, message_type: 'Exception' id: 4, user_id: 2, message_id: 4, message_type: 'Error' id: {1, 2, 3}, user_id: 1, message_id: {4, 5}, message_type: 'Warning'

I do not want to use subqueries, because the table is large. Will be retrieved by 20 news. PostgreSQL database. How can this be done?

PS I asked a question in an English resource, but either I did not correctly ask it, or they did not understand me. https://stackoverflow.com/questions/30227391/postgree-double-group-by-repeating-attribute

  • something like select user_id, message_type, array_agg (id), array_agg (message_id) from t group by user_id, message_type; - nörbörnën
  • Such a grouping does not fit; you need to group news by the same user into one identical action. Example of standard grouping sqlfiddle.com/#!15/399c3/2 Merge strings with id 5 and 8 - Daniil Maksimov
  • do not quite understand. the problem is that they are not exactly consecutive (i.e., their identifier differs by more than 1)? - nörbörnën
  • The problem is that between them are two 'Exception' user_id: 1, which means they should not be grouped. - Daniil Maksimov
  • and in the formulation of the problem something is said about it? I see that the English version of the answer as well as I, it kakbe hints. - nörbörnën

1 answer 1

I found only 1 way:

  1. Using the window f-s lead () to determine the code has been changed pair (user, type of message)
  2. Using the window function sum (), set the sequence number for each new pair
  3. Group by pair number and get the desired information.

Check:

 create table test ( id serial primary key, user_id integer, message_id integer, message_type varchar ); insert into test (user_id, message_id, message_type) values (1, 4, 'Warning'), (1, 5, 'Warning'), (1, 6, 'Warning'), (2, 4, 'Error'), (2, 1, 'Exception'), (1, 2, 'Exception'), (1, 3, 'Exception'), (2, 4, 'Exception') ; select array_agg(grouped.id) as record_ids, grouped.user_id, array_agg(grouped.message_id) as message_ids, grouped.message_type from ( select changed.*, sum(changed.changed) over (order by changed.id desc) as group_n from ( select tt.*, case when lag((user_id, message_type)) over (order by tt.id desc) is distinct from (user_id, message_type) then 1 else 0 end as changed from test tt ) changed order by id desc ) grouped group by grouped.group_n, grouped.user_id, grouped.message_type order by grouped.group_n ;

Result:

 record_ids | user_id | message_ids | message_type ------------+---------+-------------+-------------- {8} | 2 | {4} | Exception {7,6} | 1 | {3,2} | Exception {5} | 2 | {1} | Exception {4} | 2 | {4} | Error {3,2,1} | 1 | {6,5,4} | Warning (5 rows)