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

Question

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

Plus précisément, si j'ai un tableau "achats" qui ressemble à ceci :

\> SELECT \* FROM purchases:
id | customer | total
 1 | Joe      | 5
 2 | Sally    | 3
 3 | Joe      | 2
 4 | Sally    | 1

J'aimerais demander "l'identifiant du plus gros achat effectué par chaque client". Quelque chose comme ceci :

\> SELECT FIRST(id), customer, FIRST(total)
. FROM purchases
. GROUP BY customer
. ORDER BY total DESC;
FIRST(id) | customer | FIRST(total)
        1 | Joe      | 5
        2 | Sally    | 3

Answer 1

Mis à jour en août 2012 avec une version encore plus simple et plus rapide.
Sur PostgreSQL cette solution est plus simple et plus rapide :

SELECT DISTINCT ON (customer)
       id, customer, total
FROM   purchases
ORDER  BY customer, total DESC, id

Ou plus court avec des paramètres de position :

SELECT DISTINCT ON (2)
       id, customer, total
FROM   purchases
ORDER  BY 2, 3 DESC, 1

Principaux points

• DISTINCT ON est une extension PostgreSQL de la norme (où seule l'option DISTINCT dans l'ensemble SELECT est définie).

• DISTINCT ON peuvent être combinés avec ORDER BY . Les principales expressions de ORDER BY doivent correspondre à des expressions dans DISTINCT ON dans cet ordre, mais vous pouvez ajouter des colonnes/expressions supplémentaires pour choisir une ligne particulière dans chaque groupe de pairs. J'ai ajouté id à ORDER BY pour briser l'égalité :
  "Choisissez la rangée avec le plus petit id où il y en a plusieurs qui partagent le plus grand total."

• Pour des exigences plus complexes (non nécessaires dans ce cas simple) :

  • Vous n'ont pas à inclure n'importe quelle colonne / expression utilisée dans ORDER BY ou DISTINCT ON dans le SELECT liste.

  • Vous peut inclure n'importe quelle autre colonne des tables de base dans le fichier SELECT liste. Cela permet de remplacer des requêtes beaucoup plus complexes par des sous-requêtes et des fonctions d'agrégation/fenêtre.

• J'ai testé avec les versions 8.3 - 9.3. Mais cette fonctionnalité existe au moins depuis la version 7.1 (= depuis toujours).

• La première requête avec les noms de colonnes est légèrement plus rapide que la seconde avec les index positionnels. C'est à peine mesurable.

Point de repère

J'ai effectué trois tests avec PostgreSQL 9.1 sur une table réelle de 65579 lignes et des index b-tree à colonne unique sur chacune des trois colonnes concernées et j'ai pris le meilleur des 5 exécutions.
Comparaison de @OMGPonies' première requête ( A ) à ce qui précède DISTINCT ON solution ( B ) :

1. Sélectionnez le tableau entier, ce qui donne 5958 lignes dans ce cas.
   A : Temps d'exécution total : 567.218 ms
   B : Temps d'exécution total : 386.673 ms

2. Condition d'utilisation WHERE customer BETWEEN x AND y ce qui donne 1000 lignes.
   A : Temps d'exécution total : 249.136 ms
   B : Temps d'exécution total : 55.111 ms

3. Sélectionnez un seul client avec WHERE customer = x .
   A : Temps d'exécution total : 0,143 ms
   B : Temps d'exécution total : 0,072 ms

Index

Le site parfait pour la requête ci-dessus serait un indice multi-colonnes couvrant les trois colonnes dans l'ordre correspondant et avec l'ordre de tri correspondant :

CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);

C'est peut-être trop spécialisé pour les applications du monde réel. Si les performances de lecture sont cruciales dans ce cas, utilisez-la, cependant. Même test répété :

1. A : Temps d'exécution total : 277.953 ms
   B : Temps d'exécution total : 193.547 ms

2. A : Temps d'exécution total : 249.796 ms -- indice spécial non utilisé
   B : Temps d'exécution total : 28.679 ms

3. A : Temps d'exécution total : 0,120 ms
   B : Temps d'exécution total : 0,048 ms

Efficacité

Vous devez peser le coût et les avantages avant de créer un index sur mesure pour chaque requête. Le potentiel de l'index ci-dessus dépend largement distribution des données .

L'index est utilisé parce qu'il fournit des données pré-triées, et dans Postgres 9.2 ou ultérieur, la requête peut également bénéficier d'une fonction index only scan si la largeur de l'index est inférieure à celle du tableau sous-jacent. L'index doit cependant être scanné dans son intégralité.

• Pour beaucoup de les clients avec quelques lignes chacune, cette méthode est très efficace, d'autant plus si vous avez de toute façon besoin d'une sortie triée. L'avantage diminue avec le ratio de lignes par client.

• Pour quelques les clients avec beaucoup de rangs, l'équivalent d'un balayage d'index libre serait beaucoup plus efficace, mais ce n'est pas encore implémenté dans Postgres (jusqu'à la version 9.4).
  Il existe des alternatives pour l'émuler. En particulier, si vous disposez d'une table séparée (dérivée) contenant des clients uniques (ce qui est souvent le cas), il existe les possibilités suivantes techniques d'interrogation plus rapides :

  • Optimisation de la requête SQL GROUP BY pour récupérer le dernier enregistrement
  • Requête des N derniers enregistrements liés dans Postgres

Answer 2

Sur Oracle 8i+, SQL Server 2005+, PostgreSQL 8.4+, DB2, Firebird 2.1+, 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 toutes les bases de données :

Mais il faut ajouter la 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

Answer 3

C'est courant le plus grand-n par-groupe problème, qui a déjà été bien testé et qui a des solutions optimisées . Personnellement, je préfère le solution de jointure gauche par Bill Karwin (le article original avec de nombreuses autres solutions ).

Notez que de nombreuses solutions à ce problème courant peuvent étonnamment être trouvées dans l'une des sources les plus officielles, Manuel MySQL ! Voir Exemples de requêtes courantes : : Les lignes contenant le maximum groupé d'une certaine colonne .

Answer 4

Dans Postgres, vous pouvez utiliser array_agg comme ça :

SELECT  customer,
        (array_agg(id ORDER BY total DESC))[1],
        max(total)
FROM purchases
GROUP BY customer

Cela vous donnera le id de l'achat le plus important de chaque client.

Quelques points à noter :

• array_agg est une fonction d'agrégation, elle fonctionne donc avec GROUP BY .
• array_agg vous permet de spécifier un ordre qui ne s'applique qu'à lui-même, de sorte qu'il ne contraint pas la structure de l'ensemble de la requête. Il existe également une syntaxe pour le tri des NULL, si vous avez besoin de faire quelque chose de différent de la valeur par défaut.
• Une fois que nous avons construit le tableau, nous prenons le premier élément. (Les tableaux Postgres sont indexés par 1, et non par 0).
• Vous pourriez utiliser array_agg de la même manière pour votre troisième colonne de sortie, mais max(total) est plus simple.
• Contrairement à DISTINCT ON en utilisant array_agg vous permet de garder votre GROUP BY au cas où vous le voudriez pour d'autres raisons.

Answer 5

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;