Funkce okna (SQL) - Window function (SQL)
V SQL je funkce okna nebo analytická funkce funkce, která používá hodnoty z jednoho nebo více řádků k vrácení hodnoty pro každý řádek. (To kontrastuje s agregační funkcí , která vrací jednu hodnotu pro více řádků.) Funkce okna mají klauzuli OVER; jakákoli funkce bez klauzule OVER není funkcí okna, ale spíše agregovanou nebo jednoradovou (skalární) funkcí.
Jako příklad uvádíme dotaz, který pomocí funkce okna porovnává každého zaměstnance s průměrným platem jeho oddělení (příklad z dokumentace PostgreSQL ):
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
Výstup:
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)
Tyto PARTITION BY klauzule skupiny řádků do oddílů a funkce se aplikuje na každý blok zvlášť. Pokud je PARTITION BY klauzule vynechána (například kdybychom měli prázdnou OVER() klauzuli), pak bude celá sada výsledků považována za jediný oddíl. U tohoto dotazu by průměrný vykázaný plat byl průměrem převzatým ze všech řádků.
Funkce okna se vyhodnocují po agregaci (například po GROUP BY klauzuli a agregačních funkcích mimo okno).
Syntax
Podle dokumentace PostgreSQL má funkce okna syntaxi jedné z následujících:
function_name ([expression [, expression ... ]]) OVER window_name
function_name ([expression [, expression ... ]]) OVER ( window_definition )
function_name ( * ) OVER window_name
function_name ( * ) OVER ( window_definition )
kde window_definition má syntaxi:
[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]
frame_clause má syntaxi jedné z následujících možností:
{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]
{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]
frame_start a frame_end může být UNBOUNDED PRECEDING , offset PRECEDING , CURRENT ROW , offset FOLLOWING nebo UNBOUNDED FOLLOWING . frame_exclusion může být EXCLUDE CURRENT ROW , EXCLUDE GROUP , EXCLUDE TIES nebo EXCLUDE NO OTHERS .
expression odkazuje na jakýkoli výraz, který neobsahuje volání funkce okna.
Notace:
- Závorky [] označují volitelné klauze
- Kudrnaté závorky {} označují sadu různých možných možností, přičemž každá možnost je ohraničena svislou čárkou |
Příklad
Funkce okna umožňují přístup k datům v záznamech těsně před a po aktuálním záznamu. Funkce okna definuje rámeček nebo okno řádků s danou délkou kolem aktuálního řádku a provádí výpočet napříč množinou dat v okně.
NAME |
------------
Aaron| <-- Preceding (unbounded)
Andrew|
Amelia|
James|
Jill|
Johnny| <-- 1st preceding row
Michael| <-- Current row
Nick| <-- 1st following row
Ophelia|
Zach| <-- Following (unbounded)
Ve výše uvedené tabulce další dotaz extrahuje pro každý řádek hodnoty okna s jedním předchozím a jedním následujícím řádkem:
SELECT
LAG(name, 1)
OVER(ORDER BY name) "prev",
name,
LEAD(name, 1)
OVER(ORDER BY name) "next"
FROM people
ORDER BY name
Výsledný dotaz obsahuje následující hodnoty:
| 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)|
Dějiny
Funkce okna byly zavedeny v SQL: 2003 a funkce byly rozšířeny v pozdějších specifikacích.
