Vælg den første række i hver GROUP BY-gruppe?

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
DineshDB
Redigeret spørgsmål 2018ør marts 2018 в 8:18
Programmering
sql postgresql sqlite group-by greatest-n-per-group
2010ør september 2010 в 1:23
24 visninger

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.

Effektivitet / optimering af ydeevne

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.

Erwin Brandstetter
Redigeret svar 2019ør august 2019 в 7:29
Kommentarer (7)
Løsning

På Oracle 9.2+ (ikke 8i+ som oprindeligt angivet), 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

Understøttet af alle databaser:

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
mach128x
Redigeret svar 2017ør december 2017 в 6:42
Kommentarer (7)

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;
user2407394
Redigeret svar 2013ør juni 2013 в 8:39
Kommentarer (4)