Prontuario di Funzioni Finestra in Italiano

Funzioni finestra

Le funzioni finestra calcolano il risultato in base a una finestra scorrevole (frame), un insieme di righe in qualche modo correlate alla riga corrente.

Funzioni di aggregazione vs. funzioni finestra

A differenza delle funzioni di aggregazione, le funzioni finestra non comprimono le righe.

Sintassi

SELECT citta, mese, 
  SUM(venduto) OVER (
    PARTITION BY citta 
    ORDER BY mese 
    RANGE UNBOUNDED PRECEDING) totale
FROM vendite;

SELECT <colonna_1>, <colonna_2>,
  <funzione_finestra> OVER (
    PARTITION BY <...>
    ORDER BY <...>
    <window_frame>) <alias_colonna_finestra>
FROM <nome_tabella>;

Definizione di finestra con nome

SELECT paese, citta,
  RANK() OVER media_vendite_paese
FROM vendite
WHERE mese BETWEEN 1 AND 6
GROUP BY paese, citta
HAVING sum(venduto) > 10000
WINDOW media_vendite_paese AS (
  PARTITION BY paese 
  ORDER BY avg(venduto) DESC)
ORDER BY paese, citta;

SELECT <colonna_1>, <colonna_2>,
  <funzione_finestra>() OVER <nome_finestra>
FROM <nome_tabella>
WHERE <...>
GROUP BY <...>
HAVING <...>
WINDOW <nome_finestra> AS (
  PARTITION BY <...>
  ORDER BY <...>
  <window_frame>)
ORDER BY <...>;

PARTITION BY, ORDER BY e la definizione della cornice della finestra (window frame) sono tutti opzionali.

Ordine logico delle operazioni in SQL

È possibile utilizzare le funzioni finestra in SELECT e ORDER BY. Tuttavia, non è possibile inserire le funzioni finestra in nessun punto delle clausole FROM, WHERE, GROUP BY o HAVING.

PARTITION BY

PARTITION BY divide le righe in gruppi multipli, chiamati partizioni, a cui viene applicata la funzione finestra.

Partizione predefinita: senza la clausola PARTITION BY, l'intero insieme di risultati è la partizione.

ORDER BY

ORDER BY specifica l'ordine delle righe in ogni partizione a cui viene applicata la funzione finestra.

ORDER BY predefinito: senza la clausola ORDER BY, l'ordine delle righe all'interno di ogni partizione è arbitrario.

Cornice della finestra

Una cornice (frame) è un insieme di righe che sono in qualche modo collegate alla riga corrente. La cornice della finestra viene valutata separatamente all'interno di ogni partizione.

<ROWS | RANGE | GROUPS> BETWEEN limite_inferiore AND limite_superiore

I limiti possono essere una qualsiasi delle cinque opzioni:

• UNBOUNDED PRECEDING
• n PRECEDING
• CURRENT ROW
• n FOLLOWING
• UNBOUNDED FOLLOWING

Il limite_inferiore deve essere PRECEDENTE al limite_superiore.

Abbreviazioni

Cornice predefinita della finestra

Se viene specificato ORDER BY, la cornice è RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Senza ORDER BY, la cornice è ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Elenco delle funzioni finestra

• Funzioni di aggregazione
• avg()
• count()
• max()
• min()
• sum()
• Funzioni di ranking
• row_number()
• rank()
• dense_rank()
• Funzioni di distribuzione
• percent_rank()
• cume_dist()
• Funzioni analitiche
• lead()
• lag()
• ntile()
• first_value()
• last_value()
• nth_value()

Funzioni di aggregazione

• avg(expr) – valore medio per le righe all'interno della finestra
• count(expr) – conteggio dei valori per le righe all'interno del riquadro della finestra
• max(expr) – valore massimo all'interno della finestra
• min(expr) – valore minimo all'interno della finestra
• sum(expr) – somma dei valori all'interno della finestra

ORDER BY e finestre: le funzioni di aggregazione non richiedono un ORDER BY. Accettano la definizione di finestra (ROWS, RANGE, GROUPS).

Funzioni di ranking

• row_number() – numero unico per ogni riga all'interno della partizione, con numeri diversi per i valori uguali
• rank() – rango all'interno della partizione, con spazi vuoti e stesso rango per i valori uguali
• dense_rank() – rango all'interno della partizione, senza spazi vuoti e con lo stesso rango per i valori uguali

ORDER BY e Window Frame: rank() e dense_rank() richiedono ORDER BY, ma row_number() non richiede ORDER BY. Le funzioni di ranking non accettano la definizione di cornice (ROWS, RANGE, GROUPS).

Funzioni di distribuzione

• percent_rank() – il percentile di una riga, un valore nell'intervallo [0, 1]: (rank-1) / (numero totale di righe - 1)
• cume_dist() – la distribuzione cumulativa di un valore all'interno di un gruppo di valori, cioè il numero di righe con valori inferiori o uguali al valore della riga corrente diviso per il numero totale di righe; un valore nell'intervallo (0, 1]

ORDER BY e Window Frame: le funzioni di distribuzione richiedono ORDER BY. Non accettano la definizione di cornice (ROWS, RANGE, GROUPS).

Funzioni analitiche

• lead(expr, offset, predefinito) - il valore per l'offset delle righe successive alla corrente; offset e predefinito sono opzionali; valori predefiniti: offset = 1, predefinito = NULL
• lag(expr, offset, predefinito) - il valore per l'offset delle righe prima di quella corrente; offset e predefinito sono opzionali; valori predefiniti: offset = 1, predefinito = NULL

• ntile(n) – divide le righe all'interno di una partizione il più equamente possibile in n gruppi e assegna a ogni riga il numero del gruppo.

ORDER BY e Window Frame: ntile(), lead() e lag() richiedono un ORDER BY. Non accettano la definizione di cornice (ROWS, RANGE, GROUPS).

• first_value(expr) – il valore della prima riga all'interno del riquadro della finestra
• last_value(expr) – il valore dell'ultima riga all'interno della cornice della finestra

Nota: di solito si consiglia di utilizzare RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING con last_value(). Con la cornice predefinita per ORDER BY, RANGE UNBOUNDED PRECEDING, last_value() restituisce il valore della riga corrente.

• nth_value(expr, n) – il valore per la riga n-esima all'interno del riquadro della finestra; n deve essere un numero intero

ORDER BY e Window Frame: first_value(), last_value() e nth_value() non richiedono un ORDER BY. Accettano la definizione di cornice (ROWS, RANGE, GROUPS).