Ce qu'est une moyenne mobile et comment la calculer en SQL

Vous souhaitez vous plonger dans les données de séries chronologiques et examiner les tendances à long terme ? Vous voulez savoir ce qu'est une moyenne mobile et comment la calculer en SQL ? Alors cet article est pour vous. J'expliquerai une fonctionnalité puissante de SQL appelée fonctions de fenêtre et je montrerai comment vous pouvez calculer des moyennes mobiles en les utilisant.

Le meilleur moyen d'apprendre les fonctions de fenêtre SQL est le cours interactif Fonctions de fenêtrage sur LearnSQL.fr. Il contient plus de 200 exercices pratiques pour vous aider à prendre confiance dans vos compétences SQL. Ce cours n'est que l'un des 30 cours interactifs SQL de différents niveaux de difficulté que nous proposons. Inscrivez-vous dès maintenant et gratuitement !

Qu'est-ce qu'une moyenne mobile ?

La moyenne mobile est une technique de séries chronologiques permettant d'analyser et de déterminer les tendances des données. Parfois appelées moyennes mobiles, moyennes mobiles ou moyennes courantes, elles sont calculées comme la moyenne de la valeur actuelle et d'un nombre spécifié de valeurs immédiatement précédentes pour chaque point dans le temps. L'idée principale est d'examiner le comportement de ces moyennes dans le temps au lieu d'examiner le comportement des points de données originaux ou bruts.

Travailler avec des moyennes mobiles nous donne une meilleure représentation de la série chronologique, car les tendances à long terme sont beaucoup plus faciles à voir avec des moyennes mobiles qu'avec des points de données bruts. Comme les moyennes mobiles sont souvent utilisées dans l'analyse financière, je vais utiliser les données des cours quotidiens des actions d'une société particulière comme exemple pour expliquer ce qu'elles sont.

Vous trouverez ci-dessous la table nommée stock_price que nous allons utiliser dans cet article :

Dans ce tableau, nous avons deux colonnes (date et price). Il s'agit d'un ensemble de données chronologiques, puisque chaque prix de l'action est associé à un moment précis dans le temps ; autrement dit, le prix de chaque jour est stocké dans ce tableau.

Le graphique linéaire de ces prix dans le temps ressemble à ceci :

Nous pouvons glaner quelques informations à partir du tableau et du graphique avant même d'effectuer des calculs :

• Les prix dans notre tableau stock_price portent sur la période comprise entre le 7 janvier 2020 et le 30 juin 2020 inclus.
• Les prix varient approximativement de 1 000 à 1 400 (voir l'axe des y).
• Il y a des fluctuations dans les prix ; il y a quelques pics en février, plusieurs pics en juin, etc.

Calculons une moyenne mobile pour la colonne price et générons un graphique linéaire des moyennes pour voir ce qui se passe. Pour cet exemple, nous allons travailler avec une moyenne mobile sur trois jours. Pour ce faire, nous calculons la moyenne des cours des actions de trois jours consécutifs - le jour en question et les deux jours précédents - puis nous répétons l'opération pour chaque jour de l'ensemble des données. Il s'agit d'une moyenne mobile sur trois jours, car nous calculons la moyenne sur une période de trois jours.

Voici comment est calculée une moyenne mobile sur trois jours pour le 9 janvier 2020 :

Pour le 9 janvier 2020, la moyenne mobile sur trois jours est calculée comme la moyenne des prix de ce jour (1 300) et des deux jours précédents : 8 janvier (1 300) et 7 janvier (1 320). Ainsi, la moyenne mobile du 9 janvier 2020 est la moyenne de ces trois valeurs, soit 1 306,66 comme le montre l'image ci-dessus.

La moyenne mobile est calculée de la même manière pour chacune des autres dates, en additionnant les trois cours de la date en question et des deux jours précédents, puis en divisant ce total par 3. Pour le 30 juin, la moyenne mobile sur trois jours est de 1 070, soit la moyenne des cours des dates du 30 juin (1 067), du 29 juin (1 067) et du 28 juin (1 076).

Si nous reportons les points de données originaux et la moyenne mobile sur un graphique linéaire, nous obtenons ce qui suit :

La ligne rouge représente la moyenne mobile, et la ligne bleue représente les points de données originaux. Vous remarquerez que la ligne rouge est plus lisse et ne présente pas les pics de la ligne bleue. Ce lissage est l'objectif principal de la technique de la moyenne mobile... il est utilisé pour éliminer le bruit des données. Avec moins de bruit, les tendances réelles des séries chronologiques sont plus faciles à voir.

Dans cet exemple, nous avons calculé la moyenne mobile sur trois jours. Cependant, nous pouvons la calculer sur n'importe quelle période, comme la moyenne mobile sur sept jours, la moyenne mobile sur dix jours, etc.

Pourquoi et où les moyennes mobiles sont utilisées

Les moyennes mobiles sont largement utilisées dans les transactions financières et techniques, notamment dans l'analyse du cours des actions, pour examiner les tendances à court et à long terme. Si le cours de l'action reste au-dessus de la moyenne mobile, nous avons une tendance à la hausse; s'il reste en dessous, les traders disent que nous sommes dans une tendance à la baisse. Les signaux tels que les tendances à la hausse et à la baisse informent les traders lorsqu'ils décident d'acheter ou de vendre des actions.

Cela dit, l'analyse du cours des actions n'est pas le seul cas d'utilisation de la moyenne mobile. D'autres applications commerciales incluent :

• L'analyse des ventes : Les moyennes mobiles lissent les fluctuations et les pics des ventes hebdomadaires ou quotidiennes.
• Analyse des cas confirmés de COVID-19: Les moyennes mobiles permettent de montrer comment le nombre de cas confirmés évolue dans le temps.
• Analyse du trafic Internet : Les moyennes mobiles nous aident à voir les tendances à long terme du nombre de visites et de pages vues.
• Finances personnelles : Les moyennes mobiles permettent de montrer les tendances de nos dépenses (par exemple, en examinant les moyennes sur dix jours des deux dernières années).

Moyennes mobiles en SQL

Maintenant que vous savez ce qu'est une moyenne mobile, voyons comment la calculer. En SQL, c'est facile avec les fonctions de fenêtre, une fonctionnalité spéciale de SQL qui vous permet d'effectuer des agrégations sur plusieurs lignes.

Bien que similaires à GROUP BY, les fonctions de fenêtre conservent toutes les lignes lors de l'affichage du résultat ; il n'y a pas de réduction des lignes. Au lieu de cela, avec les fonctions de fenêtre, nous définissons un cadre ou une "fenêtre" de lignes d'une taille donnée autour de la ligne actuelle, puis nous effectuons un calcul sur cette fenêtre. Ainsi, une agrégation est effectuée pour chaque ligne d'un tableau ; chaque ligne a sa propre fenêtre sur laquelle un calcul est effectué.

Voici comment la colonne moving_average de notre exemple est calculée en SQL :

select *,
  avg(Price) OVER(ORDER BY Date 
     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) 
     as moving_average 
from stock_price;

Pour expliquer le code en détail :

• Nous utilisons une fonction fenêtre, dénotée par une clause OVER. Comme expliqué précédemment, les lignes ne sont pas réduites, et chaque ligne a sa propre fenêtre sur laquelle un calcul est effectué.
• Dans notre exemple, la taille de la fenêtre est de trois. Pour chaque ligne donnée, nous prenons la ligne elle-même et les deux lignes précédentes, et nous calculons le prix moyen de ces trois lignes. Cette opération est indiquée par le mot clé ROW dans l'instruction : ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. Cette instruction indique que, pour chaque ligne du tableau, quelque chose est calculé comme une agrégation de la ligne actuelle et des deux lignes précédentes. Cela signifie que la moyenne mobile pour chaque ligne est calculée comme la moyenne des prix du jour donné et des deux jours précédents.
• Nous avons un cadre de fenêtre différent pour chaque jour. Ci-dessous, vous pouvez voir une illustration du cadre de fenêtre utilisé pour la ligne correspondant au 9 janvier (en vert) et du cadre de fenêtre utilisé pour la ligne correspondant au 27 juin (en bleu) :
• Il est important que les données ne comportent pas de trous dans les dates. Pour chaque jour, nous devons calculer la moyenne des prix de ce jour et des deux jours précédents. S'il y a des dates manquantes dans les données, cette analyse n'aura pas de sens.
• Le mot-clé ORDER BY dans la clause OVER définit l'ordre des lignes sur lesquelles la moyenne mobile doit être calculée. Dans notre exemple, les lignes sont d'abord triées par la colonne de la date, puis le cadre de la fenêtre est défini, et le calcul est effectué.
• Pour cet exemple, nous n'utilisons pas le mot-clé PARTITION BY dans la clause OVER. PARTITION BY regroupe les lignes en blocs logiques par catégorie, mais nous ne regroupons pas les lignes de cette manière ici. En fait, notre ensemble de données n'est qu'une seule grande partition. Plus loin dans cet article, nous verrons un exemple avec une clause PARTITION BY.

Maintenant, vous savez comment calculer des moyennes mobiles en SQL ! Le code SQL ci-dessus peut être utilisé dans de nombreux autres scénarios commerciaux ; il vous suffit de remplacer les noms de la table et des colonnes et d'ajuster le nombre de lignes pour lesquelles vous souhaitez calculer les moyennes. Tout le reste peut rester inchangé.

Les fonctions de fenêtre ont une syntaxe spécifique, et il faut un peu de temps et de pratique pour se familiariser avec leur utilisation. Pour apprendre et pratiquer davantage, je recommande le cours sur les fonctions de fenêtre sur LearnSQL.fr. Il est interactif et comporte de nombreux exercices, ce qui vous permet de vous exercer et d'apprendre rapidement les nouvelles notions, car c'est en faisant qu'on apprend ! Pour plus d'informations sur le contenu du cours et les fonctions de fenêtre elles-mêmes, vous pouvez lire l'article Le cours SQL du mois : Fonctions de fenêtrage. Sur la même plateforme, vous pouvez également trouver d'excellents articles sur l'utilisation des fonctions de fenêtre et explorer quelques exemples.

Moyenne mobile sur un nombre spécifique de jours

Dans l'exemple précédent, nous avons calculé une moyenne mobile sur trois jours. Vous pouvez également calculer d'autres moyennes, en prenant le nombre de valeurs précédentes que vous souhaitez. Plus le nombre de valeurs précédentes est élevé, plus notre courbe sera lisse. Plus le nombre de lignes utilisées pour calculer les moyennes est faible, plus le graphique de la moyenne mobile sera proche de celui des valeurs d'origine. Il existe une énorme différence entre, par exemple, des moyennes mobiles sur deux jours et sur 30 jours.

Avant de créer un visuel pour le prouver, calculons ces deux moyennes mobiles :

select *,
  avg(Price) OVER(ORDER BY Date 
      ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) 
     as 2day_moving_average,
  avg(Price) OVER(ORDER BY Date 
      ROWS BETWEEN 29 PRECEDING AND CURRENT ROW ) 
      as 30day_moving_average
from stock_price;

Et maintenant nous pouvons visualiser comment les deux lignes diffèrent :

La ligne verte représente la moyenne mobile sur 30 jours (30 cours boursiers sont utilisés pour calculer chaque moyenne), et la ligne rouge représente la moyenne sur deux jours. La ligne rouge ressemble presque à la ligne bleue des points de données originaux. La ligne verte est plus lisse et permet de voir plus facilement les tendances à long terme.

Maintenant, vous vous demandez peut-être comment savoir le bon nombre de lignes à prendre ? Eh bien, je ne peux pas vous aider ? cela dépend vraiment des besoins et de la situation de l'entreprise elle-même et de la raison pour laquelle vous analysez la mesure en premier lieu.

Exemple : Moyenne mobile sur sept jours des cas de COVID

Maintenant, pratiquons un peu plus pour récapituler ce que nous avons appris jusqu'à présent. Le COVID-19 est encore très présent dans nos vies, nous allons donc calculer la moyenne mobile sur sept jours du nombre total de cas confirmés par pays. Le nombre de cas confirmés pour chaque jour et pour chaque pays est stocké dans un tableau nommé confirmed_coviddans la colonne confirmed_day:

Pour calculer la moyenne mobile sur sept jours du nombre de cas confirmés, nous pouvons procéder comme suit :

SELECT *,
      avg(confirmed_day) OVER(
          PARTITION BY country 
          ORDER BY date 
          ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) 
          AS 7day_moving_average
FROM confirmed_covid;

Vous avez peut-être remarqué que ce code ressemble à celui que nous avons écrit pour la moyenne mobile du cours de l'action. La principale différence est que nous avons ici des partitions... chaque pays est une partition, car nous calculons les moyennes sur sept jours séparément pour chaque pays. Sinon, seuls les noms des tables et des colonnes sont différents. Vous pouvez facilement adapter ce code à tout autre scénario commercial.

Une fois que nous avons exécuté ce code et calculé la moyenne mobile, nous pouvons générer le graphique avec la ligne montrant la tendance pour le pays spécifique, depuis le début de la pandémie jusqu'au mois de juillet. Le graphique ci-dessous est celui du pays de la Croatie :

Encore une fois, nous voyons comment la moyenne mobile lisse et réduit les pics et les fluctuations par rapport aux points de données originaux.

Vous pouvez utiliser d'autres fonctions de fenêtre pour obtenir des informations importantes à partir des données COVID-19. Si vous souhaitez en savoir plus, consultez notre récent article sur la façon d'analyser les données COVID-19 à l'aide de fonctions de fenêtre.

Déplacement des moyennes et Fonctions de fenêtrage en SQL

Dans cet article, nous avons vu comment vous pouvez calculer des moyennes mobiles en SQL à l'aide des fonctions de fenêtre. Ces moyennes nous aident à mieux voir les véritables tendances en réduisant la quantité de bruit.

Si vous souhaitez pratiquer et en savoir plus sur les fonctions de fenêtre, je vous recommande le cours interactif en ligne sur les fonctions de fenêtre publié par LearnSQL.fr. Ce cours contient plus de 200 exercices, ce qui vous permettra de mettre en pratique vos nouvelles connaissances. Et croyez-moi, lorsqu'il s'agit de SQL et de compétences de codage, il est important de pratiquer - on apprend en faisant !