Fonction de fenêtrage (SQL)

En SQL, une fonction de fenêtrage ou 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 de ligne unique (scalaire).

Histoire Les fonctions de fenêtre ont été intégrées à la norme et leurs fonctionnalités ont été étendues dans les spécifications ultérieures.

La prise en charge d'implémentations de bases de données particulières a été ajoutée comme suit :

Oracle - version 8.1.6 en 2000. PostgreSQL - version 8.4 en 2009. MySQL - version 8 en 2018. MariaDB - version 10.2 en 2016. SQLite - version 3.25.0 en 2018.

Exemple À titre d'exemple, voici une requête qui utilise une fonction de fenêtrage pour comparer le salaire de chaque employé avec le salaire moyen de son service (exemple tiré de la documentation PostgreSQL qui marche aussi en MySQL) : SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;

Résultat : depname | empno | salary | avg ----------+-------+--------+---------------------- develop | 11 | 5200 | 5020.00000000000000000 develop | 7 | 4200 | 5020.0000000000000000 develop | 9 | 4500 | 5020.00000000000000000 develop | 8 | 6000 | 5020.0000000000000000 develop | 10 | 5200 | 5020.00000000000000000 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 lignes)

La clause PARTITION BY regroupe les lignes en partitions et la fonction est appliquée à chaque partition séparément. Si la clause PARTITION BY est omise (comme avec une clause OVER() vide), alors l'ensemble des 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 l'agrégation (après la clause GROUP BY et les fonctions d'agrégation non-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'une des suivantes : { RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ] { RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]

frame_start et frame_end peuvent ê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 possibles différentes, chaque option étant délimitée par une barre verticale (|).

Exemple avancé Les fonctions de fenêtrage permettent d'accéder aux données dans les enregistrements juste avant et après l'enregistrement actuel. Une fonction de fenêtrage définit un cadre ou une fenêtre de lignes avec une longueur donnée autour de la ligne actuelle et effectue un calcul sur l'ensemble des données de la fenêtre. NOM |

La requête de résultat contient les valeurs suivantes : | PREV | NAME | NEXT |

Références

Catégorie:SQL

Fonction de fenêtrage (SQL)

Źródło i licencja