Frage SQL wählt nur Zeilen mit maximalem Wert für eine Spalte aus


Ich habe diese Tabelle für Dokumente (vereinfachte Version hier):

+------+-------+--------------------------------------+
| id   | rev   | content                              |
+------+-------+--------------------------------------+
| 1    | 1     | ...                                  |
| 2    | 1     | ...                                  |
| 1    | 2     | ...                                  |
| 1    | 3     | ...                                  |
+------+-------+--------------------------------------+

Wie wähle ich eine Zeile pro ID und nur die größte rev aus?
Mit den obigen Daten sollte das Ergebnis zwei Zeilen enthalten: [1, 3, ...] und [2, 1, ..]. Ich benutze MySQL.

Derzeit verwende ich Schecks in der while Schleife, um alte revs aus dem Resultset zu erkennen und zu überschreiben. Aber ist dies die einzige Methode, um das Ergebnis zu erreichen? Ist da nicht ein? SQL Lösung?

Aktualisieren
Wie die Antworten nahelegen, dort ist eine SQL-Lösung und hier eine sqlfiddle Demo.

Update 2
Ich bemerkte nach dem Hinzufügen der oben genannten sqlfiddle, die Rate, mit der die Frage aufgewertet wird, hat die Upvote-Rate der Antworten übertroffen. Das war nicht die Absicht! Die Geige basiert auf den Antworten, insbesondere der angenommenen Antwort.


870
2017-10-12 19:42


Ursprung


Antworten:


Auf den ersten Blick...

Alles was du brauchst ist ein GROUP BY Klausel mit der MAX Aggregatfunktion:

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

Es ist nie so einfach, oder?

Ich habe gerade bemerkt, dass du das brauchst content Spalte auch.

Dies ist eine sehr häufige Frage in SQL: Suchen Sie die gesamten Daten für die Zeile mit einem maximalen Wert in einer Spalte nach einer Gruppen-ID. Das habe ich während meiner Karriere oft gehört. Eigentlich war es eine der Fragen, die ich im technischen Interview meines aktuellen Jobs beantwortet habe.

Tatsächlich ist es so üblich, dass die StackOverflow-Community ein einzelnes Tag erstellt hat, um sich mit solchen Fragen zu beschäftigen: .

Im Grunde haben Sie zwei Ansätze, um dieses Problem zu lösen:

Beitritt mit einfach group-identifier, max-value-in-group Unterabfrage

Bei diesem Ansatz finden Sie zuerst die group-identifier, max-value-in-group (bereits oben gelöst) in einer Unterabfrage. Dann verbinden Sie Ihre Tabelle mit der Unterabfrage mit Gleichheit auf beiden group-identifier und max-value-in-group:

SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev

Linke Join mit Self, Tweaking Join Bedingungen und Filter

Bei dieser Vorgehensweise verlassen Sie die Tabelle mit sich selbst. Gleichheit geht natürlich in die group-identifier. Dann, 2 intelligente Bewegungen:

  1. Die zweite Join-Bedingung hat den linken Seitenwert kleiner als den rechten Wert
  2. Wenn Sie Schritt 1 ausführen, haben die Zeile (n), die tatsächlich den maximalen Wert haben NULL auf der rechten Seite (es ist a LEFT JOIN, merken?). Dann filtern wir das verbundene Ergebnis und zeigen nur die Zeilen an, in denen sich die rechte Seite befindet NULL.

Also hast du am Ende:

SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;

Fazit

Beide Ansätze bringen genau das gleiche Ergebnis.

Wenn Sie zwei Zeilen mit haben max-value-in-group zum group-identifierIn beiden Ansätzen sind beide Zeilen im Ergebnis enthalten.

Beide Ansätze sind SQL ANSI-kompatibel und funktionieren daher mit Ihrem bevorzugten RDBMS, unabhängig von seinem "Geschmack".

Beide Ansätze sind auch leistungsfreundlich, jedoch kann Ihre Laufleistung variieren (RDBMS, DB-Struktur, Indizes usw.). Wenn du also einen Ansatz über den anderen wählst, Benchmark. Und stellen Sie sicher, dass Sie diejenige auswählen, die für Sie am sinnvollsten ist.


1387
2017-10-12 19:43



Ich bevorzuge so wenig Code wie möglich ...

Sie können es mit verwenden IN Versuche dies:

SELECT * 
FROM t1 WHERE (id,rev) IN 
( SELECT id, MAX(rev)
  FROM t1
  GROUP BY id
)

Für mich ist es weniger kompliziert ... einfacher zu lesen und zu warten.


168
2017-10-12 19:47



Eine weitere Lösung besteht darin, eine korrelierte Unterabfrage zu verwenden:

select yt.id, yt.rev, yt.contents
    from YourTable yt
    where rev = 
        (select max(rev) from YourTable st where yt.id=st.id)

Ein Index auf (id, rev) macht die Unterabfrage fast wie eine einfache Suche ...

Es folgen Vergleiche mit den Lösungen in @ AdrianCarneiros Antwort (Unterabfrage, leftjoin), basierend auf MySQL-Messungen mit der InnoDB-Tabelle von ~ 1 Millionen Datensätzen, wobei die Gruppengröße: 1-3 ist.

Während für vollständige Tabellen-Scans Unterabfrage / linksbündige / korrelierte Zeiten miteinander in Beziehung stehen als 6/8/9, wenn es um direkte Suchvorgänge oder Stapelverarbeitung geht (id in (1,2,3)), Unterabfrage ist viel langsamer als die anderen (aufgrund der Unterabfrage der Unterabfrage). Jedoch konnte ich nicht zwischen linksbündiger und korrelierter Lösung in der Geschwindigkeit unterscheiden.

Eine letzte Note, als leftjoin erzeugt n * (n + 1) / 2 Joins in Gruppen, deren Performance stark von der Größe der Gruppen beeinflusst werden kann ...


52
2018-01-23 14:16



Ich kann mich nicht für die Leistung verbürgen, aber hier ist ein Trick, der von den Einschränkungen von Microsoft Excel inspiriert ist. Es hat einige gute Eigenschaften

GUTES ZEUG

  • Es sollte die Rückkehr von nur einem "Max Record" erzwingen, auch wenn es eine Gleichheit gibt (manchmal nützlich)
  • Es erfordert keinen Beitritt

ANSATZ

Es ist ein bisschen hässlich und erfordert, dass Sie etwas über den Bereich der gültigen Werte von wissen rev Säule. Nehmen wir an, wir kennen das rev Spalte ist eine Zahl zwischen 0.00 und 999 einschließlich Dezimalstellen, aber es gibt immer nur zwei Stellen rechts vom Dezimalpunkt (z. B. 34.17 wäre ein gültiger Wert).

Der Kern der Sache ist, dass Sie eine einzelne synthetische Spalte durch Verketten / Verpacken des primären Vergleichsfeldes zusammen mit den gewünschten Daten erstellen. Auf diese Weise können Sie die SQL-Funktion MAX () zwingen, alle Daten zurückzugeben (weil sie in eine einzelne Spalte gepackt wurde). Dann müssen Sie die Daten entpacken.

So sieht es mit dem obigen Beispiel aus, geschrieben in SQL

SELECT id, 
       CAST(SUBSTRING(max(packed_col) FROM 2 FOR 6) AS float) as max_rev,
       SUBSTRING(max(packed_col) FROM 11) AS content_for_max_rev 
FROM  (SELECT id, 
       CAST(1000 + rev + .001 as CHAR) || '---' || CAST(content AS char) AS packed_col
       FROM yourtable
      ) 
GROUP BY id

Die Verpackung beginnt mit der Erzwingung der rev Spalte eine Anzahl von bekannten Zeichen Länge unabhängig von dem Wert von rev so zum Beispiel

  • 3.2 wird 1003.201
  • 57 wird 1057.001
  • 923.88 wird 1923.881

Wenn Sie es richtig machen, sollte der String-Vergleich zweier Zahlen das gleiche "max" ergeben wie der numerische Vergleich der beiden Zahlen und es ist einfach, mit der Teilstring-Funktion (die in der einen oder anderen Form verfügbar ist) zurück in die ursprüngliche Zahl zu konvertieren überall).


34
2018-06-30 06:02



Ich bin verblüfft, dass keine Antwort SQL-Fensterfunktionslösung angeboten hat:

SELECT a.id, a.rev, a.contents
  FROM (SELECT id, rev, contents,
               ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
          FROM YourTable) a
 WHERE a.rank = 1 

Hinzugefügt im SQL-Standard ANSI / ISO Standard SQL: 2003 und später erweitert mit ANSI / ISO Standard SQL: 2008, Fenster- (oder Windowing-) Funktionen sind jetzt bei allen wichtigen Herstellern verfügbar. Es gibt mehrere Arten von Rangfunktionen, die für ein Bindeproblem zur Verfügung stehen: RANK, DENSE_RANK, PERSENT_RANK.


27
2017-08-09 15:29



Ich denke, das ist die einfachste Lösung:

SELECT *
FROM
    (SELECT *
    FROM Employee
    ORDER BY Salary DESC)
AS employeesub
GROUP BY employeesub.Salary;
  • SELECT *: Alle Felder zurückgeben.
  • FROM Employee: Tabelle gesucht.
  • (SELECT * ...) Unterabfrage: Zurückgeben aller Personen, sortiert nach Gehalt.
  • GROUP BY employeesub.Salary:: Erzwingt die Top-sortierte Gehaltszeile jedes Mitarbeiters als zurückgegebenes Ergebnis.

Wenn Sie nur die eine Zeile benötigen, ist es noch einfacher:

SELECT *
FROM Employee
ORDER BY Employee.Salary DESC
LIMIT 1

Ich denke auch, dass es am einfachsten ist, zu anderen Zwecken zu brechen, zu verstehen und zu modifizieren:

  • ORDER BY Employee.Salary DESC: Ordnen Sie die Ergebnisse nach dem Gehalt an, mit den höchsten Gehältern zuerst.
  • LIMIT 1: Gibt nur ein Ergebnis zurück.

Diesen Ansatz zu verstehen und all diese ähnlichen Probleme zu lösen, wird trivial: Mitarbeiter mit dem niedrigsten Gehalt bekommen (DESC in ASC ändern), Top-Ten-Mitarbeiter verdienen (LIMIT 1 zu LIMIT 10 ändern), nach einem anderen Feld sortieren (ORDER BY ändern) Employee.Salary zu ORDER BY Employee.Commission), etc ..


20
2017-09-14 00:28



Etwas wie das?

SELECT yourtable.id, rev, content
FROM yourtable
INNER JOIN (
    SELECT id, max(rev) as maxrev FROM yourtable
    WHERE yourtable
    GROUP BY id
) AS child ON (yourtable.id = child.id) AND (yourtable.rev = maxrev)

14
2017-10-12 19:48



Da dies die populärste Frage in Bezug auf dieses Problem ist, werde ich auch hier eine andere Antwort darauf posten:

Es sieht so aus, als ob es einen einfacheren Weg gibt, dies zu tun (aber nur in MySQL):

select *
from (select * from mytable order by id, rev desc ) x
group by id

Bitte Kredit Antwort des Benutzers Bohemian im diese Frage für eine so präzise und elegante Antwort auf dieses Problem.

BEARBEITEN: Obwohl diese Lösung für viele Menschen funktioniert, ist sie auf lange Sicht möglicherweise nicht stabil, da MySQL nicht garantiert, dass die GROUP BY-Anweisung sinnvolle Werte für Spalten zurückgibt, die nicht in der GROUP BY-Liste enthalten sind. Verwenden Sie diese Lösung auf eigene Gefahr


6
2017-07-03 14:33



Eine dritte Lösung, die ich kaum jemals erwähnt habe, ist MySQL-spezifisch und sieht so aus:

SELECT id, MAX(rev) AS rev
 , 0+SUBSTRING_INDEX(GROUP_CONCAT(numeric_content ORDER BY rev DESC), ',', 1) AS numeric_content
FROM t1
GROUP BY id

Ja, es sieht schrecklich aus (Umwandlung in String und zurück usw.), aber nach meiner Erfahrung ist es normalerweise schneller als die anderen Lösungen. Vielleicht nur für meine Anwendungsfälle, aber ich habe es auf Tabellen mit Millionen von Datensätzen und vielen einzigartigen IDs verwendet. Vielleicht liegt es daran, dass MySQL ziemlich schlecht darin ist, die anderen Lösungen zu optimieren (zumindest in den 5.0 Tagen, als ich mit dieser Lösung aufkam).

Eine wichtige Sache ist, dass GROUP_CONCAT eine maximale Länge für die Zeichenfolge hat, die es aufbauen kann. Sie möchten dieses Limit wahrscheinlich erhöhen, indem Sie das group_concat_max_len Variable. Beachten Sie, dass dies eine Skalierungsgrenze darstellt, wenn Sie eine große Anzahl von Zeilen haben.

Wie auch immer, das obige funktioniert nicht direkt, wenn Ihr Inhaltsfeld bereits Text ist. In diesem Fall möchten Sie wahrscheinlich ein anderes Trennzeichen verwenden, wie zB \ 0. Sie werden auch in die group_concat_max_len schneller begrenzen.


4
2017-10-10 11:57



Ich benutze gerne ein NOT EXISTLösung für dieses Problem:

SELECT id, rev
FROM YourTable t
WHERE NOT EXISTS (
   SELECT * FROM YourTable t WHERE t.id = id AND rev > t.rev
)

4
2017-09-05 21:58