1164 votes

Récupération du dernier enregistrement dans chaque groupe - MySQL

Il y a une table messages qui contient des données comme indiqué ci-dessous :

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 je lance une requête select * from messages group by name j'obtiendrai le résultat suivant :

1    A       A_data_1
4    B       B_data_1
6    C       C_data_1

Quelle requête donnera le résultat suivant ?

3    A       A_data_3
5    B       B_data_2
6    C       C_data_1

C'est-à-dire que le dernier enregistrement de chaque groupe doit être renvoyé.

Actuellement, c'est la requête que j'utilise :

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

Mais cela semble très inefficace. Y a-t-il d'autres moyens d'obtenir le même résultat ?

4 votes

Voir la réponse acceptée dans stackoverflow.com/questions/1379565/ pour une solution plus efficace

1 votes

10 votes

Pourquoi ne pouvez-vous pas simplement ajouter DESC, c'est-à-dire sélectionner * à partir de messages groupe par nom DESC

1190voto

Bill Karwin Points 204877

MySQL 8.0 prend désormais en charge les fonctions de fenêtrage, comme presque toutes les implémentations SQL populaires. Avec cette syntaxe standard, nous pouvons écrire des requêtes greatest-n-per-group :

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;

Vous trouverez ci-dessous la réponse originale que j'ai rédigée pour cette question en 2009 :


J'écris la solution de cette façon :

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 ce qui concerne les performances, l'une ou l'autre solution peut être meilleure, en fonction de la nature de vos données. Vous devriez donc tester les deux requêtes et utiliser celle qui est la plus performante compte tenu de votre base de données.

Par exemple, j'ai une copie de la StackOverflow - vidage des données du mois d'août . Je vais l'utiliser comme référence. Il y a 1 114 357 lignes dans le fichier Posts table. Cela fonctionne sur MySQL 5.0.75 sur mon Macbook Pro 2.40GHz.

Je vais écrire une requête pour trouver le message le plus récent pour un ID utilisateur donné (le mien).

Première utilisation de la technique présenté sur par @Eric avec le GROUP BY dans une sous-requête :

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)

Même le EXPLAIN analyse prend plus de 16 secondes :

+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
| 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)

Maintenant, produisez le même résultat de la requête en utilisant ma technique avec 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)

Le site EXPLAIN montre que les deux tables sont capables d'utiliser leurs index :

+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
| 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)

Voici la DDL de mon Posts table :

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;

11 votes

Vraiment ? Que se passe-t-il si vous avez une tonne d'entrées ? Par exemple, si vous travaillez avec un contrôle de version interne et que vous avez une tonne de versions par fichier, le résultat de la jointure serait énorme. Avez-vous déjà comparé la méthode des sous-requêtes avec celle-ci ? Je suis assez curieux de savoir laquelle l'emporterait, mais pas assez pour ne pas vous demander d'abord.

0 votes

Merci Bill. Cela fonctionne parfaitement. Pouvez-vous fournir plus d'informations concernant les performances de cette requête par rapport à la jointure fournie par Eric ?

2 votes

J'ai fait quelques tests. Sur une petite table (~300k enregistrements, ~190k groupes, donc pas de groupes massifs ou quoi que ce soit), les requêtes sont à égalité (8 secondes chacune).

170voto

newtover Points 12301

UPD : 2017-03-31, la version 5.7.5 de MySQL a fait en sorte que le commutateur ONLY_FULL_GROUP_BY soit activé par défaut (par conséquent, les requêtes GROUP BY non déterministes ont été désactivées). De plus, ils ont mis à jour l'implémentation de GROUP BY et la solution peut ne plus fonctionner comme prévu même avec le commutateur désactivé. Il faut vérifier.

La solution de Bill Karwin ci-dessus fonctionne bien lorsque le nombre d'éléments dans les groupes est plutôt faible, mais les performances de la requête deviennent mauvaises lorsque les groupes sont plutôt grands, car la solution nécessite environ n*n/2 + n/2 de seulement IS NULL comparaisons.

J'ai fait mes tests sur une table InnoDB de 18684446 rangs avec 1182 groupes. Le tableau contient les résultats des tests fonctionnels et a l'apparence d'un tableau de bord. (test_id, request_id) comme clé primaire. Ainsi, test_id est un groupe et je recherchais le dernier request_id pour chaque test_id .

La solution de Bill fonctionne déjà depuis plusieurs heures sur mon Dell e4310 et je ne sais pas quand elle va se terminer, même si elle fonctionne sur un indice de couverture (d'où le fait que la solution de Bill n'est pas disponible). using index dans EXPLAIN).

J'ai quelques autres solutions qui sont basées sur les mêmes idées :

  • si l'indice sous-jacent est un indice BTREE (ce qui est généralement le cas), la plus grande (group_id, item_value) est la dernière valeur de chaque paire group_id qui est le premier pour chaque group_id si on parcourt l'index par ordre décroissant ;
  • si on lit les valeurs qui sont couvertes par un index, les valeurs sont lues dans l'ordre de l'index ;
  • chaque index contient implicitement des colonnes de clés primaires annexées à celui-ci (c'est-à-dire que la clé primaire est dans l'index de couverture). Dans les solutions ci-dessous, j'opère directement sur la clé primaire, dans votre cas, vous aurez juste besoin d'ajouter des colonnes de clé primaire dans le résultat.
  • Dans de nombreux cas, il est beaucoup plus économique de collecter les identifiants de ligne requis dans l'ordre requis dans une sous-requête et de joindre le résultat de la sous-requête sur l'identifiant. Puisque pour chaque ligne du résultat de la sous-requête, MySQL aura besoin d'une seule recherche basée sur la clé primaire, la sous-requête sera placée en premier dans la jointure et les lignes seront affichées dans l'ordre des identifiants de la sous-requête (si nous omettons le ORDER BY explicite pour la jointure).

3 façons dont MySQL utilise les index est un excellent article pour comprendre certains détails.

Solution 1

Celui-ci est incroyablement rapide, il prend environ 0,8 secondes sur mes 18M+ rangs :

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

Si vous voulez changer l'ordre en ASC, mettez-le dans une sous-requête, renvoyez uniquement les identifiants et utilisez-la comme sous-requête pour joindre le reste des colonnes :

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;

Celui-ci prend environ 1,2 secondes sur mes données.

Solution 2

Voici une autre solution qui prend environ 19 secondes pour ma table :

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

Il renvoie également les tests par ordre décroissant. Elle est beaucoup plus lente puisqu'elle effectue un balayage complet de l'index, mais elle est là pour vous donner une idée de la façon de produire N lignes maximum pour chaque groupe.

L'inconvénient de cette requête est que son résultat ne peut pas être mis en cache par le cache de la requête.

0 votes

Veuillez créer un lien vers un dump de vos tables afin que les gens puissent le tester sur leurs plateformes.

4 votes

La solution 1 ne peut pas fonctionner, vous ne pouvez pas sélectionner request_id sans l'avoir dans la clause group by,

3 votes

@giò, cette réponse a 5 ans. Jusqu'à la version 5.7.5 de MySQL, ONLY_FULL_GROUP_BY était désactivé par défaut et cette solution fonctionnait dès le départ. dev.mysql.com/doc/relnotes/mysql/5.7/fr/ . Maintenant, je ne suis pas sûr que la solution fonctionne toujours lorsque vous désactivez le mode, car l'implémentation du GROUP BY a été modifiée.

116voto

Eric Points 35647

Utilisez votre sous-requête pour renvoyer le bon groupe, parce que vous êtes à mi-chemin.

Essayez ça :

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 ce n'est pas le cas id dont vous voulez le maximum :

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 cette façon, vous évitez les sous-requêtes corrélées et/ou l'ordonnancement dans vos sous-requêtes, qui ont tendance à être très lentes/inefficaces.

1 votes

Notez un avertissement pour la solution avec other_col si cette colonne n'est pas unique, vous pouvez obtenir plusieurs enregistrements avec la même adresse. name s'ils sont à égalité pour max(other_col) . J'ai trouvé ce poste qui décrit une solution pour mes besoins, où j'ai besoin d'exactement un enregistrement par name .

0 votes

Dans certaines situations, vous ne pouvez utiliser que cette solution, mais pas celle qui est acceptée.

0 votes

D'après mon expérience, c'est le regroupement de tout le tableau des messages qui a tendance à être lent/inefficace ! En d'autres termes, notez que la sous-requête nécessite un balayage complet de la table, et fait un regroupement sur cela pour démarrer... à moins que votre optimiseur fasse quelque chose que le mien ne fait pas. Donc cette solution dépend fortement de la conservation de la table entière en mémoire.

78voto

JYelton Points 14014

J'ai trouvé une autre solution, qui consiste à obtenir les ID du dernier message dans chaque groupe, puis à effectuer une sélection dans la table des messages en utilisant le résultat de la première requête comme argument pour une requête de type WHERE x IN construire :

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

Je ne sais pas comment cette solution se comporte par rapport à d'autres, mais elle a fonctionné de manière spectaculaire pour ma table de plus de 3 millions de lignes. (4 secondes d'exécution avec plus de 1200 résultats)

Cela devrait fonctionner à la fois sur MySQL et sur SQL Server.

1 votes

Assurez-vous simplement que vous avez un index sur (name, id).

4 votes

C'est bien mieux que l'auto-adhésion

0 votes

J'ai appris quelque chose de vous, c'est un bon travail et cette requête est plus rapide.

48voto

Vipin Points 908

Solution par sous-requête violon Lien

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

Solution Par la condition de jointure lien vers le violon

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 raison de ce post est de donner le lien du violon seulement. Le même SQL est déjà fourni dans d'autres réponses.

1 votes

@AlexanderSuraphel mysql5.5 n'est pas disponible dans fiddle maintenant, le lien fiddle a été créé en utilisant cela. Aujourd'hui, fiddle supporte mysql5.6, j'ai changé la base de données pour mysql 5.6 et je suis capable de construire le schéma et d'exécuter le sql.

0 votes

Y a-t-il une différence entre les deux approches en termes de vitesse, de performances ou d'autres facteurs ?

Prograide.com

Prograide est une communauté de développeurs qui cherche à élargir la connaissance de la programmation au-delà de l'anglais.
Pour cela nous avons les plus grands doutes résolus en français et vous pouvez aussi poser vos propres questions ou résoudre celles des autres.

Powered by:

X