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
  valore di un campo nella riga precedente;
• LEAD
  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
  riga nella partizione corrente;
• PERCENT_RANK
  concetto di RANK
• DENSE_RANK
  funzione RANK
• CUME_DIST
  distribuzione cumulativa;
• NTILE
  (indicato come argomento della funzione) ed assegna ad ognuno di questi gruppi un valore progressivo. Tali segmenti prendono il nome di bucket 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]
  classica funzione di aggregazione COUNT MAX MIN
• OVER
  della finestra;
• PARTITION BY [parametri di partizionamento]
  tutti record coinvolti nella query saranno ritenuti membri di un'unica partizione;
• ORDER BY [parametri di ordinamento]
• [limiti della 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
i risultati, vediamo che alla prima riga appare il valore 12 con NULL
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
  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 RANGE BETWEEN 3 PRECEDING AND UNBOUNDED FOLLOWING
• analogamente, si può includere tutti i valori dall'inizio ancora con la parola chiave UNBOUNDED 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);

Se vuoi aggiornamenti su su questo argomento inserisci la tua email nel box qui sotto:

Iscriviti

Sì

No

Acconsento alla ricezione di comunicazioni promozionali da parte del Titolare, relative a prodotti e servizi di terzi.

Compilando il presente form acconsento a ricevere le informazioni relative ai servizi di cui alla presente pagina ai sensi dell'informativa sulla privacy