Oltre alle funzionalità di elaborazione che filtri e raggruppamenti permettono, possiamo
applicare funzioni anche a gruppi di righe raccolti in base alla loro posizione. Ad esempio, volessimo
eseguire, per ogni riga, la somma cumulativa delle ultime cinque righe mediante il raggruppamento non sarebbe
fattibile. Per queste finalità è stato introdotto il concetto di window, finestra, che permette di indicare uno slot di righe
ricalcolato in corrispondenza di ogni valore.
Per lavorare con queste utilissime funzionalità è necessario decidere due aspetti:
- cosa rientrerà nella finestra (lo faremo con la parola chiave WINDOW);
- quale funzione dovrà essere applicata sulla finestra. Tra queste potremo chiamare in causa funzioni di aggregazione
comuni(COUNT, SUM, AVG, etc.) o le specifiche Window function
introdotte a questo scopo.
Database per esempi
Per illustrare il loro funzionamento creeremo un piccolo database che prenderà vita dal
file CSV serie.csv provvisto di un contenuto simile:
2019-12-03,12
2019-12-04,11.5
2019-12-05,11.2
2019-12-06,10.85
2019-12-07,10.52
2019-12-08,10.9
2019-12-09,10.43
...
...Il file è a due colonne e rappresenta un valore che varia quotidianamente, potremmo
immaginarlo come il prezzo di un titolo azionario o un indice di qualche genere.
Lo si può importare nel database serie_storiche, nella tabella serie:
DROP DATABASE IF EXISTS serie_storiche CASCADE;
CREATE DATABASE serie_storiche;
USE serie_storiche;
CREATE TABLE serie
(data DATE,
importo FLOAT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';
LOAD DATA LOCAL INPATH 'serie.csv' INTO TABLE serie;importabile attraverso il comando hive.
Definizione di una finestra
La definizione di una finestra avviene all'interno di una query e si può scegliere tra
due modalità diverse. Nella prima specificheremo la finestra di applicazione direttamente
nella proiezione - la porzione di codice tra SELECT e FROM - associando la finestra con la funzione
che deve agire su di essa:
SELECT importo, SUM(importo) OVER (ORDER BY data ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) FROM serie;Con questa query diciamo di voler ottenere dalla tabella serie l'importo nonchè
la somma degli importi delle righe, ordinate per il campo data, comprese tra la riga corrente e le
tre precedenti. Quest'ultima condizione è espressa dalla clausola (ORDER BY data ROWS BETWEEN 3 PRECEDING AND CURRENT ROW):
questa è la finestra che abbiamo definito ed è valida solo sulla funzione cui viene applicata con la parola chiave OVER.
I risultati sono come i seguenti:
...
...
10.43 42.7
10.1 41.95
9.5 40.93
9.8 39.83
10.2 39.6
10.5 40.0
10.9 41.4
11.4 43
...
...Il valore sulla sinistra rappresenta quanto contenuto nel campo importo mentre il valore della colonna di destra
è la somma dell'importo presente sulla stessa riga (CURRENT ROW) e quello delle tre precedenti (3 PRECEDING). Se prendiamo
in considerazione la quinta riga dello stralcio qui riportato (10.2 39.6), il valore 39.6 è la somma di 10.2, 9.8, 9.5
e 10.1.
In alternativa, avremmo potuto eseguire la stessa operazione in un altro modo ovvero definendo la finestra a fine query con la parola
chiave WINDOW assegnandole un identificatore:
SELECT importo, SUM(importo) OVER w FROM serie
WINDOW w as (ORDER BY data ROWS BETWEEN 3 PRECEDING AND CURRENT ROW);In questo caso, viene creata la finestra di nome "w" che può essere utilizzata nella query ovunque venga invocata mediante la
parola chiave OVER. Questo è un modo per poter definire una o più finestre nell'interrogazione e richiamarle più volte a proprio
piacimento: il risultato sarà lo stesso di prima.
Funzioni specifiche per window
Finora, ricapitolando, abbiamo imparato ad applicare funzioni su "finestre" usando le parole chiave WINDOW eOVER ma ad ora
abbiamo considerato solo tradizionali funzioni di aggregazione. In realtà, per massimizzare l'utilità di questa innovazione vanno prese in considerazione anche le
window function. Si tratta di queste:
- LAG: il valore precedente alla riga corrente, coerentemente con l'ordine impostato;
- LEAD: il valore seguente alla riga corrente, coerentemente con l'ordine impostato;
- FIRST_VALUE: il primo valore della finestra;
- LAST_VALUE: l'ultimo valore della finestra.
Con la query seguente vogliamo mettere alla prova queste funzioni ma contemporaneamente vogliamo sperimentare altri aspetti.
Definiamo infatti due finestre, w1 e w2, la prima basata sul semplice ordinamento da usare con LEAD e LAG, la seconda che non si limita alla
riga corrente ma usa la parola chiave FOLLOWING ossia "seguente" per includere cinque righe a slot: le due precedenti, la corrente e le due
seguenti.
SELECT importo, LAG(importo) OVER w1, LEAD(importo) OVER w1, FIRST_VALUE(importo) OVER w2, LAST_VALUE(importo) OVER w2
FROM serie
WINDOW w1 as (ORDER BY data),
w2 as (ORDER BY data ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING);Ecco una porzione del risultato:
...
...
10.85 11.2 10.52 11.5 10.9
10.52 10.85 10.9 11.2 10.43
10.9 10.52 10.43 10.85 10.1
10.43 10.9 10.1 10.52 9.5
10.1 10.43 9.5 10.9 9.8
9.5 10.1 9.8 10.43 10.2
9.8 9.5 10.2 10.1 10.5
10.2 9.8 10.5 9.5 10.9
10.5 10.2 10.9 9.8 11.4
10.9 10.5 11.4 10.2 11.8
11.4 10.9 11.8 10.5 12.4
11.8 11.4 12.4 10.9 12.9
12.4 11.8 12.9 11.4 12.7
12.9 12.4 12.7 11.8 12.6
...
...Prendiamo ad esempio la riga 9.5 10.1 9.8 10.43 10.2, la sesta mostrata. La finestra include, oltre all'importo corrente 9.5,
i due precedenti 10.1 e 10.43 ed i due seguenti 9.8 e 10.2. Infatti nella riga presa in considerazione troviamo:
10.1 (risultato di LAG che si trova prima di 9.5), 9.8 (risultato di LEAD che si trova dopo 9.5), 10.43 (valore iniziale della
finestra, FIRST_VALUE) e 10.2 (valore finale della finestra, LAST_VALUE).
