Le clausole WITHIN GROUP e FILTER di SQL in PostgreSQL 9.4

PostgreSQL 9.4 amplia lo standard SQL inserendo due nuove clausole che facilitano molte operazioni richieste in fase di sviluppo delle applicazioni: le clausole WITHIN GROUP e FILTER.

La clausola WITHIN GROUP

La clausola WITHIN GROUP è particolarmente utile nei casi in cui si vogliano effettuare aggregazioni su subset ordinati di dati.
PostgreSQL ha introdotto, 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 tali 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.
Sono state introdotte, inoltre, 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 è 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 doverne usare ulteriori 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.
Con la clausola WITHIN GROUP diventa più semplice la gestione di subset di dati ordinabili, introducendo nuove window function. La clausola FILTER facilita la gestione di subset di dati che soddisfano certe condizioni, evitando le aggregazioni.