5 exemples pratiques d'utilisation de la clause ROWS BETWEEN en SQL

Les fonctions de fenêtre SQL sont extrêmement utiles pour calculer des agrégations complexes comme les moyennes mobiles ou les totaux courants. La clause ROWS vous permet de spécifier des lignes pour vos calculs, ce qui permet des fenêtres encore plus sophistiquées. Voici cinq exemples pratiques d'utilisation de la clause ROWS BETWEEN en SQL.

Lesfonctions de fenêtre (également appelées fonctions OVER) calculent leur résultat sur la base d'un cadre de fenêtre glissante (c'est-à-dire un ensemble de lignes). Elles sont similaires aux fonctions d'agrégation dans la mesure où vous pouvez calculer la moyenne, le total ou la valeur minimale/maximale d'un groupe de lignes. Toutefois, il existe quelques différences importantes :

• Les fonctions de fenêtre ne réduisent pas les lignes comme le font les fonctions d'agrégation. Ainsi, vous pouvez toujours mélanger les attributs d'une ligne individuelle avec les résultats d'une fonction de fenêtre.
• Les fonctions de fenêtre permettent des cadres de fenêtre coulissants, ce qui signifie que l'ensemble des lignes utilisées pour le calcul d'une fonction de fenêtre peut être différent pour chaque ligne individuelle.

La syntaxe d'une fonction fenêtre est indiquée en bleu ci-dessous :

SELECT , ,
  OVER (
 	PARTITION BY <...>
   	ORDER BY <...>
    	) 
FROM ;

Lorsque vous utilisez une fonction fenêtre dans l'instruction SELECT, vous calculez essentiellement une autre colonne avec cette fonction :

• Vous commencez par spécifier une fonction (par exemple, AVG(), SUM(), ou COUNT()).
• Ensuite, vous utilisez le mot-clé OVER pour définir un ensemble de lignes. En option, vous pouvez :
  • Regroupez les lignes avec PARTITION BY afin que les fonctions soient calculées dans ces groupes plutôt que dans l'ensemble des lignes.
  • Triez les lignes dans un cadre de fenêtre en utilisant ORDER BY si l'ordre des lignes est important (par exemple, lors du calcul des totaux courants).
  • Spécifiez la relation entre le cadre de la fenêtre et la ligne actuelle (par exemple, le cadre doit être la ligne actuelle et deux lignes précédentes, ou la ligne actuelle et toutes les lignes suivantes, etc.)

Un cadre de fenêtre est défini à l'aide des clauses ROWS, RANGE et GROUPS. Dans cet article, nous nous concentrerons sur la clause ROWS et ses options. Pour en savoir plus sur les fonctions de fenêtre et la définition des cadres de fenêtre, consultez cet article contenant des exemples de fonctions de fenêtre, ce guide d'explication et, bien sûr, notre fiche de deux pages sur SQL Fonctions de fenêtrage .

Clause ROWS : Syntaxe et options

L'objectif de la clause ROWS est de spécifier le cadre de la fenêtre par rapport à la ligne actuelle. La syntaxe est la suivante :

ROWS BETWEEN lower_bound AND upper_bound

Les limites peuvent être l'une de ces cinq options :

• UNBOUNDED PRECEDING - Toutes les lignes avant la ligne actuelle.
• n PRECEDING - n rangées avant la rangée actuelle.
• CURRENT ROW - Juste la ligne actuelle.
• n FOLLOWING - n lignes après la ligne actuelle.
• UNBOUNDED FOLLOWING - Toutes les lignes après la ligne actuelle.

Source : Aide-mémoire SQL Fonctions de fenêtrage

Voici quelques éléments à garder à l'esprit lorsque vous définissez des cadres de fenêtre avec la clause ROWS:

• Le cadre de la fenêtre est évalué séparément dans chaque partition.
• L'option par défaut dépend de si vous utilisez ORDER BY:
  • Avec ORDER BY, le cadre par défaut est RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
  • Sans ORDER BY, le cadre par défaut est ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
• Si l'une de vos limites est une ligne courante, vous pouvez omettre de spécifier cette limite et utiliser une version plus courte de la définition du cadre de la fenêtre :
  • UNBOUNDED PRECEDING est identique à BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
  • n PRECEDING est identique à BETWEEN n PRECEDING AND CURRENT ROW.
  • n FOLLOWING est identique à BETWEEN CURRENT ROW AND n FOLLOWING.
  • UNBOUNDED FOLLOWING est le même que celui de BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.

Passons aux exemples pour voir comment cela fonctionne en pratique.

5 Exemples pratiques d'utilisation de ROWS dans Fonctions de fenêtrage

Exemple 1

Pour commencer à utiliser la clause ROWS, nous allons utiliser le tableau suivant contenant les données de vente d'une librairie.

Dans notre premier exemple, nous voulons ajouter une autre colonne qui indique le total des recettes depuis la première date jusqu'à la date de la ligne actuelle (c'est-à-dire le total courant). Voici la requête que nous pouvons utiliser :

SELECT date, revenue,
	SUM(revenue) OVER (
      ORDER BY date
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) running_total
FROM sales
ORDER BY date;

Pour calculer le total courant à l'aide d'une fonction fenêtre, nous suivons les étapes suivantes :

• Calcul du revenu total à l'aide de la fonction d'agrégation SUM().
• Ordonner les enregistrements dans le cadre de la fenêtre par date (l'ordre par défaut est l'ordre croissant), car l'ordre des lignes est important lors du calcul d'un total courant.
• Spécifiez le cadre de la fenêtre en définissant la limite inférieure comme UNBOUNDED PRECEDING et la limite supérieure comme CURRENT ROW. Cela inclura toutes les lignes jusqu'à la ligne actuelle incluse. Notez que le comportement par défaut sans la clause ROWS spécifiée serait le même dans ce cas. Le cadre par défaut utilise RANGE, et non ROWS. Comme chaque jour n'apparaît qu'une seule fois dans la table, le résultat sera le même pour RANGE et ROWS. Ainsi, nous pourrions également utiliser la requête suivante pour obtenir les mêmes résultats :

SELECT date, revenue,
	SUM(revenue) OVER (
      ORDER BY date) running_sum
FROM sales
ORDER BY date;

Comme vous le voyez, la requête a fonctionné comme prévu et nous avons obtenu le total courant dans notre troisième colonne. Le premier jour, il est égal aux ventes de ce jour - 1515,45 $ ; le deuxième jour, il est égal à la somme des ventes des premier et deuxième jours - 3860,80 $ ; à la ligne suivante, nous obtenons la somme des ventes des trois premiers jours - 4764,79 $, etc.

Dans nos prochains exemples, nous verrons comment fonctionne la clause ROWS lorsque les enregistrements sont divisés en plusieurs groupes.

Pour vous entraîner à définir des cadres de fenêtre, consultez ce cours interactif avec plus de 200 défis de codage. Fonctions de fenêtrage interactif avec plus de 200 défis de codage.

Exemple 2

Pour les deux exemples suivants, nous allons utiliser le tableau ci-dessous. Il contient des données fictives sur la température moyenne (en °C) et les précipitations totales (en mm) dans deux villes italiennes (Rome et Florence) sur cinq jours consécutifs.

Nous voulons calculer la température moyenne mobile sur trois jours séparément pour chaque ville. Pour séparer les calculs pour les deux villes, nous inclurons la clause PARTITION BY. Ensuite, lorsque nous spécifions le cadre de la fenêtre, nous considérons le jour actuel et les deux jours précédents :

Notez également que nous avons placé notre fonction de fenêtre à l'intérieur de la fonction ROUND() afin que la moyenne mobile sur trois jours soit arrondie à une décimale. Voici le résultat :

La moyenne mobile a été calculée séparément pour Florence et Rome. Pour le 1er septembre, la moyenne mobile est égale à la température moyenne quotidienne, car nous n'avons pas de données précédentes. Ensuite, le 2 septembre, la moyenne mobile est calculée comme la température moyenne du 1er et du 2 septembre (17,5 °C à Florence et 18,8 °C à Rome, respectivement). Le 3 septembre, nous avons enfin assez de données pour calculer la température moyenne sur trois jours (les deux précédents et le jour en cours), qui s'avère être de 18,0 °C à Florence et de 19,2 °C à Rome. Ensuite, la moyenne mobile sur trois jours du 4 septembre est calculée comme la moyenne des températures des 2, 3 et 4 septembre, et ainsi de suite.

Une dernière chose à noter : l'ordre des enregistrements dans le cadre de la fenêtre joue un rôle clé dans la détermination des lignes à prendre en compte.

Dans la requête ci-dessus, nous avons classé les enregistrements dans le cadre de la fenêtre par date dans l'ordre croissant (en utilisant le paramètre par défaut), c'est-à-dire que nous commençons par la date la plus ancienne. Ensuite, pour inclure deux jours avant le jour actuel dans nos calculs, nous avons fixé la limite inférieure à 2 PRECEDING.

Cependant, nous pourrions obtenir exactement la même fenêtre en classant les enregistrements par ordre décroissant, puis en modifiant l'option ROWS pour inclure 2 FOLLOWING au lieu de 2 PRECEDING:

SELECT city, date, temperature,
	ROUND(AVG(temperature) OVER (
      PARTITION BY city
      ORDER BY date DESC
      ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING), 1) mov_avg_3d_city
FROM weather
ORDER BY city, date;

Cette requête donne exactement le même résultat.

Exemple 3

Dans cet exemple, nous allons calculer le total des précipitations pour les trois derniers jours (c'est-à-dire un total courant sur trois jours) séparément pour deux villes.

SELECT city, date, precipitation,
	SUM(precipitation) OVER (
      PARTITION BY city
      ORDER BY date
      ROWS 2 PRECEDING) running_total_3d_city
FROM weather
ORDER BY city, date;

Dans cette requête, nous partitionnons à nouveau les données par ville. Nous utilisons la fonction SUM() pour calculer le niveau total de précipitations pour les trois derniers jours, y compris le jour en cours. Notez également que nous utilisons une abréviation pour définir le cadre de la fenêtre en spécifiant uniquement la limite inférieure : 2 PRECEDING.

Voici le résultat de la requête ci-dessus :

A partir du 3 septembre, nous obtenons un total de trois jours de précipitations à Florence : 30 mm. C'est la somme des 5 mm de précipitations du 1er septembre, des 15 mm du 2 septembre et des 10 mm du 3 septembre.

Savez-vous comment nous avons obtenu le total courant de 12 mm pour Rome le 5 septembre ? Essayez de suivre les résultats dans notre tableau de sortie pour vous assurer que vous comprenez comment les fonctions de fenêtre fonctionnent avec des cadres de fenêtre spécifiques.

Passons maintenant à de nouvelles données et à de nouveaux exemples.

Exemple 4

Pour les deux exemples suivants, nous utiliserons les données présentées ci-dessous. Elles comprennent des informations quotidiennes sur le nombre de nouveaux abonnés sur trois réseaux sociaux : Instagram, Facebook et LinkedIn.

Commençons par calculer les totaux courants du nombre de nouveaux abonnés séparément pour chaque réseau. En gros, pour chaque jour, nous voulons voir combien de personnes se sont abonnées depuis le début de la collecte des données jusqu'à la date de la ligne actuelle.

Voici une requête SQL qui répond à cette demande :

SELECT social_network, date, new_subscribers,
	SUM(new_subscribers) OVER (
      PARTITION BY social_network
      ORDER BY date
      ROWS UNBOUNDED PRECEDING) running_total_network
FROM subscribers
ORDER BY social_network, date;

Nous commençons par calculer le nombre total de nouveaux abonnés à l'aide de la fonction d'agrégation SUM(). Ensuite, nous utilisons la clause PARTITION BY pour effectuer des calculs séparés pour chaque réseau. Nous trions également les enregistrements par date dans l'ordre croissant (par défaut). Enfin, nous définissons le cadre de la fenêtre comme UNBOUNDED PRECEDING pour inclure tous les enregistrements jusqu'à l'enregistrement actuel.

Le résultat ressemble à ceci :

Dans le tableau des résultats, vous pouvez voir comment le nombre de nouveaux abonnés est ajouté au total cumulé pour chaque nouvel enregistrement. Le total cumulé est calculé séparément pour chaque réseau, comme spécifié dans la fonction fenêtre.

Cinquième exemple

Dans notre dernier exemple, je veux montrer comment nous pouvons afficher la première et la dernière valeur d'un ensemble spécifique d'enregistrements en utilisant les fonctions de fenêtre et la clause ROWS. Cette fois, ajoutons deux colonnes à la sortie :

• Le nombre de nouveaux abonnés ajoutés le premier jour, et
• le nombre de nouveaux abonnés ajoutés le dernier jour.

Avec ces informations calculées séparément pour chaque réseau social, nous pouvons voir comment les performances de chaque jour se comparent à celles du début et de la fin.

Voici la requête SQL permettant d'obtenir le résultat souhaité :

SELECT social_network, date, new_subscribers,
    FIRST_VALUE(new_subscribers) OVER(
      PARTITION BY social_network
      ORDER BY date) AS first_day,
    LAST_VALUE(new_subscribers) OVER(
      PARTITION BY social_network
      ORDER BY date
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_day
FROM subscribers
ORDER BY social_network, date;

Comme vous le voyez, nous utilisons les fonctions FIRST_VALUE() et LAST_VALUE() pour obtenir les informations sur le premier et le dernier jour, respectivement. Notez également comment nous spécifions le cadre de la fenêtre pour chacune des fonctions :

• Nous n'incluons pas la clause ROWS avec la fonction FIRST_VALUE() car le comportement par défaut (c'est-à-dire RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) convient à nos besoins.
• Cependant, nous spécifions le cadre de la fenêtre avec la fonction LAST_VALUE() car l'option par défaut utiliserait la valeur de la ligne actuelle comme dernière valeur de chaque enregistrement, ce qui n'est pas ce que nous recherchons dans cet exemple. Nous spécifions le cadre de la fenêtre comme ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING pour nous assurer que tous les enregistrements sont pris en compte.

Et voici l'ensemble des résultats :

Comme demandé, nous avons le nombre de nouveaux abonnés le premier et le dernier jour calculé séparément pour chaque réseau social.

S'entraîner à utiliser ROWS en SQL Fonctions de fenêtrage

Après avoir parcouru les exemples ci-dessus, nous espérons que vous avez envie d'apprendre les fonctions de fenêtre SQL et les options ROWS de manière plus approfondie. Ce kit d'outils vous permet de spécifier un cadre de fenêtre glissante et de calculer des agrégations complexes telles que les moyennes mobiles et les totaux courants.

Si vous souhaitez vous familiariser avec les fonctions de fenêtre, je vous recommande le cours interactif de LearnSQL.fr. Fonctions de fenêtrage interactif. Il vous montre comment calculer des totaux et des moyennes courantes, établir différents types de classements, étudier les tendances dans le temps, etc. Mieux encore, vous ferez les exercices vous-même, ce qui est la meilleure façon d'apprendre.

Si vous voulez vraiment maîtriser l'utilisation de SQL pour l'analyse de données, notre SQL avancé parcours d'apprentissage comprend également Les extensions GROUP BY en SQL et les expressions de table communes (CTE). C'est un excellent moyen d'approfondir vos connaissances des fonctions de fenêtre.

Vous voulez commencer par lire un peu ? Voici les 8 principaux articles traitant des fonctions de fenêtre SQL.

Merci de votre lecture et bon apprentissage.