I diversi volti di DISTINCT in PostgreSQL

  • Di
  • 2021-11-04 - 7 minuti
banner

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.

Partners

Community, aziende e persone che supportano attivamente il blog

Logo di Codemotion
Logo di GrUSP
Logo di Python Milano
Logo di Schrodinger Hat
Logo di Python Biella Group
Logo di Fuzzy Brains
Logo di Django Girls
Logo di Improove
Logo del libro open source

Iscriviti alla newsletter

Per non perderti gli ultimi articoli e per vincere biglietti e gadget TheRedCode

Riceverai una volta al mese (o anche meno) gli articoli più interessanti pubblicati sul blog, e potrai provare a vincere un biglietto per uno dei prossimi eventi!

Andiamo!