MySQL : obtenez la dernière entrée dans chaque groupe

Question

Il existe un tableau messages contenant des données comme celle-ci :

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 j'exécute la requête select * from messages group by name, les résultats que j'obtiendrai sont :

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

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

Actuellement, voici 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. Existe-t-il d'autres moyens d'obtenir le même résultat ?

Answer 1

UPD : 31/03/2017, version 5.7.5 MySQL active le commutateur ONLY_FULL_GROUP_BY par défaut (les requêtes GROUP BY non déterministes sont donc désactivées). De plus, ils ont mis à jour l'implémentation GROUP BY et la solution peut ne pas fonctionner comme prévu même avec le commutateur désactivé. Il faut le vérifier.

La solution ci-dessus de Bill Karwin fonctionne bien lorsque le nombre d'éléments au sein du groupe est assez petit, mais lorsque le groupe est assez grand, les performances de la requête deviennent médiocres car la solution nécessite environ n*n/2 + n/2 Comparez uniquement EST NULLIS NULL.

J'ai testé sur une table InnoDB contenant 1868444618684446 行和 1182 组的 InnoDB 表上进行了测试。该表包含功能测试的测试结果，并以 (test_id, request_id) 作为主键。因此，test_id 是一个组，我正在为每个test_id 搜索最后一个request_id lignes et 1182

groupes. Ce tableau contient les résultats des tests fonctionnels et a (test_id, request_id)

comme clé primaire. Donc, test_id

est un groupe et je recherche le dernier request_id

pour chaque test_id

.

• La solution de Bill fonctionne sur mon Dell e4310 depuis quelques heures maintenant, et bien qu'elle fonctionne sur un index de couverture (d'où l'utilisation de l'index dans EXPLAIN), je ne sais pas quand elle sera terminée. (group_id, item_value) 对是每个 group_id 中的最后一个值，即如果我们按降序遍历索引，则为每个 group_id
J'ai quelques autres solutions basées sur la même idée :
• Si l'index sous-jacent est un index BTREE (ce qui est généralement le cas), alors la plus grande paire (group_id, item_value)
est la dernière valeur de chaque group_id
• , c'est-à-dire si nous parcourons l'index dans par ordre décroissant, Ensuite, c'est le premier de chaque group_id
;

Si on lit la valeur couverte par l'index, les valeurs sont lues dans l'ordre de l'index ; Chaque index contient implicitement les colonnes de clé primaire attachées à cet index (c'est-à-dire que la clé primaire est dans un index de couverture). Dans la solution ci-dessous j'opère directement sur la clé primaire, dans votre cas il vous suffit d'ajouter la colonne de clé primaire au résultat.

Dans de nombreux cas, il est beaucoup moins coûteux de collecter les ID de ligne requis dans l'ordre requis dans une sous-requête et de concaténer les résultats de la sous-requête aux ID. Puisque pour chaque ligne du résultat de la sous-requête, MySQL devra effectuer une récupération 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 de l'identifiant dans la sous-requête (si nous omettez le ORDER BY explicite pour la jointure)

3 façons dont MySQL utilise les index

est un excellent article pour vous aider à comprendre certains détails.

Solution 1

C'est incroyablement rapide, prenant environ 0,8 seconde sur mes plus de 18 millions de lignes :

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

Si vous souhaitez changer l'ordre en ASC, placez-le dans une sous-requête qui renvoie uniquement les identifiants et utilisez-le comme sous-requête pour rejoindre 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;

Cela prend environ 1,2 seconde pour mes données. 🎜 🎜🎜Solution 2🎜🎜 🎜Voici une autre solution qui a pris environ 19 secondes pour ma montre : 🎜

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. C'est beaucoup plus lent car il effectue une analyse complète de l'index, mais cela vous donne 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 le cache des requêtes ne peut pas mettre en cache ses résultats.

Answer 2

MySQL 8.0 prend désormais en charge les fonctions de fenêtre, comme presque toutes les implémentations SQL populaires. En utilisant cette syntaxe standard, nous pouvons écrire jusqu'à n requêtes par groupe :

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;

Cette méthode et d'autres méthodes pour trouver le nombre maximum de lignes groupées sont décrites dans le manuel MySQL.

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, une solution peut être meilleure selon la nature des données. Par conséquent, vous devez tester les deux requêtes et utiliser celle qui offre les meilleures performances en fonction de votre base de données.

Par exemple, j'ai une copie du Dump des données d'août de StackOverflow. Je vais l'utiliser à des fins d'analyse comparative. Posts Il y a 1 114 357 lignes dans le tableau. Il fonctionne sur 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).

Utilisez d'abord la technique montrée < /a> par @Eric en utilisant 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 EXPLAINl'analyse< /a> prend plus de 16 secondes :

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

Utilisez maintenant Mes conseils d'utilisation 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)

EXPLAIN L'analyse 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 le DDL de ma 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;

---

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, veuillez le faire vous-même. J'ai démontré la technique ci-dessus. Stack Overflow est là pour vous montrer comment effectuer le travail de développement logiciel, et non pour faire tout le travail à votre place.