Fonctions de Fenêtre en SQL

Les fonctions de fenêtre constituent un puissant outil en SQL permettant d'effectuer des calculs sur des ensembles de lignes tout en conservant les lignes d'origine dans le résultat. Elles se distinguent par l'utilisation de la clause OVER, qui définit la fenêtre sur laquelle la fonction s'applique.

Pour illustrer le concept de base, voici un exemple de syntaxe simple :

SELECT function_name(column_1_name) OVER() 
FROM table_name;

Ici, la fonction est appliquée à la colonne spécifiée.

De plus, si l'on souhaite modifier l'ordre de calcul, il est possible d'inclure une clause ORDER BY :

SELECT function_name(column_1_name) OVER(    ORDER BY column_2_name )
FROM table_name;

Exemple pratique

L'un des cas d'utilisation les plus fréquents des fonctions de fenêtre est de créer un total cumulatif. Prenons un exemple où nous avons une table monthly_sales avec les valeurs suivantes :

| quarter | month | sales | |---------|-------|-------| | 1 | 1 | 1000 | | 1 | 2 | 500 | | 1 | 3 | 750 | | 2 | 4 | 800 | | 2 | 5 | 500 | | 2 | 6 | 400 | | 3 | 7 | 300 | | 3 | 8 | 500 | | 3 | 9 | 700 | | 4 | 10 | 800 | | 4 | 11 | 1000 | | 4 | 12 | 1250 |

Pour déterminer les ventes cumulatives jusqu'à chaque mois, la requête suivante peut être utilisée :

SELECT quarter, month, sales, SUM(sales) OVER(    ORDER BY month ) AS running_total 
FROM monthly_sales;

Le résultat sera le suivant :

| quarter | month | sales | running_total | |---------|-------|-------|---------------| | 1 | 1 | 1000 | 1000 | | 1 | 2 | 500 | 1500 | | 1 | 3 | 750 | 2250 | | 2 | 4 | 800 | 3050 | | 2 | 5 | 500 | 3550 | | 2 | 6 | 400 | 3950 | | 3 | 7 | 300 | 4250 | | 3 | 8 | 500 | 4750 | | 3 | 9 | 700 | 5450 | | 4 | 10 | 800 | 6250 | | 4 | 11 | 1000 | 7250 | | 4 | 12 | 1250 | 8500 |

Partitions

Les fonctions de fenêtre peuvent également être partitionnées, rendant ainsi possible d'appliquer une fonction à plusieurs groupes de lignes. Cela se fait en utilisant le mot-clé PARTITION BY au sein de la clause OVER.

Pour calculer la moyenne des ventes par trimestre sur l’exemple précédent, il suffit d’exécuter cette requête :

SELECT quarter, month, sales, AVG(sales) OVER(    PARTITION BY quarter ) AS quarterly_average 
FROM monthly_sales;

La sortie sera alors :

| quarter | month | sales | quarterly_average | |---------|-------|-------|------------------| | 1 | 1 | 1000 | 750 | | 1 | 2 | 500 | 750 | | 1 | 3 | 750 | 750 | | 2 | 4 | 800 | 566.66 | | 2 | 5 | 500 | 566.66 | | 2 | 6 | 400 | 566.66 | | 3 | 7 | 300 | 500 | | 3 | 8 | 500 | 500 | | 3 | 9 | 700 | 500 | | 4 | 10 | 800 | 1016.66 | | 4 | 11 | 1000 | 1016.66 | | 4 | 12 | 1250 | 1016.66 |

Fonctions de Fenêtre Principales

Voici quelques fonctions utiles dans les fenêtres : - LAG() : permet d'accéder aux données des lignes précédentes sans avoir besoin de jointure. - LEAD() : fournit un accès aux lignes après la ligne cible, avec un décalage spécifié. - NTILE() : segmente les données d'une fonction de fenêtre en groupes presque égaux. - ROW_NUMBER() : attribue un numéro séquentiel à chaque ligne du résultat.