Comment analyser une série chronologique en SQL

Les valeurs ordonnées dans le temps sont appelées des séries temporelles. Dans cet article, je vais vous montrer les modèles les plus fréquents et vous apprendre à écrire des requêtes pour les séries temporelles en SQL à l'aide des fonctions de fenêtre.

Peut-être avez-vous eu l'occasion d'analyser des variables où chaque valeur était associée à une valeur temporelle. De telles données - où les valeurs sont ordonnées dans le temps - sont appelées données de séries chronologiques. Comme elles sont très fréquemment utilisées dans le monde des affaires, il est important d'apprendre les modèles courants utilisés pour analyser ce type de données.

Exemples de données de séries chronologiques

Lesdonnées de séries chronologiques sont des variables avec une composante supplémentaire : le temps. Cela signifie que chaque valeur d'un attribut est associée à une date ou à une valeur temporelle. Voici quelques exemples de données de séries chronologiques :

• Le cours quotidien des actions d'une société donnée l'année dernière. (Chaque cours de l'action est associé à un jour spécifique).
• La moyenne quotidienne de l'indice Dow Jones pour les 10 dernières années. (Chaque valeur de l'indice est associée à un jour spécifique).
• Visites uniques d'un site Web au cours d'un mois. (Chaque mois, le nombre de visites diffère).
• Les utilisateurs enregistrés d'un site web pour chaque jour.
• Les chiffres de vente hebdomadaires.
• Recettes et dépenses annuelles d'une entreprise sur une décennie. (L'année est la valeur temporelle).
• Les connexions quotidiennes à une application sur deux mois. (Le jour est la valeur temporelle.)

Dans cet article, nous allons analyser la popularité de deux sites Web fictifs à l'aide d'une mesure appelée "nombre total de visites quotidiennes". Les requêtes SQL dont nous allons parler peuvent être utilisées pour d'autres analyses de séries chronologiques, c'est-à-dire qu'elles sont applicables à d'autres ensembles de données de séries chronologiques.

Nous observerons la période du 1er juillet 2019 au 31 décembre 2019.

Voici les données :

tabledaily_visits

Cette table s'appelle daily_visits et contient les attributs suivants :

• date - N'importe quel jour entre le 01 juillet et le 31 décembre 2019.
• visits - Le nombre total de visites du site Web à une date donnée.
• weekend - Cette valeur est 'N' si la date est un jour de semaine et 'Y' si c'est un samedi ou un dimanche.
• website - Le nom de domaine du site Web ('www.sqlanalysts.com' ou 'www.sqldevelopers.com').

Comme vous pouvez le remarquer, chaque ligne de notre table daily_visits (c'est-à-dire chaque valeur de l'attribut visites ) est associée à un jour (l'attribut date ). Il s'agit d'un exemple de données de série chronologique.

Exécution des totaux avec SQL Fonctions de fenêtrage

Nous allons commencer notre analyse en utilisant un modèle appelé le total courant. Un total courant est la somme cumulée de tous les chiffres précédents dans une colonne.

Vous trouverez ci-dessous un total courant pour le nombre de visites du 1er juillet à un jour spécifique. Notez que ce chiffre est calculé pour chaque site :

Dans l'image ci-dessus, vous pouvez voir que le 1er juillet, le total courant est de 2 805. (C'est parce qu'il n'y a pas de données pour les dates antérieures au 1er juillet ; nous commençons le calcul à partir de cette date.

Le jour suivant (02 juillet), la valeur de la ligne précédente est ajoutée au nombre actuel de visites. Le total courant pour ce jour est de 7 203 - le nombre de visites du 01 juillet plus le nombre de visites du 02 juillet. Le jour suivant, le 3 juillet, nous ajoutons ce nombre (6 744) au total précédent (7 203) et obtenons 13 947. Et ainsi de suite.

Il ne s'agit là que d'un exemple d'entreprise où le modèle du total courant est utilisé ; en voici d'autres :

• Le nombre total d'articles vendus depuis le premier jour d'un mois jusqu'au jour observé du même mois.
• Le total courant (somme cumulée) des transactions de débit/crédit d'un compte bancaire au cours du dernier trimestre ou de la dernière année.
• Total des recettes depuis le mois de janvier jusqu'au mois observé de la même année.

Maintenant que nous savons ce qu'est un total courant, nous allons écrire une requête SQL qui en calcule un. Un total courant (ou somme cumulée) peut être calculé en SQL en utilisant les fonctions de fenêtre appropriées.

Les fonctions de fenêtre sont des fonctions SQL spéciales qui fonctionnent sur un ensemble de lignes. Elles sont similaires à la clause GROUP BY, mais le résultat est affiché différemment à la fin. Dans un simple GROUP BY, les lignes sont réduites (chaque groupe est représenté par une seule ligne). Avec les fonctions de fenêtre, les lignes ne sont pas réduites ; chaque ligne est renvoyée et la valeur du calcul est affectée à chaque ligne du tableau. C'est exactement ce dont nous avons besoin pour afficher le total courant - une valeur supplémentaire dans un tableau existant.

Vous trouverez ci-dessous une requête SQL qui calcule le total courant des daily_visit données :

SELECT
   *, 
  SUM(visits) OVER (PARTITION BY website ORDER BY date) AS running_total 
FROM daily_visits;

Lorsque vous exécutez cette requête, toutes les lignes sont affichées et une colonne supplémentaire, running_total, est créée. Voici une brève explication de ce qui se passe :

• La clause OVER, avec PARTITION BY, indique à SQL qu'il s'agira d'une fonction de fenêtre.
• PARTITION BY divise les lignes en groupes logiques. Dans notre exemple, les lignes sont regroupées au niveau du site Web.
• ORDER BY définit l'ordre des lignes. Dans notre exemple, toutes les lignes sont triées par la colonne date.
• ROW/RANGE n'est pas explicitement défini dans cet exemple. Ces mots-clés facultatifs signifient que chaque cadre de fenêtre à l'intérieur d'une partition comprend toutes les lignes depuis le début de la partition jusqu'à la ligne actuelle. En d'autres termes, pour chaque ligne, la somme totale est calculée comme la somme des valeurs de la première ligne de la partition jusqu'à la ligne actuelle.

Avec cette instruction SQL (OVER en combinaison avec PARTITION BY et ORDER BY), nous établissons un total courant des visites au niveau du site Web.

Je ne vais pas me plonger ici dans cette explication et cette syntaxe. Si vous souhaitez apprendre les fonctions de fenêtre de manière plus détaillée, je vous recommande le cours LearnSQL.com sur les fonctions de fenêtre. Consultez l'article complémentaire, Cours SQL du mois - Fonctions de fenêtrage; il répond à des questions telles que : pourquoi apprendre les fonctions de fenêtrage, ce qu'elles sont, et pourquoi ce cours est un excellent choix.

Les fonctions de fenêtre sont utilisées dans d'autres calculs de motifs. Dans la section suivante, je jetterai un coup d'œil à l'aide-mémoire des fonctions de fenêtre et je vous montrerai comment écrire des requêtes SQL pour les variations en pourcentage et les moyennes mobiles.

Variation en pourcentage des visites quotidiennes d'un site Web

Vous aurez très souvent besoin de décrire l'évolution de votre activité dans le temps. Il existe un autre modèle très courant utilisé dans l'analyse des séries chronologiques appelé "pourcentage de changement" (ou pourcentage de changement). Il répond à des questions telles que :

• Comment le revenu de ce mois-ci se compare-t-il à celui du mois dernier ? A-t-il augmenté ou diminué ?
• Le nombre d'utilisateurs enregistrés sur notre site Web a-t-il augmenté ou diminué ce trimestre ?
• Ai-je vendu plus d'articles aujourd'hui qu'il y a une semaine ?
• Nos ventes sont-elles en hausse ou en baisse par rapport à l'année dernière ?

Ensuite, je vais vous montrer comment utiliser SQL pour calculer les variations en pourcentage. Dans l'exemple suivant, nous allons trouver le pourcentage de variation du nombre total de visites du site Web (aujourd'hui par rapport à hier et aujourd'hui par rapport à il y a une semaine). Une fois que vous aurez appris à calculer des pourcentages de variation grâce à cet exemple, vous pourrez l'appliquer à n'importe quel autre cas d'entreprise. La structure de la requête est la même ; seuls les noms des tables et des colonnes - et peut-être un argument LAG(), que je vais expliquer dans un instant - diffèrent.

Utilisation de la fonction LAG() pour récupérer la valeur d'une ligne précédente

La première étape du calcul du pourcentage de variation consiste à récupérer la valeur d'une ligne précédente. Pourquoi en avons-nous besoin ? Parce que le pourcentage de variation est calculé par la formule:

(current_value - previous_value)/previous value * 100.

Cette formule signifie que pour calculer l'augmentation ou la diminution du nombre de visites, vous devez avoir les deux valeurs présentées dans la même ligne.

Ainsi, notre première tâche pour calculer ce modèle est de récupérer une valeur dans une ligne précédente. Cette tâche peut être réalisée à l'aide de la fonction de fenêtre LAG(). Elle permet d'accéder à une valeur dans une ligne donnée qui précède la ligne actuelle. Vous trouverez ci-dessous une instruction SELECT qui attribue la valeur daily_visits de la ligne précédente à une nouvelle colonne(previous_day_visits) dans la ligne actuelle :

SELECT
  *,
  LAG(visits) OVER(PARTITION BY website ORDER BY date) AS previous_day_visits
FROM daily_visits;

Il s'agit d'une fonction fenêtre typique : à l'intérieur de la clause OVER, vous définissez la partition et l'ordre souhaités. La fonction LAG() prend un argument (le nom de la colonne contenant les valeurs souhaitées) et affecte la valeur de la ligne précédente à chaque ligne :

LAG() peut également être utilisée pour affecter les valeurs de n lignes en arrière, et pas seulement la ligne précédente. Par exemple, supposons que vous souhaitiez calculer le pourcentage de variation pour le même jour de la semaine dernière. Dans ce cas, vous devez attribuer à chaque ligne une valeur datant de sept jours.

Pour ce faire, nous utilisons le paramètre de décalage facultatif de LAG. Regardez la requête suivante :

SELECT
  *,
  LAG(visits,7) OVER(PARTITION BY website ORDER BY date) AS previous_day_visits
FROM daily_visits;

La valeur de décalage par défaut de LAG est 1 (la ligne précédente), mais vous pouvez la modifier pour toute autre valeur. Dans notre exemple, nous avons utilisé une valeur de 7, qui attribue à chaque ligne le nombre de visites des 7 derniers jours :

Augmentation/diminution d'un jour du nombre total de visites

Maintenant, nous pouvons facilement calculer une augmentation/diminution d'un jour avec ce code SQL :

WITH daily_visits_lag AS (
 SELECT
  *,
  LAG(visits) OVER(PARTITION BY website ORDER BY date) AS previous_day_visits
  FROM daily_visits
)
SELECT
    *,
   COALESCE(round((visits — previous_day_visits)/previous_day_visits *100),0) AS percent_change
FROM daily_visits_lag;

Cette déclaration semble compliquée, mais elle ne l'est pas vraiment. Voici ce qui se passe :

• Le SELECT qui attribue à chaque ligne une valeur de la ligne précédente est écrit à l'intérieur d'une instruction WITH. Cela signifie que nous utilisons une expression de table commune ou CTE (c'est-à-dire un ensemble de résultats temporaire nommé). Nous utilisons ensuite ce résultat temporaire dans le SELECT suivant.
• L'ensemble de résultats temporaires s'appelle daily_visits_lag. Il contient les valeurs nécessaires au calcul de la variation en pourcentage (c'est-à-dire le nombre de visites pour la ligne actuelle et le nombre de visites du jour précédent).
• daily_visits_lag est utilisé dans la requête principale. Une colonne supplémentaire, pourcent_change, est calculée dans COALESCE() avec la formule(visits-previous_day_visits)/previous_day_visits * 100.
• Après l'exécution de cette instruction, le moteur SQL abandonne le jeu de résultats temporaire ; il ne peut plus être utilisé dans le code. (C'est ainsi que fonctionne un CTE).

Nous n'avons pas assez d'espace pour aborder les CTE ici, mais notre Requêtes récursives cours est une bonne ressource pour en savoir plus sur l'utilisation et la syntaxe des CTE.

Après avoir exécuté cette requête, les valeurs suivantes sont affichées :

Augmentation/diminution du nombre total de visites sur 7 jours.

Maintenant que vous savez comment calculer une variation en pourcentage sur un jour, une instruction très similaire peut être utilisée pour calculer une augmentation/diminution sur 7 jours du nombre total de visites :

WITH daily_visits_lag AS (
 SELECT
  *,
  LAG(visits,7) OVER(PARTITION BY website ORDER BY date) AS previous_7day_visits
  FROM daily_visits
)
SELECT
    *,
   COALESCE(round((visits — previous_7day_visits)/previous_7day_visits *100),0) AS percent_change
FROM daily_visits_lag;

La seule différence ici est que nous avons utilisé LAG() avec un paramètre de décalage de 7 - nous récupérons les valeurs de visite de 7 jours plus tôt (il y a une semaine) et calculons le pourcentage de changement avec ces valeurs (jour actuel par rapport à une semaine plus tôt). En d'autres termes, nous calculons l'augmentation ou la diminution du nombre de visites sur 7 jours :

Gardez à l'esprit que la requête que nous avons utilisée ici peut être utilisée sur d'autres exemples commerciaux. Il suffit d'ajuster les noms des tables et des colonnes ; le reste peut rester inchangé.

Moyennes mobiles simples : 7 jours

Un autre modèle très fréquemment utilisé dans l'analyse des séries chronologiques est appelé moyenne mobile simple (SMA). Une SMA est la moyenne non pondérée des valeurs des n lignes précédentes ; elle est calculée pour chaque valeur d'une colonne donnée.

Les SMA sont souvent utilisées pour déterminer les tendances dans l'analyse des cours boursiers ou des crypto-monnaies. Ces informations nous aident à comprendre le comportement de notre variable : au lieu d'une seule valeur, nous obtenons de meilleures estimations en utilisant les valeurs moyennes d'une mesure spécifique. Nous lissons les fluctuations pour obtenir une vue d'ensemble.

Dans l'exemple de notre site Web, chaque jour aura deux chiffres intéressants :

• Le nombre de visites qui ont eu lieu ce jour-là.
• Le nombre moyen de visites sur les 7 derniers jours.

Une fois de plus, nous allons utiliser une fonction de fenêtre SQL pour calculer notre SMA. Contrairement à l'exemple précédent, où nous avons utilisé LAG() pour récupérer les valeurs des lignes précédentes, nous utiliserons ici le paramètre ROW/RANGE dans la clause OVER :

SELECT
   *, 
   AVG(visits) OVER(PARTITION BY website ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as SMA7
FROM daily_visits ;

Plus haut dans l'article, nous avons dit que ROW dans une clause OVER définit une fenêtre à l'intérieur de chaque partition. Lorsque nous avons calculé le total courant, nous avons utilisé des valeurs par défaut pour les paramètres row/range. Cela signifie que le point de départ de chaque fenêtre à l'intérieur de la partition était la première ligne de cette partition et que le point d'arrivée était la ligne actuelle.

Maintenant, chaque fenêtre est définie comme 7 rangs (les 6 valeurs de rangs précédents + la valeur du rang actuel). Nous avons fait cela avec ROWS BETWEEN 6 PRECEDING AND CURRENT ROW. En d'autres termes, le point de départ n'est pas la première ligne de la partition. Ici, la valeur moyenne(avg(visites)) va être calculée sur les 7 dernières lignes, y compris la ligne actuelle. C'est la définition d'un SMA.

Une fois que vous aurez exécuté cette instruction, vous obtiendrez une valeur moyenne associée à chaque ligne. Cela vous permettra d'observer les tendances des visites de manière plus détaillée. Voici le résultat :

Les fonctions de fenêtre SQL sont une fonctionnalité vraiment puissante pour l'analyse des tendances, et la SMA n'est qu'un des indicateurs qui peuvent être obtenus avec les fonctions de fenêtre. Si vous souhaitez voir d'autres exemples liés à l'analyse des tendances, essayez notre cours Revenue Trend Analysis in SQL. Vous y apprendrez comment utiliser SQL pour analyser les tendances de n'importe quelle série chronologique.

Utilisation de RANK() pour trouver le plus grand nombre de visites

Notre dernier modèle d'analyse est le classement. Comme vous pouvez le deviner, il permet de classer les résultats en fonction d'une variable donnée. Supposons que nous voulions voir quelles dates ont enregistré le plus grand nombre de visites pour chacun de nos sites Web. Pour ce faire, nous devons classer les visites quotidiennes de chaque site séparément. Nous pouvons le faire en utilisant la fonction de la fenêtre RANK():

SELECT *,
  RANK() OVER (PARTITION by website ORDER BY visits DESC) AS rank 
FROM daily_visits;

Voici ce qui se passe dans ce code :

• Le rang est déterminé par la colonne des visites. Celle-ci est définie dans ORDER BY, qui se trouve dans la clause OVER). La ligne avec la valeur de visites la plus élevée obtient le rang le plus élevé.
• Les rangs (visites) sont regroupés séparément pour chaque site Web, de sorte que le rang de chaque site Web est déterminé individuellement. Ceci est défini dans PARTITION BY dans la clause OVER.
• Les lignes ayant les mêmes critères de classement (c'est-à-dire ayant la même valeur de visites ) reçoivent le même rang.

Une fois que vous exécutez ce SELECT, le moteur SQL renvoie un ensemble de résultats avec une colonne supplémentaire appelée rank. Maintenant, nous pouvons facilement voir quels jours ont eu le plus grand nombre de visites. Ce qui est bien, c'est que le classement est défini pour chaque site, donc nous ne comparons pas les deux sites ensemble.

Le rang le plus élevé pour sqldevelopers.com

Le rang le plus élevé pour sqlanalysts.com

En savoir plus sur l'analyse des séries chronologiques avec SQL

Vous pouvez maintenant utiliser les fonctions de la fenêtre SQL pour effectuer une analyse de base des séries chronologiques. Vous savez ce que sont les séries temporelles et comment vous pouvez utiliser les fonctions de fenêtre SQL pour obtenir des informations intéressantes. Vous avez même été initié aux CTE.

Quelle est la prochaine étape ? Je vous recommande vivement d'adopter une approche organisée pour en savoir plus sur l'analyse SQL des séries chronologiques. Les cours Fonctions de fenêtrage et Revenue Trend Analysis in SQL dont j'ai déjà parlé sont de bons cours. N'oubliez pas qu'il est important de mettre vos compétences en pratique, alors assurez-vous que vous avez des exercices à résoudre dans le monde réel !