Abrufen des letzten Datensatzes in jeder Gruppe - MySQL

Question

Es gibt eine Tabelle messages die Daten wie unten dargestellt enthält:

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

Wenn ich eine Abfrage ausführe select * from messages group by name erhalte ich das folgende Ergebnis:

1    A       A_data_1
4    B       B_data_1
6    C       C_data_1

Welche Abfrage liefert das folgende Ergebnis?

3    A       A_data_3
5    B       B_data_2
6    C       C_data_1

Das heißt, der letzte Datensatz in jeder Gruppe sollte zurückgegeben werden.

Zurzeit verwende ich diese Abfrage:

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

Dies erscheint jedoch äußerst ineffizient. Gibt es andere Möglichkeiten, um das gleiche Ergebnis zu erzielen?

Answer 1

MySQL 8.0 unterstützt jetzt Windowing-Funktionen wie fast alle gängigen SQL-Implementierungen. Mit dieser Standardsyntax können wir greatest-n-per-group-Abfragen schreiben:

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;

Diese und andere Ansätze zum Auffinden von gruppenweise maximale Zeilen werden im MySQL-Handbuch erläutert.

Im Folgenden finden Sie die ursprüngliche Antwort, die ich 2009 auf diese Frage geschrieben habe:

---

Ich schreibe die Lösung auf diese Weise:

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

In Bezug auf die Leistung kann die eine oder andere Lösung besser sein, je nach der Art Ihrer Daten. Sie sollten also beide Abfragen testen und diejenige verwenden, die in Bezug auf Ihre Datenbank die bessere Leistung bietet.

Ich habe zum Beispiel eine Kopie der StackOverflow August-Datenabzug . Ich verwende das zum Benchmarking. Es gibt 1.114.357 Zeilen in der Posts Tabelle. Dies läuft unter MySQL 5.0.75 auf meinem Macbook Pro 2.40GHz.

Ich schreibe eine Abfrage, um den neuesten Beitrag für eine bestimmte Benutzer-ID (meine) zu finden.

Erste Anwendung der Technik siehe von @Eric mit dem GROUP BY in einer Unterabfrage:

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)

Auch die EXPLAIN Analyse dauert über 16 Sekunden:

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

Erzeugen Sie nun das gleiche Abfrageergebnis mit meine Technik mit 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)

El EXPLAIN Analyse zeigt, dass beide Tabellen in der Lage sind, ihre Indizes zu verwenden:

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

---

Hier ist die DDL für meine Posts Tisch:

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;

---

Hinweis an die Kommentatoren: Wenn Sie einen weiteren Benchmark mit einer anderen Version von MySQL, einem anderen Datensatz oder einem anderen Tabellendesign durchführen möchten, können Sie das gerne selbst tun. Ich habe die Technik oben gezeigt. Stack Overflow ist hier, um Ihnen zu zeigen, wie Sie Software entwickeln können, nicht um die ganze Arbeit für Sie zu erledigen.

Answer 2

Verwenden Sie Ihr Unterabfrage um die richtige Gruppierung zu finden, denn Sie haben schon die Hälfte geschafft.

Versuchen Sie dies:

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

Wenn es nicht id die Sie maximal haben wollen:

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

Auf diese Weise vermeiden Sie korrelierte Unterabfragen und/oder Bestellungen in Ihren Unterabfragen, die in der Regel sehr langsam/ineffizient sind.

Answer 3

Ich habe eine andere Lösung gefunden, die darin besteht, die IDs für den letzten Beitrag in jeder Gruppe abzurufen und dann aus der Nachrichtentabelle auszuwählen, wobei das Ergebnis der ersten Abfrage als Argument für eine WHERE x IN konstruieren:

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

Ich weiß nicht, wie das im Vergleich zu anderen Lösungen abschneidet, aber bei meiner Tabelle mit mehr als 3 Millionen Zeilen hat es hervorragend funktioniert. (4 Sekunden Ausführung mit 1200+ Ergebnissen)

Dies sollte sowohl mit MySQL als auch mit SQL Server funktionieren.

Answer 4

Lösung durch Unterabfrage fiddle Link

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

Lösung Nach Join-Bedingung Fiddle-Link

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

Der Grund für diesen Beitrag ist, dass ich nur einen Fiddle-Link angeben möchte. Das gleiche SQL ist bereits in anderen Antworten enthalten.

Answer 5

Sehen wir uns an, wie Sie MySQL verwenden können, um den letzten Datensatz in einer Gruppe von Datensätzen zu erhalten. Zum Beispiel, wenn Sie diese Ergebnismenge von Beiträgen haben.

id category_id post_title

1 1 Title 1

2 1 Title 2

3 1 Title 3

4 2 Title 4

5 2 Title 5

6 3 Title 6

Ich möchte den letzten Beitrag in jeder Kategorie, d. h. Titel 3, Titel 5 und Titel 6, abrufen können. Um die Beiträge nach Kategorie zu erhalten, verwenden Sie die MySQL-Tastatur Group By.

select * from posts group by category_id

Aber die Ergebnisse dieser Abfrage sind.

id category_id post_title

1 1 Title 1

4 2 Title 4

6 3 Title 6

Die Gruppierung nach gibt immer den ersten Datensatz der Gruppe in der Ergebnismenge zurück.

SELECT id, category_id, post_title FROM posts WHERE id IN ( SELECT MAX(id) FROM posts GROUP BY category_id );

Dadurch werden die Beiträge mit den höchsten IDs in jeder Gruppe zurückgegeben.

id category_id post_title

3 1 Title 3

5 2 Title 5

6 3 Title 6

Referenz Hier klicken