Choisir la première ligne de chaque groupe par groupe?
comme le titre l'indique, j'aimerais sélectionner la première ligne de chaque ensemble de lignes groupées avec un GROUP BY
.
spécifiquement, si j'ai une table purchases
qui ressemble à ceci:
sql prettyprint-override">SELECT * FROM purchases;
Ma Sortie:
id | customer | total ---+----------+------ 1 | Joe | 5 2 | Sally | 3 3 | Joe | 2 4 | Sally | 1
j'aimerais demander pour le id
du plus grand achat ( total
) fait par chaque customer
. Quelque chose comme ceci:
SELECT FIRST(id), customer, FIRST(total)
FROM purchases
GROUP BY customer
ORDER BY total DESC;
Résultats Escomptés:
FIRST(id) | customer | FIRST(total) ----------+----------+------------- 1 | Joe | 5 2 | Sally | 3
11 réponses
On Oracle 9.2+ (not 8i+ as originally stated), 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
supporté par n'importe quelle base de données:
mais il faut ajouter de la logique pour briser les liens:
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
Dans PostgreSQL c'est typiquement plus simple et plus rapide (en plus de l'optimisation de la performance ci-dessous):
SELECT DISTINCT ON (customer)
id, customer, total
FROM purchases
ORDER BY customer, total DESC, id;
ou plus court (s'il n'est pas aussi clair) avec des nombres ordinaux de colonnes de sortie:
SELECT DISTINCT ON (2)
id, customer, total
FROM purchases
ORDER BY 2, 3 DESC, 1;
si total
peut être nul (ne fera pas de mal dans les deux cas, mais vous voudrez faire correspondre les index existants):
...
ORDER BY customer, total DESC NULLS LAST, id;
points Importants
-
DISTINCT ON
est une extension PostgreSQL de la norme (où seulementDISTINCT
sur l'ensemble de la listeSELECT
est défini). -
liste n'importe quel nombre d'expressions dans la clause
DISTINCT ON
, la valeur de ligne combinée définit les doublons. Le manuel:évidemment, deux lignes sont sont considérés comme distincts s'ils diffèrent au moins en une valeur de la colonne. Les valeurs nulles sont considérées comme égales dans cette comparaison.
le Gras c'est moi qui souligne.
-
DISTINCT ON
peut être combiné avecORDER BY
. Les expressions de tête doivent correspondre aux expressions de têteDISTINCT ON
dans le même ordre. Vous pouvez ajouter expressions supplémentairesORDER BY
pour choisir une ligne particulière de chaque groupe de pairs. J'ai ajoutéid
comme dernier article pour briser les liens:"Choisir la ligne avec la plus petite
id
à partir de chaque groupe de partage de la plus hautetotal
."si
total
peut être nul, vous très probablement voulez la rangée avec la plus grande valeur non-nulle. AjouterNULLS LAST
comme démontré. Details: -
la
SELECT
liste n'est aucunement limitée par les expressionsDISTINCT ON
ouORDER BY
. (Pas nécessaire dans le cas simple ci-dessus):-
Vous n'ont pas à inclure les expressions
DISTINCT ON
ouORDER BY
. -
Vous peut inclut toute autre expression dans la
SELECT
liste. Cela permet de remplacer les requêtes beaucoup plus complexes par des sous-séries et des fonctions agrégat / fenêtre.
-
-
j'ai testé avec Postgres versions 8.3 – 10. Mais la fonctionnalité existe au moins depuis la version 7.1, donc essentiellement toujours.
Index
l'index parfait pour la requête ci-dessus serait un index à plusieurs colonnes couvrant les trois colonnes dans la séquence correspondante et avec l'ordre de tri correspondant:
CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);
peut être trop spécialisé pour des applications dans le monde réel. Mais utilisez-le si la performance de lecture est cruciale. Si vous avez DESC NULLS LAST
dans la requête, utilisez le même dans l'index ainsi Postgres sait trier les correspondances.
de l'Efficacité et de l'optimisation de la Performance
vous devez évaluer les coûts et les avantages Avant de créer un index adapté à chaque requête. Le potentiel de l'indice ci-dessus dépend largement de distribution des données .
l'index est utilisé parce qu'il fournit des données pré-triées, et dans Postgres 9.2 ou plus tard la requête peut également bénéficier d'un index seulement scanner si l'index est plus petit que le tableau sous-jacent. L'index doit être scanné dans son intégralité, cependant.
-
Pour quelques lignes par client , c'est très efficace (d'autant plus si vous avez besoin de trier sortie de toute façon). L'avantage diminue avec un nombre croissant de lignes par client.
Idéalement, vous avez assezwork_mem
pour traiter l'étape de tri impliqué dans la RAM et ne pas renverser sur le disque. En général, l'établissement dework_mem
trop élevé peut avoir des effets néfastes. ConsidérezSET LOCAL
pour des requêtes exceptionnellement grandes. Trouvez combien vous en avez besoin avecEXPLAIN ANALYZE
. Mention de " disque: "dans l'étape de tri indique le besoin de plus: -
Pour beaucoup lignes par client , un lâche index scan serait (bien) plus efficace, mais qui n'est actuellement pas mis en œuvre dans Postgres (jusqu'à v10).
Il y a des plus rapide requête techniques pour la remplacer. En particulier si vous avez une table séparée détenant des clients uniques, qui est le cas d'utilisation typique. Mais aussi si vous ne le faites pas:
de Référence", 15191120920"
j'ai eu un point de repère simple ici qui est dépassé maintenant. Je l'ai remplacé par un benchmark détaillé dans cette réponse séparée .
de Référence", 1519460920"
Tester les candidats les plus intéressants avec Postgres 9.4 et 9.5 avec un mi-réaliste de la table de 200k lignes dans purchases
et 10k distinctes customer_id
( avg. 20 lignes par client ).
pour Postgres 9.5 j'ai fait un 2ème test avec effectivement 86446 clients distincts. Voir ci-dessous ( avg. 2.3 lignes par client ).
Setup
tableau Principal
CREATE TABLE purchases (
id serial
, customer_id int -- REFERENCES customer
, total int -- could be amount of money in Cent
, some_column text -- to make the row bigger, more realistic
);
j'utilise un serial
(contrainte PK ajoutée ci-dessous) et un entier customer_id
puisque c'est une configuration plus typique. Également ajouté some_column
pour compenser généralement plus de colonnes.
données fictives, PK, index - une table typique a également quelques tuples morts:
INSERT INTO purchases (customer_id, total, some_column) -- insert 200k rows
SELECT (random() * 10000)::int AS customer_id -- 10k customers
, (random() * random() * 100000)::int AS total
, 'note: ' || repeat('x', (random()^2 * random() * random() * 500)::int)
FROM generate_series(1,200000) g;
ALTER TABLE purchases ADD CONSTRAINT purchases_id_pkey PRIMARY KEY (id);
DELETE FROM purchases WHERE random() > 0.9; -- some dead rows
INSERT INTO purchases (customer_id, total, some_column)
SELECT (random() * 10000)::int AS customer_id -- 10k customers
, (random() * random() * 100000)::int AS total
, 'note: ' || repeat('x', (random()^2 * random() * random() * 500)::int)
FROM generate_series(1,20000) g; -- add 20k to make it ~ 200k
CREATE INDEX purchases_3c_idx ON purchases (customer_id, total DESC, id);
VACUUM ANALYZE purchases;
customer
table de la qualité supérieure de la requête
CREATE TABLE customer AS
SELECT customer_id, 'customer_' || customer_id AS customer
FROM purchases
GROUP BY 1
ORDER BY 1;
ALTER TABLE customer ADD CONSTRAINT customer_customer_id_pkey PRIMARY KEY (customer_id);
VACUUM ANALYZE customer;
Dans mon deuxième épreuve pour 9.5 j'ai utilisé la même configuration, mais avec random() * 100000
générer customer_id
pour obtenir seulement quelques lignes par customer_id
.
tailles D'objets pour la table purchases
Généré avec requête .
what | bytes/ct | bytes_pretty | bytes_per_row
-----------------------------------+----------+--------------+---------------
core_relation_size | 20496384 | 20 MB | 102
visibility_map | 0 | 0 bytes | 0
free_space_map | 24576 | 24 kB | 0
table_size_incl_toast | 20529152 | 20 MB | 102
indexes_size | 10977280 | 10 MB | 54
total_size_incl_toast_and_indexes | 31506432 | 30 MB | 157
live_rows_in_text_representation | 13729802 | 13 MB | 68
------------------------------ | | |
row_count | 200045 | |
live_tuples | 200045 | |
dead_tuples | 19955 | |
Requêtes
1. row_number()
CTE, ( voir autres réponse )
WITH cte AS (
SELECT id, customer_id, total
, row_number() OVER(PARTITION BY customer_id ORDER BY total DESC) AS rn
FROM purchases
)
SELECT id, customer_id, total
FROM cte
WHERE rn = 1;
2. row_number()
dans la sous-requête (mon optimisation)
SELECT id, customer_id, total
FROM (
SELECT id, customer_id, total
, row_number() OVER(PARTITION BY customer_id ORDER BY total DESC) AS rn
FROM purchases
) sub
WHERE rn = 1;
3. DISTINCT ON
( voir autre réponse )
SELECT DISTINCT ON (customer_id)
id, customer_id, total
FROM purchases
ORDER BY customer_id, total DESC, id;
4. rCTE avec LATERAL
sous-requête ( voir ici )
WITH RECURSIVE cte AS (
( -- parentheses required
SELECT id, customer_id, total
FROM purchases
ORDER BY customer_id, total DESC
LIMIT 1
)
UNION ALL
SELECT u.*
FROM cte c
, LATERAL (
SELECT id, customer_id, total
FROM purchases
WHERE customer_id > c.customer_id -- lateral reference
ORDER BY customer_id, total DESC
LIMIT 1
) u
)
SELECT id, customer_id, total
FROM cte
ORDER BY customer_id;
5. customer
table avec LATERAL
( voir ici )
SELECT l.*
FROM customer c
, LATERAL (
SELECT id, customer_id, total
FROM purchases
WHERE customer_id = c.customer_id -- lateral reference
ORDER BY total DESC
LIMIT 1
) l;
6. array_agg()
avec ORDER BY
( voir autre réponse )
SELECT (array_agg(id ORDER BY total DESC))[1] AS id
, customer_id
, max(total) AS total
FROM purchases
GROUP BY customer_id;
résultats
temps d'Exécution pour les requêtes ci-dessus avec EXPLAIN ANALYZE
(et toutes les options off ), au meilleur des 5 pistes .
toutes requêtes utilisées un Scan Index Only sur purchases2_3c_idx
(parmi d'autres). Certains pour la plus petite taille de l'indice, d'autres plus efficacement.
A. Postgresql 9.4 avec 200k lignes et ~ 20% customer_id
1. 273.274 ms
2. 194.572 ms
3. 111.067 ms
4. 92.922 ms
5. 37.679 ms -- winner
6. 189.495 ms
B. même chose avec Postgres 9.5
1. 288.006 ms
2. 223.032 ms
3. 107.074 ms
4. 78.032 ms
5. 33.944 ms -- winner
6. 211.540 ms
C. identique à B., mais avec ~ 2,3 lignes par customer_id
1. 381.573 ms
2. 311.976 ms
3. 124.074 ms -- winner
4. 710.631 ms
5. 311.976 ms
6. 421.679 ms
j'ai fait trois tests avec PostgreSQL 9.1 sur une table de vie réelle de 65579 lignes et des index btree à une seule colonne sur chacune des trois colonnes impliquées et j'ai pris le meilleur temps d'exécution de 5 passages.
Comparer @OMGPonies première question ( A
) à la au-dessus de DISTINCT ON
solution ( B
):
-
sélectionner la table entière, résultats dans 5958 lignes dans ce cas.
A: 567.218 ms B: 386.673 ms
-
condition D'utilisation
WHERE customer BETWEEN x AND y
résultant en 1000 lignes.A: 249.136 ms B: 55.111 ms
-
sélectionner un seul client avec
WHERE customer = x
.A: 0.143 ms B: 0.072 ms
même essai répété avec le indice décrit dans l'autre réponse
CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);
1A: 277.953 ms
1B: 193.547 ms
2A: 249.796 ms -- special index not used
2B: 28.679 ms
3A: 0.120 ms
3B: 0.048 ms
C'est commun le plus grand-n-Par-Groupe problème, qui a déjà bien testé et très solutions optimisées . Personnellement, je préfère la gauche join solution par Bill Karwin (le poste original avec beaucoup d'autres solutions ).
notez que plusieurs solutions à ce problème commun peuvent étonnamment être trouvées dans l'une des sources les plus officielles, MySQL manuel ! Voir exemples de requêtes courantes:: les lignes contenant le Maximum par groupe d'une certaine colonne .
dans Postgres vous pouvez utiliser array_agg
comme ceci:
SELECT customer,
(array_agg(id ORDER BY total DESC))[1],
max(total)
FROM purchases
GROUP BY customer
cela vous donnera le id
du plus gros achat de chaque client.
Certaines choses sont à noter:
-
array_agg
est une fonction agrégée, donc il fonctionne avecGROUP BY
. -
array_agg
vous permet de spécifier un ordre scopé à lui-même, de sorte qu'il ne contraint pas la structure de l'ensemble de la requête. Il y a aussi une syntaxe pour la façon dont vous triez NULLs, si vous avez besoin de faire quelque chose de différent de la valeur par défaut. - une fois que nous construisons le tableau, nous prenons le premier élément. (Les tableaux de Postgres sont indexés à 1, pas à 0).
- vous pouvez utiliser
array_agg
d'une manière similaire pour votre troisième colonne de sortie, maismax(total)
est plus simple. - contrairement à
DISTINCT ON
, l'utilisation dearray_agg
vous permet de garder votreGROUP BY
, au cas où vous voulez que pour les autres raisons.
la solution n'est pas très efficace comme le souligne Erwin, en raison de la présence de sous-Q
select * from purchases p1 where total in
(select max(total) from purchases where p1.customer=customer) order by total desc;
j'utilise cette voie (postgresql seulement): https://wiki.postgresql.org/wiki/First/last_%28aggregate%29
-- Create a function that always returns the first non-NULL item
CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
SELECT ;
$$;
-- And then wrap an aggregate around it
CREATE AGGREGATE public.first (
sfunc = public.first_agg,
basetype = anyelement,
stype = anyelement
);
-- Create a function that always returns the last non-NULL item
CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
SELECT ;
$$;
-- And then wrap an aggregate around it
CREATE AGGREGATE public.last (
sfunc = public.last_agg,
basetype = anyelement,
stype = anyelement
);
alors votre exemple devrait fonctionner presque comme est:
SELECT FIRST(id), customer, FIRST(total)
FROM purchases
GROUP BY customer
ORDER BY FIRST(total) DESC;
avertissement: il ignore les lignes nulles
Edition 1 - Utiliser la postgres extension au lieu de
maintenant j'utilise cette façon: http://pgxn.org/dist/first_last_agg /
à installer sur ubuntu 14.04:
apt-get install postgresql-server-dev-9.3 git build-essential -y
git clone git://github.com/wulczer/first_last_agg.git
cd first_last_app
make && sudo make install
psql -c 'create extension first_last_agg'
c'est une extension postgres qui vous donne la première et la dernière fonction; apparemment plus rapide que le chemin ci-dessus.
Edition 2 - Commande et de filtrage
si vous utilisez des fonctions agrégées( comme celles-ci), vous pouvez commander les résultats, sans avoir besoin d'avoir les données déjà commandées:
http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES
donc l'exemple équivalent, avec la commande serait quelque chose comme:
SELECT first(id order by id), customer, first(total order by id)
FROM purchases
GROUP BY customer
ORDER BY first(total);
bien sûr, vous pouvez commander et filtrer comme vous le jugez approprié dans l'agrégat; c'est une syntaxe très puissante.
Très rapide
SELECT a.*
FROM
purchases a
JOIN (
SELECT customer, min( id ) as id
FROM purchases
GROUP BY customer
) b USING ( id );
et vraiment très rapide si la table est indexée par id:
create index purchases_id on purchases (id);
La Requête:
SELECT purchases.*
FROM purchases
LEFT JOIN purchases as p
ON
p.customer = purchases.customer
AND
purchases.total < p.total
WHERE p.total IS NULL
COMMENT CELA FONCTIONNE-T-IL! (j'y suis allé)
nous voulons Nous assurer que nous n'avons que le total le plus élevé pour chaque achat.
quelques trucs théoriques (sauter cette partie si vous voulez seulement comprendre la requête)
que Total soit une fonction T (Client, id) où il renvoie une valeur donnée le nom et l'identité Pour prouver que le total donné (t (client, id)) est le plus élevé nous devons prouver que Nous voulons prouver l'un ou l'autre
- pond x T (Client, id) > T (client, x) (ce total est plus élevé que tous les autres total pour ce client)
ou
- pond x T (Client, id) < T (client, x) (il n'existe pas de total plus élevé pour ce client)
La première approche j'ai besoin que nous obtenions tous les enregistrements pour ce nom que je n'aime pas vraiment.
le second aura besoin d'une façon intelligente de dire qu'il ne peut y avoir de record plus élevé que celui-ci.
retour au SQL
si nous avons quitté se joint à la table sur le nom et le total étant moins que la table jointe:
LEFT JOIN purchases as p
ON
p.customer = purchases.customer
AND
purchases.total < p.total
nous nous assurons que tous les enregistrements qui ont un autre enregistrement avec le total plus élevé pour le même utilisateur à rejoindre:
purchases.id, purchases.customer, purchases.total, p.id, p.customer, p.total
1 , Tom , 200 , 2 , Tom , 300
2 , Tom , 300
3 , Bob , 400 , 4 , Bob , 500
4 , Bob , 500
5 , Alice , 600 , 6 , Alice , 700
6 , Alice , 700
Qui nous aidera à filtrer le total le plus élevé pour chaque achat sans groupement:
WHERE p.total IS NULL
purchases.id, purchases.name, purchases.total, p.id, p.name, p.total
2 , Tom , 300
4 , Bob , 500
6 , Alice , 700
Et c'est la réponse dont nous avons besoin.
la solution OMG Ponies acceptée "supportée par n'importe quelle base de données" a une bonne vitesse de mon test.
ici, je fournis une même approche, mais plus complète et propre n'importe quelle solution de base de données. Les liens sont considérés (supposons que le désir d'obtenir une seule ligne pour chaque client, même plusieurs enregistrements pour le total maximum par client), et d'autres champs d'achat (par exemple purchase_payment_id) seront sélectionnés pour les lignes de correspondance réelle dans le tableau d'achat.
pris en charge par n'importe quelle base de données:
select * from purchase
join (
select min(id) as id from purchase
join (
select customer, max(total) as total from purchase
group by customer
) t1 using (customer, total)
group by customer
) t2 using (id)
order by customer
cette requête est raisonnablement rapide surtout quand il y a un indice composite comme (client, total) sur la table d'achat.
Remarque:
-
t1, t2 sont sous-requête alias qui pourraient être supprimés en fonction de la base de données.
-
Caveat : la clause
using (...)
n'est actuellement pas prise en charge dans MS-SQL et Oracle db de cette édition en janvier 2017. Vous devez l'étendre vous-même à par exempleon t2.id = purchase.id
etc. La syntaxe D'utilisation fonctionne en SQLite, MySQL et PostgreSQL.
-
si vous voulez sélectionner une ligne (par votre condition spécifique) de l'ensemble des lignes agrégées.
-
si vous voulez utiliser une autre fonction d'agrégation (
sum/avg
) en plus demax/min
. Ainsi, vous ne pouvez pas utiliser Cluedo avecDISTINCT ON
, Vous pouvez utiliser la prochaine sous-requête:
SELECT
(
SELECT **id** FROM t2
WHERE id = ANY ( ARRAY_AGG( tf.id ) ) AND amount = MAX( tf.amount )
) id,
name,
MAX(amount) ma,
SUM( ratio )
FROM t2 tf
GROUP BY name
vous pouvez remplacer amount = MAX( tf.amount )
par condition que vous voulez avec une restriction: ce sous-article ne doit pas retourner plus d'une rangée
mais si vous voulez faire de telles choses vous cherchez probablement fonctions de fenêtre