Fonction de fenêtrage (SQL)

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

[modifier | modifier le code]

Les 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 | modifier le code]

À 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

[modifier | modifier le code]

Selon 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é

[modifier | modifier le code]

Les 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 | modifier le code]
(en) Cet article est partiellement ou en totalité issu de l’article de Wikipédia en anglais intitulé « Window function (SQL) » (voir la liste des auteurs).
  1. ↑ a et b (en) « Analytic function concepts in Standard SQL | BigQuery », Google Cloud (consulté le )
  2. ↑ « Window Functions », sqlite.org (consulté le )
  3. ↑ « Window Functions Overview », MariaDB KnowledgeBase (consulté le )
  4. ↑ « Oracle 8i Release 2 (8.1.6) New Features », www.oracle.com (consulté le )
  5. ↑ « Analytic Functions in Oracle 8i », www.stanford.edu (consulté le )
  6. ↑ « PostgreSQL Release 8.4 », www.postgresql.org, (consulté le )
  7. ↑ « MySQL :: What's New in MySQL 8.0? (Generally Available) », dev.mysql.com (consulté le )
  8. ↑ « MySQL :: MySQL 8.0 Reference Manual :: 12.21.2 Window Function Concepts and Syntax », dev.mysql.com
  9. ↑ « MariaDB 10.2.0 Release Notes », mariadb.com (consulté le )
  10. ↑ « SQLite Release 3.25.0 On 2018-09-15 », www.sqlite.org (consulté le )
  11. ↑ (en) « 3.5. Window Functions », PostgreSQL Documentation, (consulté le )
  12. ↑ a et b (en) « 4.2. Value Expressions », PostgreSQL Documentation, (consulté le )
  13. ↑ Leis, Kundhikanjana, Kemper et Neumann, « Efficient Processing of Window Functions in Analytical SQL Queries », Proc. VLDB Endow., vol. 8, no 10,‎ , p. 1058–1069 (ISSN 2150-8097, DOI 10.14778/2794367.2794375)
  14. ↑ Cao, Chan, Li et Tan, « Optimization of Analytic Window Functions », Proc. VLDB Endow., vol. 5, no 11,‎ , p. 1244–1255 (ISSN 2150-8097, DOI 10.14778/2350229.2350243, arXiv 1208.0086)
  15. ↑ (en-US) « Probably the Coolest SQL Feature: Window Functions », Java, SQL and jOOQ.,‎ (lire en ligne, consulté le )
  16. ↑ (en-US) « Window Functions in SQL - Simple Talk », Simple Talk,‎ (lire en ligne, consulté le )
  17. ↑ « SQL Window Functions Introduction », Apache Drill
  18. ↑ (en) « PostgreSQL: Documentation: Window Functions », www.postgresql.org (consulté le )
v · m
Principe
Modèle
Données
Composants
Opérations