Kaip matyti iš pavadinimo, norėčiau išrinkti pirmąją eilutę iš kiekvieno eilučių rinkinio, sugrupuotų pagal GROUP BY
.
Konkrečiai, jei turiu lentelę purchases
, kuri atrodo taip:
SELECT * FROM purchases;
Mano išvestis:
id | klientas | iš viso ---+----------+------ 1 | Joe | 5 2 | Sally | 3 3 | Joe | 2 4 | Sally | 1
Norėčiau pateikti užklausą apie kiekvieno kliento didžiausio pirkinio (iš viso) id
. Kažkas panašaus į tai:
SELECT FIRST(id), customer, FIRST(total)
FROM purchases
GROUP BY customer
ORDER BY total DESC;
Prognozuojamas išvesties rezultatas:
FIRST(id) | customer | FIRST(total) ----------+----------+------------- 1 | Joe | 5 2 | Sally | 3
Naudojant PostgreSQL tai paprastai yra pročiau ir greičiau (daugiau našumo optimizavimo toliau):
SELECT DISTINCT ON (customer)
id, klientas, iš viso
FROM pirkimai
ORDER BY customer, total DESC, id;
Arba trumpiau (jei ne taip aišku) su išvesties stulpelių eilės numeriais:
SELECT DISTINCT ON (2)
id, customer, total
FROM purchases
ORDER BY 2, 3 DESC, 1;
Jei total
gali būti NULL (abiem atvejais tai nepakenks, bet norėsite suderinti esamus indeksus):
...
ORDER BY customer, total DESC NULLS LAST, id;
####Pagrindiniai punktai
- [**`DISTINCT ON`**][1] - tai "PostgreSQL" standarto išplėtimas (kai apibrėžiamas tik `DISTINCT` visame `SELECT` sąraše).
- Sąraše `DISTINCT ON` įrašykite bet kokį skaičių išraiškų, o bendra eilutės reikšmė apibrėžia dublikatus. [Vadovas:][2]
> Akivaizdu, kad dvi eilutės laikomos skirtingomis, jei jos skiriasi bent
> vieno stulpelio verte. **Šiame palyginime nulinės reikšmės laikomos vienodomis**.
Paryškintas paryškinimas mano.
- `DISTINCT ON` galima derinti su **`ORDER BY`**. Pirmaujančios išraiškos turi sutapti su pirmaujančiomis `DISTINCT ON` išraiškomis ta pačia tvarka. Prie `ORDER BY` galima pridėti *papildomų* išraiškų, kad iš kiekvienos bendraamžių grupės būtų galima pasirinkti konkrečią eilutę. Aš pridėjau `id` kaip paskutinį elementą, kad nutraukčiau ryšius:
*"Pasirinkite eilutę su mažiausiu `id` iš kiekvienos grupės, turinčios didžiausią `total`."*
Jei norite, kad rezultatų eiliškumas nesutaptų su rūšiavimo tvarka, nustatančia pirmąją kiekvienai grupei, pirmiau pateiktą užklausą galite įterpti į išorinę užklausą su kitu `ORDER BY`. Pvz:
- https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by/9796104#9796104
- Jei `total` gali būti NULL, greičiausiai norite eilutės su didžiausia nenuline verte. Pridėkite **`NULLS LAST`**, kaip parodyta. Išsami informacija:
- https://stackoverflow.com/questions/9510509/postgresql-sort-by-datetime-asc-null-first/9511492#9511492
- **Sąrašas `SELECT`** niekaip nėra ribojamas `DISTINCT ON` arba `ORDER BY` išraiškomis. (Nereikalingas pirmiau nurodytu paprastu atveju):
- Jūs *neturite įtraukti jokių `DISTINCT ON` arba `ORDER BY` išraiškų.
- Į sąrašą `SELECT` galite įtraukti bet kurią kitą išraišką. Tai padeda pakeisti daug sudėtingesnes užklausas, kuriose naudojamos dalinės užklausos ir suvestinės / lango funkcijos.
- Išbandžiau su 8.3-12 "Postgres" versijomis. Tačiau ši funkcija yra bent jau nuo 7.1 versijos, taigi iš esmės visada.
##Index
Puikus* indeksas pirmiau pateiktai užklausai būtų [kelių stulpelių indeksas][3], apimantis visus tris stulpelius atitinkama seka ir atitinkama rūšiavimo tvarka:
CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);
Gali būti per daug specializuotas. Tačiau naudokite jį, jei konkrečiai užklausai labai svarbus skaitymo našumas. Jei užklausoje nurodėte DESC NULLS LAST
, tą patį naudokite ir indekse, kad rūšiavimo tvarka sutaptų ir indeksas būtų taikomas.
Prieš kurdami kiekvienai užklausai pritaikytus indeksus, pasverkite sąnaudas ir naudą. Minėto indekso potencialas labai priklauso nuo duomenų pasiskirstymo. Indeksas naudojamas todėl, kad pateikia iš anksto surūšiuotus duomenis. Programoje "Postgres 9.2" ar vėlesnėje versijoje užklausai taip pat gali būti naudingas tik indekso nuskaitymas, jei indeksas yra mažesnis už pagrindinę lentelę. Tačiau indeksas turi būti nuskaitytas visas.
klientas
kardinalumas), tai labai efektyvu. Juo labiau, jei vis tiek reikia surūšiuotos išvesties. Nauda mažėja didėjant vieno kliento eilučių skaičiui.work_mem
*]5, kad galėtumėte apdoroti atitinkamą rūšiavimo etapą operatyviojoje atmintyje ir neišpilti jo į diską. Tačiau apskritai nustačius per didelį work_mem
kiekį, tai gali turėti neigiamų padarinių. Ypač didelėms užklausoms apsvarstykite SET LOCAL
galimybę. Sužinokite, kiek jums reikia, naudodami EXPLAIN ANALYZE
. "Disk:*" paminėjimas rūšiavimo žingsnyje rodo, kad reikia daugiau:klientas
kardinalumas), [laisvo indekso nuskaitymas]7 (dar žinomas kaip "praleidimo nuskaitymas") būtų (daug) efektyvesnis, tačiau iki Postgres 12 tai neįgyvendinta. (Įgyvendinimas, skirtas tik indekso nuskaitymui, yra kuriamas Postgres 13. Žr. čia ir čia.)Čia turėjau paprastą lyginamąjį standartą, kuris dabar jau pasenęs. Jį pakeičiau išsamiu lyginamuoju testu šiame atskirame atsakyme.
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
Tačiau reikia pridėti logikos, kad būtų galima nutraukti ryšius:
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
Sprendimas nėra labai veiksmingas, kaip nurodė Erwinas, nes yra SubQs
select * from purchases p1 where total in
(select max(total) from purchases where p1.customer=customer) order by total desc;