I diversi volti di DISTINCT in PostgreSQL
Lavorare con un DBMS può essere interessante.
Lavorare però con più DBMS può essere frustrante: ognuno di essi ha una serie di istruzioni che sono leggermente diverse rispetto al collega più vicino: se ti abitui ad utilizzare SQL Server, passare a PostgreSQL può non essere immediato.
La prima differenza che si noterà è proprio relativa ad una serie di funzionalità che proprio alcuni di questi aggiungono grazie a delle istruzioni particolari: vediamo quali sono i diversi volti del DISTINCT in PostgreSQL.
Cos’è DISTINCT?
SELECT DISTINCT elimina le righe duplicate dal risultato.
L’uso più semplice di DISTINCT serve, ad esempio, per ottenere un elenco univoco di categorie di un prodotto:
SELECT DISTINCT categoria FROM prodotto;
--------------------------
"abbigliamento"
"casa"
"alimentari"
"cartoleria"
"igiene personale"
è anche vero che potremmo fare lo stesso con una GROUP BY:
SELECT categoria FROM prodotto GROUP BY categoria;
E cos’è DISTINCT ON?
Una classica domanda da colloquio di lavoro è trovare, usando l’esempio precedente, il prodotto con il prezzo più alto in ogni categoria.
Questo è ciò che insegnano all’università nei vari corsi, usando due SELECT annidate che consentono la selezione del prezzo più alto -di certo di non semplice lettura:
SELECT
*
FROM
prodotto
WHERE
(categoria, prezzo) IN (
SELECT
categoria,
MAX(prezzo)
FROM
prodotto
GROUP BY
categoria
)
ORDER BY
categoria;
--------------------------------------------
174 "felpa" "abbigliamento" 7 6
12 "riso" "alimentari" 10 1
15 "scottona" "alimentari" 10 1
16 "emmenthal" "alimentari" 10 1
37 "uva" "alimentari" 10 1
65 "farro" "alimentari" 10 3
67 "uova" "alimentari" 10 3
69 "cracker" "alimentari" 10 3
119 "cacao amaro" "alimentari" 10 4
123 "uva" "alimentari" 10 4
134 "banana" "alimentari" 10 4
138 "lattuga" "alimentari" 10 4
149 "uova" "alimentari" 10 4
155 "wafer" "alimentari" 10 5
444 "certosa" "alimentari" 10 7
465 "ostriche" "alimentari" 10 8
471 "carciofi" "alimentari" 10 8
474 "parmigiano" "alimentari" 10 8
481 "insalata" "alimentari" 10 8
502 "bresaola" "alimentari" 10 9
510 "stracchino" "alimentari" 10 9
524 "pasta sfoglia" "alimentari" 10 9
182 "pasta" "alimentari" 10 6
183 "riso" "alimentari" 10 6
200 "cereali" "alimentari" 10 7
201 "wafer" "alimentari" 10 7
208 "vongole" "alimentari" 10 8
248 "olive" "alimentari" 10 1
254 "stracchino" "alimentari" 10 1
270 "pane al sesamo" "alimentari" 10 2
274 "passata di pomodoro" "alimentari" 10 2
291 "orzo" "alimentari" 10 2
293 "marmellata" "alimentari" 10 2
302 "pane al sesamo" "alimentari" 10 3
305 "ostriche" "alimentari" 10 3
310 "bresaola" "alimentari" 10 3
335 "ricotta" "alimentari" 10 4
339 "orata" "alimentari" 10 4
342 "bresaola" "alimentari" 10 4
344 "olive" "alimentari" 10 4
346 "parmigiano" "alimentari" 10 4
348 "certosa" "alimentari" 10 4
369 "ostriche" "alimentari" 10 5
378 "parmigiano" "alimentari" 10 5
382 "stracchino" "alimentari" 10 5
387 "orzo" "alimentari" 10 5
395 "pane al latte" "alimentari" 10 5
414 "stracchino" "alimentari" 10 6
427 "pane al latte" "alimentari" 10 6
428 "pasta sfoglia" "alimentari" 10 6
430 "pane al sesamo" "alimentari" 10 7
436 "tonno" "alimentari" 10 7
448 "yogurt" "alimentari" 10 7
460 "pasta sfoglia" "alimentari" 10 7
2 "set di penne" "cartoleria" 10 1
39 "detersivo per piatti" "casa" 8.10 1
50 "carta" "casa" 8.10 2
8 "cavo usb" "casa" 8.10 1
90 "ventilatore" "casa" 8.10 3
231 "sgrassatore" "casa" 8.10 9
167 "dentifricio" "igiene personale" 9 5
Vediamo che ci sono diversi prodotti con lo stesso prezzo, e quindi questa query non ci torna di aiuto.
Se ti sei laureat* qualche tempo fa, magari avrai sentito parlare di PARTITION BY e ROW_NUMBER(), due funzioni che permettono di effettuare operazioni come assegnare un intero sequenziale a ciascuna riga in un set di risultati (nel caso di ROW_NUMBER()).
Si potrebbe quindi trasformare la query precedente in una come questa:
WITH highest_cost_product AS (
SELECT
ROW_NUMBER() OVER (
PARTITION BY categoria ORDER BY prezzo DESC
) AS rn,
*
FROM
prodotto
)
SELECT
*
FROM
highest_cost_product
WHERE
rn = 1
ORDER BY
categoria;
Il risultato in questo caso è lo stesso, ma senza i duplicati:
1 174 "felpa" "abbigliamento" 7 6
1 183 "riso" "alimentari" 10 6
1 2 "set di penne" "cartoleria" 10 1
1 50 "carta" "casa" 8.10 2
1 167 "dentifricio" "igiene personale" 9 5
…
Fino ad ora, risulta abbastanza familiare, no?
Grazie a PostgreSQL, è possibile usare una clausola speciale DISTINCT ON per trovare la prima riga in un gruppo:
SELECT DISTINCT ON (categoria)
*
FROM
prodotto
ORDER BY
categoria,
prezzo DESC;
-----------------------------
174 "felpa" "abbigliamento" 7 6
183 "riso" "alimentari" 10 6
2 "set di penne" "cartoleria" 10 1
50 "carta" "casa" 8.10 2
167 "dentifricio" "igiene personale" 9 5
WOW! Decisamente più semplice.
Leggendo la documentazione, vediamo cosa si dice dell’istruzione DISTINCT ON:
SELECT DISTINCT ON ( espressione [, …] ) mantiene solo la prima riga di ogni insieme di righe in cui le espressioni date risultano uguali.
Il motivo per cui non se ne sente mai parlare è proprio che fa parte delle clausole cosiddette non standard:
Clausole non standard
DISTINCT ON ( … ) è un’estensione dello standard SQL.
In questo caso, PostgreSQL fa tutto il lavoro pesante per noi. L’unico requisito è ORDER BY il campo per cui raggruppiamo (in questo caso categoria e prezzo), che consente anche il “raggruppamento” per più di un campo, il che rende questa clausola ancora più potente.
IS DISTINCT FROM
Il confronto dei valori in SQL può portare a tre risultati: true, false o unknown. Prendiamo ad esempio questa query:
WITH example AS (
SELECT 1 AS a, 1 AS b UNION ALL
SELECT 1, 2 UNION ALL
SELECT NULL, 1 UNION ALL
SELECT NULL, NULL
)
SELECT
a,
b,
a = b as equal
FROM
example;
a | b | equal
------+------+-------
1 | 1 | true
1 | 2 | false
NULL | 1 | NULL
NULL | NULL | NULL
Il risultato del confronto di NULL con NULL utilizzando l’uguaglianza (=) è UNKNOWN (contrassegnato come NULL nella tabella), un po’ come 0 alla 0!
In SQL 1 è uguale a 1 e NULL IS NULL restituisce vero, ma NULL non è uguale a NULL. Da perdere la testa, no?
È importante essere consapevoli di questa sottigliezza perché il confronto dei campi nullable potrebbe produrre risultati imprevisti.
La condizione completa per ottenere true o false quando si confrontano campi nullable è:
WITH example AS (
SELECT 1 AS a, 1 AS b UNION ALL
SELECT 1, 2 UNION ALL
SELECT NULL, 1 UNION ALL
SELECT NULL, NULL
)
SELECT
a,
b,
(a is null and b is null)
or
(a is not null and b is not null and a = b)
as full_condition
FROM
example;
-------------------------------
a | b | equal | full_condition
------+------+-------+----------
1 1 true
1 2 false
NULL 1 false
NULL NULL true
Questo è il risultato che vogliamo ottenere, ma è molto lungo. C’è un modo migliore?
Anche in questo caso, PostgreSQL ci fornisce un’opzione in più, ed è IS DISTINCT FROM:
WITH example AS (
SELECT 1 AS a, 1 AS b UNION ALL
SELECT 1, 2 UNION ALL
SELECT NULL, 1 UNION ALL
SELECT NULL, NULL
)
SELECT
a,
b,
(a is null and b is null)
or
(a is not null and b is not null and a = b)
as full_condition
FROM
example;
Il risultato dunque è:
a | b | equal | full_condition
------+------+-------+----------
1 1 true
1 2 false
NULL 1 false
NULL NULL true
La documentazione di PostgreSQL spiega in questo modo IS DISTINCT FROM:
IS DISTINCT FROM e IS NOT DISTINCT FROM tratta NULL come se fosse un valore noto, piuttosto che come un caso speciale per indicare sconosciuto.
Molto meglio: breve e conciso.
Ma in che modo gli altri database gestiscono questo?
MySQL — un operatore speciale, ossia ⇔, che ha funzionalità simili; Oracle invece fornisce una funzione chiamata LNNVL per confrontare i campi nullable (in bocca al lupo).
Per quanto riguarda SQL Server? Non pervenuto.
