Die erste Zeile in jeder GROUP BY-Gruppe auswählen?

Wie der Titel andeutet, möchte ich die erste Zeile jedes Satzes von Zeilen auswählen, die mit einem GROUP BY gruppiert sind.

Genauer gesagt, wenn ich eine Tabelle "Käufe" habe, die wie folgt aussieht:

SELECT * FROM purchases;

Meine Ausgabe:

{{{}} id | kunde | gesamt ---+----------+------ 1 | Joe | 5 2 | Sally | 3 3 | Joe | 2 4 | Sally | 1

Ich möchte nach der id des größten Einkaufs (Gesamtbetrag) jedes Kunden abfragen. Etwa so:

SELECT FIRST(id), customer, FIRST(total)
FROM  purchases
GROUP BY customer
ORDER BY total DESC;

Erwartete Ausgabe:

ERST(id) | Kunde | ERST(gesamt)
----------+----------+-------------
        1 | Joe | 5
        2 | Sally | 3
DineshDB
Bearbeitete Frage 26. März 2018 в 8:18
Programmierung
sql postgresql sqlite group-by greatest-n-per-group
27. September 2010 в 1:23
24 Aufrufe

In PostgreSQL ist dies in der Regel einfacher und schneller (weitere Leistungsoptimierung weiter unten):

SELECT DISTINCT ON (Kunde)
       id, kunde, gesamt
FROM einkäufe
ORDER BY kunde, gesamt DESC, id;
Oder kürzer (wenn auch nicht so eindeutig) mit Ordnungszahlen der Ausgabespalten: 
SELECT DISTINCT ON (2)
       id, customer, total
FROM   purchases
ORDER  BY 2, 3 DESC, 1;

Wenn total NULL sein kann (schadet so oder so nicht, aber Sie wollen bestehende Indizes abgleichen):

...
ORDER BY customer, total DESC NULLS LAST, id;
###Major points - [**`DISTINCT ON`**][1] ist eine PostgreSQL-Erweiterung des Standards (wo nur `DISTINCT` auf die gesamte `SELECT`-Liste definiert ist). - Listen Sie eine beliebige Anzahl von Ausdrücken in der `DISTINCT ON` Klausel auf, der kombinierte Zeilenwert definiert Duplikate. [Das Handbuch:][2] > Offensichtlich werden zwei Zeilen als unterschiedlich angesehen, wenn sie sich in mindestens > einen Spaltenwert unterscheiden. **Nullwerte werden bei diesem Vergleich als gleichwertig betrachtet.** Fettgedruckte Hervorhebung von mir. - `DISTINCT ON` kann mit **`ORDER BY`** kombiniert werden. Führende Ausdrücke müssen mit führenden `DISTINCT ON`-Ausdrücken in der gleichen Reihenfolge übereinstimmen. Sie können *zusätzliche* Ausdrücke zu "ORDER BY" hinzufügen, um eine bestimmte Zeile aus jeder Gruppe von Gleichrangigen auszuwählen. Ich habe `id` als letztes Element hinzugefügt, um Gleichstände aufzulösen: *"Wähle die Zeile mit der kleinsten `id` aus jeder Gruppe mit der höchsten `summe`. "* Um die Ergebnisse auf eine Art und Weise zu ordnen, die nicht mit der Sortierreihenfolge übereinstimmt, die die erste pro Gruppe bestimmt, können Sie die obige Abfrage in eine äußere Abfrage mit einem anderen "ORDER BY" einfügen. Zum Beispiel: - https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by/9796104#9796104 - Wenn `total` NULL sein kann, wollen Sie *wahrscheinlich* die Zeile mit dem größten Nicht-Null-Wert. Fügen Sie **`NULLS LAST`** wie gezeigt hinzu. Einzelheiten: - https://stackoverflow.com/questions/9510509/postgresql-sort-by-datetime-asc-null-first/9511492#9511492 - Die **SELECT`-Liste** wird in keiner Weise durch Ausdrücke in `DISTINCT ON` oder `ORDER BY` eingeschränkt. (Im obigen einfachen Fall nicht erforderlich): - Sie *müssen* keinen der Ausdrücke in `DISTINCT ON` oder `ORDER BY` einschließen. - Sie *können* jeden anderen Ausdruck in die `SELECT`-Liste aufnehmen. Dies ist sehr hilfreich, um komplexere Abfragen mit Unterabfragen und Aggregat-/Fensterfunktionen zu ersetzen. - Ich habe mit den Postgres-Versionen 8.3 - 12 getestet. Aber die Funktion gibt es mindestens seit Version 7.1, also im Grunde schon immer. ##Index Der *perfekte* Index für die obige Abfrage wäre ein [mehrspaltiger Index][3], der sich über alle drei Spalten in passender Reihenfolge und mit passender Sortierreihenfolge erstreckt: 
CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);

Das mag zu speziell sein. Verwenden Sie ihn aber, wenn die Leseleistung für die jeweilige Abfrage entscheidend ist. Wenn Sie DESC NULLS LAST in der Abfrage haben, verwenden Sie dasselbe im Index, damit die Sortierreihenfolge übereinstimmt und der Index anwendbar ist.

Effektivität / Leistungsoptimierung

Wägen Sie Kosten und Nutzen ab, bevor Sie maßgeschneiderte Indizes für jede Abfrage erstellen. Das Potenzial des oben genannten Index hängt weitgehend von der Datenverteilung ab. Der Index wird verwendet, weil er vorsortierte Daten liefert. In Postgres 9.2 oder höher kann die Abfrage auch von einem index only scan profitieren, wenn der Index kleiner ist als die zugrunde liegende Tabelle. Der Index muss jedoch in seiner Gesamtheit gescannt werden.

Erwin Brandstetter
Bearbeitete Antwort 28. August 2019 в 7:29
Kommentare (7)
Lösung

Auf Oracle 9.2+ (nicht 8i+ wie ursprünglich angegeben), SQL Server 2005+, PostgreSQL 8.4+, DB2, Firebird 3.0+, Teradata, Sybase, Vertica:

WITH summary AS (
    SELECT p.id, 
           p.customer, 
           p.total, 
           ROW_NUMBER() OVER(PARTITION BY p.customer 
                                 ORDER BY p.total DESC) AS rk
      FROM PURCHASES p)
SELECT s.*
  FROM summary s
 WHERE s.rk = 1

Unterstützt von jeder Datenbank:

Sie müssen jedoch eine Logik zur Auflösung von Gleichständen hinzufügen:

  SELECT MIN(x.id),  -- change to MAX if you want the highest
         x.customer, 
         x.total
    FROM PURCHASES x
    JOIN (SELECT p.customer,
                 MAX(total) AS max_total
            FROM PURCHASES p
        GROUP BY p.customer) y ON y.customer = x.customer
                              AND y.max_total = x.total
GROUP BY x.customer, x.total
mach128x
Bearbeitete Antwort 7. Dezember 2017 в 6:42
Kommentare (7)

Die Lösung ist nicht sehr effizient, wie Erwin feststellte, da SubQs vorhanden sind.

select * from purchases p1 where total in
(select max(total) from purchases where p1.customer=customer) order by total desc;
user2407394
Bearbeitete Antwort 17. Juni 2013 в 8:39
Kommentare (4)