Window Function

MySQL ha introdotto con la versione 8 le Window Function, funzioni da eseguire su gruppi di righe individuate per posizione.
Tradizionalmente, il linguaggio SQL permette di eseguire funzioni su tabelle intere
(eventualmente frutto di JOIN) o su raggruppamenti di righe prodotti con un
GROUP BY.
In tali casi non rientrano situazioni in cui, ad esempio, si vuole confrontare un valore di un campo con quello analogo della riga
che precede o un valore con il massimo delle trenta righe seguenti. Le Window Function intervengono proprio in queste situazioni, avvantaggiandosi della
parola chiave OVER che introduce la definizione della finestra su cui esse devono operare. I risultati della query possono a loro volta essere suddivisi
per partizioni mediante PARTITION BY seguito da un criterio che può essere il nome di un campo o il risultato di una funzione.

Quali sono le Window Function

Per prima cosa vediamo quali sono le Window Function introdotte con la versione 8 di MySQL e subito dopo il modo in cui esse possono essere invocate:

• LAG: recupera il
  valore di un campo nella riga precedente;
• LEAD: recupera il valore
  corrispondente nella riga successiva;
• FIRST_VALUE:
  indica il primo valore della finestra;
• LAST_VALUE:
  indica l'ultimo valore della finestra;
• NTH_VALUE:
  recupera l'ennesimo valore della finestra;
• ROW_NUMBER:
  recupera il numero di riga corrente nella partizione;
• RANK: fornisce l'ordine della
  riga nella partizione corrente;
• PERCENT_RANK: è lo stesso
  concetto di RANK del punto precedente, ma espresso in percentuale;
• DENSE_RANK: identica alla
  funzione RANK ma considera nella stessa posizione gli elementi duplicati;
• CUME_DIST: è finalizzato al calcolo della
  distribuzione cumulativa;
• NTILE: suddivide i valori di un campo in un certo numero di gruppi
  (indicato come argomento della funzione) ed assegna ad ognuno di questi gruppi un valore progressivo. Tali segmenti prendono il nome di bucket. La funzione NTILE
  fornisce il numero di bucket della riga corrente.

L'applicazione delle Window Function segue questa struttura:

[funzione]
OVER(
	PARTITION BY [parametri di partizionamento]
	ORDER BY [parametri di ordinamento]
	[limiti della finestra]
)

dove:

• [funzione] indica la funzione da applicare sulla finestra di righe. Può trattarsi di una delle Window Function viste poco fa o di una
  classica funzione di aggregazione come COUNT, MAX, MIN e via dicendo;
• OVER è la parola chiave centrale delle Window Function che separa la funzione da applicare dalla definizione
  della finestra;
• PARTITION BY [parametri di partizionamento] indica il criterio in base al quale i record vanno partizionati. Non è obbligatorio, se omesso
  tutti record coinvolti nella query saranno ritenuti membri di un'unica partizione;
• ORDER BY [parametri di ordinamento] specifica in che ordine saranno messe le righe al fine di una corretta distribuzione tra le finestre;
• [limiti della finestra] specifica dove inizia e finisce ogni finestra.

Esempi

Come esempio, immaginiamo di avere una lista di numeri associati ad un ID, 20 record nella tabella valori:

+------+--------+
| id   | valore |
+------+--------+
|    1 |     12 |
|    2 |     21 |
|    3 |     26 |
|    4 |     28 |
|    5 |     22 |
|    6 |     19 |
|    7 |     15 |
|    8 |     17 |
|    9 |     12 |
|   10 |      8 |
|   11 |     10 |
|   12 |     14 |
|   13 |     18 |
|   14 |     22 |
|   15 |     26 |
|   16 |     23 |
|   17 |     24 |
|   18 |     19 |
|   19 |     18 |
|   20 |     15 |
+------+--------+

Supponiamo di voler confrontare un valore con quello della riga che lo precede, considerando i record in ordine crescente rispetto agli ID:

> SELECT id, valore, LAG(valore) OVER (ORDER BY id) AS precedente FROM valori;
+------+--------+------------+
| id   | valore | precedente |
+------+--------+------------+
|    1 |     12 |       NULL |
|    2 |     21 |         12 |
|    3 |     26 |         21 |
|    4 |     28 |         26 |
|    5 |     22 |         28 |

Per la definizione della finestra abbiamo richiesto solo che le righe venissero ordinate ma con la funzione LAG non è necessario porre limiti. Osservando
i risultati, vediamo che alla prima riga appare il valore 12 con NULL al secondo campo. Ciò in quanto 12 non è preceduto da nessuno. Dalle
righe successive, però, vediamo che ogni valore della sequenza è confrontato con quello della riga precedente.
A titolo di confronto, eseguiamo la medesima interrogazione ma con la funzione LEAD:

>  SELECT id, valore, LEAD(valore) OVER (ORDER BY id) AS successivo FROM valori;
+------+--------+------------+
| id   | valore | successivo |
+------+--------+------------+
|    1 |     12 |         21 |
|    2 |     21 |         26 |
|    3 |     26 |         28 |
|    4 |     28 |         22 |
|    5 |     22 |         19 |
  ...      ...    ...    ...
  ...      ...    ...    ...
|   19 |     18 |         15 |
|   20 |     15 |       NULL |
+------+--------+------------+

In questo caso, notiamo che la prima riga mette a confronto il valore 12 con 21 ossia l'elemento che lo segue. All'ultima riga non viene restituito nessun
valore da LEAD tanto che appare un NULL.

Funzioni come LEAD e LAG sono utili per poter calcolare scostamenti assoluti o percentuali tra un valore e l'altro di una sequenza:

> SELECT id, valore, LAG(valore) OVER (ORDER BY id) AS precendente, valore-LAG(valore) OVER (ORDER BY id) AS differenza FROM valori;
+------+--------+-------------+------------+
| id   | valore | precendente | differenza |
+------+--------+-------------+------------+
|    1 |     12 |        NULL |       NULL |
|    2 |     21 |          12 |          9 |
|    3 |     26 |          21 |          5 |
|    4 |     28 |          26 |          2 |
|    5 |     22 |          28 |         -6 |
|    6 |     19 |          22 |         -3 |

In molti casi, è utile creare delle finestre contenenti elementi in numero limitato e su queste applicare la funzione che interessa. Ad esempio,
confrontiamo ogni valore con il massimo delle tre righe successive: la finestra quindi è lunga complessivamente quattro righe (quella corrente più le
tre successive).

> SELECT id, valore, MAX(valore) OVER (ORDER BY id RANGE BETWEEN CURRENT ROW AND 3 FOLLOWING) AS massimo FROM valori;
+------+--------+-------------+
| id   | valore |   massimo   |
+------+--------+-------------+
|    1 |     12 |          28 |
|    2 |     21 |          28 |
|    3 |     26 |          28 |
|    4 |     28 |          28 |
|    5 |     22 |          22 |
|    6 |     19 |          19 |
|    7 |     15 |          17 |
|    8 |     17 |          17 |
|    9 |     12 |          14 |
|   10 |      8 |          18 |

Osservando i risultati si può vedere che nelle prime righe il massimo segnalato è 28 (valore con id paria a 4) ma dalla quinta riga in poi il
massimo varia a seconda dei valori che vengono incontrati. L'ampiezza di una finestra può essere regolata in vari modi:

• ci si può rivolgere alle righe che precedono quella corrente con RANGE BETWEEN 3 PRECEDING AND CURRENT ROW;
• si può definire un intervallo che include la riga corrente con RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING. In questo caso
  la finestra sarebbe di sette righe (tre precedenti, quella corrente e le tre seguenti);
• si può indicare un punto di fine illimitato con la parola chiave UNBOUNDED come in RANGE BETWEEN 3 PRECEDING AND UNBOUNDED FOLLOWING;
• analogamente, si può includere tutti i valori dall'inizio ancora con la parola chiave UNBOUNDED come in RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Quando abbiamo necessità di utilizzare la medesima finestra per più funzioni possiamo unificarne la definizione posizionandola dopo la clausola
FROM. Nel seguente esempio dobbiamo calcolare massimo e media sul medesimo range di valori pertanto decidiamo di unificarne la definizione con la parola chiave
WINDOW. La finestra prende il nome di w1 ed è sufficiente indicarla dopo le funzioni con la notazione OVER w1:

SELECT id, valore, MAX(valore) OVER w1 AS massimo, AVG(valore) OVER w1 AS media
FROM valori
WINDOW w1 AS (ORDER BY id RANGE BETWEEN 3 PRECEDING AND CURRENT ROW);