Funcția Window (SQL) - Window function (SQL)
În SQL , o funcție de fereastră sau funcție analitică este o funcție care folosește valori de pe unul sau mai multe rânduri pentru a returna o valoare pentru fiecare rând. (Aceasta contrastează cu o funcție agregată , care returnează o singură valoare pentru mai multe rânduri.) Funcțiile ferestrei au o clauză OVER; orice funcție fără clauză OVER nu este o funcție de fereastră, ci mai degrabă o funcție agregată sau cu un singur rând (scalar).
De exemplu, iată o interogare care folosește o funcție de fereastră pentru a compara fiecare angajat cu salariul mediu al departamentului său (exemplu din documentația PostgreSQL ):
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
Ieșire:
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)
Cele PARTITION BY grupează clauză rânduri în partiții și funcția se aplică pentru fiecare partiție separat. Dacă PARTITION BY clauza este omisă (cum ar fi dacă avem o OVER() clauză goală ), atunci întregul set de rezultate tratat ca o singură partiție. Pentru această interogare, salariul mediu raportat ar fi media preluată pe toate rândurile.
Funcțiile ferestrei sunt evaluate după agregare (de exemplu, după GROUP BY clauză și funcțiile agregate care nu sunt ferestre).
Sintaxă
Conform documentației PostgreSQL, o funcție de fereastră are sintaxa uneia dintre următoarele:
function_name ([expression [, expression ... ]]) OVER window_name
function_name ([expression [, expression ... ]]) OVER ( window_definition )
function_name ( * ) OVER window_name
function_name ( * ) OVER ( window_definition )
unde window_definition are sintaxă:
[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]
frame_clause are sintaxa uneia dintre următoarele:
{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]
{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]
frame_start și frame_end poate fi UNBOUNDED PRECEDING , offset PRECEDING , CURRENT ROW , offset FOLLOWING sau UNBOUNDED FOLLOWING . frame_exclusion poate fi EXCLUDE CURRENT ROW , EXCLUDE GROUP , EXCLUDE TIES sau EXCLUDE NO OTHERS .
expression se referă la orice expresie care nu conține un apel către o funcție de fereastră.
Notaţie:
- Parantezele [] indică clauze opționale
- Acoladurile {} indică un set de diferite opțiuni posibile, cu fiecare opțiune delimitată de o bară verticală |
Exemplu
Funcțiile de fereastră permit accesul la datele din înregistrări chiar înainte și după înregistrarea curentă. O funcție de fereastră definește un cadru sau o fereastră de rânduri cu o lungime dată în jurul rândului curent și efectuează un calcul pe ansamblul de date din fereastră.
NAME |
------------
Aaron| <-- Preceding (unbounded)
Andrew|
Amelia|
James|
Jill|
Johnny| <-- 1st preceding row
Michael| <-- Current row
Nick| <-- 1st following row
Ophelia|
Zach| <-- Following (unbounded)
În tabelul de mai sus, următoarea interogare extrage pentru fiecare rând valorile unei ferestre cu un rând precedent și unul următor:
SELECT
LAG(name, 1)
OVER(ORDER BY name) "prev",
name,
LEAD(name, 1)
OVER(ORDER BY name) "next"
FROM people
ORDER BY name
Interogarea rezultatului conține următoarele 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)|
Istorie
Funcțiile Window au fost introduse în SQL: 2003 și funcționalitatea a fost extinsă în specificațiile ulterioare.
