Sélectionner la première ligne dans chaque groupe GROUP BY ?

Comme le titre le suggère, j&#8217aimerais sélectionner la première ligne de chaque ensemble de lignes regroupées avec un GROUP BY.

Plus précisément, si j'ai une table purchases qui ressemble à ceci :

SELECT * FROM purchases;

Mon résultat:

id | client | total
---+----------+------
 1 | Joe | 5
 2 | Sally | 3
 3 | Joe | 2
 4 | Sally | 1

J&#8217aimerais demander l&#8217identifiant du plus gros achat (total) effectué par chaque client. Quelque chose comme ceci :

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

Sortie attendue:

FIRST(id) | client | FIRST(total)
----------+----------+-------------
        1 | Joe | 5
        2 | Sally | 3
DineshDB
Question modifiée 26 mars 2018 в 8:18
Programmation
sql postgresql sqlite group-by greatest-n-per-group
27 septembre 2010 в 1:23
25 vues

En PostgreSQL, cela est typiquement simple et plus rapide (plus d'optimisation des performances ci-dessous) :

SELECT DISTINCT ON (client)
       id, client, total
FROM achats
ORDER BY customer, total DESC, id;
Ou plus court (si pas aussi clair) avec les numéros ordinaux des colonnes de sortie : 
SELECT DISTINCT ON (2)
       id, customer, total
FROM   purchases
ORDER  BY 2, 3 DESC, 1;

Si total peut être NULL (ça ne fera pas de mal de toute façon, mais vous voudrez faire correspondre les index existants) :

...
ORDER BY customer, total DESC NULLS LAST, id;
###Points majeurs - **`DISTINCT ON`**][1] est une extension PostgreSQL du standard (où seul `DISTINCT` sur toute la liste `SELECT` est défini). - En listant un nombre quelconque d'expressions dans la clause `DISTINCT ON`, la valeur combinée des lignes définit les doublons. [Le manuel :] [2] > Évidemment, deux lignes sont considérées comme distinctes si elles diffèrent par au moins une valeur de colonne. > une valeur de colonne. **Les valeurs nulles sont considérées comme égales dans cette comparaison.** C'est moi qui souligne en gras. - `DISTINCT ON` peut être combiné avec **`ORDER BY`**. Les premières expressions doivent correspondre aux premières expressions `DISTINCT ON` dans le même ordre. Vous pouvez ajouter des expressions *additionnelles* à `ORDER BY` pour choisir une ligne particulière dans chaque groupe de pairs. J'ai ajouté `id` comme dernier élément pour briser l'égalité : *"Choisissez la ligne avec le plus petit `id` de chaque groupe partageant le plus grand `total`. "* Pour ordonner les résultats d'une manière qui ne soit pas en accord avec l'ordre de tri déterminant le premier par groupe, vous pouvez imbriquer la requête ci-dessus dans une requête externe avec un autre `ORDER BY`. Par exemple : - https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by/9796104#9796104 - Si `total` peut être NULL, vous voulez *surtout* la ligne avec la plus grande valeur non-nulle. Ajoutez **`NULLS LAST`** comme démontré. Détails : - https://stackoverflow.com/questions/9510509/postgresql-sort-by-datetime-asc-null-first/9511492#9511492 - La liste `SELECT` n'est en aucun cas contrainte par les expressions de `DISTINCT ON` ou `ORDER BY`. (Pas nécessaire dans le cas simple ci-dessus) : - Vous *n'êtes pas obligé* d'inclure l'une des expressions de `DISTINCT ON` ou `ORDER BY`. - Vous *pouvez* inclure n'importe quelle autre expression dans la liste `SELECT`. Cela permet de remplacer des requêtes beaucoup plus complexes par des sous-requêtes et des fonctions d'agrégat / fenêtre. - J'ai testé avec les versions 8.3 - 12 de Postgres. Mais cette fonctionnalité existe au moins depuis la version 7.1, donc en principe toujours. ##Index L'index *parfait* pour la requête ci-dessus serait un [index multi-colonnes][3] couvrant les trois colonnes dans la même séquence et avec le même ordre de tri : 
CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);

C'est peut-être trop spécialisé. Mais utilisez-le si les performances de lecture pour une requête particulière sont cruciales. Si vous avez DESC NULLS LAST dans la requête, utilisez la même chose dans l'index pour que l'ordre de tri corresponde et que l'index soit applicable.

Efficacité / Optimisation des performances

Pesez les coûts et les avantages avant de créer des index personnalisés pour chaque requête. Le potentiel de l'index ci-dessus dépend largement de la distribution des données. L'index est utilisé car il fournit des données pré-triées. Dans Postgres 9.2 ou plus, la requête peut également bénéficier d'un [scan de l'index seulement][4] si l'index est plus petit que la table sous-jacente. L'index doit cependant être scanné dans son intégralité.

Erwin Brandstetter
Réponse modifiée 28 août 2019 в 7:29
Commentaires (7)
Solution

Sur Oracle 9.2+ (et non 8i+ comme indiqué initialement), 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

Pris en charge par n'importe quelle base de données :

Mais vous devez ajouter une logique pour briser les égalités :

  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
Réponse modifiée 7 décembre 2017 в 6:42
Commentaires (7)

La solution n'est pas très efficace, comme l'a souligné Erwin, en raison de la présence de SubQs.

select * from purchases p1 where total in
(select max(total) from purchases where p1.customer=customer) order by total desc;
user2407394
Réponse modifiée 17 juin 2013 в 8:39
Commentaires (4)