Fonction de fenêtre (SQL) - Window function (SQL)
En SQL , une fonction de fenêtre ou une fonction analytique est une fonction qui utilise les valeurs d'une ou plusieurs lignes pour renvoyer une valeur pour chaque ligne. (Cela contraste avec une fonction d'agrégation , qui renvoie une valeur unique pour plusieurs lignes.) Les fonctions de fenêtre ont une clause OVER; toute fonction sans clause OVER n'est pas une fonction de fenêtre, mais plutôt une fonction d'agrégation ou à une seule ligne (scalaire).
A titre d'exemple, voici une requête qui utilise une fonction de fenêtre pour comparer chaque employé avec le salaire moyen de son service (exemple de la documentation PostgreSQL ):
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
Production:
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 clause regroupe les lignes en partitions et la fonction est appliquée à chaque partition séparément. Si la PARTITION BY clause est omise (comme si nous avons une OVER() clause vide ), alors l'ensemble de résultats est traité comme une seule partition. Pour cette requête, le salaire moyen indiqué serait la moyenne prise sur toutes les lignes.
Les fonctions de fenêtre sont évaluées après agrégation (après la GROUP BY clause et les fonctions d'agrégation sans fenêtre, par exemple).
Syntaxe
Selon la documentation PostgreSQL, une fonction de fenêtre a la syntaxe de l'une des suivantes:
function_name ([expression [, expression ... ]]) OVER window_name
function_name ([expression [, expression ... ]]) OVER ( window_definition )
function_name ( * ) OVER window_name
function_name ( * ) OVER ( window_definition )
où window_definition a la syntaxe:
[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]
frame_clause a la syntaxe de l'un des éléments suivants:
{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]
{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]
frame_start et frame_end peut être UNBOUNDED PRECEDING , offset PRECEDING , CURRENT ROW , offset FOLLOWING ou UNBOUNDED FOLLOWING . frame_exclusion peut être EXCLUDE CURRENT ROW , EXCLUDE GROUP , EXCLUDE TIES ou EXCLUDE NO OTHERS .
expression fait référence à toute expression qui ne contient pas d'appel à une fonction de fenêtre.
Notation:
- Les crochets [] indiquent les clauses facultatives
- Les accolades {} indiquent un ensemble d'options différentes possibles, chaque option étant délimitée par une barre verticale |
Exemple
Les fonctions de fenêtre permettent d'accéder aux données dans les enregistrements juste avant et après l'enregistrement en cours. Une fonction de fenêtre définit un cadre ou une fenêtre de lignes d'une longueur donnée autour de la ligne actuelle et effectue un calcul sur l'ensemble de données de la fenêtre.
NAME |
------------
Aaron| <-- Preceding (unbounded)
Andrew|
Amelia|
James|
Jill|
Johnny| <-- 1st preceding row
Michael| <-- Current row
Nick| <-- 1st following row
Ophelia|
Zach| <-- Following (unbounded)
Dans le tableau ci-dessus, la requête suivante extrait pour chaque ligne les valeurs d'une fenêtre avec une ligne précédente et une suivante:
SELECT
LAG(name, 1)
OVER(ORDER BY name) "prev",
name,
LEAD(name, 1)
OVER(ORDER BY name) "next"
FROM people
ORDER BY name
La requête de résultat contient les valeurs suivantes:
| 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)|
Histoire
Les fonctions de fenêtre ont été introduites dans SQL: 2003 et ont été étendues dans les spécifications ultérieures.
