Pasirinkite pirmąją eilutę kiekvienoje GROUP BY grupėje?

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
DineshDB
Redaguotas klausimas priešpietr kovas 2018 в 8:18
Programavimas
sql postgresql sqlite group-by greatest-n-per-group
priešpietr rugsėjis 2010 в 1:23
22 peržiūros

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.

Efektyvumas / našumo optimizavimas

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.

Erwin Brandstetter
Redaguotas atsakymas priešpietr rugpjūtis 2019 в 7:29
Komentarai (7)
Sprendimas

"Oracle 9.2+" (ne 8i+, kaip buvo nurodyta iš pradžių), "SQL Server 2005+", "PostgreSQL 8.4+", DB2, "Firebird 3.0+", "Teradata", "Sybase", "Vertica":

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

Palaikoma bet kuri duomenų bazė:

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
mach128x
Redaguotas atsakymas priešpietr gruodis 2017 в 6:42
Komentarai (7)

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;
user2407394
Redaguotas atsakymas popietr birželis 2013 в 8:39
Komentarai (4)