Funzione finestra (SQL) - Window function (SQL)
In SQL , una funzione finestra o una funzione analitica è una funzione che utilizza valori da una o più righe per restituire un valore per ogni riga. (Ciò contrasta con una funzione aggregata , che restituisce un singolo valore per più righe.) Le funzioni della finestra hanno una clausola OVER; qualsiasi funzione senza una clausola OVER non è una funzione finestra, ma piuttosto una funzione aggregata o a riga singola (scalare).
Ad esempio, ecco una query che utilizza una funzione finestra per confrontare ogni dipendente con lo stipendio medio del proprio dipartimento (esempio dalla documentazione di PostgreSQL ):
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
Produzione:
depname | empno | salary | avg ----------+-------+--------+---------------------- develop | 11 | 5200 | 5020.0000000000000000 develop | 7 | 4200 | 5020.0000000000000000 develop | 9 | 4500 | 5020.0000000000000000 develop | 8 | 6000 | 5020.0000000000000000 develop | 10 | 5200 | 5020.0000000000000000 personnel | 5 | 3500 | 3700.0000000000000000 personnel | 2 | 3900 | 3700.0000000000000000 sales | 3 | 4800 | 4866.6666666666666667 sales | 1 | 5000 | 4866.6666666666666667 sales | 4 | 4800 | 4866.6666666666666667 (10 rows)
La PARTITION BY clausola raggruppa le righe in partizioni e la funzione viene applicata a ciascuna partizione separatamente. Se la PARTITION BY clausola viene omessa (ad esempio se abbiamo una OVER() clausola vuota ), l'intero set di risultati viene trattato come una singola partizione. Per questa query, lo stipendio medio riportato sarebbe la media rilevata su tutte le righe.
Le funzioni di finestra vengono valutate dopo l'aggregazione (dopo la GROUP BY clausola e le funzioni di aggregazione non di finestra, ad esempio).
Sintassi
Secondo la documentazione di PostgreSQL, una funzione finestra ha la sintassi di una delle seguenti:
function_name ([expression [, expression ... ]]) OVER window_name
function_name ([expression [, expression ... ]]) OVER ( window_definition )
function_name ( * ) OVER window_name
function_name ( * ) OVER ( window_definition )
dove window_definition ha la sintassi:
[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]
frame_clause ha la sintassi di una delle seguenti:
{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]
{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]
frame_start e frame_end può essere UNBOUNDED PRECEDING , offset PRECEDING , CURRENT ROW , offset FOLLOWING , o UNBOUNDED FOLLOWING . frame_exclusion Può essere EXCLUDE CURRENT ROW , EXCLUDE GROUP , EXCLUDE TIES , o EXCLUDE NO OTHERS .
expression si riferisce a qualsiasi espressione che non contiene una chiamata a una funzione finestra.
Notazione:
- Le parentesi [] indicano clausole facoltative
- Le parentesi graffe {} indicano un insieme di diverse opzioni possibili, con ciascuna opzione delimitata da una barra verticale |
Esempio
Le funzioni della finestra consentono l'accesso ai dati nei record subito prima e dopo il record corrente. Una funzione finestra definisce un frame o una finestra di righe con una data lunghezza attorno alla riga corrente ed esegue un calcolo sull'insieme di dati nella finestra.
NAME |
------------
Aaron| <-- Preceding (unbounded)
Andrew|
Amelia|
James|
Jill|
Johnny| <-- 1st preceding row
Michael| <-- Current row
Nick| <-- 1st following row
Ophelia|
Zach| <-- Following (unbounded)
Nella tabella sopra, la query successiva estrae per ogni riga i valori di una finestra con una riga precedente e una successiva:
SELECT
LAG(name, 1)
OVER(ORDER BY name) "prev",
name,
LEAD(name, 1)
OVER(ORDER BY name) "next"
FROM people
ORDER BY name
La query dei risultati contiene i seguenti valori:
| PREV | NAME | NEXT | |----------|----------|----------| | (null)| Aaron| Andrew| | Aaron| Andrew| Amelia| | Andrew| Amelia| James| | Amelia| James| Jill| | James| Jill| Johnny| | Jill| Johnny| Michael| | Johnny| Michael| Nick| | Michael| Nick| Ophelia| | Nick| Ophelia| Zach| | Ophelia| Zach| (null)|
Storia
Le funzioni della finestra sono state introdotte in SQL: 2003 e le funzionalità sono state ampliate nelle specifiche successive.
