Recuperando el último registro de cada grupo – MySQL

Sé libre de divulgar nuestra página y códigos en tus redes, apóyanos para hacer crecer esta comunidad.

Solución:

MySQL 8.0 ahora admite funciones de ventanas, como casi todas las implementaciones populares de SQL. Con esta sintaxis estándar, podemos escribir consultas de n por grupo más grandes:

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 muestra 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 tenga mejor 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 ejecuta en MySQL 5.0.75 en mi Macbook Pro 2.40GHz.

Escribiré una consulta para encontrar la publicación más reciente para un ID de usuario determinado (el mío).

Primero usando la técnica mostrada 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 el análisis tarda más de 16 segundos:

+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
| id | select_type | table      | type   | possible_keys              | key         | key_len | ref          | rows    | Extra       |
+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
|  1 | PRIMARY     |  | 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 produzca 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;

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

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 los resultados de las pruebas funcionales y tiene la (test_id, request_id) como el principal key. Por lo tanto, test_id es un grupo y estaba buscando el ultimo request_id para cada test_id.

La solución de Bill ya se ha estado ejecutando durante varias horas en mi dell e4310 y no sé cuándo terminará a pesar de que opera en un índice de cobertura (por lo tanto using index en EXPLICAR).

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 mayor (group_id, item_value) par es el último valor dentro de cada group_id, ese es el primero de cada group_id si 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 primario key columnas adjuntas a eso (que es el principal key está en el índice de cobertura). En las soluciones a continuación, opero directamente en el primario. key, en su caso, solo tendrá que agregar key columnas en el resultado.
• en muchos casos, es mucho más barato 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 búsqueda única basada en key, 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 combinación)

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

Solucion 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;

Este toma alrededor de 1,2 segundos en mis datos.

Solucion 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)[email protected]:=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 la caché de consultas no puede almacenar en caché su resultado.

Use su subconsulta para devolver la agrupación correcta, porque 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 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 manera, evita las subconsultas correlacionadas y / o el orden en sus subconsultas, que tienden a ser muy lentas / ineficientes.

Eres capaz de añadir valor a nuestro contenido informacional cooperando tu veteranía en las crónicas.

---

---

Utiliza Nuestro Buscador