Pregunta Recuperando el último registro en cada grupo - MySQL


Hay una mesa messages que 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 arrojará 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.

En la actualidad, esta es la consulta que uso:

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

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


684
2017-08-21 17:04


origen


Respuestas:


MySQL 8.0 ahora es compatible con funciones de ventana, como casi todas las implementaciones populares de SQL. Con esta sintaxis estándar, podemos escribir las consultas de mayor n-por-grupo:

WITH ranked_messages AS (
  SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn
  FROM messages AS m
)
SELECT * FROM ranked_messages WHERE rn = 1;

A continuación se encuentra la respuesta original que escribí para esta pregunta en 2009:


Escribo la solución de esta manera:

SELECT m1.*
FROM messages m1 LEFT JOIN messages m2
 ON (m1.name = m2.name AND m1.id < m2.id)
WHERE m2.id IS NULL;

En cuanto al rendimiento, una solución u otra puede ser mejor, dependiendo de la naturaleza de sus datos. Por lo tanto, debe probar ambas consultas y usar la que es mejor en rendimiento dada su base de datos.

Por ejemplo, tengo una copia del Volcado de datos de agosto de StackOverflow. Lo usaré para la evaluación comparativa. Hay 1,114,357 filas en el Posts mesa. Esto se está ejecutando MySQL 5.0.75 en mi Macbook Pro 2.40GHz.

Escribiré una consulta para encontrar la publicación más reciente para una ID de usuario determinada (mía).

Primero usando la técnica mostrado por @Eric con el GROUP BY en una subconsulta:

SELECT p1.postid
FROM Posts p1
INNER JOIN (SELECT pi.owneruserid, MAX(pi.postid) AS maxpostid
            FROM Posts pi GROUP BY pi.owneruserid) p2
  ON (p1.postid = p2.maxpostid)
WHERE p1.owneruserid = 20860;

1 row in set (1 min 17.89 sec)

Incluso el EXPLAIN análisis toma más de 16 segundos:

+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
| id | select_type | table      | type   | possible_keys              | key         | key_len | ref          | rows    | Extra       |
+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                       | NULL        | NULL    | NULL         |   76756 |             | 
|  1 | PRIMARY     | p1         | eq_ref | PRIMARY,PostId,OwnerUserId | PRIMARY     | 8       | p2.maxpostid |       1 | Using where | 
|  2 | DERIVED     | pi         | index  | NULL                       | OwnerUserId | 8       | NULL         | 1151268 | Using index | 
+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
3 rows in set (16.09 sec)

Ahora produce el mismo resultado de consulta usando mi técnica con LEFT JOIN:

SELECT p1.postid
FROM Posts p1 LEFT JOIN posts p2
  ON (p1.owneruserid = p2.owneruserid AND p1.postid < p2.postid)
WHERE p2.postid IS NULL AND p1.owneruserid = 20860;

1 row in set (0.28 sec)

los EXPLAIN el análisis muestra que ambas tablas pueden usar sus índices:

+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
| id | select_type | table | type | possible_keys              | key         | key_len | ref   | rows | Extra                                |
+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
|  1 | SIMPLE      | p1    | ref  | OwnerUserId                | OwnerUserId | 8       | const | 1384 | Using index                          | 
|  1 | SIMPLE      | p2    | ref  | PRIMARY,PostId,OwnerUserId | OwnerUserId | 8       | const | 1384 | Using where; Using index; Not exists | 
+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
2 rows in set (0.00 sec)

Aquí está el DDL para mi Posts mesa:

CREATE TABLE `posts` (
  `PostId` bigint(20) unsigned NOT NULL auto_increment,
  `PostTypeId` bigint(20) unsigned NOT NULL,
  `AcceptedAnswerId` bigint(20) unsigned default NULL,
  `ParentId` bigint(20) unsigned default NULL,
  `CreationDate` datetime NOT NULL,
  `Score` int(11) NOT NULL default '0',
  `ViewCount` int(11) NOT NULL default '0',
  `Body` text NOT NULL,
  `OwnerUserId` bigint(20) unsigned NOT NULL,
  `OwnerDisplayName` varchar(40) default NULL,
  `LastEditorUserId` bigint(20) unsigned default NULL,
  `LastEditDate` datetime default NULL,
  `LastActivityDate` datetime default NULL,
  `Title` varchar(250) NOT NULL default '',
  `Tags` varchar(150) NOT NULL default '',
  `AnswerCount` int(11) NOT NULL default '0',
  `CommentCount` int(11) NOT NULL default '0',
  `FavoriteCount` int(11) NOT NULL default '0',
  `ClosedDate` datetime default NULL,
  PRIMARY KEY  (`PostId`),
  UNIQUE KEY `PostId` (`PostId`),
  KEY `PostTypeId` (`PostTypeId`),
  KEY `AcceptedAnswerId` (`AcceptedAnswerId`),
  KEY `OwnerUserId` (`OwnerUserId`),
  KEY `LastEditorUserId` (`LastEditorUserId`),
  KEY `ParentId` (`ParentId`),
  CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`PostTypeId`) REFERENCES `posttypes` (`PostTypeId`)
) ENGINE=InnoDB;

707
2017-08-21 17:39



UPD: 2017-03-31, la versión 5.7.5 de MySQL hizo que el switch ONLY_FULL_GROUP_BY fuera habilitado por defecto (por lo tanto, las consultas GROUP BY no deterministas se desactivaron). Además, actualizaron la implementación GROUP BY y la solución podría no funcionar como se esperaba, incluso con el interruptor desactivado. Uno necesita verificar

La solución de Bill Karwin anterior 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 aproximadamente n*n/2 + n/2 de sólo IS NULL comparaciones

Hice mis pruebas en una tabla InnoDB de 18684446 filas con 1182 grupos. La tabla contiene resultados de prueba para pruebas funcionales y tiene el (test_id, request_id) como la clave principal. Así, test_id es un grupo y estaba buscando el último request_id para cada test_id.

La solución de Bill ya ha estado funcionando durante varias horas en mi Dell e4310 y no sé cuándo va a terminar aunque funcione en un índice de cobertura (por lo tanto, using index en EXPLAIN).

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

  • si el índice subyacente es el índice BTREE (que generalmente es el caso), el índice más grande (group_id, item_value) par es el último valor dentro de cada group_id, ese es el primero para cada group_id si caminamos por 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 anexadas a eso (que es la clave primaria en el índice de cobertura). En las soluciones a continuación, opero directamente en la clave primaria, en su caso, solo tendrá que 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 ID. Dado que para cada fila en el resultado de la subconsulta, MySQL necesitará una única obtención basada en la clave principal, la subconsulta se colocará primero en la combinación y las filas se mostrarán en el orden de las identificaciones en la subconsulta (si omitimos ORDER BY para la unión)

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

Solución 1

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

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

Si desea cambiar el orden a ASC, póngalo en una subconsulta, devuelva los ids únicamente y úselos como subconsulta para unir el resto de las columnas:

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

Este toma aproximadamente 1,2 segundos en mis datos.

Solución 2

Aquí hay otra solución que toma alrededor de 19 segundos para 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

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

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


116
2018-01-06 11:21



Usa tu subconsulta para devolver la agrupación correcta, porque estás 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 id quieres 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 forma, evita las subconsultas correlacionadas y / o el orden en sus subconsultas, que tienden a ser muy lentas / ineficientes.


80
2017-08-21 17:06



Llegué a una solución diferente, que es obtener los ID para la última publicación dentro de cada grupo, luego seleccionar de la tabla de mensajes usando el resultado de la primera consulta como argumento para un WHERE x IN construir:

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 algunas de las otras soluciones, pero funcionó de manera espectacular para mi mesa con más de 3 millones de filas. (4 segundos de ejecución con más de 1200 resultados)

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


33
2018-02-20 21:46



Solución por sub consulta violín Enlace

select * from messages where id in
(select max(id) from messages group by Name)

Solución por condición de unión violín

select m1.* from messages m1 
left outer join messages m2 
on ( m1.id<m2.id and m1.name=m2.name )
where m2.id is null

La razón de esta publicación es para dar el enlace violín solamente. El mismo SQL ya se proporciona en otras respuestas.


22
2017-12-25 08:36



Todavía no he probado con grandes bases de datos, pero creo que esto podría ser más rápido que unir mesas:

SELECT *, Max(Id) FROM messages GROUP BY Name

7
2018-03-31 14:44



Aquí hay dos sugerencias. Primero, si mysql admite ROW_NUMBER (), es muy simple:

WITH Ranked AS (
  SELECT Id, Name, OtherColumns,
    ROW_NUMBER() OVER (
      PARTITION BY Name
      ORDER BY Id DESC
    ) AS rk
  FROM messages
)
  SELECT Id, Name, OtherColumns
  FROM messages
  WHERE rk = 1;

Supongo que por "último" te refieres al último en el orden Id. De lo contrario, cambie la cláusula ORDER BY de la ventana ROW_NUMBER () en consecuencia. Si ROW_NUMBER () no está disponible, esta es otra solución:

En segundo lugar, si no es así, esta es una buena manera de proceder:

SELECT
  Id, Name, OtherColumns
FROM messages
WHERE NOT EXISTS (
  SELECT * FROM messages as M2
  WHERE M2.Name = messages.Name
  AND M2.Id > messages.Id
)

En otras palabras, seleccione mensajes donde no haya un mensaje de Id posterior con el mismo nombre.


4
2017-08-21 17:26



Aquí está mi solución:

SELECT 
  DISTINCT NAME,
  MAX(MESSAGES) OVER(PARTITION BY NAME) MESSAGES 
FROM MESSAGE;

4
2018-06-08 18:49



Aquí hay otra forma de obtener el último registro relacionado usando GROUP_CONCAT con orden por y SUBSTRING_INDEX para elegir uno de los registros de la lista

SELECT 
  `Id`,
  `Name`,
  SUBSTRING_INDEX(
    GROUP_CONCAT(
      `Other_Columns` 
      ORDER BY `Id` DESC 
      SEPARATOR '||'
    ),
    '||',
    1
  ) Other_Columns 
FROM
  messages 
GROUP BY `Name` 

Por encima de la consulta se agruparán todas las Other_Columns que están en el mismo Name grupo y uso ORDER BY id DESC se unirá a todos los Other_Columns en un grupo específico en orden descendente con el separador provisto en mi caso he usado || ,utilizando SUBSTRING_INDEX sobre esta lista elegirá el primero

Fiddle Demo


3
2018-03-29 14:51



SELECT 
  column1,
  column2 
FROM
  table_name 
WHERE id IN 
  (SELECT 
    MAX(id) 
  FROM
    table_name 
  GROUP BY column1) 
ORDER BY column1 ;

3
2018-04-11 06:55