Question Récupérer le dernier enregistrement de 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 cours une requête select * from messages group by name, Je vais obtenir le résultat comme:

1    A       A_data_1
4    B       B_data_1
6    C       C_data_1

Quelle requête retournera 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 retourné.

À l'heure actuelle, 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. D'autres façons d'atteindre le même résultat?


684
2017-08-21 17:04


origine


Réponses:


MySQL 8.0 supporte maintenant les fonctions de fenêtrage, comme presque toutes les implémentations SQL populaires. Avec cette syntaxe standard, nous pouvons écrire des requêtes plus grand-n-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;

Voici la réponse originale que j'ai écrite 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, une solution ou l'autre 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 pour votre base de données.

Par exemple, j'ai une copie du StackOverflow Août fichier vidage. Je vais utiliser cela pour l'analyse comparative. Il y a 1.114.357 lignes dans le Posts table. Cela fonctionne sur MySQL 5.0.75 sur mon Macbook Pro 2.40GHz.

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

D'abord en utilisant la technique montré 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 une 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 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 EXPLAIN l'analyse montre que les deux tables peuvent 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 pour 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;

707
2017-08-21 17:39



UPD: 2017-03-31, la version 5.7.5 de MySQL a activé le commutateur ONLY_FULL_GROUP_BY 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 pourrait 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 au sein des groupes est plutôt petit, mais la performance de la requête devient mauvaise lorsque les groupes sont assez 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 des rangées avec 1182 groupes. La table contient des tests pour les tests fonctionnels et a le (test_id, request_id) comme la clé primaire. Ainsi, test_id est un groupe et je cherchais 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ù 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 l'indice BTREE (ce qui est généralement le cas), le plus grand (group_id, item_value) paire est la dernière valeur dans chaque group_id, c'est le premier pour chaque group_id si nous marchons à travers l'index dans l'ordre décroissant;
  • si nous lisons 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é primaire ajoutées à celle-ci (c'est-à-dire que la clé primaire se trouve dans l'index de couverture). Dans les solutions ci-dessous, je travaille directement sur la clé primaire, dans ce cas, il vous suffira d'ajouter des colonnes de clé primaire dans le résultat.
  • Dans de nombreux cas, il est beaucoup moins coûteux 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. Étant donné que pour chaque ligne de MySQL résultat sous-requête aura besoin d'un seul récupération basé sur la clé primaire, la sous-requête sera mis d'abord dans la jointure et les lignes seront émis dans l'ordre des ids dans la sous-requête (si nous omettons ORDER explicite pour la jointure)

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

Solution 1

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

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

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

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;

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 dans l'ordre décroissant. Il est beaucoup plus lent car il fait un scan d'index complet mais il est là pour vous donner une idée de la manière de générer des lignes N max pour chaque groupe.

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


116
2018-01-06 11:21



Utilise ton sous-requête pour retourner le groupement correct, parce que vous êtes à mi-chemin.

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


80
2017-08-21 17:06



Je suis arrivé à une solution différente, qui consiste à obtenir les ID pour le dernier message dans chaque groupe, puis sélectionnez dans la table des messages en utilisant le résultat de la première requête comme argument pour un WHERE x IN construction:

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

Je ne sais pas comment cela fonctionne par rapport à d'autres solutions, mais cela a fonctionné de manière spectaculaire pour ma table avec 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 SQL Server.


33
2018-02-20 21:46



Solution par sous-requête violon Lien

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

Solution par condition de jointure lien de 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

Raison pour ce poste est de donner un lien de violon seulement. Le même SQL est déjà fourni dans d'autres réponses.


22
2017-12-25 08:36



Je n'ai pas encore testé avec une grande base de données, mais je pense que cela pourrait être plus rapide que de joindre des tables:

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

7
2018-03-31 14:44



Voici deux suggestions. Premièrement, si mysql supporte ROW_NUMBER (), c'est très 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;

Je suppose par "dernier" que vous voulez dire dernier dans l'ordre d'Id. Sinon, modifiez la clause ORDER BY de la fenêtre ROW_NUMBER () en conséquence. Si ROW_NUMBER () n'est pas disponible, ceci est une autre solution:

Deuxièmement, si ce n'est pas le cas, c'est souvent une bonne façon de procéder:

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 d'autres termes, sélectionnez les messages dans lesquels il n'y a pas de message d'identification ultérieure avec le même nom.


4
2017-08-21 17:26



Voici ma solution:

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

4
2018-06-08 18:49



Voici une autre façon d'obtenir le dernier enregistrement lié en utilisant GROUP_CONCAT avec ordre par et SUBSTRING_INDEX choisir l'un des enregistrements de la liste

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

Au-dessus de la requête va regrouper tous les Other_Columns qui sont dans le même Name groupe et en utilisant ORDER BY id DESC rejoindra tous les Other_Columns dans un groupe spécifique dans l'ordre décroissant avec le séparateur fourni dans mon cas, j'ai utilisé || ,en utilisant SUBSTRING_INDEX sur cette liste va choisir le premier

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



Essaye ça:

SELECT jos_categories.title AS name,
       joined .catid,
       joined .title,
       joined .introtext
FROM   jos_categories
       INNER JOIN (SELECT *
                   FROM   (SELECT `title`,
                                  catid,
                                  `created`,
                                  introtext
                           FROM   `jos_content`
                           WHERE  `sectionid` = 6
                           ORDER  BY `id` DESC) AS yes
                   GROUP  BY `yes`.`catid` DESC
                   ORDER  BY `yes`.`created` DESC) AS joined
         ON( joined.catid = jos_categories.id )  

2
2017-07-15 02:05