Qu'est-ce que la clause OVER() en SQL ?

Les fonctions de fenêtre sont l'une des ressources les plus puissantes de SQL, mais elles ne sont pas fréquemment utilisées par le développeur SQL moyen. Dans cet article, nous allons expliquer comment vous pouvez définir différents types de fenêtres à l'aide de la clause OVER clause.

La clause OVER est essentielle aux fonctions de fenêtre SQL. Comme les fonctions d'agrégation, les fonctions de fenêtre effectuent des calculs sur la base d'un ensemble d'enregistrements - par exemple, trouver le salaire moyen d'un groupe d'employés.

Dans certains cas, les fonctions d'agrégation ne peuvent pas être utilisées car elles regroupent tous les enregistrements individuels en un seul groupe, ce qui rend impossible la référence à des valeurs spécifiques (comme le salaire d'un employé parmi le groupe). Dans ces situations, les fonctions de fenêtre sont préférables car elles ne réduisent pas les lignes ; vous pouvez vous référer à une valeur de colonne au niveau de la ligne ainsi qu'à la valeur agrégée.

Il existe d'autres scénarios dans lesquels les fonctions de fenêtre sont utiles. Par exemple, nous pouvons avoir besoin d'effectuer des calculs arithmétiques impliquant une colonne individuelle et un calcul basé sur un ensemble de lignes. Un exemple concret est le calcul de la différence entre le salaire moyen d'un département et le salaire de chaque employé de ce département.

Lorsque vous utilisez des fonctions de fenêtre, la définition du jeu d'enregistrements dans lequel la fonction sera calculée est essentielle. Cet ensemble d'enregistrements est appelé le cadre de la fenêtre ; nous le définissons à l'aide de la clause SQL OVER.

Tout au long de cet article, nous allons démontrer des requêtes SQL en utilisant la base de données d'une petite entreprise de montres de luxe. L'entreprise stocke ses informations de vente dans une table appelée sales:

Le cadre de la fenêtre est un ensemble de lignes qui dépend de la ligne actuelle ; ainsi, l'ensemble de lignes peut changer pour chaque ligne traitée par la requête. Nous définissons les cadres de fenêtre à l'aide de la clause OVER. La syntaxe est la suivante :

  OVER ([PARTITION BY columns] [ORDER BY columns])

La sous-clause PARTITION BY définit les critères que les enregistrements doivent satisfaire pour faire partie du cadre de fenêtre. En d'autres termes, PARTITION BY définit les groupes dans lesquels les lignes sont divisées ; cela sera plus clair dans notre prochain exemple de requête. Enfin, la clause ORDER BY définit l'ordre des enregistrements dans le cadre de la fenêtre.

Voyons la clause SQL OVER en action. Voici une requête simple qui renvoie la quantité totale d'unités vendues pour chaque article.

SELECT sale_day, sale_time, 
       branch, article, quantity, revenue,
       SUM(quantity) OVER (PARTITION BY article) AS total_units_sold
FROM   sales

Cette requête affichera tous les enregistrements de la table sales avec une nouvelle colonne affichant le nombre total d'unités vendues pour l'article concerné. Nous pouvons obtenir la quantité d'unités vendues en utilisant la fonction d'agrégation SUM, mais nous ne pourrions alors pas afficher les enregistrements individuels.

Dans cette requête, la sous-clause article OVER PARTITION BY indique que le cadre de la fenêtre est déterminé par les valeurs de la colonne article; tous les enregistrements ayant la même valeur article seront dans un groupe. Ci-dessous, nous avons le résultat de cette requête :

La colonne total_units_sold du rapport a été obtenue par l'expression :

SUM(quantity) OVER (PARTITION BY article) total_units_sold

Pour les lecteurs qui veulent approfondir le sujet, je suggère les deux articles suivants : Quelle est la différence entre GROUP BY et PARTITION BY et Fonctions de fenêtrage dans SQL Server : Première partie : la clause OVER()

La clause SQL OVER en action

Pour chaque article, supposons que nous voulions comparer la quantité totale de cet article vendu au cours de chaque mois de 2021 avec la quantité totale de cet article vendu au cours de l'année entière. Pour ce faire, nous allons créer un rapport simple avec les colonnes article, month, units_sold_month et units_sold_year. La requête est la suivante :

SELECT DISTINCT article,
       EXTRACT('month' FROM sale_day) AS month,
       SUM(quantity) OVER (PARTITION BY article, sale_month) AS units_sold_month,
       SUM(quantity) OVER (PARTITION BY article) AS units_sold_year
FROM  sales 
WHERE EXTRACT('YEAR' FROM sale_day) = 2021
ORDER BY article, month

Les résultats de la requête sont :

Ici, nous avons calculé le total des unités vendues en utilisant deux granularités de regroupement différentes : le mois et l'année. La première clause OVER...

OVER (PARTITION BY article, sale_month) 

... nous permet d'obtenir le nombre d'unités de chaque article vendu au cours d'un mois. La deuxième clause OVER...

OVER (PARTITION BY article)

... nous permet de calculer le nombre total d'unités d'un article donné vendues sur l'année entière.

Dans la prochaine requête, nous ajouterons simplement la colonne month_percentage pour montrer le pourcentage qu'un mois spécifique occupe dans le total annuel. Nous pouvons le calculer en utilisant la requête suivante :

SELECT DISTINCT article,
       EXTRACT('month' FROM sale_day) as month,
       SUM(quantity) OVER (PARTITION BY article, sale_month) AS units_sold_month,
       SUM(quantity) OVER (PARTITION BY article) AS units_sold_year,
       ( ( SUM(quantity) OVER (PARTITION BY article, sale_month)::decimal /
           SUM(quantity) OVER (PARTITION BY article)::decimal ) * 100
       ) AS month_percentage
FROM sales 
WHERE extract('YEAR' FROM sale_day) = 2021
ORDER BY article, month

Notez que dans la requête précédente, nous n'avons pas utilisé différentes clauses OVER; nous avons simplement réutilisé les mêmes fonctions de fenêtre et calculé un pourcentage. Vous pouvez voir les résultats ci-dessous :

Nous allons maintenant créer un rapport différent qui examine les performances des différentes branches. Nous voulons voir les colonnes branch et month. Nous avons également besoin de calculs pour obtenir le :

• Le revenu total pour ce mois.
• Le revenu groupé par branche et par mois.
• Le revenu moyen mensuel par branche.
• La différence entre le revenu de chaque branche et le revenu moyen mensuel.

SELECT DISTINCT branch,
       EXTRACT('month' FROM sale_day) AS month,
       SUM(revenue) OVER (PARTITION BY sale_month) AS total_revenue_month,
       SUM(revenue) OVER (PARTITION BY branch, sale_month) AS branch_revenue_month,
      
 -- Next column is the branch average revenue in the current month
	 ( 
		  SUM(revenue) OVER (PARTITION BY sale_month)::decimal 
		   / 
		  (SELECT COUNT(DISTINCT branch) FROM sales)::decimal 
	 ) AS average_month_branch,
 
 -- Next column is the difference between branch revenue and average branch revenue

        SUM(revenue) OVER (PARTITION BY branch, sale_month) -
	 ( 
		  SUM(revenue) OVER (PARTITION BY sale_month)::decimal 
		   / 
		  (SELECT COUNT(DISTINCT branch) FROM sales)::decimal 
	 ) AS gap_branch_average

FROM sales 
WHERE extract('YEAR' from sale_day) = 2021
ORDER BY branch, month

Une fois encore, nous n'avons utilisé que deux clauses OVER, mais nous avons utilisé des expressions arithmétiques différentes pour obtenir certaines valeurs. Nous avons utilisé ...

SUM(revenue) OVER (PARTITION BY sale_month) 

... pour calculer le revenu total du mois, mais nous l'avons également utilisé dans une expression arithmétique pour obtenir le revenu moyen mensuel de la branche.

Nous avons utilisé ...

SUM(revenue) OVER (PARTITION BY branch, sale_month) 

... pour calculer le revenu mensuel de la branche et la différence entre le revenu mensuel de cette branche et la moyenne.

Le tableau suivant est le résultat de la requête. Remarquez que la colonne gap_branch_average peut contenir des nombres positifs ou négatifs. Un nombre négatif indique que le revenu mensuel de cette branche est inférieur au revenu moyen.

Pour plus d'informations sur les fonctions de fenêtre en SQL, je suggère Exemple de fonction fenêtre SQL avec explications, un article d'entrée de gamme sur les fonctions de fenêtre. Pour les lecteurs plus avancés, How to Rank Rows Within a Partition in SQL montre comment créer des classements dans vos rapports à l'aide de la fonction fenêtre RANK().

La clause OVER et l'analyse Fonctions de fenêtrage

Dans les requêtes précédentes, nous avons utilisé les fonctions de fenêtre pour comparer des chiffres mensuels (revenus et unités vendues, respectivement) à des chiffres annuels. Dans cette section, nous allons utiliser des cadres de fenêtre ordonnés, ce qui nous permet de choisir un enregistrement dans le cadre en fonction de sa position. Par exemple, nous pouvons choisir le premier enregistrement dans le cadre de la fenêtre, ou l'enregistrement précédent l'enregistrement actuel, ou l'enregistrement suivant l'enregistrement actuel. Ces fonctions de fenêtre analytique offrent une grande puissance d'expression à SQL.

Dans la requête suivante, nous allons montrer l'augmentation/diminution du chiffre d'affaires pour la même branche sur deux mois contigus. Pour ce faire, nous devons calculer la différence entre le revenu du mois en cours et celui du mois précédent. Pour cela, il faut utiliser la fonction de fenêtre analytique LAG(), qui permet d'obtenir une valeur de colonne à partir d'une ligne antérieure à la ligne actuelle.

WITH branch_month_sales AS (
	SELECT    DISTINCT
		    branch,
		    EXTRACT('MONTH' FROM sale_day) AS month,
		    SUM(revenue) OVER (PARTITION BY branch, sale_month ) AS revenue
	FROM sales
)
SELECT branch, 
	month,
	revenue AS revenue_current_month,
	LAG(revenue) OVER (PARTITION BY branch ORDER BY month) AS revenue_prev_month,
	revenue - LAG(revenue) OVER (PARTITION BY branch ORDER BY month) AS revenue_delta
FROM branch_month_sales
ORDER BY branch, month

Dans cette requête, nous avons utilisé une expression de table commune (CTE) appelée branch_month_sales pour stocker le revenu total de chaque branche et de chaque mois. Nous avons ensuite écrit une deuxième requête qui utilise la fonction de fenêtre LAG() pour obtenir le revenu du mois précédent (en utilisant les informations de branch_month_sales). Notez que le cadre de la fenêtre est ordonné par mois.

Voici les résultats :

Dans toutes les requêtes présentées dans cet article, nous n'avons utilisé que quelques fonctions de fenêtre. Il existe un grand nombre d'autres fonctions de fenêtre dans SQL. Voici une liste de chacune d'entre elles :

En savoir plus sur la clause SQL OVER

Dans cet article, nous avons abordé la clause OVER et les sous-clauses PARTITION BY et ORDER BY.

Si vous souhaitez poursuivre votre apprentissage des fonctions de fenêtre, je vous propose deux articles. Le premier est intitulé 8 Best SQL Window Function Articles, qui vous renvoie à d'autres articles intéressants. Le second est un aide-mémoire sur les fonctions de fenêtre qui comprend la syntaxe, des exemples et des images ; c'est mon article préféré sur les fonctions de fenêtre.

Pour ceux qui veulent aller plus loin, je vous suggère notre cours interactif Fonctions de fenêtrage SQL. Si vous souhaitez améliorer vos compétences SQL en général, essayez le titre SQL de A à Z. Il s'agit d'un aperçu complet de tout ce que vous devez savoir pour travailler efficacement avec SQL.