Vinduesfunktion (SQL) - Window function (SQL)
I SQL er en vinduesfunktion eller en analytisk funktion en funktion, der bruger værdier fra en eller flere rækker til at returnere en værdi for hver række. (Dette står i kontrast til en samlet funktion , som returnerer en enkelt værdi for flere rækker.) Vinduesfunktioner har en OVER-sætning; enhver funktion uden en OVER-klausul er ikke en vinduesfunktion, men snarere en samlet eller en række (skalar) funktion.
Som et eksempel er her en forespørgsel, der bruger en vinduesfunktion til at sammenligne hver medarbejder med den gennemsnitlige løn for deres afdeling (eksempel fra PostgreSQL- dokumentationen):
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
Produktion:
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)
De PARTITION BY clause grupper rækker i skillevægge, og funktionen anvendes på hver skillevæg separat. Hvis PARTITION BY klausulen udelades (f.eks. Hvis vi har en tom OVER() klausul), behandles hele resultatsættet som en enkelt partition. For denne forespørgsel vil den rapporterede gennemsnitlige løn være gennemsnittet over alle rækker.
Vinduesfunktioner evalueres efter sammenlægning (f.eks. Efter GROUP BY paragrafen og ikke-vinduesaggregatfunktioner).
Syntaks
Ifølge PostgreSQL-dokumentationen har en vinduesfunktion syntaksen for et af følgende:
function_name ([expression [, expression ... ]]) OVER window_name
function_name ([expression [, expression ... ]]) OVER ( window_definition )
function_name ( * ) OVER window_name
function_name ( * ) OVER ( window_definition )
hvor window_definition har syntaksen:
[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]
frame_clause har syntaksen for et af følgende:
{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]
{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]
frame_start og frame_end kan være UNBOUNDED PRECEDING , offset PRECEDING , CURRENT ROW , offset FOLLOWING , eller UNBOUNDED FOLLOWING . frame_exclusion kan være EXCLUDE CURRENT ROW , EXCLUDE GROUP , EXCLUDE TIES , eller EXCLUDE NO OTHERS .
expression henviser til ethvert udtryk, der ikke indeholder et opkald til en vinduesfunktion.
Notation:
- Beslag [] angiver valgfri klausuler
- Krøllede seler {} angiver et sæt forskellige mulige indstillinger, hvor hver indstilling afgrænses af en lodret bjælke |
Eksempel
Vinduesfunktioner giver adgang til data i posterne lige før og efter den aktuelle post. En vinduesfunktion definerer en ramme eller et vindue med rækker med en given længde omkring den aktuelle række og udfører en beregning på tværs af datasættet i vinduet.
NAME |
------------
Aaron| <-- Preceding (unbounded)
Andrew|
Amelia|
James|
Jill|
Johnny| <-- 1st preceding row
Michael| <-- Current row
Nick| <-- 1st following row
Ophelia|
Zach| <-- Following (unbounded)
I ovenstående tabel udtrækker den næste forespørgsel værdierne for et vindue med en forudgående og en efterfølgende række for hver række:
SELECT
LAG(name, 1)
OVER(ORDER BY name) "prev",
name,
LEAD(name, 1)
OVER(ORDER BY name) "next"
FROM people
ORDER BY name
Resultatforespørgslen indeholder følgende værdier:
| 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)|
Historie
Vinduesfunktioner blev introduceret i SQL: 2003 og fik funktionalitet udvidet i senere specifikationer.
