Récupérer le dernier enregistrement de chaque groupe en utilisant MySQL

Question

Il existe une table appelée messages qui contient des données comme celle-ci :

Id Nom Other_Columns
-----------------------
1 A A_données_1
2 A A_données_2
3A A_data_3
4 B B_data_1
5 B B_data_2
6 C C_data_1

Si j'exécute la requête select * from messages group by name, j'obtiens les résultats suivants :

1 A A_data_1
4 B B_data_1
6 C C_data_1

Quelle requête renverra les résultats suivants ?

3 A A_data_3
5 B B_data_2
6 C C_data_1

En d’autres termes, le dernier enregistrement de chaque groupe doit être renvoyé.

Actuellement, voici la requête que j'utilise :

SELECT
  *
DE (SÉLECTIONNER
  *
DE messages
ORDRE PAR id DESC) AS x
GROUPE PAR nom

Mais cela semble inefficace. Existe-t-il d'autres moyens d'obtenir le même résultat ?

Answer 1

UPD : 31/03/2017, la version MySQL 5.7.5 a le commutateur ONLY_FULL_GROUP_BY activé par défaut (par conséquent, les requêtes GROUP BY non déterministes sont désactivées). De plus, ils ont mis à jour l'implémentation GROUP BY et la solution peut ne plus fonctionner comme prévu même avec le commutateur désactivé. Une inspection est requise.

La solution de Bill Karwin fonctionne bien lorsque le nombre d'éléments au sein du groupe est petit, mais les performances de la requête se détériorent lorsque le groupe est plus grand car la solution nécessite environ n*n/2 + n/2次IS NULLcomparaisons.

Je suis inclus dans un 18684446行和1182个组的InnoDB表上进行了测试。该表包含功能测试的测试结果，并且(test_id, request_id)是主键。因此，test_id是一个组，我正在寻找每个test_id的最后一个request_id.

La solution de Bill fonctionne sur mon Dell e4310 depuis quelques heures maintenant, je ne sais pas quand elle sera complète, bien qu'elle fonctionne sur un index couvert (d'où l'EXPLIQUE affiché using index).

J'ai quelques autres solutions basées sur la même idée :

• Si l'indice sous-jacent est un indice BTREE (ce qui est le cas habituel), la première valeur de chaque group_id中的最大(group_id, item_value)对就是每个group_id的最后一个值，如果我们按降序遍历索引，则是每个group_id ;
Si l'on lit les valeurs couvertes par un index, les valeurs seront lues dans l'ordre de l'index
• ;
Chaque index contient implicitement des colonnes de clé primaire supplémentaires (c'est-à-dire que la clé primaire est dans un index de couverture). Dans la solution ci-dessous, je manipule directement la clé primaire, dans votre cas, il vous suffit d'ajouter la colonne de clé primaire dans le résultat.
Dans de nombreux cas, une approche moins coûteuse consiste à collecter les ID de ligne requis dans l'ordre souhaité dans une sous-requête et à concaténer les résultats de la sous-requête avec les ID. Puisque MySQL nécessite une seule récupération basée sur la clé primaire pour chaque ligne du résultat de la sous-requête, la sous-requête sera placée en premier dans la jointure et les lignes seront affichées par ordre d'ID dans la sous-requête (si nous omettons le ORDER BY explicite de la jointure)

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

Solution 1

Cette solution est très rapide, prenant environ 0,8 seconde pour mes plus de 18 millions de lignes de données :

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

Si vous souhaitez changer l'ordre en croissant, placez-le dans une sous-requête, renvoyez uniquement l'ID et joignez-le en tant que sous-requête avec d'autres 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;

Pour mes données, cette solution prend environ 1,2 seconde.

Solution 2

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

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 résultats des tests par ordre décroissant. C'est plus lent car il effectue une analyse complète de l'index, mais cela peut vous donner une idée de la façon de générer les N lignes maximales pour chaque groupe.

L'inconvénient de cette requête est que ses résultats ne peuvent pas être mis en cache par la requête.

Answer 2

MySQL 8.0 prend désormais en charge les Fonctions Windows, comme le sont presque toutes les implémentations SQL populaires. En utilisant cette syntaxe standard, nous pouvons écrire des requêtes max-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;

Le

Manuel MySQL démontre cette méthode ainsi que d'autres méthodes pour trouver la plus grande ligne groupée.

Voici la réponse originale que j'ai écrite à cette question en 2009 :

---

J'ai écrit la solution comme ceci :

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

Concernant les performances, selon la nature des données, une des solutions peut être meilleure. Par conséquent, vous devez tester les deux requêtes et choisir la meilleure en fonction des performances de votre base de données.

Par exemple, j'ai une copie du StackOverflow August Data Dump. Je vais l'utiliser pour le benchmarking. Il y a 1 114 357 lignes de données dans le tableau Posts. Il exécute MySQL 5.0.75 sur mon Macbook Pro 2,40 GHz.

J'écrirai une requête pour trouver les derniers messages pour un identifiant utilisateur donné (le mien).

Première utilisation de la technique d'utilisation de Eric dans une sous-requête : GROUP BY

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行结果（1分17.89秒）

Même

l'analyseEXPLAIN 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行结果（16.09秒）

Maintenant, en utilisant en utilisant LEFT JOIN ma technique produit les mêmes résultats de requête :

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行结果（0.28秒）

L'analyse montre que les deux tables peuvent utiliser leurs index : EXPLAIN

+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
| 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行结果（0.00秒）

---

Voici le DDL de ma

table : Posts

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;

---

Note aux commentateurs : si vous souhaitez exécuter un autre benchmark en utilisant une version différente de MySQL, un ensemble de données différent ou une conception de table différente, n'hésitez pas à le faire vous-même. J'ai démontré la technique ci-dessus. Le but de Stack Overflow est de vous montrer comment effectuer un travail de développement logiciel, et non de faire tout le travail à votre place.