SQL Interview Prep · Leçon

Tronquer et regrouper les dates

Regrouper par semaine, mois et trimestre avec DATE_TRUNC et ses équivalents

Tronquer et regrouper les dates est une leçon SQL Interview Prep gratuite sur CoddyKit. Ceci est la leçon 2 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage SQL Interview Prep, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours SQL Interview Prep comprend 4 leçons au total.

Pourquoi le regroupement des dates revient souvent

« Affichez le chiffre d’affaires par semaine » ou « les utilisateurs actifs par mois » sont de grands classiques des entretiens d’analystes. La compétence évaluée consiste à regrouper des horodatages précis dans un intervalle plus large afin que les lignes soient regroupées ensemble.

L’erreur des débutants consiste à extraire uniquement le numéro du mois, ce qui fusionne le même mois de plusieurs années. La réponse professionnelle est la troncature : associer chaque horodatage au début de sa période.

Intervalles hebdomadaires, mensuels, trimestriels et annuels
DATE_TRUNC et les fonctions équivalentes selon le dialecte
Effectuer le regroupement correctement pour que les graphiques soient alignés

DATE_TRUNC : l’outil essentiel

Dans PostgreSQL, DATE_TRUNC(unit, ts) met à zéro tout ce qui est plus précis que l’unité indiquée. La troncature à 'month' transforme n’importe quel horodatage de mars en 2024-03-01 00:00:00.

La valeur renvoyée reste un horodatage : elle se trie chronologiquement et permet un regroupement parfait. Il s’agit de la fonction de dates la plus utile pour les rapports.

SELECT DATE_TRUNC('month', TIMESTAMP '2024-03-17 14:30:00');
-- 2024-03-01 00:00:00

Regrouper le chiffre d’affaires par mois

L’exemple classique. Tronquez l’horodatage au mois, puis regroupez et additionnez. Comme l’intervalle contient l’année, janvier 2023 et janvier 2024 restent séparés.

Un tri par la valeur tronquée produit une série temporelle claire, prête à être utilisée dans un graphique.

SELECT
  DATE_TRUNC('month', order_ts) AS month,
  SUM(amount)                   AS revenue
FROM orders
GROUP BY 1
ORDER BY 1;

EXTRACT ou DATE_TRUNC

Les recruteurs vérifient directement cette distinction. Les deux fonctions extraient des informations sur la période, mais elles répondent à des questions différentes.

EXTRACT(MONTH FROM ts) renvoie le nombre 3 pour tous les mois de mars, quelle que soit l’année, ce qui est utile pour analyser la saisonnalité.
DATE_TRUNC('month', ts) renvoie le début du mois précis, en conservant la distinction entre les années, ce qui est utile pour les séries temporelles.

Si vous regroupez par EXTRACT(MONTH ...) pour un graphique de tendance mensuelle, vous mélangerez discrètement les années.

-- Seasonality: which month is busiest on average?
SELECT EXTRACT(MONTH FROM order_ts) AS month_num, COUNT(*)
FROM orders GROUP BY 1 ORDER BY 1;

-- Time series: month-by-month trend (years kept separate)
SELECT DATE_TRUNC('month', order_ts) AS month, COUNT(*)
FROM orders GROUP BY 1 ORDER BY 1;

Intervalles hebdomadaires et question du lundi

Le regroupement hebdomadaire dissimule une subtilité que les recruteurs aiment aborder : quand la semaine commence-t-elle ? Dans PostgreSQL, DATE_TRUNC('week', ts) se positionne toujours sur le lundi (semaines ISO).

Si l’entreprise veut des semaines commençant le dimanche, vous devez appliquer un décalage. Une astuce courante consiste à reculer la date d’un jour, à effectuer la troncature, puis à avancer la date d’un jour.

-- ISO week (Monday start)
SELECT DATE_TRUNC('week', order_ts) AS iso_week FROM orders;

-- Sunday-start week
SELECT DATE_TRUNC('week', order_ts + INTERVAL '1 day') - INTERVAL '1 day'
  AS sunday_week
FROM orders;

Intervalles trimestriels

Les rapports trimestriels sont courants dans les postes liés à la finance. DATE_TRUNC('quarter', ts) associe n’importe quel horodatage au premier jour de son trimestre : 1er janvier, 1er avril, 1er juillet ou 1er octobre.

Pour afficher plutôt le numéro du trimestre, combinez EXTRACT(QUARTER ...) avec l’année.

SELECT
  DATE_TRUNC('quarter', order_ts)                  AS quarter_start,
  EXTRACT(YEAR FROM order_ts) || '-Q'
    || EXTRACT(QUARTER FROM order_ts)              AS quarter_label,
  SUM(amount)                                      AS revenue
FROM orders
GROUP BY 1, 2
ORDER BY 1;

MySQL ne possède pas DATE_TRUNC

Une question fréquente sur les dialectes : « MySQL ne possède pas DATE_TRUNC ; comment regrouper les données par mois ? » La réponse compatible avec plusieurs systèmes consiste à formater la date selon la précision souhaitée.

DATE_FORMAT(ts, '%Y-%m-01') fournit le début du mois sous forme de texte ou de date.
DATE_FORMAT(ts, '%Y-%m') fournit une clé textuelle triable telle que 2024-03.

Pour les semaines, MySQL propose YEARWEEK() avec un argument de mode qui contrôle le début de la semaine.

-- MySQL month bucket
SELECT DATE_FORMAT(order_ts, '%Y-%m-01') AS month, SUM(amount)
FROM orders
GROUP BY 1
ORDER BY 1;

Regroupement par intervalles avec SQL Server

SQL Server ne proposait traditionnellement pas de fonction directe de troncature ; les candidats utilisaient donc DATEFROMPARTS ou l’idiome DATEADD/DATEDIFF. Les versions modernes (2022 et ultérieures) ajoutent DATETRUNC.

L’idiome classique, qui consiste à « compter les unités depuis l’époque de référence, puis à les ajouter à nouveau », fonctionne avec toutes les versions et mérite d’être connu.

-- Portable SQL Server month truncation
SELECT DATEADD(month, DATEDIFF(month, 0, order_ts), 0) AS month_start
FROM orders;

-- SQL Server 2022+
SELECT DATETRUNC(month, order_ts) AS month_start FROM orders;

Combler les périodes manquantes d’une série temporelle

La troncature seule supprime les périodes qui ne contiennent aucune ligne : un mois sans commande n’apparaîtra tout simplement pas. Les recruteurs vérifient si vous remarquez ce problème.

La solution consiste à générer un calendrier complet des périodes, puis à effectuer un LEFT JOIN des données avec celui-ci. Dans Postgres, generate_series construit ce calendrier.

SELECT
  cal.month,
  COALESCE(SUM(o.amount), 0) AS revenue
FROM generate_series(DATE '2024-01-01', DATE '2024-12-01',
                      INTERVAL '1 month') AS cal(month)
LEFT JOIN orders o
  ON DATE_TRUNC('month', o.order_ts) = cal.month
GROUP BY cal.month
ORDER BY cal.month;

Exemple approfondi : utilisateurs actifs par semaine

Combinez le regroupement par intervalles avec un comptage distinct. « Utilisateurs actifs par semaine » signifie compter les utilisateurs distincts pour chaque intervalle hebdomadaire ; c’est une demande réelle en analyse produit.

Tronquez l’horodatage de l’événement à la semaine, puis utilisez COUNT(DISTINCT user_id). Préciser que vous joindriez un calendrier hebdomadaire pour afficher les semaines sans activité vous rapportera des points supplémentaires.

SELECT
  DATE_TRUNC('week', event_ts) AS week,
  COUNT(DISTINCT user_id)      AS wau
FROM events
GROUP BY 1
ORDER BY 1;

Regroupement sur une colonne indexée

Voici une réserve importante concernant les performances : envelopper la colonne de dates dans DATE_TRUNC à l’intérieur d’une clause WHERE peut empêcher le planificateur d’utiliser un index sur cette colonne.

C’est parfaitement adapté à GROUP BY, mais pour filtrer, comparez plutôt la colonne brute à des bornes calculées. Nous avons abordé précédemment ce modèle d’intervalle semi-ouvert ; il s’applique également ici.

-- Avoid in WHERE: DATE_TRUNC('month', order_ts) = '2024-03-01'
-- Prefer:
SELECT * FROM orders
WHERE order_ts >= DATE '2024-03-01'
  AND order_ts <  DATE '2024-04-01';

Vérification rapide

Choisissez l’outil approprié pour un graphique de tendance mensuelle qui conserve la distinction entre les années.

Récapitulatif : tronquer et regrouper les dates

À retenir :

DATE_TRUNC(unit, ts) associe les horodatages au début d’une période et conserve la distinction entre les années : c’est l’outil adapté aux séries temporelles.
EXTRACT renvoie un simple nombre, utile pour la saisonnalité, mais fusionne les années.
Dans Postgres, les semaines commencent le lundi ; appliquez un décalage si vous avez besoin du dimanche.
MySQL utilise DATE_FORMAT ; les anciennes versions de SQL Server utilisent l’idiome DATEADD(DATEDIFF(...)) ; les versions 2022 et ultérieures proposent DATETRUNC.
Utilisez un calendrier de dates généré + LEFT JOIN pour afficher les périodes vides, et excluez DATE_TRUNC de WHERE afin de préserver l’utilisation des index.

Questions Fréquemment Posées

La leçon « Tronquer et regrouper les dates » est-elle gratuite ?

Oui — le texte complet de « Tronquer et regrouper les dates » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours SQL Interview Prep, passe à CoddyKit PRO. Le cours SQL Interview Prep comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « Tronquer et regrouper les dates » ?

Regrouper par semaine, mois et trimestre avec DATE_TRUNC et ses équivalents Tu pratiques SQL Interview Prep avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.

Dois-je avoir de l'expérience pour commencer SQL Interview Prep ?

Aucune expérience préalable n'est requise. SQL Interview Prep sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 2 sur 4.

Combien de temps prend la leçon « Tronquer et regrouper les dates » ?

La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.

Peux-tu écrire et exécuter du code dans cette leçon SQL Interview Prep ?

Oui. Chaque leçon SQL Interview Prep inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.

Toutes les leçons de ce cours

Calcul arithmétique sur les dates et intervalles
Tronquer et regrouper les dates
Analyser et formater des chaînes
Fuseaux horaires et horodatages

← Retour à SQL Interview Prep