Comment calculer la longueur d'une série avec SQL

Comment calculer la longueur d'une série en SQL ? Je peux répondre à cette question en deux mots : fonctions de fenêtre ! Oui, vous aurez besoin d'une certaine connaissance des fonctions de fenêtre SQL pour calculer la longueur des séries. Mais qu'est-ce qu'une série chronologique, et pourquoi voulez-vous connaître sa longueur ?

Qu'est-ce qu'une série ?

Bien que la définition de "série chronologique" varie quelque peu, il s'agit essentiellement d'une séquence de données classées dans l'ordre chronologique.

Dans une base de données, cela est généralement représenté par des événements séparés par un temps égal. Par exemple, les connexions d'un utilisateur au site Web pendant des jours consécutifs sont considérées comme une série. Le tableau ci-dessous montre de telles dates de connexion :

Si je voulais analyser la durée de cette série, je compterais le nombre de connexions consécutives. La durée de la première série est de trois jours, puisque l'utilisateur s'est connecté les 2020-06-01, 2020-06-02 et 2020-06-03. La durée de la deuxième série est de six jours (l'utilisateur s'est connecté tous les jours entre le 06-06-20 et le 11-06-20). En suivant la même logique, les deux séries suivantes ont une durée de quatre et deux jours, respectivement.

Pourquoi calculer la longueur d'une série ?

Les séries chronologiques sont très utilisées et il existe de nombreuses situations dans lesquelles vous devez calculer la longueur des séries. Voici quelques exemples de calcul de la longueur d'une série :

• Mesurer une série de connexions sur Stack Overflow.
• Voir la durée de votre activité sur Duolingo.
• Suivre le nombre de jours d'utilisation d'une application de fitness.
• Analyser une série de ventes sur un site de commerce électronique.
• Trouver l'augmentation ou la diminution consécutive d'une valeur monétaire.

En gros, partout où vous avez une série chronologique, vous aurez probablement besoin de calculer sa longueur.

La question principale demeure : Comment calculer la longueur d'une série chronologique, et comment le faire en SQL ? Comme le montre le tableau ci-dessus, les séries chronologiques ne sont pas un concept difficile à appréhender. Dès que vous avez vu cet exemple, vous avez su intuitivement comment obtenir la longueur de la série.

Cependant, c'est une chose de trouver manuellement la longueur d'une série sur plusieurs lignes de données. Mais vous ne pouvez pas le faire sur une base de données comportant des centaines, des milliers ou des millions de lignes. Heureusement, les fonctions de fenêtre SQL sont là pour vous sauver la mise !

Si vous avez besoin de rafraîchir vos connaissances sur les fonctions de fenêtre, essayez cette aide-mémoire sur les fonctions de fenêtre. Elle peut s'avérer particulièrement utile lorsque nous arrivons à la partie calcul.

Si vous ne savez même pas ce que sont les fonctions de fenêtre, je vous recommande de lire Pourquoi devrais-je apprendre SQL Fonctions de fenêtrage? avant d'aller plus loin. La meilleure façon d'acquérir une connaissance vraiment complète des fonctions de fenêtre est le cours sur les fonctions de fenêtre LearnSQL.fr .

Comment calculer la longueur d'une série en SQL ?

Pour cet exemple, imaginons que vous appreniez le haut valyrien sur Duolingo. La plateforme Duolingo propose une fonction appelée "streak". Comme l'explique le site, une série est "le nombre de jours consécutifs où vous avez terminé une leçon". Lorsque vous terminez une leçon dans l'application ou sur le site Web, votre série augmente d'un jour. Vous recevrez votre récompense quotidienne lorsque vous aurez atteint votre objectif d'XP." (Le suivi de votre série de leçons vous permet de recevoir des incitations spécifiques de la plate-forme. Comment Duolingo peut-il savoir si votre série est longue ?

Votre série chronologique d'apprentissage pour juillet 2020 peut être présentée par le tableau lesson_completed. Il contient les colonnes suivantes :

• id: L'ID de la leçon.
• date_completed: La date à laquelle vous avez terminé la leçon.

Maintenant, écrivons une requête pour trouver votre série. Je vais utiliser une expression de table commune (CTE) pour organiser cette requête. Pour ceux d'entre vous qui ne sont pas familiers, notre article sur les CTE est un bon point de départ. L'exécution de cette requête permettra de calculer la longueur de la série :

WITH groups AS (
	SELECT	RANK() OVER (ORDER BY date_completed) AS row_number,
			date_completed,
			DATEADD (day, -RANK() OVER (ORDER BY date_completed), 
date_completed) AS date_group
FROM lesson_completed)

SELECT	COUNT(*) AS days_streak,
		MIN (date_completed) AS min_date,
		MAX (date_completed) AS max_date
FROM groups
GROUP BY date_group;

Laissez-moi vous expliquer ce que j'ai fait. La requête peut être divisée en deux parties :

• La création du CTE.
• Sélection des données dans le CTE.

Création du CTE

La partie de la requête qui crée le CTE est reprise ci-dessous :

WITH groups AS (
	SELECT	RANK() OVER (ORDER BY date_completed) AS row_number,
			date_completed,
			DATEADD (day, -RANK() OVER (ORDER BY date_completed), 
date_completed) AS date_group
FROM lesson_completed)

Le CTE est défini par la clause WITH. J'ai décidé que le nom de ce CTE devait être groupes. Tout ce qui est écrit entre les parenthèses après le mot-clé AS n'est qu'une déclaration assez régulière de SELECT.

Maintenant, qu'est-ce que cela fait ? Tout d'abord, j'ai ajouté le nombre de lignes à la table. Pour ce faire, j'ai utilisé la fonction RANK(). Il s'agit d'une fonction de fenêtre, c'est pourquoi elle est définie par la clause OVER(). Je veux que les rangs soient ajoutés séquentiellement en fonction des dates, le résultat de cette fonction est donc ordonné par la colonne date_completed.

Le CTE sélectionne ensuite la colonne date_completed. J'ai utilisé la fonction DATEADD (SQL Server) pour déduire le numéro de rang de date_completed. Dans cette instruction ...

DATEADD (day, -RANK() OVER (ORDER BY date_completed), date_completed) AS date_group 

... J'ai dû définir l'intervalle qui sera ajouté (ou déduit), c'est-à-dire le jour. Combien de jours dois-je déduire ? Les jours qui sont égaux au nombre de lignes - c'est pourquoi j'ai seulement copié la fonction de la fenêtre RANK() que j'avais déjà définie et ajouté le signe négatif devant elle. De quoi dois-je déduire cela ? Du site date_completed, bien sûr !

Cette partie de la requête donnera le résultat suivant :

Pourquoi ai-je besoin de cela ? Cela va m'aider à calculer la longueur de la série. Regardez le tableau. Avez-vous remarqué que les jours consécutifs appartiennent au même groupe de dates ? Comment cela se fait-il ? Si les dates sont consécutives et que vous en déduisez le numéro de ligne, vous obtiendrez toujours la même date. Regardez ceci :

Déduisez le numéro de ligne (1) de la date (2020-07-01) et vous obtiendrez 2020-06-30. C'est précisément ce que j'ai dans le tableau.

Et ensuite la ligne suivante. Si vous déduisez le numéro de ligne (2) de la date (2020-07-02), le résultat est à nouveau 2020-06-30 ! Voyons maintenant ce qui se passe lorsque les dates ne sont pas consécutives.

Pour la ligne 5, le résultat est toujours le même, à savoir 2020-06-30. C'est bien, mais qu'en est-il de la ligne suivante ? Déduisez la ligne numéro 6 de la date (2020-07-08), et qu'obtenez-vous ? 2020-07-02. Il s'agit maintenant d'un nouveau groupe de dates, ce qui n'est pas surprenant puisque 2020-07-08 n'est pas consécutif à 2020-07-05 dans aucun calendrier !

Une belle petite astuce, non ? En ce qui concerne les dates de la colonne date_group, les dates que vous obtenez n'ont pas vraiment d'importance. Elles serviront uniquement de valeurs, qui seront comptées dans la deuxième partie de la requête. Comptez le nombre de fois où chaque groupe apparaît et vous aurez votre longueur de série !

Sélection des données à partir du CTE

La deuxième partie de la requête sélectionne les données à partir du CTE que j'ai défini ci-dessus :

SELECT	COUNT(*) AS days_streak,
		MIN (date_completed) AS min_date,
		MAX (date_completed) AS max_date
FROM groups
GROUP BY date_group;

Cette simple instruction SELECT compte le nombre de lignes et affiche les résultats dans la nouvelle colonne days_streak. Ensuite, elle sélectionne la date minimale et maximale dans la colonne date_completed et les résultats sont affichés dans les colonnes min_date et max_date, respectivement. Toutes ces données seront sélectionnées dans le CTE nommé groupes.

Enfin, les données doivent être regroupées par date_group. Pourquoi ? Parce que je n'ai pas besoin du nombre total de lignes ; je veux le nombre de lignes pour chaque groupe de dates.

C'est une façon de calculer la longueur d'une série ...

Ce petit exemple devrait vous donner une idée de la façon de calculer la longueur d'une série avec SQL. Cependant, il n'y a pas qu'une seule façon de procéder. Et, comme vous l'avez vu, il n'existe pas de fonction SQL simple qui vous donnera ce que vous voulez. Vos données et ce que vous devez calculer détermineront votre code SQL. Cela nécessite généralement une petite astuce ; votre expérience et vos connaissances en SQL vous aideront à décider laquelle !

Ce que vous aurez presque certainement besoin de connaître pour calculer la longueur de la série, ce sont les fonctions de fenêtre SQL. Si vous n'êtes toujours pas convaincu de la nécessité d'apprendre les fonctions de fenêtre, lisez cet entretien avec la créatrice du cours - elle explique tous les avantages des fonctions de fenêtre.

Cet article ne vous a montré qu'un aperçu de ce que sont les séries chronologiques. Si vous souhaitez apprendre à analyser ce type spécifique de données, LearnSQL propose des conseils très détaillés.

Si cet article vous a aidé à résoudre des problèmes de séries temporelles, faites-le moi savoir dans la section des commentaires.