-- language-all: lang-sql -->
Come suggerisce il titolo, vorrei selezionare la prima riga di ogni serie di righe raggruppate con un GROUP BY
.
In particolare, se ho una tabella acquisti
che assomiglia a questa:
SELECT * FROM purchases;
Il mio risultato:
pre> id | cliente | totale ---+----------+------ 1 | Joe | 5 2 | Sally | 3 3 | Joe | 2 4 | Sally | 1 Prego;
Vorrei interrogare l' id
del più grande acquisto (totale
) fatto da ogni cliente
. Qualcosa del genere:
SELECT FIRST(id), customer, FIRST(total)
FROM purchases
GROUP BY customer
ORDER BY total DESC;
Uscita prevista: pre> PRIMO(id) | cliente | PRIMO(totale) ----------+----------+------------- 1 | Joe | 5 2 | Sally | 3 pre>
In PostgreSQL questo è tipicamente più semplice e veloce (più avanti l'ottimizzazione delle prestazioni): SELECT
SELECT DISTINCT ON (cliente)
id, cliente, totale
dagli acquisti
ORDER BY customer, total DESC, id;
Oppure più breve (se non altrettanto chiaro) con i numeri ordinali delle colonne di output:
SELECT DISTINCT ON (2)
id, customer, total
FROM purchases
ORDER BY 2, 3 DESC, 1;
Se il totale
può essere NULL (non farà male in entrambi i casi, ma vorrete far corrispondere gli indici esistenti):
pre> ...
ORDER BY customer, total DESC NULLS LAST, id;
DISTINCT ON
]1 è un'estensione PostgreSQL dello standard (dove è definito solo DISTINCT
sull'intera lista SELECT
).DISTINCT ON
, il valore combinato della riga definisce i duplicati. Il manuale:
Ovviamente, due righe sono considerate distinte se differiscono in almeno
un valore di colonna. **I valori nulli sono considerati uguali in questo confronto.
Enfasi in grassetto mia.DISTINCT ON
può essere combinato con ORDER BY
. Le espressioni principali devono corrispondere alle espressioni principali DISTINCT ON
nello stesso ordine. Puoi aggiungere espressioni aggiuntive a ORDER BY
per scegliere una riga particolare da ogni gruppo di pari. Ho aggiunto id
come ultimo elemento per rompere i legami:
"Scegli la riga con il più piccolo id
da ogni gruppo che condivide il più alto totale
."
Per ordinare i risultati in un modo che non sia in accordo con l'ordine che determina il primo per gruppo, puoi annidare la query di cui sopra in una query esterna con un altro ORDER BY
. Come:totale
può essere NULL, tu molto probabilmente vuoi la riga con il più grande valore non nullo. Aggiungi NULLS LAST
come dimostrato. Dettagli:SELECT
** non è vincolata da espressioni in DISTINCT ON
o ORDER BY
in alcun modo. (Non è necessario nel caso semplice di cui sopra):DISTINCT ON
o ORDER BY
.SELECT
. Questo è strumentale per sostituire query molto più complesse con subquery e funzioni di aggregazione/finestra.L'indice perfetto per la query di cui sopra sarebbe un indice multi-colonna che abbraccia tutte e tre le colonne in sequenza corrispondente e con ordine di ordinamento corrispondente:
CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);
Potrebbe essere troppo specializzato. Ma usatelo se le prestazioni di lettura per quella particolare query sono cruciali. Se hai DESC NULLS LAST
nella query, usa lo stesso nell'indice in modo che l'ordine corrisponda e l'indice sia applicabile.
Ponderare costi e benefici prima di creare indici su misura per ogni query. Il potenziale di questo indice dipende in gran parte dalla distribuzione dei dati. L'indice è utilizzato perché fornisce dati pre-ordinati. In Postgres 9.2 o successivo la query può anche beneficiare di un index only scan se l'indice è più piccolo della tabella sottostante. L'indice deve essere scansionato nella sua interezza, però.
cliente
), questo è molto efficiente. Ancora di più se avete bisogno di un output ordinato in ogni caso. Il beneficio si riduce con un numero crescente di righe per cliente.work_mem
per elaborare il passo di ordinamento coinvolto nella RAM e non riversarlo su disco. Ma generalmente l'impostazione di work_mem
troppo alta può avere effetti negativi. Considerate SET LOCAL
per query eccezionalmente grandi. Trova quanto ti serve con EXPLAIN ANALYZE
. La menzione di "Disk:" nel passo di ordinamento indica la necessità di altro:cliente
), un loose index scan (a.k.a. "skip scan") sarebbe (molto) più efficiente, ma questo'non è implementato fino a Postgres 12. (Un'implementazione per le scansioni di solo indice è in sviluppo per Postgres 13. Vedere qui e qui).Avevo un semplice benchmark qui che è ormai superato. L'ho sostituito con un dettagliato benchmark in questa risposta separata.
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
Ma è necessario aggiungere la logica per rompere i legami:
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 soluzione non è molto efficiente come indicato da Erwin, a causa della presenza di SubQs
select * from purchases p1 where total in
(select max(total) from purchases where p1.customer=customer) order by total desc;