Fönsterfunktion (SQL)
I SQL är en fönsterfunktion eller analytisk funktion en funktion som använder värden från en eller flera rader för att returnera ett värde för varje rad. (Detta står i kontrast till en aggregatfunktion , som returnerar ett enda värde för flera rader.) Fönsterfunktioner har en OVER-sats; vilken funktion som helst utan en OVER-sats är inte en fönsterfunktion, utan snarare en aggregerad eller enradsfunktion (skalär).
Exempel
Som ett exempel, här är en fråga som använder en fönsterfunktion för att jämföra lönen för varje anställd med den genomsnittliga lönen för deras avdelning (exempel från PostgreSQL- dokumentationen ):
VÄLJ depname , empno , salary , avg ( lön ) OVER ( PARTITION BY depname ) FRÅN empsalary ;
Produktion:
depname | empno | lön | avg ----------+--------+--------+---------------------------- - utveckla | 11 | 5200 | 5020.0000000000000000 utveckla | 7 | 4200 | 5020.0000000000000000 utveckla | 9 | 4500 | 5020.0000000000000000 utveckla | 8 | 6000 | 5020.0000000000000000 utveckla | 10 | 5200 | 5020.0000000000000000 personal | 5 | 3500 | 3700.0000000000000000 personal | 2 | 3900 | 3700.0000000000000000 försäljning | 3 | 4800 | 4866.6666666666666667 försäljning | 1 | 5000 | 4866.6666666666666667 försäljning | 4 | 4800 | 4866.6666666666666667 (10 rader)
PARTITION BY- satsen grupperar rader i partitioner och funktionen tillämpas på varje partition separat. Om PARTITION BY -satsen utelämnas (som om vi har en tom OVER()- sats), behandlas hela resultatuppsättningen som en enda partition. För den här frågan skulle den rapporterade genomsnittslönen vara det genomsnitt som tagits över alla rader.
Fönsterfunktioner utvärderas efter aggregering (till exempel efter GROUP BY- satsen och icke-fönsteraggregatfunktioner).
Syntax
Enligt PostgreSQL-dokumentationen har en fönsterfunktion syntaxen för något av följande:
funktionsnamn ([ uttryck [, uttryck ... ]]) ÖVER fönsternamn funktionsnamn ([ uttryck [, uttryck ... ]]) ÖVER ( fönsterdefinition ) funktionsnamn ( * ) ÖVER fönsternamn funktionsnamn ( * ) ÖVER ( fönsterdefinition )
där fönsterdefinition har syntax:
[ existerande_fönsternamn ] [ PARTITION BY expression [, ... ] ] [ ORDER BY expression [ ASC | DESC | ANVÄNDER operator ] [ NULLS { FÖRSTA | LAST } ] [, ... ] ] [ frame_clause ]
frame_clause har syntaxen för något av följande:
{ OMRÅDE | RADER | GRUPPER } frame_start [ frame_exclusion ] { RANGE | RADER | GRUPPER } MELLAN frame_start AND frame_end [ frame_exclusion ]
frame_start och frame_end kan vara UNBOUNDED PRECEDING , offset PRECEDING , CURRENT ROW , offset FOLLOWING , eller UNBOUNDED FOLLOWING . frame_exclusion kan vara EXKLUDERA AKTUELL RADER , EXKLUDERA GRUPP , EXKLUDERA BILDER eller EXKLUDERA INGA ANDRA .
uttryck hänvisar till alla uttryck som inte innehåller ett anrop till en fönsterfunktion.
Notering:
- Hakparenteser [] indikerar valfria satser
- Lockiga hängslen {} indikerar en uppsättning olika möjliga alternativ, där varje alternativ avgränsas av en vertikal streck |
Exempel
Fönsterfunktioner ger tillgång till data i posterna precis före och efter den aktuella posten. En fönsterfunktion definierar en ram eller ett fönster med rader med en given längd runt den aktuella raden och utför en beräkning över datauppsättningen i fönstret.
NAMN | ------------ Aaron| <-- Föregående (oavgränsad) Andrew| Amelia| James| Jill| Johnny| <-- 1:a föregående raden Michael| <-- Nuvarande rad Nick| <-- 1:a följande rad Ophelia| Zach| <-- Följer (obegränsad)
I tabellen ovan extraherar nästa fråga för varje rad värdena för ett fönster med en föregående och en efterföljande rad:
VÄLJ LAG ( namn , 1 ) ÖVER ( ORDER BY NAME ) "prev" , namn , LEAD ( namn , 1 ) OVER ( ORDER BY NAME ) "nästa" FRÅN personer ORDER BY name
Resultatfrågan innehåller följande värden:
| FÖREGÅENDE | NAMN | NÄSTA | |--------|--------|--------| | (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)|
Historia
Fönsterfunktioner introducerades i SQL:2003 och fick funktionalitet utökad i senare specifikationer.
MySQL lade till stöd för fönsterfunktioner i version 8 under 2018, och MariaDB introducerade första fönsterfunktioner med version 10.2.