După cum sugerează și titlul, am'd place să selectați primul rând din fiecare set de rânduri grupate cu un GRUP DE`.
Mai exact, dacă am'am luat-o "cumpără" de masă care arata ca aceasta:
SELECT * FROM purchases;
Mea De Ieșire:
id-ul | client | total ---+----------+------ 1 | Joe | 5 2 | Sally | 3 3 | Joe | 2 4 | Sally | 1
Am'd place să interogare pentru id
al mai mare de cumpărare ("total") realizate de fiecare "client". Ceva de genul asta:
SELECT FIRST(id), customer, FIRST(total)
FROM purchases
GROUP BY customer
ORDER BY total DESC;
Temperatura De Ieșire:
PRIMA(id) | client | PRIMA(total) ----------+----------+------------- 1 | Joe | 5 2 | Sally | 3
În PostgreSQL acest lucru este de obicei mai simplă și mai rapidă (mai mult de performanță de optimizare de mai jos):
Sau mai scurt (dacă nu la fel de clar) cu numerele de ordine de ieșire coloane:SELECTAȚI DISTINCT PE (client) id, client, total DE achiziții COMENZII DE catre client, total DESC, id;
SELECT DISTINCT ON (2)
id, customer, total
FROM purchases
ORDER BY 2, 3 DESC, 1;
Dacă "total" poate fi NULL (a castigat't rănit oricum, dar'll doriți pentru a se potrivi existente indici):
###Puncte importante - [**`DISTINCT PE`**][1] este o PostgreSQL extensie a standardului (în cazul în care numai `DISTINCT` pe ansamblu "SELECTAȚI" lista este definită). - Lista orice număr de expresii în `DISTINCT PE clauză, combinate rând valoare definește duplicate. [Manualul:][2] > în mod Evident, două rânduri sunt considerate distincte dacă ele diferă în cel puțin > o coloană de valoare. **Valori Null sunt considerate egale în această comparație.** Bold accent pe a mea. - `DISTINCT PE` pot fi combinate cu **`ORDER BY`**. Lider expresii trebuie să se potrivească conduce DISTINCT PE expresii în aceeași ordine. Puteți adăuga *suplimentare* expresii cu "ORDINUL PRIN" pentru a alege un anumit rand din fiecare grup de colegi. Am adăugat " id " ca ultimul element pentru a rupe legăturile: *"Alege rând, cu cel mai mic " id "de la fiecare grup de partajare cel mai mare "total"."* Pentru a comanda rezultatele într-un mod care nu este de acord cu ordinea de sortare de stabilire a primelor pentru fiecare grup, puteți cuib de mai sus interogării într-o interogare exterior cu un alt `ORDER BY`. Cum ar fi: - https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by/9796104#9796104 - Dacă "total" poate fi NULL, te *cel mai probabil* vreau rând cu cea mai mare non-valoare nulă. Adaugă **`NULL ULTIMA`** ca demonstrat. Detalii: - https://stackoverflow.com/questions/9510509/postgresql-sort-by-datetime-asc-null-first/9511492#9511492 - **La "SELECTAȚI" lista** nu este constrânsă de expresii în `DISTINCT PRIVIND " sau " ORDER BY` în nici un fel. (Nu este necesar în caz simplu de mai sus): - Te *nici't trebuie să* include oricare dintre expresiile în `DISTINCT PRIVIND " sau " ORDER BY`. - Ai *poate* include orice alte expresia în "SELECTAȚI" lista. Acest lucru este esențial pentru înlocuirea mult mai multe interogări complexe cu subinterogări și agregate / fereastra de funcții. - L-am testat cu Postgres versiunile 8.3 – 12. Dar caracteristica a fost acolo cel puțin începând cu versiunea 7.1, deci practic întotdeauna. ##Indicele La *perfect* index pentru interogarea de mai sus ar fi o [multi-coloană index][3] acoperă toate cele trei coloane în potrivire secvență și cu potrivire ordinea de sortare:... COMENZII DE catre client, total DESC NULL ULTIMA, id;
CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);
Poate fi prea specializate. Dar să-l utilizați dacă citit de performanță pentru o anumită interogare este crucială. Dacă ai DESC NULL TRECUTĂ în interogare, utilizați același în index, astfel încât ordinea de sortare meciuri și indicele este aplicabilă.
Se cântăresc costurile și beneficiile înainte de a crea adaptate indici pentru fiecare interogare. Potențialul de mai sus indicele depinde în mare măsură de distribuție de date. Indicele este utilizat, deoarece acesta oferă pre-sortate de date. În Postgres 9.2 sau mai târziu, interogarea poate, de asemenea, beneficia de un index numai scan dacă indicele este mai mic decât cel de fond al sistemului masă. Indicele trebuie să fie scanate în întregime, totuși.
work_mem
pentru a procesa implicate fel pas în memoria RAM și nu vărsați pe disc. Dar, în general, setarea work_mem*prea* mare poate avea efecte adverse. Consider
SET LOCALE pentru extrem de mare nelamurire. Găsi cât de mult ai nevoie de cu EXPLICA ANALIZA
. Menționez de "Disk:" într-un fel de pas indică nevoia de mai mult: Am avut o simplă referință aici care este depășit până acum. Am înlocuit-o cu un detaliate de referință în acest răspuns separat.
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
Dar ai nevoie pentru a adăuga logica pentru a rupe legăturile:
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
Testarea cele mai interesante candidați cu Postgres 9.4 și 9.5 cu o jumătate realist masa de 200k rânduri în "cumpără" și 10k distincte customer_id
(avg. 20 de rânduri pentru fiecare client).
Pentru Postgres 9.5 am dat un al 2-lea test cu efectiv 86446 distincte de clienți. A se vedea mai jos (avg. 2.3 rânduri pe client).
Masă 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
);
Eu folosesc un serial
(PK constrângere a adăugat mai jos) și un număr întreg customer_id
ca's o mai tipic de instalare. Adăugat, de asemenea, `some_column face de obicei mai multe coloane.
Date fictive, PK, index - un tipic de masă are, de asemenea, unele mort tupluri:
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;
"client" tabel - pentru superior de interogare
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;
În al doilea test pentru 9.5 am folosit acelasi setup, dar cu random() * 100000
a genera customer_id
pentru a obține doar câteva rânduri pe customer_id
.
Generat cu interogare.
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 | |
row_număr()
în CTE, (vezi alte răspunde)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;
row_număr()
în subinterogare (mea optimizare)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;
DISTINCT PE
(vezi alte răspunde)SELECT DISTINCT ON (customer_id)
id, customer_id, total
FROM purchases
ORDER BY customer_id, total DESC, id;
LATERAL
subinterogare (vezi aici)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;
LATERAL
(vezi aici)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;
array_agg()
cu ORDER BY
(vezi alte răspunde)SELECT (array_agg(id ORDER BY total DESC))[1] AS id
, customer_id
, max(total) AS total
FROM purchases
GROUP BY customer_id;
Timpul de executie pentru întrebările de mai sus cu EXPLICA ANALIZA
(și toate opțiunile off), cel mai bun din 5 puncte.
Toate interogări folosit un Indicele de Numai Scanare pe purchases2_3c_idx
(printre altele). Unele dintre ele doar pentru dimensiuni mai mici ale indicelui, altele mai eficient.
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
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
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
Aici este unul nou prin "ogr" de testare cu 10M rânduri și 60k unic "clienții" pe Postgres 11.5 (în vigoare din Septembrie. 2019). Rezultatele sunt încă în conformitate cu ceea ce am văzut până acum:
Am făcut trei teste cu PostgreSQL 9.1 pe o masă de 65579 rânduri și o coloană btree indicii pe fiecare dintre cele trei coloane implicate și a luat cele mai bune timp de executie de 5 puncte.
Comparând @OMGPonies' prima interogare (A
) la sus DISTINCT PE soluție
("B"):
O: 567.218 ms B: 386.673 ms
O: 249.136 ms B: 55.111 ms
în cazul în CARE clientul = x
.O: 0.143 ms B: 0.072 ms
Același test repetat cu indicele descrise în alte răspunde
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
Acest lucru este comun [tag:mai mare-n-pe-grup] problema, care deja a testat si foarte soluții optimizate. Personal, prefer stânga se alăture soluție de Lege Karwin (a original post cu o mulțime de alte soluții).
Rețineți că grămadă de soluții la această problemă comună poate surprinzător fi găsit în una din cele mai multe surse oficiale, MySQL manual! A se vedea Exemple de Interogări :: Rândurile care Deține Grupul înțelept Maxim de o Anumită Coloană.
În Postgres puteți folosi array_agg` astfel:
SELECT customer,
(array_agg(id ORDER BY total DESC))[1],
max(total)
FROM purchases
GROUP BY customer
Acest lucru vă va da " id " de fiecare client's cea mai mare de cumpărare.
Câteva lucruri de reținut:
array_agg
este o funcție agregată, astfel încât acesta funcționează cu GROUP BY
.array_agg
vă permite să specificați o comanda de domeniu doar în sine, așa că nu - 't constrânge structura întregii interogare. Există, de asemenea, sintaxa pentru modul de sortare Null-uri, dacă aveți nevoie pentru a face ceva diferit de default.DISTINCT PE
, folosind array_agg
vă permite să păstrați GRUP DE`, în cazul în care doriți pentru alte motive.Foarte repede o soluție
SELECT a.*
FROM
purchases a
JOIN (
SELECT customer, min( id ) as id
FROM purchases
GROUP BY customer
) b USING ( id );
și într-adevăr foarte repede, dacă masa este indexat de identitate:
create index purchases_id on purchases (id);
Eu folosesc acest mod (postgresql numai): 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 $1;
$$;
-- 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 $2;
$$;
-- And then wrap an aggregate around it
CREATE AGGREGATE public.last (
sfunc = public.last_agg,
basetype = anyelement,
stype = anyelement
);
Apoi exemplul tău ar trebui să funcționeze aproape cum este:
SELECT FIRST(id), customer, FIRST(total)
FROM purchases
GROUP BY customer
ORDER BY FIRST(total) DESC;
PRECIZARE: Se ignora's NUL rânduri
Acum am folosi acest mod: http://pgxn.org/dist/first_last_agg/
Pentru a instala pe 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'
L's o postgres extensie care vă oferă primul și ultimul funcții; aparent mai repede decât cele de mai sus.
Dacă utilizați funcții agregate (ca astea), puteți comanda rezultatele, fără a fi nevoie de a avea datele comandat deja:
http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES
Deci echivalentul exemplu, cu comanda ar fi ceva de genul:
SELECT first(id order by id), customer, first(total order by id)
FROM purchases
GROUP BY customer
ORDER BY first(total);
Desigur, puteți comanda și filtru cum crezi de cuviință în agregat; it's foarte puternic sintaxă.
Folosesc ARRAY_AGG funcția de PostgreSQL, U-SQL, IBM DB2, Google BigQuery SQL:
SELECT customer, (ARRAY_AGG(id ORDER BY total DESC))[1], MAX(total)
FROM purchases
GROUP BY customer
Interogare:
SELECT purchases.*
FROM purchases
LEFT JOIN purchases as p
ON
p.customer = purchases.customer
AND
purchases.total < p.total
WHERE p.total IS NULL
CUM FUNCȚIONEAZĂ! (M-am'am fost acolo)
Vrem să ne asigurăm că avem doar cel mai mare total pentru fiecare achiziție.
Unele Lucruri Teoretice (săriți peste această parte, dacă doriți doar pentru a înțelege de interogare)
Să Totalul să fie o funcție de T(client,id) în cazul în care se întoarce o valoare dat numele și id-ul Pentru a dovedi că a dat total (T(client,id)) este cel mai înalt trebuie să demonstreze că Vrem să dovedim fie
SAU
Prima abordare va avea nevoie de noi pentru a obține toate înregistrările pentru care numele care nu-mi place.
Cel de-al doilea va avea nevoie de un mod inteligent de a spune nu poate fi nici o înregistrare mai mare decât aceasta.
Înapoi la SQL
Dacă am plecat se alătură la masă pe numele și totală să fie mai mică decât s-a alăturat tabelul:
LEFT JOIN purchases as p
ON
p.customer = purchases.customer
AND
purchases.total < p.total
suntem asigurați-vă că toate înregistrările care au un alt record cu cel mai mare total pentru același utilizator pentru a fi alăturat:
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
Asta ne va ajuta filtru pentru cel mai mare total pentru fiecare achiziție cu nici o grupare nevoie de:
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
Și că's răspunsul de care avem nevoie.
În SQL Server puteți face acest lucru:
SELECT *
FROM (
SELECT ROW_NUMBER()
OVER(PARTITION BY customer
ORDER BY total DESC) AS StRank, *
FROM Purchases) n
WHERE StRank = 1
Explicație:Aici Grup de se face pe baza de client și apoi comanda de total atunci fiecare astfel de grup este dat de serie ca StRank și vom lua mai întâi 1 client al cărui StRank este 1
Acceptate OMG Ponei' "Sprijinit de orice baza de date" soluția are viteza buna la testul meu.
Aici am oferi o aceeași abordare, dar mai complet și curățați orice-baza de date soluție. Legăturile sunt considerate (presupune dorința de a obține doar un singur rand pentru fiecare client, chiar mai multe înregistrări pentru max total per client), și alte achiziționarea de domenii (de exemplu, purchase_payment_id) vor fi selectate pentru real rânduri de potrivire în achiziționarea de masă.
Sprijinit de orice baza de date:
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
Această interogare este destul de repede, mai ales atunci când există un indice compozit, cum ar fi (client, total), la achiziționarea de masă.
Remarca:
t1, t2 sunt subinterogare alias care ar putea fi eliminate în funcție de baza de date.
Avertisment: folosind ( ... ) clauza nu este acceptată în prezent în MS-SQL și Oracle db ca din acest edita în ianuarie 2017. Trebuie să-și extindă singur exemplu, de pe t2.id = cumpărare.id
etc. FOLOSIND sintaxa de lucrări în SQLite, MySQL și PostgreSQL.
Pentru SQl Server cel mai eficient mod este:
with
ids as ( --condition for split table into groups
select i from (values (9),(12),(17),(18),(19),(20),(22),(21),(23),(10)) as v(i)
)
,src as (
select * from yourTable where <condition> --use this as filter for other conditions
)
,joined as (
select tops.* from ids
cross apply --it`s like for each rows
(
select top(1) *
from src
where CommodityId = ids.i
) as tops
)
select * from joined
și don't uita pentru a crea cluster de index pentru coloane utilizate
Dacă doriți să selectați orice (de unele condiții specifice) rând din setul de agregate rânduri.
Dacă doriți să utilizați un alt (suma/avg
) funcția de agregare în plus față de max/min. Astfel, nu puteți utiliza indiciu cu
DISTINCT PE
Puteți folosi următoarea subinterogare:
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
Puteți înlocui `suma = MAX( tf.suma) cu orice condiție pe care doriți cu o singură restricție: Acest subinterogare nu trebuie să se întoarcă mai mult de un rând
Dar dacă vrei să faci astfel de lucruri, probabil, în căutarea pentru fereastra de funcții