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

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

Answer 2

Hier sind zwei Vorschläge. Erstens, wenn mysql ROW_NUMBER() unterstützt, ist es sehr einfach:

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;

Ich nehme an, dass Sie mit "zuletzt" den letzten in der Id-Reihenfolge meinen. Wenn nicht, ändern Sie die ORDER BY-Klausel des Fensters ROW_NUMBER() entsprechend. Wenn ROW_NUMBER() nicht verfügbar ist, ist dies eine andere Lösung:

Zweitens, wenn dies nicht der Fall ist, ist dies oft ein guter Weg, um weiterzumachen:

SELECT
  Id, Name, OtherColumns
FROM messages
WHERE NOT EXISTS (
  SELECT * FROM messages as M2
  WHERE M2.Name = messages.Name
  AND M2.Id > messages.Id
)

Mit anderen Worten: Wählen Sie Nachrichten aus, für die es keine spätere Nachricht mit demselben Namen gibt.

Answer 3

Natürlich gibt es viele verschiedene Möglichkeiten, die gleichen Ergebnisse zu erhalten. Ihre Frage scheint zu sein, was eine effiziente Möglichkeit ist, die letzten Ergebnisse in jeder Gruppe in MySQL zu erhalten. Wenn Sie mit großen Datenmengen arbeiten und davon ausgehen, dass Sie InnoDB selbst mit den neuesten Versionen von MySQL (wie 5.7.21 und 8.0.4-rc) verwenden, gibt es möglicherweise keine effiziente Methode, dies zu tun.

Manchmal müssen wir dies bei Tabellen mit mehr als 60 Millionen Zeilen tun.

Für diese Beispiele verwende ich Daten mit nur etwa 1,5 Millionen Zeilen, bei denen die Abfragen Ergebnisse für alle Gruppen in den Daten finden müssen. In unseren tatsächlichen Fällen müssten wir oft Daten von etwa 2.000 Gruppen zurückgeben (was hypothetisch gesehen nicht erfordert, sehr viele Daten zu untersuchen).

Ich werde die folgenden Tabellen verwenden:

CREATE TABLE temperature(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
  groupID INT UNSIGNED NOT NULL, 
  recordedTimestamp TIMESTAMP NOT NULL, 
  recordedValue INT NOT NULL,
  INDEX groupIndex(groupID, recordedTimestamp), 
  PRIMARY KEY (id)
);

CREATE TEMPORARY TABLE selected_group(id INT UNSIGNED NOT NULL, PRIMARY KEY(id)); 

Die Tabelle temperature wird mit etwa 1,5 Millionen zufälligen Datensätzen und 100 verschiedenen Gruppen gefüllt. Die selected_group wird mit diesen 100 Gruppen befüllt (in unserem Fall wären das normalerweise weniger als 20 % für alle Gruppen).

Da es sich um Zufallsdaten handelt, können mehrere Zeilen denselben Zeitstempel haben. Was wir wollen, ist eine Liste aller ausgewählten Gruppen in der Reihenfolge der groupID mit dem letzten recordedTimestamp für jede Gruppe zu erhalten, und wenn die gleiche Gruppe mehr als eine übereinstimmende Zeile wie das dann die letzte übereinstimmende id dieser Zeilen hat.

Wenn MySQL hypothetisch eine last()-Funktion hätte, die Werte aus der letzten Zeile in einer speziellen ORDER BY-Klausel zurückliefert, könnten wir das einfach tun:

SELECT 
  last(t1.id) AS id, 
  t1.groupID, 
  last(t1.recordedTimestamp) AS recordedTimestamp, 
  last(t1.recordedValue) AS recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON t1.groupID = g.id
ORDER BY t1.recordedTimestamp, t1.id
GROUP BY t1.groupID;

die in diesem Fall nur ein paar 100 Zeilen untersuchen müsste, da sie keine der normalen GROUP BY-Funktionen verwendet. Dies würde in 0 Sekunden ausgeführt werden und wäre daher äußerst effizient. Beachten Sie, dass in MySQL normalerweise eine ORDER BY-Klausel auf die GROUP BY-Klausel folgt. Diese ORDER BY-Klausel wird jedoch verwendet, um die ORDNUNG für die last()-Funktion zu bestimmen; wenn sie nach der GROUP BY-Klausel stünde, würde sie die GRUPPEN ordnen. Wenn keine GROUP BY-Klausel vorhanden ist, werden die letzten Werte in allen zurückgegebenen Zeilen gleich sein.

MySQL verfügt jedoch nicht über diese Möglichkeit, so dass wir uns verschiedene Ideen ansehen, was es hat, und beweisen, dass keine davon effizient ist.

Beispiel 1

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON t1.id = (
  SELECT t2.id
  FROM temperature t2 
  WHERE t2.groupID = g.id
  ORDER BY t2.recordedTimestamp DESC, t2.id DESC
  LIMIT 1
);

Dies untersuchte 3.009.254 Zeilen und dauerte ~0,859 Sekunden auf 5.7.21 und etwas länger auf 8.0.4-rc

Beispiel 2

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM temperature t1
INNER JOIN ( 
  SELECT max(t2.id) AS id   
  FROM temperature t2
  INNER JOIN (
    SELECT t3.groupID, max(t3.recordedTimestamp) AS recordedTimestamp
    FROM selected_group g
    INNER JOIN temperature t3 ON t3.groupID = g.id
    GROUP BY t3.groupID
  ) t4 ON t4.groupID = t2.groupID AND t4.recordedTimestamp = t2.recordedTimestamp
  GROUP BY t2.groupID
) t5 ON t5.id = t1.id;

Dies untersuchte 1.505.331 Zeilen und dauerte ~1,25 Sekunden auf 5.7.21 und etwas länger auf 8.0.4-rc

Beispiel 3

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM temperature t1
WHERE t1.id IN ( 
  SELECT max(t2.id) AS id   
  FROM temperature t2
  INNER JOIN (
    SELECT t3.groupID, max(t3.recordedTimestamp) AS recordedTimestamp
    FROM selected_group g
    INNER JOIN temperature t3 ON t3.groupID = g.id
    GROUP BY t3.groupID
  ) t4 ON t4.groupID = t2.groupID AND t4.recordedTimestamp = t2.recordedTimestamp
  GROUP BY t2.groupID
)
ORDER BY t1.groupID;

Dies untersuchte 3.009.685 Zeilen und dauerte ~1,95 Sekunden auf 5.7.21 und etwas länger auf 8.0.4-rc

Beispiel 4

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON t1.id = (
  SELECT max(t2.id)
  FROM temperature t2 
  WHERE t2.groupID = g.id AND t2.recordedTimestamp = (
      SELECT max(t3.recordedTimestamp)
      FROM temperature t3 
      WHERE t3.groupID = g.id
    )
);

Dies untersuchte 6.137.810 Zeilen und dauerte ~2,2 Sekunden auf 5.7.21 und etwas länger auf 8.0.4-rc

Beispiel 5

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM (
  SELECT 
    t2.id, 
    t2.groupID, 
    t2.recordedTimestamp, 
    t2.recordedValue, 
    row_number() OVER (
      PARTITION BY t2.groupID ORDER BY t2.recordedTimestamp DESC, t2.id DESC
    ) AS rowNumber
  FROM selected_group g 
  INNER JOIN temperature t2 ON t2.groupID = g.id
) t1 WHERE t1.rowNumber = 1;

Dies untersuchte 6.017.808 Zeilen und dauerte ~4,2 Sekunden auf 8.0.4-rc

Beispiel 6

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM (
  SELECT 
    last_value(t2.id) OVER w AS id, 
    t2.groupID, 
    last_value(t2.recordedTimestamp) OVER w AS recordedTimestamp, 
    last_value(t2.recordedValue) OVER w AS recordedValue
  FROM selected_group g
  INNER JOIN temperature t2 ON t2.groupID = g.id
  WINDOW w AS (
    PARTITION BY t2.groupID 
    ORDER BY t2.recordedTimestamp, t2.id 
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  )
) t1
GROUP BY t1.groupID;

Dies untersuchte 6.017.908 Zeilen und dauerte ~17,5 Sekunden auf 8.0.4-rc

Beispiel 7

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM selected_group g
INNER JOIN temperature t1 ON t1.groupID = g.id
LEFT JOIN temperature t2 
  ON t2.groupID = g.id 
  AND (
    t2.recordedTimestamp > t1.recordedTimestamp 
    OR (t2.recordedTimestamp = t1.recordedTimestamp AND t2.id > t1.id)
  )
WHERE t2.id IS NULL
ORDER BY t1.groupID;

Das hier hat ewig gedauert, also musste ich es beenden.

Answer 4

Hier ist eine weitere Möglichkeit, den letzten Bezugsdatensatz zu erhalten, indem Sie GROUP_CONCAT mit Bestellung durch und SUBSTRING_INDEX um einen der Datensätze aus der Liste auszuwählen

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

Die obige Abfrage gruppiert alle Other_Columns die sich in der gleichen Name Gruppe und mit ORDER BY id DESC wird sich mit allen Other_Columns in einer bestimmten Gruppe in absteigender Reihenfolge mit dem angegebenen Trennzeichen in meinem Fall habe ich verwendet || mit SUBSTRING_INDEX über diese Liste wird die erste ausgewählt

Fiedel-Demo

Answer 5

Hallo @Vijay Dev, wenn Ihre Tabelle Nachrichten enthält Id die Autoinkrement-Primärschlüssel ist, um den neuesten Datensatz auf der Grundlage des Primärschlüssels abzurufen, sollte Ihre Abfrage wie folgt lauten:

SELECT m1.* FROM messages m1 INNER JOIN (SELECT max(Id) as lastmsgId FROM messages GROUP BY Name) m2 ON m1.Id=m2.lastmsgId