Utilisation de DATE_TRUNC pour l'agrégation de données

[SaraDev (Sara Devlaeminck)]

La fonction date_trunc est un outil puissant en SQL. Elle permet de tronquer une valeur TIMESTAMP ou INTERVAL en fonction d’une partie de date spécifiée, ce qui en fait une fonction précieuse lorsque vous souhaitez agréger ou regrouper des données selon une période temporelle donnée.

Syntaxe

La syntaxe de la fonction date_trunc est la suivante :

date_trunc('date_part', field)

• date_part : Il s’agit d’une chaîne de caractères qui spécifie la partie de la date ou du horodatage à laquelle tronquer. Elle peut prendre l’une des valeurs suivantes :
  • millennium
  • century
  • decade
  • year
  • quarter
  • month
  • week
  • day
  • hour
  • minute
  • second
  • milliseconds
  • microseconds
• field : Il s’agit du horodatage ou de l’intervalle à tronquer.

Exemples d’utilisation dans les requêtes DE

Examinons quelques requêtes d’exemple qui utilisent date_trunc :

Nombre de nouveaux sujets par mois

Niveau de complexité : Débutant

Cette requête SQL permet de compter le nombre de sujets créés chaque mois dans la base de données Discourse.

SELECT 
    date_trunc('month', created_at)::DATE AS month,
    count(id)
FROM topics
GROUP BY month
ORDER BY month DESC

Dans cette requête, date_trunc('month', created_at)::DATE tronque le horodatage created_at au niveau du mois, puis le cast en date pour l’affichage, regroupant ainsi efficacement les sujets par mois de création.

La fonction count(id) compte ensuite le nombre de sujets créés chaque mois. Les résultats sont triés par mois dans l’ordre décroissant, de sorte que le mois le plus récent apparaît en premier.

Résultats d’exemple :

month	count
2023-09-01	1
2023-08-01	6
2023-07-01	10

Explication détaillée avec commentaires en ligne

-- Sélectionner le mois de création du sujet et le nombre de sujets
SELECT 
    -- Tronquer le horodatage 'created_at' au niveau du mois et le cast en date
    -- Cela regroupe les sujets par mois de création
    date_trunc('month', created_at)::DATE AS month,
    -- Compter le nombre de sujets créés chaque mois
    count(id)
-- Depuis la table 'topics'
FROM topics
-- Regrouper les résultats par mois
GROUP BY month
-- Trier les résultats par mois dans l'ordre décroissant
-- Cela signifie que le mois le plus récent apparaîtra en premier
ORDER BY month DESC

Total cumulatif des utilisateurs

Niveau de complexité : Intermédiaire

Cette requête fournit un rapport hebdomadaire des inscriptions d’utilisateurs sur un forum Discourse, ainsi qu’un total cumulé des utilisateurs. Elle utilise une clause WITH pour créer un jeu de résultats temporaire (daily_signups), puis sélectionne à partir de ce jeu de résultats.

-- [params]
-- date :start_date = 2000-01-01
-- date :end_date

WITH daily_signups AS(
SELECT
    to_char(date_trunc('week', u.created_at)::date,'YYYY-MM-DD') AS Date,
    Count (id) as Signups
FROM users u
WHERE
    u.created_at::date BETWEEN :start_date::date AND :end_date::date
GROUP BY Date
)

SELECT
    Date, Signups, SUM(Signups) OVER (ORDER BY Date) as total_users
FROM daily_signups
ORDER BY Date Asc

Voici une analyse du fonctionnement de cette requête :

• La clause WITH crée un jeu de résultats temporaire nommé daily_signups. Ce jeu de résultats contient le nombre d’inscriptions d’utilisateurs pour chaque semaine entre les dates de début et de fin spécifiées par les paramètres :start_date et :end_date.
• Dans le jeu de résultats daily_signups, date_trunc('week', u.created_at)::date tronque le horodatage created_at au niveau de la semaine, puis le cast en date. Cela regroupe efficacement les utilisateurs par semaine d’inscription.
• Count(id) compte ensuite le nombre d’utilisateurs inscrits chaque semaine.
• Dans l’instruction SELECT principale, SUM(Signups) OVER (ORDER BY Date) calcule un total cumulé des utilisateurs. La clause OVER (ORDER BY Date) spécifie que la somme doit être calculée sur les lignes triées par date, fournissant ainsi une somme cumulative des inscriptions jusqu’à chaque date.
• Les résultats sont ensuite triés par date dans l’ordre croissant.

Résultats d’exemple :

date	signups	total_users
2013-01-28	20	20.0
2013-02-04	2136	2156.0
2013-02-11	442	2598.0

Explication détaillée avec commentaires en ligne

-- Définir les paramètres pour les dates de début et de fin
-- [params]
-- date :start_date = 2000-01-01
-- date :end_date

-- Créer une expression de table commune (CTE) pour compter le nombre d'inscriptions d'utilisateurs chaque semaine
WITH daily_signups AS(
SELECT
    -- Tronquer le horodatage 'created_at' au niveau de la semaine et le formater en chaîne de date
    to_char(date_trunc('week', u.created_at)::date,'YYYY-MM-DD') AS Date,
    -- Compter le nombre d'utilisateurs inscrits
    Count (id) as Signups
FROM users u
WHERE
    -- Inclure uniquement les utilisateurs inscrits entre les dates de début et de fin
    u.created_at::date BETWEEN :start_date::date AND :end_date::date
GROUP BY Date
)

-- Sélectionner la date, le nombre d'inscriptions et le total cumulé des inscriptions
SELECT
    Date, 
    Signups, 
    -- Calculer le total cumulé des inscriptions
    SUM(Signups) OVER (ORDER BY Date) as total_users
FROM daily_signups
-- Trier les résultats par date dans l'ordre croissant
ORDER BY Date Asc

Nombre de questions résolues et non résolues par mois

Niveau de complexité : Intermédiaire / Nécessite le plugin Discourse Solved

Cette requête fournit un rapport mensuel du nombre de questions résolues et non résolues sur un forum Discourse. Cette requête suppose que tous les sujets d’un site peuvent être résolus.

-- [params]
-- date :start_date
-- date :end_date

WITH monthly_questions AS (
    SELECT
        date_trunc('month', created_at)::DATE AS month,
        COUNT(id) as total_questions
    FROM topics
    WHERE
        created_at::date BETWEEN :start_date::date AND :end_date::date
    GROUP BY month
),
solved_questions AS (
    SELECT
        date_trunc('month', created_at)::DATE AS month,
        COUNT(id) as solved
    FROM user_actions
    WHERE
        created_at::date BETWEEN :start_date::date AND :end_date::date
    AND action_type = 15
    GROUP BY month
)

SELECT
    mq.month, 
    mq.total_questions, 
    COALESCE(sq.solved, 0) as solved,
    mq.total_questions - COALESCE(sq.solved, 0) as unsolved
FROM monthly_questions mq
LEFT JOIN solved_questions sq ON mq.month = sq.month
ORDER BY mq.month ASC

Dans cette requête, la CTE monthly_questions compte le nombre total de questions (sujets) créés chaque mois. La CTE solved_questions compte le nombre de questions marquées comme résolues chaque mois en comptant le nombre d’id de la table user_actions avec action_type = 15.

L’instruction SELECT principale calcule ensuite le nombre de questions non résolues en soustrayant le nombre de questions résolues du nombre total de questions. Les résultats sont triés par mois dans l’ordre croissant, de sorte que le mois le plus ancien apparaît en premier.

Résultats d’exemple :

month	total_questions	solved	unsolved
2023-07-01	10	3	7
2023-08-01	6	0	6
2023-09-01	1	1	0

Explication détaillée avec commentaires en ligne

-- [params]
-- date :start_date = 2000-01-01
-- date :end_date

-- Créer une CTE pour compter le nombre total de questions (sujets) créés chaque mois
WITH monthly_questions AS (
    SELECT
        -- Tronquer le horodatage 'created_at' au niveau du mois
        date_trunc('month', created_at)::DATE AS month,
        -- Compter le nombre de sujets créés chaque mois
        COUNT(id) as total_questions
    FROM topics
    WHERE
        -- Inclure uniquement les sujets créés entre les dates de début et de fin
        created_at::date BETWEEN :start_date::date AND :end_date::date
    GROUP BY month
),
-- Créer une CTE pour compter le nombre de questions marquées comme résolues chaque mois
solved_questions AS (
    SELECT
        -- Tronquer le horodatage 'created_at' au niveau du mois
        date_trunc('month', created_at)::DATE AS month,
        -- Compter le nombre de questions résolues chaque mois
        COUNT(id) as solved
    FROM user_actions
    WHERE
        -- Inclure uniquement les actions effectuées entre les dates de début et de fin
        created_at::date BETWEEN :start_date::date AND :end_date::date
        -- Ne considérer que les actions où le type d'action est 15 (indiquant une question résolue)
        AND action_type = 15
    GROUP BY month
)

-- Sélectionner le mois, le nombre total de questions, le nombre de questions résolues et le nombre de questions non résolues
SELECT
    mq.month, 
    mq.total_questions, 
    -- S'il n'y a pas de questions résolues dans un mois, afficher 0
    COALESCE(sq.solved, 0) as solved,
    -- Soustraire le nombre de questions résolues du nombre total de questions pour obtenir le nombre de questions non résolues
    mq.total_questions - COALESCE(sq.solved, 0) as unsolved
FROM monthly_questions mq
-- Joindre les CTE 'monthly_questions' et 'solved_questions' sur le mois
LEFT JOIN solved_questions sq ON mq.month = sq.month
-- Trier les résultats par mois dans l'ordre croissant
ORDER BY mq.month ASC

Statistiques de réponse aux sujets

Niveau de complexité : Avancé

Cette requête SQL complexe fournit un rapport hebdomadaire sur l’activité des sujets dans un forum Discourse. Elle décompose les données des sujets en plusieurs indicateurs clés : le nombre de sujets ayant au moins une réponse, le nombre de sujets sans réponse, le nombre maximal de jours pendant lesquels un sujet est resté sans réponse, et le temps moyen avant la première réponse.

WITH posts_list AS (
    SELECT 
        t.id topic_id,
        p.post_number,
        p.created_at,
        ROW_NUMBER () OVER (
                    PARTITION BY p.topic_id 
                    ORDER BY p.post_number)  AS post_order
    FROM posts p
    INNER JOIN topics t ON t.id = p.topic_id AND archetype = 'regular' AND t.deleted_at ISNULL
    WHERE p.post_type = 1
        AND p.deleted_at ISNULL
        AND t.deleted_at ISNULL
        AND t.archetype = 'regular'
    ORDER BY p.topic_id, p.post_number
),
atleast_1_response AS (
    SELECT 
        date_trunc('week', t.created_at::date)::date AS "week",
        COUNT(*) AS "topics with atleast one response"
    FROM topics t
    WHERE t.archetype = 'regular'
        AND t.deleted_at ISNULL
        AND t.posts_count >= 2
    GROUP BY "week"
),
no_response AS(
    SELECT 
        date_trunc('week', t.created_at::date)::date AS "week",
        COUNT(*) AS "topics without response"
    FROM topics t
    WHERE t.archetype = 'regular'
        AND t.deleted_at ISNULL
        AND t.posts_count = 1
    GROUP BY "week"
),
max_days_without_response AS(
    SELECT 
        date_trunc('week', t.created_at::date)::date AS "week",
        CURRENT_DATE::date-MIN(created_at)::date AS "max days without response"
    FROM topics t
    WHERE t.archetype = 'regular'
        AND t.deleted_at ISNULL
        AND t.posts_count = 1
    GROUP BY "week"
),
avg_time_first_response AS (
    SELECT 
        date_trunc('week', pl.created_at::date)::date AS "week",
        EXTRACT(epoch FROM AVG(pl2.created_at-pl.created_at))/3600 AS "avg time first response (h)"
    FROM posts_list pl
    INNER JOIN posts_list pl2 ON pl.topic_id = pl2.topic_id AND pl2.post_order = 2
    WHERE  pl.post_order = 1
    GROUP BY "week" 
)

SELECT ar.week, 
    "topics without response", 
    "max days without response", 
    "topics with atleast one response",
    "avg time first response (h)"
FROM atleast_1_response ar
INNER JOIN no_response nr ON nr.week = ar.week
INNER JOIN max_days_without_response mdwr ON mdwr.week = ar.week
INNER JOIN avg_time_first_response atfr ON atfr.week = ar.week
ORDER BY "week" DESC

Voici un résumé du fonctionnement de cette requête :

• L’expression de table commune (CTE) posts_list sélectionne une liste de tous les messages réguliers des tables posts et topics, triés par topic_id et post_number. Elle attribue également un numéro de ligne (post_order) à chaque message au sein de son sujet.
• La CTE atleast_1_response compte le nombre de sujets réguliers ayant au moins une réponse (c’est-à-dire que posts_count est supérieur ou égal à 2) pour chaque semaine.
• La CTE no_response compte le nombre de sujets réguliers sans réponse (c’est-à-dire que posts_count est égal à 1) pour chaque semaine.
• La CTE max_days_without_response calcule le nombre maximal de jours pendant lesquels un sujet sans réponse est resté sans réponse pour chaque semaine.
• La CTE avg_time_first_response calcule le temps moyen avant la première réponse pour chaque sujet en heures, pour chaque semaine.
• L’instruction SELECT principale joint ensuite ces CTEs ensemble sur la semaine et sélectionne les colonnes pertinentes. Les résultats sont triés par semaine dans l’ordre décroissant.

week	topics without response	max days without response	topics with atleast one response	avg time first response (h)
2023-09-04	15	2	47	2.6778684519444444
2023-08-28	30	9	138	8.7899938238888889
2023-08-21	22	16	130	9.3280889688888889

Explication détaillée avec commentaires en ligne

-- Créer une table temporaire (CTE) de tous les messages réguliers, triés par topic_id et post_number
WITH posts_list AS (
    SELECT 
        t.id topic_id,  -- ID du sujet
        p.post_number,  -- Numéro du message
        p.created_at,   -- Date de création du message
        -- Attribuer un numéro de ligne à chaque message au sein de son sujet
        ROW_NUMBER () OVER (
                    PARTITION BY p.topic_id 
                    ORDER BY p.post_number)  AS post_order
    FROM posts p
    -- Joindre avec la table topics, en ne considérant que les sujets réguliers non supprimés
    INNER JOIN topics t ON t.id = p.topic_id AND archetype = 'regular' AND t.deleted_at ISNULL
    WHERE p.post_type = 1
        AND p.deleted_at ISNULL  -- Exclure les messages supprimés
        AND t.deleted_at ISNULL  -- Exclure les sujets supprimés
        AND t.archetype = 'regular'  -- Ne considérer que les sujets réguliers
    ORDER BY p.topic_id, p.post_number
),
-- Créer une CTE pour compter le nombre de sujets réguliers ayant au moins une réponse pour chaque semaine
atleast_1_response AS (
    SELECT 
        -- Tronquer le horodatage created_at au niveau de la semaine
        date_trunc('week', t.created_at::date)::date AS "week",
        COUNT(*) AS "topics with atleast one response"
    FROM topics t
    WHERE t.archetype = 'regular'  -- Ne considérer que les sujets réguliers
        AND t.deleted_at ISNULL  -- Exclure les sujets supprimés
        AND t.posts_count >= 2  -- Ne considérer que les sujets ayant au moins une réponse
    GROUP BY "week"
),
-- Créer une CTE pour compter le nombre de sujets réguliers sans réponse pour chaque semaine
no_response AS(
    SELECT 
        -- Tronquer le horodatage created_at au niveau de la semaine
        date_trunc('week', t.created_at::date)::date AS "week",
        COUNT(*) AS "topics without response"
    FROM topics t
    WHERE t.archetype = 'regular'  -- Ne considérer que les sujets réguliers
        AND t.deleted_at ISNULL  -- Exclure les sujets supprimés
        AND t.posts_count = 1  -- Ne considérer que les sujets sans réponse
    GROUP BY "week"
),
-- Créer une CTE pour calculer le nombre maximal de jours pendant lesquels un sujet sans réponse est resté sans réponse pour chaque semaine
max_days_without_response AS(
    SELECT 
        -- Tronquer le horodatage created_at au niveau de la semaine
        date_trunc('week', t.created_at::date)::date AS "week",
        -- Calculer le nombre de jours entre la date de création du sujet et la date actuelle
        CURRENT_DATE::date-MIN(created_at)::date AS "max days without response"
    FROM topics t
    WHERE t.archetype = 'regular'  -- Ne considérer que les sujets réguliers
        AND t.deleted_at ISNULL  -- Exclure les sujets supprimés
        AND t.posts_count = 1  -- Ne considérer que les sujets sans réponse
    GROUP BY "week"
),
-- Créer une CTE pour calculer le temps moyen avant la première réponse pour chaque sujet en heures, pour chaque semaine
avg_time_first_response AS (
    SELECT 
        -- Tronquer le horodatage created_at au niveau de la semaine
        date_trunc('week', pl.created_at::date)::date AS "week",
        -- Calculer le temps moyen avant la première réponse en heures
        EXTRACT(epoch FROM AVG(pl2.created_at-pl.created_at))/3600 AS "avg time first response (h)"
    FROM posts_list pl
    -- Joindre avec la CTE posts_list, en ne considérant que le deuxième message de chaque sujet
    INNER JOIN posts_list pl2 ON pl.topic_id = pl2.topic_id AND pl2.post_order = 2
    WHERE  pl.post_order = 1  -- Ne considérer que le premier message de chaque sujet
    GROUP BY "week" 
)

-- Sélectionner la semaine, le nombre de sujets sans réponse, le nombre maximal de jours sans réponse, le nombre de sujets ayant au moins une réponse et le temps moyen avant la première réponse
SELECT ar.week, 
    "topics without response", 
    "max days without response", 
    "topics with atleast one response",
    "avg time first response (h)"
FROM atleast_1_response ar
-- Joindre les CTEs ensemble sur la semaine
INNER JOIN no_response nr ON nr.week = ar.week
INNER JOIN max_days_without_response mdwr ON mdwr.week = ar.week
INNER JOIN avg_time_first_response atfr ON atfr.week = ar.week
-- Trier par semaine dans l'ordre décroissant
ORDER BY "week" DESC

Ce ne sont là que quelques exemples de la manière dont vous pouvez utiliser date_trunc dans vos requêtes Data Explorer. N’hésitez pas à utiliser l’une de ces requêtes sur votre site, et si vous avez des questions, n’hésitez pas à les poser ci-dessous.