Valitse ensimmäinen rivi jokaisesta GROUP BY -ryhmästä?

Kuten otsikosta käy ilmi, haluaisin valita ensimmäisen rivin jokaisesta GROUP BY -ryhmällä ryhmitellystä rivisarjasta.

Tarkemmin sanottuna, jos minulla on ostot-taulukko, joka näyttää tältä:

SELECT * FROM purchases;

Tulokseni:

id | asiakas | yhteensä
---+----------+------
 1 | Joe | 5
 2 | Sally | 3
 3 | Joe | 2
 4 | Sally | 1

Haluaisin tiedustella kunkin asiakkaan tekemän suurimman ostoksen (yhteensä) tunnusta. Jotain tällaista:

SELECT FIRST(id), customer, FIRST(total)
FROM  purchases
GROUP BY customer
ORDER BY total DESC;

Odotetut tulokset:

FIRST(id) | asiakas | FIRST(total)
----------+----------+-------------
        1 | Joe | 5
        2 | Sally | 3
DineshDB
Muokattu kysymysT00 maaliskuu 2018 в 8:18
Ohjelmointi
sql postgresql sqlite group-by greatest-n-per-group
T00 syyskuu 2010 в 1:23
23 näkymät

PostgreSQL:ssä tämä on yleensä yksinkertaisempaa ja nopeampaa (lisää suorituskyvyn optimoinnista jäljempänä):

SELECT DISTINCT ON (asiakas)
       id, asiakas, yhteensä
FROM ostot
ORDER BY asiakas, yhteensä DESC, id;
Tai lyhyempi (joskaan ei yhtä selkeä) tulossarakkeiden järjestysluvuilla: 
SELECT DISTINCT ON (2)
       id, customer, total
FROM   purchases
ORDER  BY 2, 3 DESC, 1;

Jos total voi olla NULL (ei haittaa kummallakaan tavalla, mutta haluat sovittaa olemassa olevat indeksit yhteen):

...
ORDER BY asiakas, yhteensä DESC NULLS LAST, id;
###Major points - [**``DISTINCT ON`**][1] on PostgreSQL:n laajennus standardista (jossa määritellään vain `DISTINCT` koko `SELECT`-listalle). - Listaa `DISTINCT ON`-lausekkeessa mikä tahansa määrä lausekkeita, yhdistetty riviarvo määrittelee duplikaatit. [Käsikirja:][2] > On selvää, että kaksi riviä pidetään erillisinä, jos ne eroavat toisistaan vähintään > yhdessä sarakearvossa. **Tyhjät arvot katsotaan tässä vertailussa yhtä suuriksi.** Lihavointi minun. - `DISTINCT ON` voidaan yhdistää **`ORDER BY`**:n kanssa. Johtavien lausekkeiden on vastattava johtavia `DISTINCT ON`-lausekkeita samassa järjestyksessä. Voit lisätä `ORDER BY` -lausekkeeseen *lisälausekkeita* valitaksesi tietyn rivin kustakin vertaisryhmästä. Lisäsin `id` viimeiseksi eräksi tasapelien katkaisemiseksi: *"Valitse rivi, jolla on pienin `id` jokaisesta ryhmästä, jolla on suurin `total`."* Jos haluat järjestää tulokset tavalla, joka on eri mieltä kuin lajittelujärjestys, joka määrittää ensimmäisen per ryhmä, voit sijoittaa yllä olevan kyselyn ulkoiseen kyselyyn, jossa on toinen `ORDER BY`. Kuten esim: - https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by/9796104#9796104 - Jos `total` voi olla NULL, haluat todennäköisesti rivin, jolla on suurin ei-nolla-arvo. Lisää **`NULLS LAST`** kuten kuvassa. Yksityiskohtia: - https://stackoverflow.com/questions/9510509/postgresql-sort-by-datetime-asc-null-first/9511492#9511492 - `SELECT`-listaa** eivät rajoita millään tavalla `DISTINCT ON`- tai `ORDER BY`-lausekkeet. (Ei tarvita yllä olevassa yksinkertaisessa tapauksessa): - Sinun *ei tarvitse* sisällyttää mitään `DISTINCT ON`- tai `ORDER BY`-lausekkeita. - Voit *voi* sisällyttää minkä tahansa muun lausekkeen `SELECT`-luetteloon. Tämä on hyödyllistä, kun korvataan paljon monimutkaisempia kyselyjä alikyselyillä ja aggregaatti-/ikkunafunktioilla. - Testasin Postgresin versioilla 8.3 - 12. Ominaisuus on kuitenkin ollut käytössä ainakin versiosta 7.1 lähtien, eli periaatteessa aina. ##Index *Täydellinen* indeksi edellä mainittuun kyselyyn olisi [monisarakkeinen indeksi][3], joka kattaa kaikki kolme saraketta samassa järjestyksessä ja samassa lajittelujärjestyksessä: 
CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);

Saattaa olla liian erikoistunut. Käytä sitä kuitenkin, jos lukusuorituskyky kyseisessä kyselyssä on ratkaisevan tärkeää. Jos kyselyssä on DESC NULLS LAST, käytä samaa indeksissä, jotta lajittelujärjestys täsmää ja indeksi on sovellettavissa.

Tehokkuus / suorituskyvyn optimointi

Punnitse kustannuksia ja hyötyjä ennen kuin luot räätälöityjä indeksejä kutakin kyselyä varten. Edellä mainitun indeksin mahdollisuudet riippuvat suurelta osin tiedon jakelusta. Indeksiä käytetään, koska se toimittaa valmiiksi lajiteltua dataa. Postgres 9.2:ssa tai uudemmissa versioissa kysely voi hyötyä myös index only scan:stä, jos indeksi on pienempi kuin taustalla oleva taulukko. Indeksi on kuitenkin skannattava kokonaisuudessaan.

Erwin Brandstetter
Muokattu vastausta T22 elokuu 2019 в 7:29
Kommentit (7)
Ratkaisu

Oracle 9.2+ (ei 8i+, kuten alun perin ilmoitettiin), 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

Tuettu kaikissa tietokannoissa:

Mutta sinun on lisättävä logiikka tasapelien katkaisemiseksi:

  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
Muokattu vastausta T33 joulukuu 2017 в 6:42
Kommentit (7)

Ratkaisu ei ole kovin tehokas, kuten Erwin huomautti, koska SubQ:t ovat läsnä.

select * from purchases p1 where total in
(select max(total) from purchases where p1.customer=customer) order by total desc;
user2407394
Muokattu vastausta T00 kesäkuu 2013 в 8:39
Kommentit (4)