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
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.
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.
Kunde
) ist dies sehr effizient. Noch mehr, wenn Sie ohnehin eine sortierte Ausgabe benötigen. Der Vorteil schrumpft mit zunehmender Anzahl von Zeilen pro Kunde.work_mem
, um den betreffenden Sortierschritt im RAM zu verarbeiten und nicht auf die Festplatte zu verlagern. Aber im Allgemeinen kann eine zu hohe Einstellung von work_mem
nachteilige Auswirkungen haben. Ziehen Sie SET LOCAL
für außergewöhnlich große Abfragen in Betracht. Finden Sie mit EXPLAIN ANALYZE
heraus, wie viel Sie brauchen. Die Erwähnung von "Disk:" im Sortierschritt zeigt an, dass mehr benötigt wird:Kunde
) wäre ein loose index scan (auch bekannt als "skip scan") (viel) effizienter, aber das ist bis Postgres 12 nicht implementiert. (Eine Implementierung für reine Index-Scans ist für Postgres 13 in Entwicklung. Siehe hier und hier.)Ich hatte hier einen einfachen Benchmark, der mittlerweile veraltet ist. Ich habe ihn durch einen detaillierten Benchmark in dieser separaten Antwort ersetzt.
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
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
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;