Apprendre la clause OVER() en SQL avec 7 exemples

Apprenez à utiliser la clause OVER() dans vos requêtes SQL et vous ferez un pas en avant dans vos connaissances SQL.

La plupart des analystes de données et des développeurs SQL connaissent bien les clauses SELECT, FROM, WHERE, GROUP BY et ORDER BY. Cependant, ces clauses ne constituent qu'un sous-ensemble limité du langage SQL. Il existe d'autres clauses, telles que OVER(), qui nous permettent de créer des rapports SQL flexibles et ajoutent une énorme puissance expressive au langage SQL.

Dans cet article, nous allons expliquer la clause OVER() en démontrant plusieurs exemples de son utilisation. C'est parti !

La clause OVER() en SQL

La courbe d'apprentissage du langage SQL est assez facile ; tout le monde peut apprendre à créer des requêtes simples en SQL. En fait, l'informaticien moyen ou le développeur SQL manipule facilement les clauses de base SELECT, FROM, WHERE, GROUP BY et ORDER BY. Ces clauses étaient disponibles dans SQL-92, qui a 30 ans ! Cependant, le langage SQL ne s'arrête pas là ; de nombreux nouveaux éléments ont été ajoutés depuis 1992. L'un d'entre eux est la clause OVER(), qui nous permet d'utiliser des fonctions de fenêtre dans les requêtes SQL.

En SQL, les fonctions de fenêtre sont similaires à GROUP BY en ce sens qu'elles fonctionnent sur un groupe de lignes. Cependant, les fonctions de fenêtre sont basées sur une fenêtre de données, ou un ensemble de lignes liées à la ligne actuelle. Contrairement à GROUP BY, les fonctions de fenêtre ne réduisent pas les lignes ; elles conservent les détails des lignes individuelles.

Pour ceux qui souhaitent approfondir OVER() et les fonctions de fenêtre en SQL, je suggère notre cours en ligneFonctions de fenêtrage . Il contient de nombreux exemples utilisant différentes fonctions de fenêtre.

Ok, revenons maintenant à la fonction OVER(). Tout d'abord, examinons nos données.

Présentation des données

Nos exemples de requêtes seront basés sur les données suivantes.

Supposons que nous ayons un restaurant de fruits de mer sur la côte méditerranéenne. Nous disposons également d'une table de base de données dans laquelle nous stockons l'historique de l'activité du restaurant. Notre base de données comporte une table appelée restaurant_activity avec les colonnes suivantes :

• table_number
• waiter_name
• start_date
• start_time
• served_time
• end_time
• total_diners
• amount_payment
• total_tips.

Vous trouverez ci-dessous un exemple de données :

Restaurant_activity

Tout cela devrait s'expliquer, mais passons rapidement en revue quelques colonnes. Les colonnes start_date et start_time indiquent la date et l'heure auxquelles un repas a été commandé par les clients à l'adresse table_number; served_time indique le moment où le repas a été servi et end_time le moment où les clients ont demandé leur addition. amount_payment est le coût du repas, sans compter le pourboire (qui est stocké sous total_tips).

Exemple de requête n° 1 : une utilisation simple de OVER()

Commençons par utiliser la clause OVER() pour calculer le pourcentage de chaque commande par rapport au revenu quotidien total sur 5/5/2022. Nous pouvons calculer le revenu total du restaurant ce jour-là (à l'exclusion des pourboires) en utilisant OVER() combiné à la fonction de fenêtre SUM(). La clause OVER() est toujours associée à une fonction window ; les fonctions window calculent une valeur sur la base d'un ensemble d'enregistrements défini par la clause OVER(). La requête est la suivante :

SELECT 
  start_date AS date,
  SUM(amount_payment) OVER () AS daily_revenue,
  amount_payment AS total_order,
  (amount_payment / SUM(amount_payment) OVER ())*100 AS order_percentage
FROM   restaurant_activity
WHERE  start_date = ’5/5/2022’

La requête précédente calcule daily_revenue pour 5/5/2022 en additionnant chaque montant de paiement pour ce jour. La clause OVER() est vide, ce qui signifie que la fenêtre d'enregistrements utilisée pour calculer la fonction SUM() est l'ensemble complet des enregistrements renvoyés par la requête. En d'autres termes, la fenêtre est composée de tous les enregistrements pour cette date.

Dans le calcul de la colonne order_percentage, nous divisons le montant de la commande individuelle par le revenu quotidien total pour obtenir le pourcentage ; il s'agit d'un point central de la requête, car nous combinons les colonnes au niveau de la ligne avec les résultats de la fonction de fenêtre dans une seule expression.

Il est essentiel de comprendre quels enregistrements font partie de la fenêtre pour comprendre le fonctionnement des fonctions de fenêtre ; nous reviendrons sur ce point plus loin dans l'article. Pour l'instant, examinons les résultats :

Avant d'aborder des exemples plus complexes sur OVER(), j'aimerais vous suggérer notre jeu d'exercicesFonctions de fenêtrage . Si vous voulez vraiment apprendre à utiliser OVER(), ces 100 exercices interactifs feront l'affaire.

Exemple de requête n° 2 : Utilisation de la sous-clause PARTITION BY

Dans cet exemple, nous utiliserons la sous-clause PARTITION BY; elle fonctionne avec OVER() pour définir les fenêtres de données.

Supposons que nous voulions un rapport similaire au précédent, mais étendu à tous les jours du mois de mai 2022. Pour chaque jour, nous voulons connaître le revenu quotidien total, le montant individuel de toutes les commandes et le pourcentage du revenu quotidien attribué à chaque commande. Voici la requête :

SELECT start_date AS date,
  		SUM(amount_payment) OVER (PARTITION BY start_date) AS daily_revenue,
  		amount_payment AS total_order,
  		(amount_payment / SUM(amount_payment) OVER (PARTITION BY start_date)) * 100 AS
    order_percentage
FROM restaurant_activity
WHERE start_date BETWEEN ’5/1/2022’ AND ’5/31/2022’ 

Nous avons mentionné que les fonctions de fenêtre fonctionnent sur la base d'une fenêtre d'enregistrements (ou d'un ensemble d'enregistrements) liés à l'enregistrement actuel. La requête précédente utilise la sous-clause PARTITION BY start_date pour définir quels enregistrements appartiennent à chaque fenêtre. Dans notre exemple, tous les enregistrements ayant la même valeur start_date que la ligne actuelle feront partie de la fenêtre. Comme nous n'avons que deux valeurs uniques pour start_date, nous n'avons que deux fenêtres différentes. Elles sont représentées en vert et en rouge dans le résultat ci-dessous :

Exemple de requête n° 3 : Utilisation de la sous-clause ORDER BY

Introduisons maintenant la sous-clause ORDER BY, qui vous permet de placer les lignes dans un ordre spécifique à l'intérieur de la fenêtre.

Supposons que nous voulions un rapport simple avec les cinq commandes les plus chères ainsi que le serveur, la date et le numéro de table. La requête est la suivante :

WITH ranking AS (
	SELECT 
table_number, 
amount_payment, 
waiter_name, 
start_date, 
	       RANK() OVER (ORDER BY amount_payment DESC) AS position
	FROM restaurant_activity
)
SELECT 
  amount_payment, 
  waiter_name, 
  start_date, 
  table_number, 
  position
FROM ranking
WHERE position <= 5
ORDER BY position

Ici, nous avons utilisé un CTE ( Common Table Expression) pour établir le classement. Dans l'ETC, nous avons calculé la position de chaque commande en utilisant la fonction de fenêtre RANK() combinée à la clause OVER() suivante :

RANK() OVER (ORDER BY amount_payment DESC)

La clause ci-dessus définit une fenêtre d'enregistrements formée par tous les enregistrements de la table, de sorte que toutes les commandes du restaurant sont incluses. Cette fenêtre est triée par amount_payment dans l'ordre décroissant : le montant le plus élevé est le premier enregistrement de la fenêtre, et ainsi de suite. La fonction RANK() renvoie la position de la ligne actuelle dans la fenêtre ordonnée et stocke cette valeur dans la colonne position de l'ETC.

Après avoir construit l'ETC de classement, le reste de la requête utilise ranking comme n'importe quelle autre table. Nous filtrons les enregistrements pour n'obtenir que les 5 premiers rangs, puis nous ordonnons les résultats par position. Voici ce que nous obtenons :

À ce stade, j'aimerais vous suggérer l'article SQL Fonctions de fenêtrage vs. GROUP BY : Quelle est la différence ? Il explique les différences entre la clause GROUP BY et les fonctions de fenêtre à l'aide de plusieurs exemples de requêtes SQL. Un autre article intéressant avec des détails supplémentaires est Qu'est-ce que la clause OVER() en SQL ?

Exemple de requête n° 4 : Calculer le temps libre d'une table avec OVER() et LAG()

Les fonctions Window offrent de nombreuses possibilités pour faciliter les calculs complexes. L'une d'entre elles est la fonction LAG(), qui renvoie une valeur de n'importe quelle colonne de la ligne précédente liée à la ligne actuelle de la fenêtre.

Le propriétaire du restaurant souhaite savoir combien de temps les tables sont libres, c'est-à-dire le temps qui s'écoule entre deux clients. Pour ce faire, nous pouvons créer un rapport avec les colonnes table_number, date, free_start, free_end et free_time_duration.

Pour calculer le temps libre, nous devons accéder à deux lignes. Nous avons besoin de end_time de l'occupation précédente et de start_time de l'occupation suivante ; nous pouvons ensuite calculer le temps écoulé entre les deux. C'est ici que la fonction de fenêtre LAG() entre en jeu, car LAG() permet d'accéder à n'importe quelle colonne de l'enregistrement précédent. Voici la requête que nous utiliserions :

    SELECT 
start_date AS date,
table_number,
-- ending time of the previous occupation 
COALESCE(LAG(end_time) OVER (PARTITION BY start_date, table_number 
                             ORDER BY start_time),'11:00') AS start_free_time,
-- starting time of current occupation
start_time AS end_free_time,
-- calculating the free time when the table was unoccupied
start_time - 
COALESCE(LAG(end_time) OVER (PARTITION BY start_date, table_number 
                             ORDER BY start_time),'11:00') AS free_time_duration
    FROM   restaurant_activity

La clé de la requête précédente est la fonction fenêtre LAG(). Nous l'avons utilisée pour obtenir le end_time de l'occupation précédente. La clause ...

OVER (PARTITION BY start_date, table_number ORDER BY start_time)

... définit une fenêtre (ou un ensemble de lignes) pour chaque paire distincte de <start_date, table_number>, et chacune de ces fenêtres est ordonnée par start_time. Ensuite, LAG(end_time) renvoie l'heure de fin de l'occupation précédente de la table.

Vous avez probablement remarqué que nous avons utilisé LAG() à deux reprises. La première est utilisée pour obtenir l'heure de début de la période libre, et la seconde pour calculer la durée de la période libre à l'aide de l'expression suivante :

start_time - 
coalesce(LAG(end_time) OVER (PARTITION BY start_date, table_number 
                             ORDER BY start_time),'11:00')

Les résultats de la requête sont :

Avant de passer à la section suivante, je vous conseille les articles suivants pour plus de détails :

• Exemple de fonction SQL Window avec explications
• Comment classer les lignes en SQL : Un guide complet
• La fonction LAG et la fonction LEAD en SQL
• Comment calculer la longueur d'une série en SQL

Exemple de requête n° 5 : Calculer des classements avec la clause OVER()

Dans cette section, nous allons aborder une situation professionnelle dans laquelle la clause SQL OVER() peut être appliquée pour créer un classement comme celui des 10 meilleurs vendeurs ou des 5 produits les plus vendus. Vous pouvez utiliser la clause OVER() combinée à la fonction de fenêtre RANK() pour obtenir ce type de rapport. Voyons un exemple de requête qui renvoie les deux plus gros pourboires de la journée et le serveur qui les a reçus :

SELECT  *
FROM (
  		SELECT 	waiter_name,
  			start_date AS date,
			total_tips AS tip_amount,
			RANK() OVER(PARTITION BY start_date ORDER BY total_tips DESC) AS ranking
  	  	FROM restaurant_activity
     	     ) AS ranking_table
WHERE ranking <= 2;

Nous avons utilisé une sous-requête dans la clause FROM pour créer une "table" temporaire appelée ranking_table. Elle utilise la colonne ranking pour stocker la position du pourboire dans le classement quotidien des pourboires. La position dans le classement est calculée à l'aide de la fonction de fenêtre RANK(). Les autres colonnes de la table temporaire sont waiter_name, date et tip_amount.

Dans la requête externe, nous filtrons uniquement les conseils classés 1 et 2. Le résultat de la requête est illustré ci-dessous :

Exemple de requête n° 6 : calcul des différences par rapport à une période antérieure

Une autre possibilité intéressante consiste à montrer la différence de valeur entre la période précédente et la période actuelle. La clause OVER() combinée à des fonctions de fenêtre telles que LEAD() et LAG() sont utilisées pour créer ce type de rapport. Pour plus d'informations, voir Comment calculer la différence entre deux lignes en SQL.

Supposons que nous voulions voir le revenu quotidien de chaque serveur ainsi que la différence par rapport au jour précédent. Dans la même ligne, nous voulons également voir la différence exprimée en pourcentage. Voici la requête :

SELECT	
  waiter_name,
  date,
  today_revenue,
  -- revenue variation ----------------------------------------------
  LAG(today_revenue) OVER (
    PARTITION BY waiter_name ORDER BY date) yesterday_revenue,
  today_revenue - LAG(today_revenue) OVER (
    PARTITION BY waiter_name ORDER BY date) AS revenue_variation,
  -- -----------------------------------------------------------------
  -- revenue variation percentage ------------------------------------------------
  round((today_revenue - 
   LAG(today_revenue) OVER (PARTITION BY waiter_name ORDER BY date)) /
   LAG(today_revenue) OVER (PARTITION BY waiter_name ORDER BY date),2) * 100 
AS revenue_variation_percentage
  -- -----------------------------------------------------------------------------		
FROM (
  SELECT  DISTINCT 
    start_date::date AS "date",
    waiter_name,
    SUM(total_tips) OVER ( 
      PARTITION BY waiter_name, start_date::date ORDER BY start_date)
AS today_revenue
  FROM 	restaurant_activity
) AS daily_revenue_per_waiter;

Nous avons créé une sous-requête dans la clause FROM appelée daily_revenue_per_waiter qui contient waiter_name, date et le revenu total réalisé par ce serveur ce jour-là. Dans la requête externe, nous utilisons la fonction de fenêtre LAG() pour obtenir les recettes du jour précédent, puis nous obtenons la variation des recettes entre hier et aujourd'hui et le pourcentage de variation. Le résultat est le suivant :

Exemple de requête n° 7 : Moyenne mobile

Les moyennes mobiles sont une mesure fréquemment utilisée pour atténuer les fluctuations à court terme. Vous pouvez en savoir plus dans l'article Qu'est-ce qu'une moyenne mobile et comment la calculer en SQL; voyons comment nous pouvons en construire une en utilisant la clause OVER().

Supposons par exemple que le propriétaire d'un restaurant veuille connaître le montant moyen payé par personne à la dernière table servie à un moment donné. Il utilise cette moyenne comme mesure pour connaître le montant payé par les clients et pour activer certaines promotions ou réductions. Nous pouvons facilement calculer cette moyenne avec l'expression amount_payment/total_diners; cependant, le propriétaire s'est rendu compte que cette mesure subit d'importantes fluctuations, il a donc décidé d'utiliser le montant moyen payé par personne sur les 3 et 6 dernières tables servies. La requête permettant de calculer ce rapport est la suivante :

SELECT start_date AS "date",
start_time AS "time",
table_number,
amount_payment AS total_amount,
total_diners,
       	ROUND(amount_payment/total_diners,2) AS diner_avg,
ROUND(AVG(amount_payment/total_diners) 
     OVER (ORDER BY start_date, start_time ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),2) 
     AS diner_moving_avg_last_3_tables_served,
	ROUND(AVG(amount_payment/total_diners) 
     OVER (ORDER BY start_date, start_time ROWS BETWEEN 5 PRECEDING AND CURRENT ROW),2) 
     AS diner_moving_avg_last_6_tables_served
FROM   restaurant_activity

La requête précédente calcule 3 moyennes différentes. La première est une moyenne simple basée sur l'expression :

ROUND(amount_payment/total_diners,2)

La deuxième moyenne est la moyenne mobile pour les 3 dernières tables servies ; la troisième moyenne est la même, mais pour les 6 dernières tables servies :

ROUND(AVG(amount_payment/total_diners) 
     OVER (ORDER BY start_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),2)
…
OVER (ORDER BY start_date, start_time ROWS BETWEEN 5 PRECEDING AND CURRENT ROW),2) 

Le terme "ROWS BETWEEN 2 PRECEDING AND CURRENT ROW" précise que la moyenne est calculée sur 3 lignes : la ligne actuelle et les 2 lignes qui la précèdent immédiatement. La fenêtre est ordonnée par l'heure de début de la table. Voici le résultat :

D'autres utilisations professionnelles de la clause OVER() incluent le calcul des totaux courants (utile dans toutes sortes de scénarios d'analyse financière) et le calcul de la longueur d'une série de données.

Prêt à pratiquer la clause SQL OVER() ?

Nous avons démontré plusieurs requêtes comportant la clause SQL OVER(). Comme OVER() doit être utilisé en combinaison avec une fonction de fenêtre, nous avons également abordé quelques-unes d'entre elles : SUM(), AVG(), LAG() et RANK().

Si vous souhaitez mettre en pratique vos nouvelles connaissances de la clause SQL OVER(), je vous recommande notre cours interactif Fonctions de fenêtrage, suivi de notre jeu d'exercicesFonctions de fenêtrage . Vous pouvez lire ce cours dans l'article Cours SQL du mois - Fonctions de fenêtrage. Vous pouvez également obtenir une copie de notre feuille de contrôle gratuite SQL Fonctions de fenêtrage pour vous aider dans votre apprentissage. Développez vos compétences et augmentez vos atouts !