Menu

PostgreSQL DISTINCT ON: una riga per gruppo

SELECT DISTINCT ON (key) ... ORDER BY key, sort tiene la prima riga di ogni gruppo, come l'ultimo ordine di ogni cliente. Esiste solo in PostgreSQL.

Questa pagina include editor eseguibili: modifica, esegui e vedi subito l'output.

SELECT DISTINCT ON (expr) ... restituisce una riga per ogni valore distinto di expr: la prima riga di quel gruppo secondo l'ordine di ORDER BY. Ordina prima per la chiave del gruppo, poi per il criterio che decide qual è la "prima". Per l'ultimo ordine di ogni cliente, questo significa ORDER BY customer, created_at DESC.

Restituisce tre righe: l'ordine 2 per asha, il 4 per ben e il 5 per chen, cioè il più recente di ogni cliente. DISTINCT ON è un'estensione di PostgreSQL. Gli altri database hanno bisogno della forma con funzione finestra mostrata più sotto.

Come l'ORDER BY decide quale riga tenere

PostgreSQL ordina le righe, poi tiene la prima riga ogni volta che il valore di DISTINCT ON cambia. Quindi:

  • L'ORDER BY deve iniziare con le espressioni di DISTINCT ON, altrimenti PostgreSQL genera "SELECT DISTINCT ON expressions must match initial ORDER BY expressions".
  • Senza un ORDER BY che vada oltre quelle espressioni, la riga tenuta è imprevedibile e può cambiare da un'esecuzione all'altra.

Cambia created_at DESC in total DESC e la stessa query restituisce l'ordine più grande di ogni cliente:

Ora la riga di asha è l'ordine 1 (40) e quella di ben è l'ordine 3 (60).

Risolvere i pareggi di proposito

Quando due righe sono pari sulla colonna di ordinamento, può vincere l'una o l'altra. Aggiungi un'ultima chiave di ordinamento unica, come la chiave primaria, così il risultato è sempre lo stesso:

Le righe 2 e 3 hanno entrambe 95 punti. id come chiave finale fa vincere la riga 2.

Raggruppare per più di una colonna

Elenca più espressioni per ottenere una riga per ogni combinazione:

Ogni coppia di sensore e tipo tiene la sua lettura più recente: s1 humidity 40, s1 temp 22, s2 temp 19.

La versione portabile con ROW_NUMBER

ROW_NUMBER() numera le righe dentro ogni partizione. Tenere il numero 1 dà lo stesso risultato di DISTINCT ON, e funziona anche in MySQL 8, SQL Server e SQLite:

SELECT customer, id, total, created_at
FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY customer ORDER BY created_at DESC) AS rn
    FROM orders o
) ranked
WHERE rn = 1;

Usa questa forma quando ti servono le prime n righe per gruppo (rn <= 3) o quando la query deve girare su un altro database. La pagina sulle funzioni finestra spiega PARTITION BY.

Renderla veloce

DISTINCT ON ordina l'input, quindi su una tabella grande dagli un indice che corrisponda all'ORDER BY:

CREATE INDEX orders_customer_latest ON orders (customer, created_at DESC);

Così PostgreSQL può leggere le righe già in ordine invece di ordinarle. Con pochi gruppi e molte righe per gruppo, un join LATERAL che prende LIMIT 1 per cliente dallo stesso indice di solito legge meno righe. La pagina sui join mostra LATERAL. Confronta le due soluzioni con EXPLAIN ANALYZE sui tuoi dati.

Domande frequenti

Che differenza c'è tra DISTINCT e DISTINCT ON?

DISTINCT rimuove le righe identiche in tutte le colonne selezionate. DISTINCT ON (a) tiene una riga per ogni valore di a, e le altre colonne vengono dalla riga che risulta prima nell'ordinamento di quel gruppo.

DISTINCT ON è SQL standard?

No. È un'estensione di PostgreSQL, quindi non funziona in MySQL o SQL Server. La versione portabile è ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) in una subquery, filtrata sul numero di riga 1.

Perché ottengo "SELECT DISTINCT ON expressions must match initial ORDER BY expressions"?

L'ORDER BY deve iniziare con le stesse espressioni di DISTINCT ON, nello stesso ordine. Scrivi DISTINCT ON (customer) ... ORDER BY customer, created_at DESC, non soltanto ORDER BY created_at DESC.

DISTINCT ON può restituire le prime 3 righe per gruppo?

No, restituisce esattamente una riga per gruppo. Per le prime n, numera le righe con ROW_NUMBER() e tieni rn <= 3, oppure usa una subquery LATERAL con LIMIT 3.

Illustrazione dei linguaggi di programmazione di Coddy

Impara a programmare con Coddy

Inizia