Comme le titre le suggère, j’aimerais 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’aimerais demander l’identifiant 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
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.
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é.
customer
), ceci est très efficace. Encore plus si vous avez besoin d'une sortie triée de toute façon. L'avantage se réduit avec un nombre croissant de lignes par client.work_mem
][5] pour traiter l'étape de tri impliquée en RAM et ne pas la déverser sur le disque. Mais en général, une valeur trop élevée de work_mem
peut avoir des effets négatifs. Considérez SET LOCAL
pour les requêtes exceptionnellement grosses. Trouvez la quantité dont vous avez besoin avec EXPLAIN ANALYZE
. La mention de "Disk:" dans l'étape de tri indique que vous avez besoin de plus :customer
), un [scannage d'index libre][7] (alias "skip scan") serait (beaucoup) plus efficace, mais ce n'est pas implémenté jusqu'à Postgres 12. (Une implémentation pour les scans d'index seulement est en cours de développement pour Postgres 13. Voir [ici][8] et ici).J'avais un benchmark simple ici qui est maintenant périmé. Je l'ai remplacé par un benchmark détaillé dans cette réponse séparée. [1] : https://www.postgresql.org/docs/current/sql-select.html#SQL-DISTINCT [2] : https://www.postgresql.org/docs/current/queries-select-lists.html#QUERIES-DISTINCT [3] : https://www.postgresql.org/docs/current/indexes-multicolumn.html [4] : https://www.postgresql.org/docs/current/indexes-index-only-scans.html [5] : https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM [6] : https://dba.stackexchange.com/questions/48630/optimize-simple-query-using-order-by-date-and-text/48633#48633 [7] : https://wiki.postgresql.org/wiki/Loose_indexscan [8] : https://commitfest.postgresql.org/19/1741/
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
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
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;