Selecteer eerste rij in elke GROUP BY groep?

Zoals de titel suggereert, zou ik graag de eerste rij selecteren van elke set rijen gegroepeerd met een GROUP BY.

Specifiek, als ik'heb een aankopen tabel die er als volgt uitziet:

SELECT * FROM purchases;

Mijn uitvoer:

id | klant | totaal
---+----------+------
 1: Joe: 5
 2: Sally: 3
 3: Joe: 2
 4 | Sally | 1

Ik wil een query uitvoeren voor het id van de grootste aankoop (totaal) gedaan door elke klant. Zoiets als dit:

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

Uitgang verwacht:

EERSTE(id) | klant | EERSTE(totaal)
----------+----------+-------------
        1: Joe: 5
        2 | Sally | 3
DineshDB
Bewerkte vraag 26ste0 maart 2018 в 8:18
Programmering
sql postgresql sqlite group-by greatest-n-per-group
27ste0 september 2010 в 1:23
21 views

In PostgreSQL is dit gewoonlijk simpeler en sneller (meer performance optimalisatie hieronder):

SELECT DISTINCT ON (klant)
       id, klant, totaal
VAN aankopen
ORDER BY customer, total DESC, id;
Of korter (als niet zo duidelijk) met rangtelwoorden van uitvoerkolommen: 
SELECT DISTINCT ON (2)
       id, customer, total
FROM   purchases
ORDER  BY 2, 3 DESC, 1;

Als totaal NULL kan zijn (kan in beide gevallen geen kwaad, maar je'zult bestaande indexen willen matchen):

...
ORDER BY customer, total DESC NULLS LAST, id;
###Major points - **`DISTINCT ON`**][1] is een PostgreSQL uitbreiding van de standaard (waar alleen `DISTINCT` op de hele `SELECT` lijst is gedefinieerd). - Geef een willekeurig aantal expressies op in de `DISTINCT ON` clausule, de gecombineerde rij waarde definieert duplicaten. [De handleiding:][2] > Vanzelfsprekend worden twee rijen als verschillend beschouwd als ze verschillen in ten minste > één kolomwaarde verschillen. **Null waarden worden in deze vergelijking als gelijk beschouwd.** Vetgedrukte nadruk van mij. - `DISTINCT ON` kan gecombineerd worden met **`ORDER BY`**. Voorlopende expressies moeten overeenkomen met voorlopende `DISTINCT ON` expressies in dezelfde volgorde. Je kunt *extra* expressies toevoegen aan `ORDER BY` om een bepaalde rij te kiezen uit elke groep van peers. Ik voegde `id` toe als laatste item om de gelijke rij te breken: *"Kies de rij met de kleinste `id` van elke groep met het hoogste `totaal`."* Om de resultaten te rangschikken op een manier die niet overeenkomt met de sorteervolgorde die de eerste per groep bepaalt, kun je bovenstaande query in een outer query met een andere `ORDER BY` nesten. Zoals: - https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by/9796104#9796104 - Als `totaal` NULL kan zijn, wil je *waarschijnlijk* de rij met de grootste niet-nul waarde. Voeg **`NULLS LAST`** toe zoals gedemonstreerd. Details: - https://stackoverflow.com/questions/9510509/postgresql-sort-by-datetime-asc-null-first/9511492#9511492 - **De `SELECT` lijst** wordt op geen enkele manier beperkt door expressies in `DISTINCT ON` of `ORDER BY`. (Niet nodig in het eenvoudige geval hierboven): - U *hoeft* geen van de uitdrukkingen in `DISTINCT ON` of `ORDER BY` op te nemen. - Je *kunt* elke andere expressie in de `SELECT` lijst opnemen. Dit is nuttig voor het vervangen van veel complexere queries met subqueries en aggregate / window functies. - Ik heb getest met Postgres versies 8.3 - 12. Maar de functie is er in ieder geval al sinds versie 7.1, dus eigenlijk altijd. ##Index De *perfecte* index voor de bovenstaande query zou een [multi-column index][3] zijn die alle drie de kolommen in dezelfde volgorde en met dezelfde sorteervolgorde omvat: 
CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);

Dat is misschien te gespecialiseerd. Maar gebruik het als leesprestatie voor de specifieke query cruciaal is. Als je DESC NULLS LAST in de query hebt, gebruik dan hetzelfde in de index, zodat de sorteervolgorde overeenkomt en de index toepasbaar is.

Effectiviteit / Prestatie optimalisatie

Weeg kosten en baten af voordat je op maat gemaakte indexen maakt voor elke query. Het potentieel van bovenstaande index hangt grotendeels af van datadistributie. De index wordt gebruikt omdat hij voorgesorteerde gegevens levert. In Postgres 9.2 of later kan de query ook profiteren van een index only scan als de index kleiner is dan de onderliggende tabel. De index moet wel in zijn geheel gescand worden.

Erwin Brandstetter
Bewerkt antwoord 28ste2 augustus 2019 в 7:29
Commentaren (7)
Oplossing

Op Oracle 9.2+ (niet 8i+ zoals oorspronkelijk vermeld), 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

Ondersteund door elke database:

Maar je moet logica toevoegen om de gelijke standen te breken:

  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
Bewerkt antwoord 7de3 december 2017 в 6:42
Commentaren (7)

De oplossing is niet erg efficiënt, zoals Erwin heeft opgemerkt, vanwege de aanwezigheid van SubQ's

select * from purchases p1 where total in
(select max(total) from purchases where p1.customer=customer) order by total desc;
user2407394
Bewerkt antwoord 17de0 juni 2013 в 8:39
Commentaren (4)