Le total mobile en SQL : Qu'est-ce que c'est et comment le calculer

Le calcul d'un total mobile en SQL est simple lorsque vous utilisez les fonctions de fenêtre. Ces mesures pratiques sont similaires aux totaux courants et aux moyennes mobiles, et elles vous aideront à obtenir une image claire de vos données. Dans cet article, nous allons expliquer et explorer les totaux glissants.

En SQL, un total glissant est une métrique importante qui vous aide à voir comment les tendances évoluent dans le temps, ce qui vous donne une image claire de vos données. Il calcule une somme sur une "fenêtre" ou un sous-ensemble de lignes qui se déplace dans l'ensemble de résultats. Par exemple, pour suivre les visites d'un site web, vous pouvez demander le nombre total de visites pour chaque jour et également faire la somme des 7 jours précédents pour repérer les tendances. Lorsque vous utilisez les fonctions de fenêtre de SQL, vous pouvez voir ces tendances dans chaque ligne des résultats de votre requête, sans avoir recours à des outils externes.

Pour calculer facilement des totaux glissants en SQL, vous avez besoin de fonctions de fenêtre. Notre cours Fonctions de fenêtrage est parfait si vous souhaitez approfondir ce sujet. Lorsque vous suivez ce cours, vous effectuez 218 exercices qui couvrent la syntaxe et la sémantique des fonctions window. Vous découvrirez tout le potentiel des fonctions de fenêtre et les cas d'utilisation typiques. Et vous acquerrez une compréhension du langage SQL avancé qui vous permettra d'effectuer des analyses plus complexes et plus efficaces.

Un simple total mobile en SQL

Voyons une situation commerciale courante dans laquelle le total mobile démontre son utilité. Considérons une table appelée daily_sales qui enregistre les totaux quotidiens des ventes d'un magasin :

Si nous examinons ces données, ou même si nous créons un graphique à partir de ces données, voici ce que nous voyons :

Nous pouvons analyser des événements spécifiques, par exemple trouver des dates où les ventes sont anormalement basses. Par exemple, les ventes du 2024-08-03 sont inférieures à celles des autres jours. Cependant, cette façon de visualiser les informations n'est pas efficace pour détecter les tendances dans l'évolution des ventes. Pour cela, il faut regarder les totaux glissants. Et pour cela, nous avons besoin des fonctions SQL window.

Si vous avez besoin d'un guide de référence rapide sur les fonctions de fenêtre SQL, gardez cette feuille de contrôleFonctions de fenêtrage à portée de main. Elle vous permettra d'accélérer vos tâches quotidiennes d'analyse de données.

Calcul d'un total mobile en SQL

Écrivons une requête SQL qui renvoie les deux colonnes de daily_sales et qui ajoute un total mobile sur trois jours à l'ensemble des résultats :

SELECT
    purchase_date,
    total_sales,
    SUM(total_sales) OVER (
        	ORDER BY purchase_date
        	ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
     ) AS rolling_total
FROM daily_sales;

Dans l'exemple ci-dessus, la clause OVER après la fonction SUM() définit la fenêtre (c'est-à-dire le groupe de lignes) que nous voulons additionner pour chaque ligne. Dans ce cas, elle indique à SQL que la somme doit inclure les données des deux lignes précédentes et de la ligne actuelle. L'ordre est basé sur la date d'achat. Il s'agit de notre fenêtre mobile : le jour en cours et les deux jours précédents.

La requête permet d'obtenir ces données :

Dans la première ligne, on remarque que le total glissant ne fait que répéter la valeur de la colonne total_sales - il n'y a pas de lignes précédentes à additionner. Dans la deuxième ligne, la valeur total_sales est additionnée pour la première et la deuxième ligne ; à partir de la troisième ligne, la somme des valeurs des ventes des deux lignes précédentes et de la ligne actuelle est renvoyée.

Réalisons un graphique avec les données du total mobile pour comprendre comment il nous aide à analyser les tendances :

La première chose qui saute aux yeux lorsque nous regardons ce graphique est que nous devrions écarter les deux premiers points de données (les deux premières dates), car ils ne représentent pas un total de trois jours comme le reste des dates. À partir du 2024-08-03, la ligne devient presque horizontale, ce qui indique que les totaux des ventes sur trois jours restent stables. Ceci est vrai jusqu'à la dernière date, qui présente une légère hausse car le total n'est plus affecté par la "chute" des ventes à partir du 2024-08-03.

Avec un si petit ensemble de données, même des fluctuations mineures affectent la tendance générale ; avec un grand ensemble de données, ce ne serait pas le cas. Cependant, nous pouvons conclure que la combinaison des fonctions agrégées et des fonctions de fenêtre "lisse" l'effet des fluctuations ou des anomalies dans les données. Cela permet de visualiser clairement les tendances des données.

Total glissant, total courant et moyenne mobile

Outre le total mobile, il existe deux autres cas d'utilisation courants des fonctions de fenêtre : le total courant et la moyenne mobile.

• Un total courant est un calcul cumulatif qui s'exécute sur un ensemble de résultats. Pour chaque ligne, il calcule la somme de toutes les valeurs d'une colonne, de la première ligne à la ligne actuelle. Il est similaire au total glissant, mais il calcule toujours la somme à partir de la première ligne de l'ensemble de résultats.
• Une moyenne mobile est semblable à un total glissant en ce sens qu'elle prend les valeurs de la fenêtre définie par rapport à la ligne actuelle. La différence est qu'elle renvoie la moyenne de ces valeurs au lieu du total.

Le calcul de ces mesures est assez similaire à celui d'un total mobile. Voyons quelques exemples.

Calcul d'un total mobile en SQL

Voici un exemple de total courant :

SELECT
    purchase_date,
    total_sales,
    SUM(total_sales) OVER (ORDER BY purchase_date) AS running_total
FROM
    daily_sales;

La différence entre le total courant et le total mobile en SQL réside dans la définition de la fenêtre dans la clause OVER. Au lieu de définir un sous-ensemble qui s'étend sur un certain nombre de lignes par rapport à la ligne actuelle, il suffit de spécifier le critère d'ordre ; il est entendu que le total s'étend de la première ligne à la ligne actuelle. Vous pouvez trouver plus d'informations sur les totaux courants dans notre article Qu'est-ce qu'un total courant et comment le calculer en SQL.

Calcul d'une moyenne mobile en SQL

Le concept de moyenne mobile est très similaire à celui de total mobile. La fonction agrégée AVG() opère sur une fenêtre qui défile sur un nombre défini de lignes. En fait, la requête pour créer une moyenne mobile en SQL est pratiquement la même que celle que nous avons utilisée pour le total mobile. Il suffit de remplacer la fonction SUM() par AVG():

SELECT
    purchase_date,
    total_sales,
    AVG(total_sales) OVER (
        	ORDER BY purchase_date
     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) AS moving_average
FROM
    daily_sales;

Celle-ci renverra la moyenne des ventes sur une période de trois jours : le jour en cours et les deux jours précédents. Vous trouverez plus de détails sur les moyennes mobiles dans cet article.

Autres cas d'utilisation des totaux glissants

Consommation d'énergie hebdomadaire

Il existe de nombreux autres scénarios dans lesquels un total glissant peut être appliqué. La plupart des exemples concernent des tableaux chronologiques, où la fenêtre de données représente un intervalle de temps (par exemple 7 jours, 30 jours, 3 mois, etc.).

Un exemple pourrait être l'analyse de l'évolution de la consommation d'énergie d'un ménage au fil du temps. Ces informations peuvent être stockées dans un tableau (appelé daily_consumption_per_day) qui enregistre le nombre total de kilowattheures consommés par le ménage chaque jour.

Voici la requête :

SELECT
    measure_date,
    daily_consumption,
    SUM(daily_consumption) OVER (
        ORDER BY measure_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) AS weekly_consumption
FROM
    daily_consumption_per_day;

Pour chaque date, le résultat indique la somme des consommations des 7 derniers jours (y compris le jour courant). Cela nous permet de voir la variation des tendances de la consommation d'énergie dans le temps.

Les fonctions de fenêtre ont un grand potentiel. Découvrez tout ce que vous pouvez faire avec elles dans notre guide complet des fonctions de fenêtre SQL.

Utilisation des totaux glissants au-delà de l'analyse des séries temporelles

La plupart des cas d'utilisation des totaux glissants sont liés à l'analyse des séries temporelles. Cependant, ces statistiques peuvent être appliquées à n'importe quel ensemble de données trié par une dimension où il est logique de faire des calculs agrégés (par exemple, des totaux, des moyennes) sur des fenêtres glissantes qui isolent des sous-ensembles de cette dimension.

Un exemple serait une analyse du coût des prestations médicales en fonction de l'âge. Supposons que nous disposions d'un tableau qui enregistre le coût des prestations médicales reçues par des personnes de différents âges. Si nous classons les lignes par âge, un total mobile nous permettra de voir comment les coûts médicaux varient en fonction de la tranche d'âge.

Supposons que le tableau s'appelle medical_costs_by_age et qu'il comporte deux colonnes : âge et total_medical_cost. Pour analyser ces informations, nous allons écrire une requête avec un total glissant qui couvre une fenêtre de cinq ans centrée sur l'âge de la ligne actuelle (de deux ans en dessous à deux ans au-dessus) :

SELECT
    age,
    total_medical_cost,
    SUM(total_medical_cost) OVER (
        ORDER BY age
        ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
        ) AS weighted_medial_cost
FROM
    medical_costs_by_age;

Cet exemple nous permet d'obtenir une compréhension claire et assez granulaire de la relation entre les groupes d'âge et les coûts des soins de santé.

Vous voulez en savoir plus sur les totaux glissants en SQL ?

Tout au long de cet article, nous avons vu plusieurs façons d'utiliser un total glissant en SQL. Nous l'avons comparé à des mesures similaires, notamment le total courant et la moyenne mobile.

Si vous ne connaissiez pas les fonctions de fenêtre SQL avant de lire cet article, vous êtes probablement impressionné par ce qu'elles peuvent faire. Si vous souhaitez apprendre les fonctions de fenêtre à partir de zéro, je vous recommande notre cours de Fonctions de fenêtrage cours. Si vous les connaissez déjà, suivez le cours Fonctions de fenêtrage Practice Set. Il contient 100 exercices interactifs sur les fonctions de fenêtre qui utilisent trois bases de données réelles différentes. Notre plate-forme d'apprentissage vous fournit tous les outils nécessaires - vous n'avez rien à installer. Il vous suffit d'utiliser votre navigateur et une connexion internet et le tour est joué !

Enfin, vous pouvez maintenir votre maîtrise des totaux roulants en SQL grâce à cet ensemble d'exercices gratuits d'entraînement à la fonction de fenêtre. Et si vous aspirez à un emploi qui requiert la maîtrise de SQL et que vous voulez vous préparer à l'entretien d'embauche, n'oubliez pas de lire ces questions d'entretien sur la fonction SQL window. Merci d'avoir lu cet article et bon apprentissage !