Som titlen antyder, vil jeg gerne vælge den første række i hvert sæt af rækker grupperet med en GROUP BY
.
Specifikt, hvis jeg'har en køb
tabel, der ser sådan ud:
SELECT * FROM purchases;
Mit output:
id | customer | total ---+----------+------ 1 | Joe | 5 2 | Sally | 3 3 | Joe | 2 4 | Sally | 1
Jeg vil gerne søge efter id
for det største køb (total
) foretaget af hver enkelt kunde
. Noget som dette:
SELECT FIRST(id), customer, FIRST(total)
FROM purchases
GROUP BY customer
ORDER BY total DESC;
Forventet output:
FIRST(id) | kunde | FIRST(total) ----------+----------+------------- 1 | Joe | 5 2 | Sally | 3
I PostgreSQL er dette typisk enklere og hurtigere (mere om optimering af ydeevne nedenfor):
SELECT DISTINCT ON (customer)
id, kunde, total
FROM køb
ORDER BY customer, total DESC, id;
Eller kortere (om end ikke så klart) med ordinalnumre på udgangsspalterne:
SELECT DISTINCT ON (2)
id, customer, total
FROM purchases
ORDER BY 2, 3 DESC, 1;
Hvis total
kan være NULL (det skader ikke på nogen måde, men du vil gerne matche eksisterende indekser):
...
ORDER BY customer, total DESC NULLS LAST, id;
####Større punkter
- **`DISTINCT ON`**][1] er en PostgreSQL udvidelse af standarden (hvor kun `DISTINCT` på hele `SELECT` listen er defineret).
- Liste et vilkårligt antal udtryk i `DISTINCT ON`-klausulen, den kombinerede rækkeværdi definerer dubletter. [Håndbogen:][2]
> Det er klart, at to rækker anses for at være forskellige, hvis de adskiller sig i mindst
> én kolonneværdi. **Nulværdier betragtes som lige i denne sammenligning.**
Fed fremhævning er min.
- `DISTINCT ON` kan kombineres med **`ORDER BY`**. Ledende udtryk skal matche ledende `DISTINCT ON`-udtryk i samme rækkefølge. Du kan tilføje *tillægsudtryk* til `ORDER BY` for at vælge en bestemt række fra hver gruppe af jævnaldrende. Jeg tilføjede `id` som sidste punkt for at bryde lighed:
*"Vælg rækken med det mindste `id` fra hver gruppe, der deler den højeste `total`."*
Hvis du vil ordne resultaterne på en måde, der er uenig med den sorteringsrækkefølge, der bestemmer den første pr. gruppe, kan du indlejre ovenstående forespørgsel i en ydre forespørgsel med en anden `ORDER BY`. Som f.eks:
- https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by/9796104#9796104
- Hvis `total` kan være NULL, vil du *højst sandsynligt* have den række med den største ikke-null-værdi. Tilføj **`NULLS LAST`** som vist. Detaljer:
- https://stackoverflow.com/questions/9510509/postgresql-sort-by-datetime-asc-null-first/9511492#9511492
- **Listen `SELECT`** er ikke begrænset af udtryk i `DISTINCT ON` eller `ORDER BY` på nogen måde. (Ikke nødvendigt i det enkle tilfælde ovenfor):
- Du *behøver ikke* at inkludere nogen af udtrykkene i `DISTINCT ON` eller `ORDER BY`.
- Du *kan* inkludere ethvert andet udtryk i `SELECT`-listen. Dette er afgørende for at erstatte meget mere komplekse forespørgsler med underafspørgsler og aggregerede/vinduesfunktioner.
- Jeg har testet med Postgres-versioner 8.3 - 12. Men funktionen har været der i hvert fald siden version 7.1, så stort set altid.
##Index
Det *perfekte* indeks til ovenstående forespørgsel ville være et [multi-column index][3], der dækker alle tre kolonner i matchende rækkefølge og med matchende sorteringsrækkefølge:
CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);
Kan være for specialiseret. Men brug det, hvis læseydelsen for den pågældende forespørgsel er afgørende. Hvis du har DESC NULLS LAST
i forespørgslen, skal du bruge det samme i indekset, så sorteringsrækkefølgen passer, og indekset kan anvendes.
Afvej omkostninger og fordele, før du opretter skræddersyede indekser for hver forespørgsel. Potentialet af ovenstående indeks afhænger i høj grad af datadistribution. Indekset anvendes, fordi det leverer forsorterede data. I Postgres 9.2 eller senere kan forespørgslen også drage fordel af en index only scan, hvis indekset er mindre end den underliggende tabel. Indekset skal dog scannes i sin helhed.
customer
) er dette meget effektivt. Endnu mere, hvis du alligevel har brug for sorteret output. Fordelen skrumper med et stigende antal rækker pr. kunde.work_mem
til at behandle det involverede sorteringstrin i RAM og ikke spilde til disk. Men generelt kan det have negative virkninger at sætte work_mem
for højt. Overvej SET LOCAL
for usædvanligt store forespørgsler. Find ud af, hvor meget du har brug for med EXPLAIN ANALYZE
. Omtalen af "Disk:" i sorteringstrinnet indikerer, at der er behov for mere:customer
) ville en loose index scan (a.k.a. "skip scan") være (meget) mere effektiv, men det'er ikke implementeret op til Postgres 12. (En implementering til index-only scanninger er under udvikling til Postgres 13. Se her og her).Jeg havde en simpel benchmark her, som nu er forældet. Jeg har erstattet den med en detaljeret benchmark i dette separate svar.
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
Men du skal tilføje logik til at bryde uafgjort:
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
Løsningen er ikke særlig effektiv, som Erwin påpeger, på grund af tilstedeværelsen af SubQs
select * from purchases p1 where total in
(select max(total) from purchases where p1.customer=customer) order by total desc;