Recuperando el último registro de cada grupo - MySQL

Hay una tabla messagesque contiene datos como se muestra a continuación:

Id   Name   Other_Columns
-------------------------
1    A       A_data_1
2    A       A_data_2
3    A       A_data_3
4    B       B_data_1
5    B       B_data_2
6    C       C_data_1

Si ejecuto una consulta select * from messages group by name, obtendré el resultado como:

1    A       A_data_1
4    B       B_data_1
6    C       C_data_1

¿Qué consulta devolverá el siguiente resultado?

3    A       A_data_3
5    B       B_data_2
6    C       C_data_1

Es decir, se debe devolver el último registro de cada grupo.

Actualmente, esta es la consulta que uso:

SELECT
  *
FROM (SELECT
  *
FROM messages
ORDER BY id DESC) AS x
GROUP BY name

Pero esto parece muy ineficiente. ¿Alguna otra forma de lograr el mismo resultado?

Aug 22 '09 00:08 Vijay Dev

ACTUALIZACIÓN: 2017-03-31, la versión 5.7.5 de MySQL activó el interruptor ONLY_FULL_GROUP_BY de forma predeterminada (por lo tanto, las consultas GROUP BY no deterministas quedaron deshabilitadas). Además, actualizaron la implementación de GROUP BY y es posible que la solución ya no funcione como se esperaba, incluso con el interruptor desactivado. Hay que comprobarlo.

La solución anterior de Bill Karwin funciona bien cuando el recuento de elementos dentro de los grupos es bastante pequeño, pero el rendimiento de la consulta se vuelve malo cuando los grupos son bastante grandes, ya que la solución requiere solo n*n/2 + n/2comparaciones IS NULL.

Hice mis pruebas en una tabla InnoDB de 18684446filas con 1182grupos. La tabla contiene resultados de pruebas funcionales y tiene (test_id, request_id)como clave principal. Por lo tanto, test_ides un grupo y estaba buscando el último request_idpara cada uno test_id.

La solución de Bill ya ha estado ejecutándose durante varias horas en mi Dell e4310 y no sé cuándo terminará a pesar de que opera con un índice de cobertura (por lo tanto, using indexEXPLIQUE).

Tengo un par de otras soluciones que se basan en las mismas ideas:

• si el índice subyacente es el índice BTREE (que suele ser el caso), el (group_id, item_value)par más grande es el último valor dentro de cada uno group_id, es decir, el primero de cada uno group_idsi recorremos el índice en orden descendente;
• si leemos los valores que están cubiertos por un índice, los valores se leen en el orden del índice;
• cada índice contiene implícitamente columnas de clave principal adjuntas (es decir, la clave principal está en el índice de cobertura). En las soluciones siguientes, opero directamente con la clave principal; en su caso, solo necesitará agregar columnas de clave principal en el resultado.
• en muchos casos, es mucho más económico recopilar los identificadores de fila requeridos en el orden requerido en una subconsulta y unir el resultado de la subconsulta en el identificador. Dado que para cada fila en el resultado de la subconsulta, MySQL necesitará una única búsqueda basada en la clave principal, la subconsulta se colocará primero en la combinación y las filas se generarán en el orden de los identificadores en la subconsulta (si omitimos ORDER BY explícito para la unión)

3 formas en que MySQL usa índices es un gran artículo para comprender algunos detalles.

Solución 1

Este es increíblemente rápido, toma alrededor de 0,8 segundos en mis más de 18 millones de filas:

SELECT test_id, MAX(request_id) AS request_id
FROM testresults
GROUP BY test_id DESC;

Si desea cambiar el orden a ASC, colóquelo en una subconsulta, devuelva solo los identificadores y utilícelo como subconsulta para unirse al resto de las columnas:

SELECT test_id, request_id
FROM (
    SELECT test_id, MAX(request_id) AS request_id
    FROM testresults
    GROUP BY test_id DESC) as ids
ORDER BY test_id;

Éste tarda aproximadamente 1,2 segundos en mis datos.

Solución 2

Aquí hay otra solución que tarda unos 19 segundos en mi mesa:

SELECT test_id, request_id
FROM testresults, (SELECT @group:=NULL) as init
WHERE IF(IFNULL(@group, -1)=@group:=test_id, 0, 1)
ORDER BY test_id DESC, request_id DESC

También devuelve pruebas en orden descendente. Es mucho más lento ya que realiza un escaneo de índice completo, pero está aquí para darle una idea de cómo generar N filas máximas para cada grupo.

La desventaja de la consulta es que el caché de consultas no puede almacenar en caché su resultado.

Jan 06 '2012 11:01 newtover

Utilice su subconsulta para devolver la agrupación correcta, porque ya está a mitad de camino.

Prueba esto:

select
    a.*
from
    messages a
    inner join 
        (select name, max(id) as maxid from messages group by name) as b on
        a.id = b.maxid

Si no es así, idquieres el máximo de:

select
    a.*
from
    messages a
    inner join 
        (select name, max(other_col) as other_col 
         from messages group by name) as b on
        a.name = b.name
        and a.other_col = b.other_col

De esta manera, evita subconsultas correlacionadas y/o ordenamiento en sus subconsultas, que tienden a ser muy lentas/ineficientes.

Aug 21 '2009 17:08 Eric

Llegué a una solución diferente, que consiste en obtener los ID de la última publicación dentro de cada grupo y luego seleccionarlos de la tabla de mensajes usando el resultado de la primera consulta como argumento para una WHERE x INconstrucción:

SELECT id, name, other_columns
FROM messages
WHERE id IN (
    SELECT MAX(id)
    FROM messages
    GROUP BY name
);

No sé cómo funciona esto en comparación con otras soluciones, pero funcionó espectacularmente para mi tabla con más de 3 millones de filas. (Ejecución de 4 segundos con más de 1200 resultados)

Esto debería funcionar tanto en MySQL como en SQL Server.

Feb 20 '2012 21:02 JYelton