Comment grouper des données par semaine dans SQL Server

Il s'agit d'un contenu supplémentaire pour le cours LearnSQL.fr Customer Behavior Analysis in SQL Server.

Dans ce cours, nous vous avons montré comment analyser le cycle de vie des clients (acquisition, conversion, activité, rétention et désabonnement) avec SQL. Nous avons abordé les cohortes d'enregistrement des clients, ou les groupes de clients qui se sont enregistrés au cours de la même période (par exemple, la même semaine, le même mois). L'analyse des cohortes d'enregistrement des clients vous permet de voir les tendances en matière d'enregistrement et de relier les cohortes d'enregistrement aux campagnes de marketing.

En pratique, les cohortes d'enregistrement les plus pratiques sont les cohortes d'enregistrement hebdomadaires. Les cohortes d'enregistrement annuelles, trimestrielles ou mensuelles sont trop imprécises pour une analyse significative. D'autre part, les cohortes d'enregistrement quotidiennes ou horaires sont trop spécifiques. Les cohortes d'enregistrement hebdomadaires sont généralement parfaites.

Dans cet article, nous allons examiner les différentes façons de regrouper les données par semaine dans SQL Server.

Comment regrouper des données par semaine dans SQL Server ?

SQL Server fournit une fonction appelée DATEPART(), qui renvoie une partie spécifiée(année, trimestre, mois, semaine, heure, minute, etc.) d'une date spécifiée.

Pour regrouper les clients qui se sont inscrits en 2018 par semaine, vous pouvez utiliser cette requête :

SELECT
  DATEPART(week, RegistrationDate) AS Week,
  COUNT(CustomerID) AS Registrations
FROM Customers
WHERE '20180101' <= RegistrationDate
  AND RegistrationDate < '20190101'
GROUP BY DATEPART(week, RegistrationDate)
ORDER BY DATEPART(week, RegistrationDate);

Comme vous pouvez le constater, la fonction DATEPART() prend deux arguments : datepart (c'est-à-dire l'identifiant de la partie souhaitée) et la date dont vous extrayez la partie.

La fonction DATEPART() a deux arguments datepart qui renverront les données de la semaine :

• week (également abrégé en wk, ww).
• iso_week (également abrégé en isowk, isoww).

Nous expliquerons bientôt les différences entre ces deux types. Mais d'abord, nous devons discuter d'un autre paramètre.

Le paramètre DATEFIRST

Le paramètre DATEFIRST indique au serveur SQL quel jour de la semaine il doit considérer comme le premier jour de la semaine. DATEFIRST peut prendre l'une des valeurs suivantes :

Le paramètre DATEFIRST dépend de la version linguistique de SQL Server. La valeur par défaut pour l'anglais américain est 7 (c'est-à-dire dimanche).

Vous pouvez modifier la valeur de DATEFIRST comme suit :

SET DATEFIRST 1

DATEFIRST est un paramètre de session, ce qui signifie que vous pouvez le modifier sans que cela ait un impact sur les autres utilisateurs.

Vous pouvez trouver la valeur actuelle du paramètre DATEFIRST avec la fonction @@DATEFIRST:

SELECT @@DATEFIRST;

Résultat :

7

Le premier jour de la semaine est le 7, c'est-à-dire le dimanche.

Utilisation de DATEPART() avec la semaine

Maintenant que nous connaissons le paramètre DATEFIRST, regardons comment fonctionne DATEPART() with week.

Le comportement de DATEPART() avec semaine dépend du jour que DATEFIRST a défini comme premier jour de la semaine. Les semaines de chaque année sont numérotées séparément. La semaine 1 est la semaine qui contient (mais ne commence pas nécessairement par) le 1er janvier.

Prenons un exemple. L'image montre trois calendriers différents pour janvier 2019 ; chacun d'eux spécifie un jour de semaine différent comme premier jour de la semaine :

• Le premier calendrier indique le dimanche comme premier jour de la semaine, comme cela est généralement le cas aux États-Unis.
• Le deuxième calendrier indique le lundi comme premier jour de la semaine, comme cela est généralement utilisé en Europe.
• Le troisième calendrier utilise le jeudi comme premier jour de la semaine. (Juste à titre d'exemple d'une semaine non typique).

DATEPART() avec la semaine utilise la numérotation des semaines montrée dans l'image :

• Si le dimanche est le premier jour de la semaine (DATEFIRST = 7), la semaine 2 commence le dimanche 6 janvier et se termine le samedi 12 janvier.
• Si le lundi est le premier jour de la semaine (DATEFIRST = 1), la semaine 2 commence le lundi 7 janvier et se termine le dimanche 13 janvier.
• Si le jeudi est le premier jour de la semaine (DATEFIRST = 4), la semaine 2 commence le jeudi 3 janvier et se termine le mercredi 9 janvier.

Une utilisation typique de DATEPART() avec semaine est de regrouper les données par semaine via la clause GROUP BY. Nous l'utilisons également dans la clause SELECT pour afficher le numéro de la semaine. Jetez un coup d'œil à la requête ci-dessous et à son résultat :

SELECT
  DATEPART(week, RegistrationDate) AS Week,
  COUNT(CustomerID) AS Registrations
FROM Customers
WHERE '20180101' <= RegistrationDate
  AND RegistrationDate < '20190101'
GROUP BY DATEPART(week, RegistrationDate)
ORDER BY DATEPART(week, RegistrationDate);

Si vous trouvez les numéros de semaine illisibles, consultez l'article intitulé Comment obtenir le premier jour de la semaine.

Remarquez que pour DATEPART() avec semaine, la semaine où l'année se termine et celle où la suivante commence est souvent divisée. En d'autres termes, les derniers jours de décembre sont placés dans la semaine 52/53 de l'année précédente, tandis que les premiers jours de janvier sont dans la semaine 1 de la nouvelle année.

L'utilisation de DATEPART() avec iso_week

DATEPART() dispose d'un autre argument de datepart pour les semaines : iso_week. Cet argument traite les semaines conformément à la norme ISO 8601, qui est une norme internationale couvrant l'échange de données sur la date et l'heure.

Dans la norme ISO 8601, les semaines commencent le lundi. La semaine 1 d'une année est la semaine dans laquelle se trouve le premier jeudi de cette année. Ce style de numérotation des semaines est généralement utilisé dans les pays européens.

Par exemple, la semaine 1 de 2017 allait du lundi 2 janvier au dimanche 8 janvier. Il est important de noter que si le 1er janvier tombe un vendredi, un samedi ou un dimanche, il est considéré comme faisant partie de la semaine 52/53 de l'année précédente.

Voici le calendrier pour janvier 2017 :

Une fois encore, vous utilisez généralement DATEPART() avec iso_week dans GROUP BY et SELECT :

SELECT
  DATEPART(iso_week, RegistrationDate) AS Week,
  COUNT(CustomerID) AS Registrations
FROM Customers
WHERE '20180101' <= RegistrationDate
  AND RegistrationDate < '20190101'
GROUP BY DATEPART(iso_week, RegistrationDate)
ORDER BY DATEPART(iso_week, RegistrationDate);

Si vous trouvez les numéros de semaine illisibles, consultez l'article Comment obtenir le premier jour de la semaine.