Le clausole WITHIN GROUP e FILTER di SQL in PostgreSQL 9.4

Salve,

Potete consultare l'articolo originale su
http://blog.2ndquadrant.it/within-group-e-filter-di-sql-in-postgresql-9-4/.

----

PostgreSQL 9.4 amplia lo standard SQL inserendo due nuove clausole nei
comandi SQL, facilitando molte operazioni richieste in fase di sviluppo
delle applicazioni: la clausola WITHIN GROUP e la clausola FILTER.

La clausola WITHIN GROUP

È una clausola particolarmente utile nei casi in cui si vogliano effettuare
aggregazioni su subset ordinati di dati. PostgreSQL ha visto introdotte
fin dalla versione 9.0 le window function per poter lavorare su subset di
dati correlabili a ciascun record corrente delle tabelle, definendo
una sorta di "finestre di aggregazione" centrate su ogni specifico record
man mano che la query viene eseguita tramite la clausola SQL OVER
(PARTITION BY/ORDER BY ), e sfruttando alcune
funzioni che possono essere eseguite su tali aggregazioni.

Con la versione 9.4 di PostgreSQL è stata introdotta la clausola SQL WITHIN
GROUP che permette di semplificare molte operazioni finora possibili solo
con l'uso delle window function, definendo aggregazioni di subset ordinati
di dati. Inoltre, sono state introdotte nuove funzioni che possono essere
applicate su tali subset e che ampliano la collezione delle window function
presenti:

* percentile_cont(), percentile_disc() per il calcolo di percentili
* mode() funzione statistica che calcola la moda su subset ordinati
* rank(), dense_rank(), percent_rank(), cume_dist(): window function già
presenti in PostgreSQL per essere eseguite sui subset ottenuti tramite la
clausola OVER (PARTITION BY/ORDER BY ) e da adesso in grado di prendere
come parametro subset ordinati prodotti con la clausola WITHIN GROUP

Per capire meglio, supponiamo ad esempio di voler calcolare il 25°, il 50°,
il 75° ed il 100° percentile dei primi 20 numeri interi: finora era
possibile solo partizionando i numeri in 4
set tramite la clausola OVER (PARTITION BY/ORDER BY ) per poi ordinarli
internamente in 4 subset ordinati di cui poi prendiamo il massimo valore,
ad esempio sfruttando una CTE:

$ CREATE TABLE t AS SELECT generate_series(1,20) AS val;

$ WITH subset AS (
SELECT val,
ntile(4) OVER (ORDER BY val) AS tile
FROM t
)
SELECT max(val)
FROM subset GROUP BY tile ORDER BY tile;

max
------
5
10
15
20
(4 rows)

Con PostgreSQL 9.4 tutto si riduce ad un solo comando SQL, comportando
notevoli vantaggi in termini di leggibilità degli script e di esecuzione
dei comandi:

$ CREATE TABLE t AS SELECT generate_series(1,20) AS val;

$ SELECT unnest(percentile_disc(array[0.25,0.5,0.75,1])
WITHIN GROUP (ORDER BY val))
FROM t;

max
------
5
10
15
20
(4 rows)

Clausola +FILTER+ di SQL

Questa seconda clausola dei comandi SQL introdotta in PostgreSQL 9.4 è
utile nei casi in cui si vogliano applicare dei filtri su subset di dati
senza necessariamente eseguire aggregazioni.
Ad esempio è ora possibile effettuare un count totale dei record di una
tabella ed anche parziale di un suo subset che soddisfi una certa
condizione (espressa mediante la clausola WHERE) all'interno di una unica
query, senza dover usare ulteriori query da eseguire sulle aggregazioni:

$ SELECT count(*) count_all,
count(*) FILTER(WHERE bid=1) count_1,
count(*) FILTER(WHERE bid=2) count_2
FROM pgbench_history;

count_all | count_1 | count_2
----------+---------+---------­­­­­­­­­
7914 | 758 | 784
(1 row)

Semplificando anche in questo caso la leggibilità degli script e
migliorando le performance in esecuzione.

Conclusioni

L'estensione dello standard SQL tramite l'introduzione di queste nuove
clausole facilita ulteriormente il compito degli sviluppatori, che si
trovano a poter delegare sempre più
al database la manipolazione e l'aggregazione di subset di dati. Tramite
la clausola WITHIN GROUP diventa più semplice la gestione di subset di dati
ordinabili, introducendo nuove window
function.
La clausola FILTER semplifica la gestione di subset di dati che soddisfano
certe condizioni evitando le aggregazioni.

--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe(dot)broccolo(at)2ndQuadrant(dot)it | www.2ndQuadrant.it