Wie der Titel schon sagt, möchte ich die erste Zeile jeder mit einem GROUP BY
gruppierten Zeilen auswählen.
Insbesondere wenn ich eine purchases
-Tabelle habe, die wie folgt aussieht:
SELECT * FROM purchases;
Meine Ausgabe:
id | Kunde | insgesamt --- + ---------- + ------ 1 | Joe | 5 2 | Sally | 3 3 | Joe | 2 4 | Sally | 1
Ich möchte nach der id
des größten Kaufs (total
) fragen, die von jeder customer
getätigt wird. Etwas wie das:
SELECT FIRST(id), customer, FIRST(total)
FROM purchases
GROUP BY customer
ORDER BY total DESC;
Erwartete Ausgabe:
ERSTE (id) | Kunde | FIRST (insgesamt) ---------- + ---------- + ------------- 1 | Joe | 5 2 | Sally | 3
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 Logik hinzufügen, um die Beziehungen zu brechen:
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
In PostgreSQL ist dies normalerweise einfacher und schneller (weitere Leistungsoptimierung siehe unten). :
_SELECT DISTINCT ON (customer)
id, customer, total
FROM purchases
ORDER BY customer, total DESC, id;
_
Oder kürzer (wenn nicht so klar) mit Ordnungszahlen der Ausgabespalten:
_SELECT DISTINCT ON (2)
id, customer, total
FROM purchases
ORDER BY 2, 3 DESC, 1;
_
Wenn total
NULL sein kann (dies schadet in keiner Weise, aber Sie möchten, dass vorhandene Indizes übereinstimmen):
_...
ORDER BY customer, total DESC NULLS LAST, id;
_
DISTINCT ON
ist eine PostgreSQL-Erweiterung des Standards (wobei nur DISTINCT
im Ganzen SELECT
Liste ist definiert).
Listen Sie eine beliebige Anzahl von Ausdrücken in der _DISTINCT ON
_ -Klausel auf. Der kombinierte Zeilenwert definiert Duplikate. Das Handbuch:
Offensichtlich werden zwei Zeilen als unterschiedlich betrachtet, wenn sie sich in mindestens einem Spaltenwert unterscheiden. Nullwerte werden in diesem Vergleich als gleich angesehen.
Meine kühne Betonung.
_DISTINCT ON
_ kann mit ORDER BY
kombiniert werden. Führende Ausdrücke müssen mit führenden _DISTINCT ON
_ Ausdrücken in derselben Reihenfolge übereinstimmen. Sie können zusätzliche Ausdrücke zu _ORDER BY
_ hinzufügen, um eine bestimmte Zeile aus jeder Gruppe von Peers auszuwählen. Ich habe id
als letzten Gegenstand hinzugefügt, um die Krawatten zu lösen:
"Wähle die Zeile mit dem kleinsten id
aus jeder Gruppe mit dem höchsten total
."
Um die Ergebnisse so zu sortieren, dass die Sortierreihenfolge der ersten pro Gruppe nicht übereinstimmt, können Sie die oben angegebene Abfrage in eine äußere Abfrage mit einem anderen _ORDER BY
_ verschachteln. Mögen:
Wenn total
NULL sein kann, möchten Sie höchstwahrscheinlich die Zeile mit dem größten Nicht-Null-Wert. Fügen Sie NULLS LAST
hinzu, wie gezeigt. Einzelheiten:
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 nicht einen der Ausdrücke in _DISTINCT ON
_ oder _ORDER BY
_ aufnehmen.
Sie können einen beliebigen anderen Ausdruck in die Liste SELECT
aufnehmen. Dies ist hilfreich, um komplexere Abfragen durch Unterabfragen und Aggregat-/Fensterfunktionen zu ersetzen.
Ich habe mit den Postgres-Versionen 8.3 - 12 getestet. Aber das Feature gibt es zumindest seit Version 7.1, also grundsätzlich immer.
Der perfect Index für die obige Abfrage wäre ein mehrspaltiger Index , der alle drei Spalten in übereinstimmender Reihenfolge und mit übereinstimmender Sortierreihenfolge umfasst:
_CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);
_
Kann zu spezialisiert sein. Verwenden Sie sie jedoch, wenn die Leseleistung für die jeweilige Abfrage entscheidend ist. Wenn die Abfrage _DESC NULLS LAST
_ enthält, verwenden Sie dasselbe im Index, damit die Sortierreihenfolge übereinstimmt und der Index anwendbar ist.
Wägen Sie Kosten und Nutzen ab, bevor Sie für jede Abfrage maßgeschneiderte Indizes erstellen. Das Potenzial des obigen 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 nur Index-Scan profitieren, wenn der Index kleiner als die zugrunde liegende Tabelle ist. Der Index muss jedoch vollständig gescannt werden.
Für wenige Zeilen pro Kunde (hohe Kardinalität in Spalte customer
) ist dies sehr effizient. Umso mehr, wenn Sie trotzdem eine sortierte Ausgabe benötigen. Der Nutzen verringert sich mit einer wachsenden Anzahl von Zeilen pro Kunde.
Idealerweise haben Sie genug work_mem
, um den betreffenden Sortierschritt in RAM und nicht auf die Festplatte verschütten. Im Allgemeinen kann die Einstellung von _work_mem
_ z hoch jedoch nachteilige Auswirkungen haben. Betrachten Sie _SET LOCAL
_ für außergewöhnlich große Abfragen. Finden Sie mit _EXPLAIN ANALYZE
_ heraus, wie viel Sie benötigen. Die Erwähnung von "Disk:" im Sortierschritt weist auf die Notwendigkeit von mehr hin:
Für viele Zeilen pro Kunde (niedrige Kardinalität in Spalte customer
) ist ein Loose Index Scan (auch bekannt als "Skip Scan") wäre (viel) effizienter, aber das ist bis Postgres 12 nicht implementiert. (Eine Implementierung für Index -only scans ist für Postgres 13 in der Entwicklung. Siehe hier und hier .)
Momentan gibt es schnellere Abfragetechniken , um dies zu ersetzen. Insbesondere, wenn Sie eine separate Tabelle mit eindeutigen Kunden haben, was der typische Anwendungsfall ist. Aber auch wenn Sie nicht:
Ich hatte hier einen einfachen Benchmark, der mittlerweile veraltet ist. Ich habe es in dieser separaten Antwort durch einen detaillierten Benchmark ersetzt .
Testen Sie die interessantesten Kandidaten mit Postgres 9.4 und 9.5 mit einer halbwegs realistischen Tabelle mit 200.000 Zeilen in purchases
und 10.000 verschiedenen customer_id
( durchschnittlich 20 Zeilen pro Kunde).
Für Postgres 9.5 führte ich einen zweiten Test mit effektiv 86446 verschiedenen Kunden durch. Siehe unten ( durchschnittlich 2,3 Zeilen pro Kunde).
Haupttisch
CREATE TABLE purchases (
id serial
, customer_id int -- REFERENCES customer
, total int -- could be amount of money in Cent
, some_column text -- to make the row bigger, more realistic
);
Ich verwende eine serial
(PK-Einschränkung unten hinzugefügt) und eine Ganzzahl customer_id
, da dies ein typischeres Setup ist. Es wurde auch some_column
hinzugefügt, um in der Regel mehr Spalten auszugleichen.
Dummy-Daten, PK, Index - eine typische Tabelle enthält auch einige tote Tupel:
INSERT INTO purchases (customer_id, total, some_column) -- insert 200k rows
SELECT (random() * 10000)::int AS customer_id -- 10k customers
, (random() * random() * 100000)::int AS total
, 'note: ' || repeat('x', (random()^2 * random() * random() * 500)::int)
FROM generate_series(1,200000) g;
ALTER TABLE purchases ADD CONSTRAINT purchases_id_pkey PRIMARY KEY (id);
DELETE FROM purchases WHERE random() > 0.9; -- some dead rows
INSERT INTO purchases (customer_id, total, some_column)
SELECT (random() * 10000)::int AS customer_id -- 10k customers
, (random() * random() * 100000)::int AS total
, 'note: ' || repeat('x', (random()^2 * random() * random() * 500)::int)
FROM generate_series(1,20000) g; -- add 20k to make it ~ 200k
CREATE INDEX purchases_3c_idx ON purchases (customer_id, total DESC, id);
VACUUM ANALYZE purchases;
customer
Tabelle - für übergeordnete Abfrage
CREATE TABLE customer AS
SELECT customer_id, 'customer_' || customer_id AS customer
FROM purchases
GROUP BY 1
ORDER BY 1;
ALTER TABLE customer ADD CONSTRAINT customer_customer_id_pkey PRIMARY KEY (customer_id);
VACUUM ANALYZE customer;
In meinem zweiten Test für 9.5 habe ich das gleiche Setup verwendet, aber mit random() * 100000
um customer_id
nur zu generieren wenige Zeilen pro customer_id
.
purchases
Generiert mit diese Abfrage .
what | bytes/ct | bytes_pretty | bytes_per_row
-----------------------------------+----------+--------------+---------------
core_relation_size | 20496384 | 20 MB | 102
visibility_map | 0 | 0 bytes | 0
free_space_map | 24576 | 24 kB | 0
table_size_incl_toast | 20529152 | 20 MB | 102
indexes_size | 10977280 | 10 MB | 54
total_size_incl_toast_and_indexes | 31506432 | 30 MB | 157
live_rows_in_text_representation | 13729802 | 13 MB | 68
------------------------------ | | |
row_count | 200045 | |
live_tuples | 200045 | |
dead_tuples | 19955 | |
row_number()
in CTE, ( siehe andere Antwort )WITH cte AS (
SELECT id, customer_id, total
, row_number() OVER(PARTITION BY customer_id ORDER BY total DESC) AS rn
FROM purchases
)
SELECT id, customer_id, total
FROM cte
WHERE rn = 1;
row_number()
in der Unterabfrage (meine Optimierung)SELECT id, customer_id, total
FROM (
SELECT id, customer_id, total
, row_number() OVER(PARTITION BY customer_id ORDER BY total DESC) AS rn
FROM purchases
) sub
WHERE rn = 1;
DISTINCT ON
( siehe andere Antwort )SELECT DISTINCT ON (customer_id)
id, customer_id, total
FROM purchases
ORDER BY customer_id, total DESC, id;
LATERAL
Unterabfrage ( siehe hier )WITH RECURSIVE cte AS (
( -- parentheses required
SELECT id, customer_id, total
FROM purchases
ORDER BY customer_id, total DESC
LIMIT 1
)
UNION ALL
SELECT u.*
FROM cte c
, LATERAL (
SELECT id, customer_id, total
FROM purchases
WHERE customer_id > c.customer_id -- lateral reference
ORDER BY customer_id, total DESC
LIMIT 1
) u
)
SELECT id, customer_id, total
FROM cte
ORDER BY customer_id;
customer
Tabelle mit LATERAL
( siehe hier )SELECT l.*
FROM customer c
, LATERAL (
SELECT id, customer_id, total
FROM purchases
WHERE customer_id = c.customer_id -- lateral reference
ORDER BY total DESC
LIMIT 1
) l;
array_agg()
mit ORDER BY
( siehe andere Antwort )SELECT (array_agg(id ORDER BY total DESC))[1] AS id
, customer_id
, max(total) AS total
FROM purchases
GROUP BY customer_id;
Ausführungszeit für obige Abfragen mit EXPLAIN ANALYZE
(und allen Optionen aus), Best of 5 Runs.
Alle Abfragen verwendeten einen Nur-Index-Scan für purchases2_3c_idx
(unter anderen Schritten). Einige davon nur für die kleinere Größe des Index, andere effektiver.
customer_id
1. 273.274 ms
2. 194.572 ms
3. 111.067 ms
4. 92.922 ms
5. 37.679 ms -- winner
6. 189.495 ms
1. 288.006 ms
2. 223.032 ms
3. 107.074 ms
4. 78.032 ms
5. 33.944 ms -- winner
6. 211.540 ms
customer_id
1. 381.573 ms
2. 311.976 ms
3. 124.074 ms -- winner
4. 710.631 ms
5. 311.976 ms
6. 421.679 ms
Ich habe drei Tests mit PostgreSQL 9.1 in einer realen Tabelle mit 65579 Zeilen und einspaltigen Btree-Indizes in jeder der drei beteiligten Spalten durchgeführt und die besten Ergebnisse erzielt Ausführungszeit von 5 Läufen.
Vergleichen von @ OMGPonies ' erster Abfrage (A
) mit oben DISTINCT ON
solution (B
):
Wählen Sie die gesamte Tabelle aus, in diesem Fall werden 5958 Zeilen angezeigt.
A: 567.218 ms
B: 386.673 ms
Verwenden Sie die Bedingung WHERE customer BETWEEN x AND y
, die 1000 Zeilen ergibt.
A: 249.136 ms
B: 55.111 ms
Wählen Sie einen einzelnen Kunden mit WHERE customer = x
aus.
A: 0.143 ms
B: 0.072 ms
Der gleiche Test wurde mit dem in der anderen Antwort beschriebenen Index wiederholt
CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);
1A: 277.953 ms
1B: 193.547 ms
2A: 249.796 ms -- special index not used
2B: 28.679 ms
3A: 0.120 ms
3B: 0.048 ms
Dies ist ein allgemeines best-n-per-group Problem, das bereits gut getestet wurde und in hohem Maße optimierte Lösungen . Persönlich bevorzuge ich das Link Join-Lösung von Bill Karwin (das ursprünglicher Beitrag mit vielen anderen Lösungen ).
Beachten Sie, dass eine Reihe von Lösungen für dieses häufige Problem überraschenderweise in einer der offiziellen Quellen, dem MySQL-Handbuch, zu finden ist. Siehe Beispiele für häufig gestellte Abfragen :: Die Zeilen, die das gruppenweise Maximum einer bestimmten Spalte enthalten .
In Postgres können Sie array_agg
folgendermaßen verwenden:
SELECT customer,
(array_agg(id ORDER BY total DESC))[1],
max(total)
FROM purchases
GROUP BY customer
Dadurch erhalten Sie die id
des größten Kaufs jedes Kunden.
Einige Dinge zu beachten:
array_agg
ist eine Aggregatfunktion und funktioniert daher mit GROUP BY
.array_agg
können Sie einen Bereich für die Reihenfolge nur für sich selbst angeben, sodass die Struktur der gesamten Abfrage nicht eingeschränkt wird. Es gibt auch eine Syntax für das Sortieren von NULL-Werten, wenn Sie etwas anderes als den Standardwert verwenden müssen.array_agg
auf ähnliche Weise für Ihre dritte Ausgabespalte verwenden, max(total)
ist jedoch einfacher.DISTINCT ON
können Sie mit array_agg
Ihren GROUP BY
behalten, falls Sie dies aus anderen Gründen wünschen.Die Lösung ist nicht sehr effizient, wie Erwin zeigt, da SubQs vorhanden sind
select * from purchases p1 where total in
(select max(total) from purchases where p1.customer=customer) order by total desc;
Ich verwende diesen Weg (nur für Postgresql): https://wiki.postgresql.org/wiki/First/last_%28aggregate%29
-- Create a function that always returns the first non-NULL item
CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
SELECT $1;
$$;
-- And then wrap an aggregate around it
CREATE AGGREGATE public.first (
sfunc = public.first_agg,
basetype = anyelement,
stype = anyelement
);
-- Create a function that always returns the last non-NULL item
CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
SELECT $2;
$$;
-- And then wrap an aggregate around it
CREATE AGGREGATE public.last (
sfunc = public.last_agg,
basetype = anyelement,
stype = anyelement
);
Dann sollte Ihr Beispiel fast so funktionieren, wie es ist:
SELECT FIRST(id), customer, FIRST(total)
FROM purchases
GROUP BY customer
ORDER BY FIRST(total) DESC;
CAVEAT: NULL-Zeilen werden ignoriert
Jetzt benutze ich diesen Weg: http://pgxn.org/dist/first_last_agg/
So installieren Sie auf Ubuntu 14.04:
apt-get install postgresql-server-dev-9.3 git build-essential -y
git clone git://github.com/wulczer/first_last_agg.git
cd first_last_app
make && Sudo make install
psql -c 'create extension first_last_agg'
Es ist eine Postgres-Erweiterung, die Ihnen erste und letzte Funktionen gibt. offenbar schneller als der oben genannte Weg.
Wenn Sie Aggregatfunktionen (wie diese) verwenden, können Sie die Ergebnisse bestellen, ohne dass die Daten bereits bestellt werden müssen:
http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES
Das äquivalente Beispiel mit der Bestellung wäre also etwa so:
SELECT first(id order by id), customer, first(total order by id)
FROM purchases
GROUP BY customer
ORDER BY first(total);
Natürlich können Sie nach Belieben innerhalb des Aggregats bestellen und filtern. Es ist eine sehr mächtige Syntax.
Sehr schnelle Lösung
SELECT a.*
FROM
purchases a
JOIN (
SELECT customer, min( id ) as id
FROM purchases
GROUP BY customer
) b USING ( id );
und wirklich sehr schnell, wenn die Tabelle mit der ID indiziert ist:
create index purchases_id on purchases (id);
Die Abfrage:
SELECT purchases.*
FROM purchases
LEFT JOIN purchases as p
ON
p.customer = purchases.customer
AND
purchases.total < p.total
WHERE p.total IS NULL
WIE FUNKTIONIERT DAS! (Ich war dort)
Wir möchten sicherstellen, dass wir für jeden Einkauf nur die höchste Summe haben.
Einige theoretische Dinge (überspringen Sie diesen Teil, wenn Sie nur die Abfrage verstehen möchten)
Lassen Sie Total eine Funktion T (customer, id) sein, bei der ein mit Name und id angegebener Wert zurückgegeben wird. Um zu beweisen, dass die angegebene Summe (T (customer, id)) am höchsten ist, müssen wir das beweisen... Wir will es beweisen
OR
Der erste Ansatz erfordert, dass wir alle Datensätze für diesen Namen erhalten, die mir nicht wirklich gefallen.
Der zweite wird eine intelligente Methode benötigen, um zu sagen, dass es keinen höheren Datensatz als diesen geben kann.
Zurück zu SQL
Wenn wir die Tabelle mit dem Namen verbinden und insgesamt weniger als die verknüpfte Tabelle sind:
LEFT JOIN purchases as p
ON
p.customer = purchases.customer
AND
purchases.total < p.total
wir stellen sicher, dass alle Datensätze, die einen anderen Datensatz haben, mit der höheren Gesamtsumme für den gleichen Benutzer, der Mitglied werden soll:
purchases.id, purchases.customer, purchases.total, p.id, p.customer, p.total
1 , Tom , 200 , 2 , Tom , 300
2 , Tom , 300
3 , Bob , 400 , 4 , Bob , 500
4 , Bob , 500
5 , Alice , 600 , 6 , Alice , 700
6 , Alice , 700
Dies hilft uns, bei jedem Kauf nach der höchsten Summe zu filtern, ohne dass eine Gruppierung erforderlich ist:
WHERE p.total IS NULL
purchases.id, purchases.name, purchases.total, p.id, p.name, p.total
2 , Tom , 300
4 , Bob , 500
6 , Alice , 700
Und das ist die Antwort, die wir brauchen.
Verwenden Sie die Funktion ARRAY_AGG
für PostgreSQL , U-SQL , IBM DB2 und Google BigQuery SQL :
SELECT customer, (ARRAY_AGG(id ORDER BY total DESC))[1], MAX(total)
FROM purchases
GROUP BY customer
Die von OMG Ponies akzeptierte Lösung "Unterstützt durch eine beliebige Datenbank" hat im Test gute Ergebnisse erzielt.
Hier stelle ich den gleichen Ansatz vor, aber eine vollständigere und sauberere Datenbanklösung. Krawatten werden berücksichtigt (der Wunsch, nur eine Zeile für jeden Kunden zu erhalten, sogar mehrere Datensätze für die maximale Summe pro Kunde), und andere Kauffelder (z. B. buy_payment_id) werden für die echten übereinstimmenden Zeilen in der Kauftabelle ausgewählt.
Unterstützt von jeder Datenbank:
select * from purchase
join (
select min(id) as id from purchase
join (
select customer, max(total) as total from purchase
group by customer
) t1 using (customer, total)
group by customer
) t2 using (id)
order by customer
Diese Abfrage ist relativ schnell, insbesondere wenn ein zusammengesetzter Index wie (Kunde, Gesamt) in der Kauftabelle vorhanden ist.
Anmerkung:
t1, t2 sind Unterabfragealiasnamen, die je nach Datenbank entfernt werden können.
Caveat : Die using (...)
-Klausel wird derzeit in MS-SQL und Oracle db ab dieser Änderung vom Januar 2017 nicht unterstützt. Sie müssen sie selbst erweitern, z. on t2.id = purchase.id
etc. Die USING-Syntax funktioniert in SQLite, MySQL und PostgreSQL.
In SQL Server können Sie Folgendes tun:
SELECT *
FROM (
SELECT ROW_NUMBER()
OVER(PARTITION BY customer
ORDER BY total DESC) AS StRank, *
FROM Purchases) n
WHERE StRank = 1
Erklärung: Hier Group by wird auf Basis des Kunden durchgeführt und dann nach Summe bestellt. Jede dieser Gruppen erhält die Seriennummer als StRank, und wir nehmen zuerst einen Kunden heraus, dessen Rang 1 ist
Für SQl Server ist der effizienteste Weg:
with
ids as ( --condition for split table into groups
select i from (values (9),(12),(17),(18),(19),(20),(22),(21),(23),(10)) as v(i)
)
,src as (
select * from yourTable where <condition> --use this as filter for other conditions
)
,joined as (
select tops.* from ids
cross apply --it`s like for each rows
(
select top(1) *
from src
where CommodityId = ids.i
) as tops
)
select * from joined
vergessen Sie nicht, Clustered-Index für verwendete Spalten zu erstellen
Wenn Sie aus Ihrer Gruppe von aggregierten Zeilen eine beliebige Zeile auswählen möchten.
Wenn Sie neben sum/avg
eine weitere Aggregationsfunktion (max/min
) verwenden möchten. Daher können Sie keine Ahnung mit DISTINCT ON
verwenden.
Sie können die nächste Unterabfrage verwenden:
SELECT
(
SELECT **id** FROM t2
WHERE id = ANY ( ARRAY_AGG( tf.id ) ) AND amount = MAX( tf.amount )
) id,
name,
MAX(amount) ma,
SUM( ratio )
FROM t2 tf
GROUP BY name
Sie können amount = MAX( tf.amount )
mit einer beliebigen Bedingung durch eine Einschränkung ersetzen: Diese Unterabfrage darf nicht mehr als eine Zeile zurückgeben
Aber wenn Sie solche Dinge tun wollen, suchen Sie wahrscheinlich Fensterfunktionen