una - que es un atributo en sql
Grupo por repetición de atributo (4)
La cláusula GROUP BY colapsará la respuesta en 2 registros: uno con user_id 1 y otro con user_id 2 sin importar la cláusula ORDER BY por lo que te recomendamos que envíes solo el 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
Básicamente tengo una tabla de messages , con el campo user_id que identifica a un usuario que creó el mensaje.
Cuando visualizo una conversación (conjunto de mensajes) entre dos usuarios, quiero poder agrupar los mensajes por user_id , pero de una manera complicada:
Digamos que hay algunos mensajes (ordenados por 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
Quiero obtener 3 grupos de mensajes en el siguiente orden: [1,2], [3,4], [5]
Debe agrupar por * user_id * hasta que vea una diferente y luego agrupe por esa.
Estoy usando PostgreSQL y me complacería usar algo específico para él, cualquiera que sea el mejor rendimiento.
Pruebe algo como esto:
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;
La expresion:
row_number() OVER (ORDER BY created_at)-
row_number() OVER (PARTITION BY user_id ORDER BY created_at) conv_id
Le dará una identificación especial para cada grupo de mensajes (este conv_id se puede repetir para otro user_id , pero user_id, conv_id le dará todos los grupos de mensajes distintos)
Mi SQLFiddle con el ejemplo.
Detalles: row_number() , OVER (PARTITION BY ... ORDER BY ...)
Puedes usar chunk :
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
La salida:
"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>]"
SQL apropiado
@Igor presenta una buena técnica de SQL puro con funciones de ventana.
Sin embargo:
Quiero obtener 3 grupos de mensajes en el siguiente orden: [1,2], [3,4], [5]
Para obtener el pedido solicitado, agregue 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);
La adición apenas justificaría otra respuesta. El problema más importante es esto:
Más rápido con PL / pgSQL
Estoy usando PostgreSQL y me complacería usar algo específico para él, cualquiera que sea el mejor rendimiento .
El SQL puro es agradable y brillante, pero una función procesal del lado del servidor es mucho más rápida para esta tarea. Si bien procesar las filas procesalmente es generalmente más lento , plpgsql gana esta competencia a lo grande, ya que puede hacerlo con una única exploración de tabla y una sola operación 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;
Llamada:
SELECT * FROM f_msg_groups();
Benchmark y enlaces
Ejecuté una prueba rápida con EXPLAIN ANALYZE en una tabla similar de la vida real con 60k filas (ejecute varias veces, elija el resultado más rápido para excluir los efectos de cambio):
SQL:
Tiempo de ejecución total: 1009.549 ms
Pl / pgSQL:
Tiempo de ejecución total: 336.971 ms
También considere estas preguntas estrechamente relacionadas: