Zoals de titel suggereert, zou ik graag de eerste rij selecteren van elke set rijen gegroepeerd met een GROUP BY
.
Specifiek, als ik'heb een aankopen
tabel die er als volgt uitziet:
SELECT * FROM purchases;
Mijn uitvoer:
id | klant | totaal ---+----------+------ 1: Joe: 5 2: Sally: 3 3: Joe: 2 4 | Sally | 1
Ik wil een query uitvoeren voor het id
van de grootste aankoop (totaal
) gedaan door elke klant
. Zoiets als dit:
SELECT FIRST(id), customer, FIRST(total)
FROM purchases
GROUP BY customer
ORDER BY total DESC;
Uitgang verwacht:
EERSTE(id) | klant | EERSTE(totaal) ----------+----------+------------- 1: Joe: 5 2 | Sally | 3
In PostgreSQL is dit gewoonlijk simpeler en sneller (meer performance optimalisatie hieronder):
SELECT DISTINCT ON (klant)
id, klant, totaal
VAN aankopen
ORDER BY customer, total DESC, id;
Of korter (als niet zo duidelijk) met rangtelwoorden van uitvoerkolommen:
SELECT DISTINCT ON (2)
id, customer, total
FROM purchases
ORDER BY 2, 3 DESC, 1;
Als totaal
NULL kan zijn (kan in beide gevallen geen kwaad, maar je'zult bestaande indexen willen matchen):
...
ORDER BY customer, total DESC NULLS LAST, id;
###Major points
- **`DISTINCT ON`**][1] is een PostgreSQL uitbreiding van de standaard (waar alleen `DISTINCT` op de hele `SELECT` lijst is gedefinieerd).
- Geef een willekeurig aantal expressies op in de `DISTINCT ON` clausule, de gecombineerde rij waarde definieert duplicaten. [De handleiding:][2]
> Vanzelfsprekend worden twee rijen als verschillend beschouwd als ze verschillen in ten minste
> één kolomwaarde verschillen. **Null waarden worden in deze vergelijking als gelijk beschouwd.**
Vetgedrukte nadruk van mij.
- `DISTINCT ON` kan gecombineerd worden met **`ORDER BY`**. Voorlopende expressies moeten overeenkomen met voorlopende `DISTINCT ON` expressies in dezelfde volgorde. Je kunt *extra* expressies toevoegen aan `ORDER BY` om een bepaalde rij te kiezen uit elke groep van peers. Ik voegde `id` toe als laatste item om de gelijke rij te breken:
*"Kies de rij met de kleinste `id` van elke groep met het hoogste `totaal`."*
Om de resultaten te rangschikken op een manier die niet overeenkomt met de sorteervolgorde die de eerste per groep bepaalt, kun je bovenstaande query in een outer query met een andere `ORDER BY` nesten. Zoals:
- https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by/9796104#9796104
- Als `totaal` NULL kan zijn, wil je *waarschijnlijk* de rij met de grootste niet-nul waarde. Voeg **`NULLS LAST`** toe zoals gedemonstreerd. Details:
- https://stackoverflow.com/questions/9510509/postgresql-sort-by-datetime-asc-null-first/9511492#9511492
- **De `SELECT` lijst** wordt op geen enkele manier beperkt door expressies in `DISTINCT ON` of `ORDER BY`. (Niet nodig in het eenvoudige geval hierboven):
- U *hoeft* geen van de uitdrukkingen in `DISTINCT ON` of `ORDER BY` op te nemen.
- Je *kunt* elke andere expressie in de `SELECT` lijst opnemen. Dit is nuttig voor het vervangen van veel complexere queries met subqueries en aggregate / window functies.
- Ik heb getest met Postgres versies 8.3 - 12. Maar de functie is er in ieder geval al sinds versie 7.1, dus eigenlijk altijd.
##Index
De *perfecte* index voor de bovenstaande query zou een [multi-column index][3] zijn die alle drie de kolommen in dezelfde volgorde en met dezelfde sorteervolgorde omvat:
CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);
Dat is misschien te gespecialiseerd. Maar gebruik het als leesprestatie voor de specifieke query cruciaal is. Als je DESC NULLS LAST
in de query hebt, gebruik dan hetzelfde in de index, zodat de sorteervolgorde overeenkomt en de index toepasbaar is.
Weeg kosten en baten af voordat je op maat gemaakte indexen maakt voor elke query. Het potentieel van bovenstaande index hangt grotendeels af van datadistributie. De index wordt gebruikt omdat hij voorgesorteerde gegevens levert. In Postgres 9.2 of later kan de query ook profiteren van een index only scan als de index kleiner is dan de onderliggende tabel. De index moet wel in zijn geheel gescand worden.
klant
), is dit zeer efficiënt. Zelfs nog efficiënter als je toch al gesorteerde uitvoer nodig hebt. Het voordeel krimpt met een groeiend aantal rijen per klant.work_mem
om de betreffende sorteerstap in RAM te verwerken en niet naar schijf te morsen. Maar over het algemeen kan het instellen van work_mem
te hoog nadelige effecten hebben. Overweeg SET LOCAL
voor uitzonderlijk grote queries. Zoek uit hoeveel je nodig hebt met EXPLAIN ANALYZE
. Vermelding van "Disk:" in de sorteerstap geeft aan dat er meer nodig is:klant
) zou een losse index scan (a.k.a. "skip scan") (veel) efficiënter zijn, maar dat'is niet geïmplementeerd tot Postgres 12. (Een implementatie voor index-only scans is in ontwikkeling voor Postgres 13. Zie hier en hier).Ik had hier een eenvoudige benchmark die inmiddels verouderd is. Ik heb het vervangen door een gedetailleerde benchmark in dit afzonderlijke antwoord.
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
Maar je moet logica toevoegen om de gelijke standen te breken:
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
De oplossing is niet erg efficiënt, zoals Erwin heeft opgemerkt, vanwege de aanwezigheid van SubQ's
select * from purchases p1 where total in
(select max(total) from purchases where p1.customer=customer) order by total desc;