Worldlex Wiki
Article de reference

Fonction de fenêtre (SQL)

En SQL , une fonction de fenêtre ou fonction analytique est une fonction qui utilise les valeurs d'une ou plusieurs lignes pour renvoyer une valeur pour chaque ligne. (Ceci cont...

Worldlex WikiContenu en francaisLecture gratuite

En SQL , une fonction de fenêtre ou fonction analytique est une fonction qui utilise les valeurs d'une ou plusieurs lignes pour renvoyer une valeur pour chaque ligne. (Ceci contraste avec une fonction d'agrégation , qui renvoie une seule valeur pour plusieurs lignes.) Les fonctions de fenêtre possèdent 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 fonction scalaire (à une seule ligne).

Exemple

À titre d'exemple, voici une requête qui utilise une fonction de fenêtre pour comparer le salaire de chaque employé avec le salaire moyen de son département (exemple tiré de la documentation PostgreSQL ) : 

SELECT depname , empno , salary , avg ( salary ) OVER ( PARTITION BY depname ) FROM empsalary ;

Sortir: 

nom du département | numéro d'employé | salaire | moyenne ----------+-------+--------+---------------------- développement | 11 | 5200 | 5020.00000000000000000 développement | 7 | 4200 | 5020.0000000000000000 développement | 9 | 4500 | 5020.0000000000000000 développement | 8 | 6000 | 5020.0000000000000000 développement | 10 | 5200 | 5020.0000000000000000 Personnel | 5 | 3500 | 3700 Personnel | 2 | 3900 | 3700 ventes | 3 | 4800 | 4866.6666666666666667 Ventes | 1 | 5000 | 4866.6666666666666667 ventes | 4 | 4800 | 4866.6666666666666667 (10 lignes)

Cette PARTITION BYclause regroupe les lignes en partitions, et la fonction est appliquée à chaque partition séparément. Si la PARTITION BYclause est omise (par exemple, si OVER()elle est vide), l'ensemble des résultats est traité comme une seule partition. Pour cette requête, le salaire moyen indiqué correspond à la moyenne calculée sur toutes les lignes.

Les fonctions de fenêtrage sont évaluées après l'agrégation (après la GROUP BYclause et les fonctions d'agrégation non fenêtrées, par exemple).

Syntaxe

Selon la documentation PostgreSQL, une fonction de fenêtre a la syntaxe de l'une des suivantes : 

nom_fonction ([ expression [, expression ... ]]) SUR nom_fenêtre nom_fonction ([ expression [, expression ... ]]) SUR ( définition_fenêtre ) nom_fonction ( * ) SUR nom_fenêtre nom_fonction ( * ) SUR ( définition_fenêtre )

où window_definitiona une syntaxe :

[ nom_fenêtre_existant ] [ PARTITION BY expression [, ... ] ] [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ... ] ] [ frame_clause ]

frame_clausepossède la syntaxe suivante :

{ PLAGE | LIGNES | GROUPES } début_cadre [ exclusion_cadre ] { PLAGE | LIGNES | GROUPES } ENTRE début_cadre ET fin_cadre [ exclusion_cadre ]

frame_startet frame_endpeut être UNBOUNDED PRECEDING, offset PRECEDING, CURRENT ROW, offset FOLLOWING, ou UNBOUNDED FOLLOWING. frame_exclusionpeut être EXCLUDE CURRENT ROW, EXCLUDE GROUP, EXCLUDE TIES, ou EXCLUDE NO OTHERS.

expressiondésigne toute expression qui ne contient pas d'appel à une fonction de fenêtre.

Notation:

  • Les crochets [] indiquent des clauses facultatives
  • Les accolades {} indiquent un ensemble d'options possibles différentes, chaque option étant délimitée par une barre verticale |

Exemple

Les fonctions de fenêtre permettent d'accéder aux données des enregistrements précédant et suivant immédiatement l'enregistrement courant. Une fonction de fenêtre définit une fenêtre de lignes d'une longueur donnée autour de la ligne courante et effectue un calcul sur l'ensemble des données contenues dans cette fenêtre. 

 NOM | ------------ Aaron| <-- Précédant (sans limite) Amélia | André James Jill| Johnny| <-- 1ère ligne précédente Michael| <-- Ligne actuelle Nick | <-- 1ère ligne suivante Ophélie | Zach | <-- Suivi (illimité)

Dans le tableau ci-dessus, la requête suivante extrait pour chaque ligne les valeurs d'une fenêtre comportant une ligne précédente et une ligne 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 : 

| PRÉCÉDENT | NOM | SUIVANT | |----------|----------|----------| | (null)| Aaron| Amelia| | Aaron | Amélia | Andrew | | Amélie | Andrew | James | | Andrew | James | Jill | | James | Jill | Johnny | | Jill | Johnny | Michael | | Johnny | Michael | Nick | | Michael | Nick | Ophélie | | Nick | Ophélie | Zach | | Ophélie | Zach | (null) |

Histoire

Les fonctions de fenêtrage ont été intégrées à la norme SQL:2003 et leurs fonctionnalités ont été étendues dans des spécifications ultérieures.

La prise en charge de certaines implémentations de bases de données a été ajoutée comme suit :