Abrufen des letzten Datensatzes in jeder Gruppe - MySQL


Answers

UPD: 2017-03-31, die Version 5.7.5 von MySQL hat den ONLY_FULL_GROUP_BY-Schalter standardmäßig aktiviert (daher wurden nicht-deterministische GROUP BY-Abfragen deaktiviert). Darüber hinaus haben sie die GROUP BY-Implementierung aktualisiert und die Lösung funktioniert möglicherweise nicht mehr wie erwartet, selbst wenn der Schalter deaktiviert ist. Man muss das überprüfen.

Bill Karwins obige Lösung funktioniert gut, wenn die Elementanzahl in Gruppen relativ klein ist, aber die Leistung der Abfrage wird schlecht, wenn die Gruppen ziemlich groß sind, da die Lösung ungefähr n*n/2 + n/2 von nur IS NULL Vergleichen erfordert.

Ich habe meine Tests in einer InnoDB-Tabelle mit 18684446 Zeilen mit 1182 Gruppen durchgeführt. Die Tabelle enthält Testergebnisse für Funktionstests und hat die (test_id, request_id) als Primärschlüssel. Daher ist test_id eine Gruppe und ich suchte nach der letzten request_id für jede test_id .

Bills Lösung läuft schon mehrere Stunden auf meinem Dell E4310 und ich weiß nicht, wann es fertig sein wird, obwohl es auf einem Coverage-Index operiert (daher using index in EXPLAIN).

Ich habe ein paar andere Lösungen, die auf den gleichen Ideen basieren:

  • Wenn der zugrunde liegende Index der BTREE-Index ist (was normalerweise der Fall ist), ist das größte (group_id, item_value) der letzte Wert innerhalb jeder group_id , also der erste für jede group_id wenn wir den Index in absteigender Reihenfolge durchlaufen;
  • Wenn wir die Werte lesen, die von einem Index abgedeckt werden, werden die Werte in der Reihenfolge des Index gelesen;
  • Jeder Index enthält implizit Primärschlüsselspalten, die an diesen angehängt sind (dh der Primärschlüssel befindet sich im Überdeckungsindex). In den folgenden Lösungen arbeite ich direkt mit dem Primärschlüssel, in Ihrem Fall müssen Sie nur Primärschlüsselspalten in das Ergebnis einfügen.
  • In vielen Fällen ist es viel billiger, die erforderlichen Zeilen-IDs in der erforderlichen Reihenfolge in einer Unterabfrage zu erfassen und das Ergebnis der Unterabfrage für die ID zu verknüpfen. Da MySQL für jede Zeile des Unterabfrageergebnisses einen einzigen Abruf basierend auf dem Primärschlüssel benötigt, wird die Unterabfrage zuerst in den Join eingefügt, und die Zeilen werden in der Reihenfolge der IDs in der Unterabfrage ausgegeben (wenn explizite ORDER BY weggelassen werden) für den Beitritt)

3 Wege, wie MySQL Indizes verwendet, sind ein großartiger Artikel, um einige Details zu verstehen.

Lösung 1

Dieser ist unglaublich schnell, es dauert ungefähr 0,8 Sekunden auf meinen 18M + Zeilen:

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

Wenn Sie die Reihenfolge in ASC ändern möchten, fügen Sie sie in eine Unterabfrage ein, geben Sie nur die IDs zurück und verwenden Sie diese als Unterabfrage, um sie mit den restlichen Spalten zu verknüpfen:

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;

Dieser dauert ungefähr 1,2 Sekunden auf meinen Daten.

Lösung 2

Hier ist eine andere Lösung, die ungefähr 19 Sekunden für meinen Tisch benötigt:

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

Es gibt Tests auch in absteigender Reihenfolge zurück. Es ist viel langsamer, da es einen vollständigen Index-Scan durchführt, aber es gibt hier eine Idee, wie N max-Zeilen für jede Gruppe ausgegeben werden.

Der Nachteil der Abfrage besteht darin, dass ihr Ergebnis nicht vom Abfrage-Cache zwischengespeichert werden kann.

Question

Es gibt eine Tabelle messages , die Daten wie folgt 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ühren select * from messages group by name , ich bekomme das Ergebnis als:

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.

Derzeit ist dies die Abfrage, die ich verwende:

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

Aber das sieht sehr ineffizient aus. Gibt es noch andere Möglichkeiten, um das gleiche Ergebnis zu erzielen?




Ich habe noch nicht mit großen DB getestet, aber ich denke, das könnte schneller sein als Tabellen beitreten:

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



SELECT 
  column1,
  column2 
FROM
  table_name 
WHERE id IN 
  (SELECT 
    MAX(id) 
  FROM
    table_name 
  GROUP BY column1) 
ORDER BY column1 ;



Hier ist meine Lösung:

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



select * from messages group by name desc



Ich kam zu einer anderen Lösung, nämlich die IDs für den letzten Beitrag innerhalb jeder Gruppe zu erhalten, und dann aus der Nachrichtentabelle das Ergebnis der ersten Abfrage als Argument für ein WHERE x IN Konstrukt auszuwählen:

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 funktioniert, aber es funktionierte spektakulär für meinen Tisch mit 3 Millionen Zeilen. (4 Sekunden Ausführung mit 1200+ Ergebnissen)

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




Sie können auch von hier aus sehen.

http://sqlfiddle.com/#!9/ef42b/9

Erste Lösung

SELECT d1.ID,Name,City FROM Demo_User d1
INNER JOIN
(SELECT MAX(ID) AS ID FROM Demo_User GROUP By NAME) AS P ON (d1.ID=P.ID);

Zweite Lösung

SELECT * FROM (SELECT * FROM Demo_User ORDER BY ID DESC) AS T GROUP BY NAME ;



Natürlich gibt es viele verschiedene Möglichkeiten, die gleichen Ergebnisse zu erzielen. Ihre Frage scheint zu sein, wie effizient die letzten Ergebnisse in jeder Gruppe in MySQL erzielt werden können. Wenn Sie mit großen Datenmengen arbeiten und annehmen, dass Sie InnoDB sogar mit den neuesten Versionen von MySQL verwenden (z. B. 5.7.21 und 8.0.4-rc), gibt es möglicherweise keine effiziente Möglichkeit, 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, in 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 2000 Gruppen zurückgeben (was hypothetisch nicht sehr viele Daten erfordern würde).

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 Temperaturtabelle enthält etwa 1,5 Millionen zufällige Datensätze und 100 verschiedene Gruppen. Die selected_group wird mit diesen 100 Gruppen gefüllt (in unseren Fällen wären dies normalerweise weniger als 20% für alle Gruppen).

Da diese Daten zufällig sind, bedeutet dies, dass mehrere Zeilen dieselben aufgezeichneten Zeitstempel haben können. Was wir wollen, ist eine Liste aller ausgewählten Gruppen in der Reihenfolge der Gruppen-ID mit dem letzten aufgezeichneten Zeitstempel für jede Gruppe zu erhalten, und wenn die gleiche Gruppe mehr als eine passende Zeile hat, dann die letzte passende ID dieser Zeilen.

Wenn hypothetisch MySQL eine last () -Funktion hatte, die Werte von der letzten Zeile in einer speziellen ORDER BY-Klausel zurückgibt, könnten wir 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;

das würde in diesem Fall nur einige 100 Zeilen untersuchen müssen, da es keine der normalen GROUP BY-Funktionen verwendet. Dies würde in 0 Sekunden ausgeführt werden und daher sehr effizient sein. Beachten Sie, dass normalerweise in MySQL eine ORDER BY-Klausel nach der GROUP BY-Klausel angezeigt wird, diese ORDER BY-Klausel jedoch verwendet wird, um die ORDER für die Funktion last () zu bestimmen, wenn sie nach GROUP BY liegt. Wenn keine GROUP BY-Klausel vorhanden ist, sind die letzten Werte in allen zurückgegebenen Zeilen identisch.

Wie auch immer, MySQL hat das nicht, also schauen wir uns verschiedene Ideen an 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 nahm ~ 0.859 Sekunden bei 5.7.21 und etwas länger bei 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 bei 5.7.21 und etwas länger bei 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 nahm ~ 1.95 Sekunden bei 5.7.21 und etwas länger bei 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 bei 5,7.21 und etwas länger bei 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 bei 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 bei 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;

Dieser dauerte ewig, also musste ich ihn töten.




Wenn Sie die letzte Zeile für jeden Name , können Sie jeder Name eine Zeilennummer Name und zwar nach Name und nach Reihenfolge in absteigender Reihenfolge.

ABFRAGE

SELECT t1.Id, 
       t1.Name, 
       t1.Other_Columns
FROM 
(
     SELECT Id, 
            Name, 
            Other_Columns,
    (
        CASE Name WHEN @curA 
        THEN @curRow := @curRow + 1 
        ELSE @curRow := 1 AND @curA := Name END 
    ) + 1 AS rn 
    FROM messages t, 
    (SELECT @curRow := 0, @curA := '') r 
    ORDER BY Name,Id DESC 
)t1
WHERE t1.rn = 1
ORDER BY t1.Id;

SQL-Geige




Die unten stehende Abfrage funktioniert nach Ihrer Frage.

SELECT M1.* 
FROM MESSAGES M1,
(
 SELECT SUBSTR(Others_data,1,2),MAX(Others_data) AS Max_Others_data
 FROM MESSAGES
 GROUP BY 1
) M2
WHERE M1.Others_data = M2.Max_Others_data
ORDER BY Others_data;





Related