Nagu pealkiri ütleb, ma'tahaksin valida iga GROUP BY
-ga grupeeritud ridade kogumi esimese rea.
Täpsemalt, kui mul on tabel purchases
, mis näeb välja selline:
SELECT * FROM purchases;
Minu väljund:
id | klient | kokku ---+----------+------ 1 | Joe | 5 2 | Sally | 3 3 | Joe | 2 4 | Sally | 1
Tahaksin küsida iga kliendi suurima ostu (kokku) "id". Midagi sellist:
SELECT FIRST(id), customer, FIRST(total)
FROM purchases
GROUP BY customer
ORDER BY total DESC;
Oodatav väljund:
FIRST(id) | klient | FIRST(kokku) ----------+----------+------------- 1 | Joe | 5 2 | Sally | 3
PostgreSQLis on see tavaliselt lihtsam ja kiirem (rohkem jõudluse optimeerimist allpool):
SELECT DISTINCT ON (klient)
id, klient, kogusumma
FROM ostud
ORDER BY customer, total DESC, id;
Või lühem (kui mitte nii selge) väljundveergude järjekorranumbritega:
SELECT DISTINCT ON (2)
id, customer, total
FROM purchases
ORDER BY 2, 3 DESC, 1;
Kui total
võib olla NULL (ei'ei tee kahju kummalgi viisil, kuid sa'tahad sobitada olemasolevaid indekseid):
...
ORDER BY customer, total DESC NULLS LAST, id;
###Major points
- [**``DISTINCT ON`**][1] on PostgreSQLi laiendus standardist (kus on defineeritud ainult `DISTINCT` kogu `SELECT` loendis).
- Loetlege `DISTINCT ON`-klauslis ükskõik kui palju väljendeid, kombineeritud rea väärtus määratleb duplikaadid. [Käsiraamat:][2]
> Ilmselt loetakse kaks rida erinevateks, kui nad erinevad vähemalt järgmise poolest
> ühes veeru väärtuses. **Null väärtusi loetakse selles võrdluses võrdseks.**
Rõhk rõhutatud minu poolt.
- `DISTINCT ON` saab kombineerida **`ORDER BY`**. Juhtivad väljendid peavad vastama juhtivatele `DISTINCT ON` väljenditele samas järjekorras. Saate lisada `ORDER BY`-le *täiendavaid* väljendeid, et valida igast võrdsete rühmast konkreetne rida. Ma lisasin `id` viimase elemendina, et katkestada sidemeid:
*"Valige väikseima `id`ga rida igast grupist, millel on suurim `summa`."*
Et järjestada tulemusi viisil, mis ei ole kooskõlas sorteerimise järjekorraga, mis määrab esimese iga rühma kohta, võite pesitseda ülaltoodud päringu välisse päringusse teise `ORDER BY`-ga. Näiteks:
- https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by/9796104#9796104
- Kui `total` võib olla NULL, siis *tõenäoliselt* tahate kõige suurema mittetäieliku väärtusega rida. Lisage **`NULLS LAST`** nagu näidatud. Üksikasjad:
- https://stackoverflow.com/questions/9510509/postgresql-sort-by-datetime-asc-null-first/9511492#9511492
- **SELECT'i nimekirja** ei piira kuidagi väljendid `DISTINCT ON` või `ORDER BY`. (Ülaltoodud lihtsal juhul ei ole seda vaja):
- Te *ei pea* lisama ühtegi väljendit `DISTINCT ON` või `ORDER BY`.
- Sa *võid* lisada `SELECT` loendisse mis tahes muu väljendi. See on abiks palju keerulisemate päringute asendamisel alampäringute ja koond-/aknafunktsioonidega.
- Testisin Postgres'i versioonidega 8.3 - 12. Aga see funktsioon on olnud olemas vähemalt alates versioonist 7.1, seega põhimõtteliselt alati.
##Index
*täiuslik* indeks ülaltoodud päringu jaoks oleks [mitme veeru indeks][3], mis hõlmab kõiki kolme veergu sobivas järjestuses ja sobivas sorteerimisjärjekorras:
CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);
Võib olla liiga spetsialiseeritud. Kuid kasutage seda, kui lugemisjõudlus konkreetse päringu puhul on oluline. Kui teil on päringus DESC NULLS LAST
, kasutage seda ka indeksis, et sorteerimisjärjekord sobiks ja indeks oleks kohaldatav.
Kaaluge kulu ja kasu enne iga päringu jaoks kohandatud indeksite loomist. Ülaltoodud indeksi potentsiaal sõltub suuresti andmeväljastusest. Indeksit kasutatakse, sest see tarnib eelnevalt sorteeritud andmeid. Postgres 9.2 või hilisemas versioonis võib päring saada kasu ka index only scan, kui indeks on väiksem kui aluseks olev tabel. Indeks tuleb siiski skaneerida tervikuna.
klient
) on see väga tõhus. Seda enam, kui teil on niikuinii vaja sorteeritud väljundit. Kasu väheneb, kui ridade arv kliendi kohta kasvab.work_mem
, et töödelda asjaomast sorteerimissammu RAMis ja mitte kettale kanda. Kuid üldiselt võib work_mem
i liiga suureks seadmisel olla negatiivne mõju. Kaaluge SET LOCAL
erakordselt suurte päringute puhul. Uuri välja, kui palju sul on vaja EXPLAIN ANALYZE
abil. Märge "Disk:" sorteerimissammul viitab vajadusele suurema hulga järele:customer
) oleks loose index scan (ka "skip scan") (palju) tõhusam, kuid seda ei ole Postgres 12-ga rakendatud. (Postgres 13 jaoks on väljatöötamisel ainult indeksiga skaneerimise rakendus. Vt siin ja siin).Mul oli siin lihtne võrdlusnäitaja, mis on praeguseks vananenud. Ma asendasin selle detailitud võrdlusuuringuga selles eraldi vastuses.
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
Aga sa pead lisama loogika sidemete katkestamiseks:
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
Nagu Erwin märkis, ei ole see lahendus väga tõhus, sest SubQ-d on olemas.
select * from purchases p1 where total in
(select max(total) from purchases where p1.customer=customer) order by total desc;