Группировать, повторяя атрибут

В основном у меня есть табличные messages с полем user_id которое идентифицирует пользователя, создавшего сообщение.

Когда я показываю разговор (набор сообщений) между двумя пользователями, я хочу иметь возможность группировать сообщения user_id , но сложным способом:

Допустим, есть сообщения (отсортированные по 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 

Я хочу получить 3 группы сообщений в следующем порядке: [1,2], [3,4], [5]

Он должен группироваться * user_id * до тех пор, пока он не увидит другой, а затем группирует его.

Я использую PostgreSQL и буду рад использовать что-то конкретное, независимо от того, что даст лучшую производительность.

Правильный SQL

@Igor представляет хороший метод pure-SQL с функциями окна.
Однако:

Я хочу получить 3 группы сообщений в следующем порядке: [1,2], [3,4], [5]

Чтобы получить запрошенный заказ, добавьте ORDER BY min(id) :

 SELECT array_agg(id) AS ids FROM ( SELECT id ,user_id ,row_number() OVER (ORDER BY id) - row_number() OVER (PARTITION BY user_id ORDER BY id) AS grp FROM messages ORDER BY id) t -- for ordered arrays in result GROUP BY grp, user_id ORDER BY min(id); 

SQL Fiddle.

Добавление вряд ли оправдало бы другой ответ. Более важная проблема заключается в следующем:

Быстрее с PL / pgSQL

Я использую PostgreSQL и буду рад использовать что-то конкретное, независимо от того, что даст лучшую производительность .

Чистый SQL – все хорошо и блестяще, но процедурная серверная функция намного быстрее для этой задачи. Хотя обработка строк процедурно, как правило, медленнее , plpgsql выигрывает в этом соревновании большое время, потому что он может справиться с одним сканированием таблицы и одним оператором ORDER BY :

 CREATE OR REPLACE FUNCTION f_msg_groups() RETURNS TABLE (ids int[]) AS $func$ DECLARE _id int; _uid int; _id0 int; -- id of last row _uid0 int; -- user_id of last row BEGIN FOR _id, _uid IN SELECT id, user_id FROM messages ORDER BY id LOOP IF _uid <> _uid0 THEN RETURN QUERY VALUES (ids); -- output row (never happens after 1 row) ids := ARRAY[_id]; -- start new array ELSE ids := ids || _id; -- add to array END IF; _id0 := _id; _uid0 := _uid; -- remember last row END LOOP; RETURN QUERY VALUES (ids); -- output last iteration END $func$ LANGUAGE plpgsql; 

Вызов:

 SELECT * FROM f_msg_groups(); 

Бенчмаркинг и ссылки

Я провел быстрый тест с EXPLAIN ANALYZE на аналогичной реальной таблице жизни с 60 тыс. Строк (выполнить несколько раз, выбрать самый быстрый результат, чтобы исключить эффекты оглашения):

SQL:
Общая продолжительность выполнения: 1009,549 мс
Pl / PGSQL:
Общая продолжительность выполнения: 336,971 мс

Также рассмотрите эти тесно связанные вопросы:

  • GROUP BY и агрегировать последовательные числовые значения
  • ГРУППА ПО последовательным датам, ограниченным пробелами
  • Упорядоченный счет последовательных повторов / дубликатов

Попробуйте что-то вроде этого:

 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; 

Выражение:

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

conv_id вам специальный идентификатор для каждой группы сообщений (этот conv_id может быть повторен для другого user_id , но user_id, conv_id предоставит вам все отдельные группы сообщений)

Мой SQLFiddle с примером.

Подробности: row_number() , OVER (PARTITION BY ... ORDER BY ...)

Предложение GROUP BY user_id ответ в двух записях: один с user_id 1 и один с user_id 2 независимо от предложения ORDER BY , поэтому я рекомендую вам отправить только ORDER BY created_at

 prev_id = -1 messages.each do |m| if ! m.user_id == prev_id do prev_id = m.user_id #do whatever you want with a new message group end end 

Вы можете использовать кусок :

 Message = Struct.new :id, :user_id messages = [] messages << Message.new(1, 1) messages << Message.new(2, 1) messages << Message.new(3, 2) messages << Message.new(4, 2) messages << Message.new(5, 1) messages.chunk(&:user_id).each do |user_id, records| p "#{user_id} - #{records.inspect}" end 

Выход:

 "1 - [#<struct Message id=1, user_id=1>, #<struct Message id=2, user_id=1>]" "2 - [#<struct Message id=3, user_id=2>, #<struct Message id=4, user_id=2>]" "1 - [#<struct Message id=5, user_id=1>]"