En SQL, une fonction de fenêtrage ou fonction analytique[1] 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)[2].
Histoire
modifierLes fonctions de fenêtre ont été intégrées à la norme SQL:2003 (en) et leurs fonctionnalités ont été étendues dans les spécifications ultérieures[3].
La prise en charge d'implémentations de bases de données particulières a été ajoutée comme suit :
Exemple
modifierÀ 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)[11] :
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[12]. 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)[1].
Syntaxe
modifierSelon la documentation PostgreSQL, une fonction de fenêtre a la syntaxe de l'une des suivantes[12] :
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é
modifierLes fonctions de fenêtrage permettent d'accéder aux données dans les enregistrements juste avant et après l'enregistrement actuel[13],[14],[15],[16]. 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[17],[18].
NOM |
------------
Aaron | <-- Précédent (illimité)
André |
Amélie |
Jacques|
Jill |
Johnny | <-- 1ère rangée précédente
Michael | <-- Ligne actuelle
Nick | <-- 1ère rangée suivante
Ophélie |
Zach | <-- Suivant (illimité)
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 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 :
| 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)|
Références
modifier- (en) « Analytic function concepts in Standard SQL | BigQuery », Google Cloud (consulté le 23 mars 2021)
- ↑ « Window Functions », sqlite.org (consulté le 23 mars 2021)
- ↑ « Window Functions Overview », MariaDB KnowledgeBase (consulté le 23 mars 2021)
- ↑ « Oracle 8i Release 2 (8.1.6) New Features », www.oracle.com (consulté le 23 janvier 2025)
- ↑ « Analytic Functions in Oracle 8i », www.stanford.edu (consulté le 23 janvier 2025)
- ↑ « PostgreSQL Release 8.4 », www.postgresql.org, 24 juillet 2014 (consulté le 10 mars 2024)
- ↑ « MySQL :: What's New in MySQL 8.0? (Generally Available) », dev.mysql.com (consulté le 21 novembre 2022)
- ↑ « MySQL :: MySQL 8.0 Reference Manual :: 12.21.2 Window Function Concepts and Syntax », dev.mysql.com
- ↑ « MariaDB 10.2.0 Release Notes », mariadb.com (consulté le 10 mars 2024)
- ↑ « SQLite Release 3.25.0 On 2018-09-15 », www.sqlite.org (consulté le 5 février 2025)
- ↑ (en) « 3.5. Window Functions », PostgreSQL Documentation, 11 février 2021 (consulté le 23 mars 2021)
- (en) « 4.2. Value Expressions », PostgreSQL Documentation, 11 février 2021 (consulté le 23 mars 2021)
- ↑ Leis, Kundhikanjana, Kemper et Neumann, « Efficient Processing of Window Functions in Analytical SQL Queries », Proc. VLDB Endow., vol. 8, no 10, juin 2015, p. 1058–1069 (ISSN 2150-8097, DOI 10.14778/2794367.2794375)
- ↑ Cao, Chan, Li et Tan, « Optimization of Analytic Window Functions », Proc. VLDB Endow., vol. 5, no 11, juillet 2012, p. 1244–1255 (ISSN 2150-8097, DOI 10.14778/2350229.2350243, arXiv 1208.0086)
- ↑ (en-US) « Probably the Coolest SQL Feature: Window Functions », Java, SQL and jOOQ., 3 novembre 2013 (lire en ligne, consulté le 26 septembre 2017)
- ↑ (en-US) « Window Functions in SQL - Simple Talk », Simple Talk, 31 octobre 2013 (lire en ligne, consulté le 26 septembre 2017)
- ↑ « SQL Window Functions Introduction », Apache Drill
- ↑ (en) « PostgreSQL: Documentation: Window Functions », www.postgresql.org (consulté le 4 avril 2020)
