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.

 

 

 

Ti potrebbe piacere anche…

 

Condividi la tua opinione