Utilizzo di DATE_TRUNC per l'Aggregazione Dati

Comunità Dati e reportistica
1

La funzione date_trunc è uno strumento potente in SQL. Consente di troncare un valore TIMESTAMP o INTERVAL in base a una parte di data specificata, rendendola una funzione preziosa quando si desidera aggregare o raggruppare i dati in base a un determinato periodo di tempo.

Sintassi

La sintassi della funzione date_trunc è la seguente:

date_trunc('date_part', field)
  • date_part: è una stringa che specifica la parte della data o del timestamp a cui troncare. Può assumere uno dei seguenti valori:
    • millennium
    • century
    • decade
    • year
    • quarter
    • month
    • week
    • day
    • hour
    • minute
    • second
    • milliseconds
    • microseconds
  • field: è il timestamp o l’intervallo da troncare.

Esempi di utilizzo nelle query DE

Esaminiamo alcuni esempi di query che utilizzano date_trunc:

Numero di nuovi argomenti per mese

Livello di complessità: Principiante

Questa query SQL viene utilizzata per contare il numero di argomenti creati in ciascun mese nel database di Discourse.

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

In questa query, date_trunc('month', created_at)::DATE tronca il timestamp created_at al mese e lo converte in una data, raggruppando efficacemente gli argomenti in base al mese in cui sono stati creati.

La funzione count(id) conta quindi il numero di argomenti creati in ciascun mese. I risultati sono ordinati per mese in ordine decrescente, quindi il mese più recente apparirà per primo.

Esempi di risultati:

month count
2023-09-01 1
2023-08-01 6
2023-07-01 10
Spiegazione dettagliata con commenti inline 
-- Seleziona il mese in cui è stato creato l'argomento e il conteggio degli argomenti
SELECT 
    -- Tronca il timestamp 'created_at' al mese e lo converte in una data
    -- Questo raggruppa gli argomenti in base al mese in cui sono stati creati
    date_trunc('month', created_at)::DATE AS month,
    -- Conta il numero di argomenti creati in ciascun mese
    count(id)
-- Dalla tabella 'topics'
FROM topics
-- Raggruppa i risultati per mese
GROUP BY month
-- Ordina i risultati per mese in ordine decrescente
-- Ciò significa che il mese più recente apparirà per primo
ORDER BY month DESC

Totale cumulativo degli utenti

Livello di complessità: Intermedio

Questa query fornirà un report settimanale delle iscrizioni degli utenti su un forum Discourse, insieme a un totale cumulativo degli utenti. Utilizza una clausola WITH per creare un set di risultati temporaneo (daily_signups), per poi selezionare da tale set.

-- [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

Ecco una spiegazione del funzionamento di questa query:

  • La clausola WITH crea un set di risultati temporaneo denominato daily_signups. Questo set contiene il numero di iscrizioni degli utenti per ciascuna settimana tra le date di inizio e fine specificate dai parametri :start_date e :end_date.
  • All’interno del set di risultati daily_signups, date_trunc('week', u.created_at)::date tronca il timestamp created_at alla settimana e lo converte in una data. Ciò raggruppa efficacemente gli utenti in base alla settimana in cui si sono iscritti.
  • Count(id) conta quindi il numero di utenti che si sono iscritti in ciascuna settimana.
  • Nella clausola SELECT principale, SUM(Signups) OVER (ORDER BY Date) calcola un totale cumulativo degli utenti. La clausola OVER (ORDER BY Date) specifica che la somma deve essere calcolata sulle righe ordinate per data, fornendo così una somma cumulativa delle iscrizioni fino a ciascuna data.
  • I risultati sono quindi ordinati per data in ordine crescente.

Esempi di risultati:

date signups total_users
2013-01-28 20 20.0
2013-02-04 2136 2156.0
2013-02-11 442 2598.0
Spiegazione dettagliata con commenti inline 
-- Definisce i parametri per le date di inizio e fine
-- [params]
-- date :start_date = 2000-01-01
-- date :end_date

-- Crea un'espressione di tabella comune (CTE) per contare il numero di iscrizioni degli utenti ogni settimana
WITH daily_signups AS(
SELECT
    -- Tronca il timestamp 'created_at' alla settimana e lo formatta come stringa di data
    to_char(date_trunc('week', u.created_at)::date,'YYYY-MM-DD') AS Date,
    -- Conta il numero di utenti che si sono iscritti
    Count (id) as Signups
FROM users u
WHERE
    -- Include solo gli utenti che si sono iscritti tra le date di inizio e fine
    u.created_at::date BETWEEN :start_date::date AND :end_date::date
GROUP BY Date
)

-- Seleziona la data, il numero di iscrizioni e il totale cumulativo delle iscrizioni
SELECT
    Date, 
    Signups, 
    -- Calcola il totale cumulativo delle iscrizioni
    SUM(Signups) OVER (ORDER BY Date) as total_users
FROM daily_signups
-- Ordina i risultati per data in ordine crescente
ORDER BY Date Asc

Numero di domande risolte e non risolte per mese

Livello di complessità: Intermedio / Richiede il plugin Discourse Solved

Questa query fornirà un report mensile del numero di domande risolte e non risolte su un forum Discourse. Si assume che tutti gli argomenti su un sito possano essere risolti.

-- [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

In questa query, la CTE monthly_questions conta il numero totale di domande (argomenti) creati ogni mese. La CTE solved_questions conta il numero di domande contrassegnate come risolte ogni mese contando il numero di id dalla tabella user_actions con action type = 15.

La clausola SELECT principale calcola quindi il numero di domande non risolte sottraendo il numero di domande risolte dal numero totale di domande. I risultati sono ordinati per mese in ordine crescente, quindi il mese più antico apparirà per primo.

Esempi di risultati:

month total_questions solved unsolved
2023-07-01 10 3 7
2023-08-01 6 0 6
2023-09-01 1 1 0
Spiegazione dettagliata con commenti inline 
-- [params]
-- date :start_date = 2000-01-01
-- date :end_date

-- Crea una CTE per contare il numero totale di domande (argomenti) creati ogni mese
WITH monthly_questions AS (
    SELECT
        -- Tronca il timestamp 'created_at' al mese
        date_trunc('month', created_at)::DATE AS month,
        -- Conta il numero di argomenti creati in ciascun mese
        COUNT(id) as total_questions
    FROM topics
    WHERE
        -- Include solo gli argomenti creati tra le date di inizio e fine
        created_at::date BETWEEN :start_date::date AND :end_date::date
    GROUP BY month
),
-- Crea una CTE per contare il numero di domande contrassegnate come risolte ogni mese
solved_questions AS (
    SELECT
        -- Tronca il timestamp 'created_at' al mese
        date_trunc('month', created_at)::DATE AS month,
        -- Conta il numero di domande risolte in ciascun mese
        COUNT(id) as solved
    FROM user_actions
    WHERE
        -- Include solo le azioni eseguite tra le date di inizio e fine
        created_at::date BETWEEN :start_date::date AND :end_date::date
        -- Considera solo le azioni con action_type = 15 (che indicano una domanda risolta)
        AND action_type = 15
    GROUP BY month
)

-- Seleziona il mese, il numero totale di domande, il numero di domande risolte e il numero di domande non risolte
SELECT
    mq.month, 
    mq.total_questions, 
    -- Se non ci sono domande risolte in un mese, visualizza 0
    COALESCE(sq.solved, 0) as solved,
    -- Sottrai il numero di domande risolte dal numero totale di domande per ottenere il numero di domande non risolte
    mq.total_questions - COALESCE(sq.solved, 0) as unsolved
FROM monthly_questions mq
-- Unisci le CTE 'monthly_questions' e 'solved_questions' in base al mese
LEFT JOIN solved_questions sq ON mq.month = sq.month
-- Ordina i risultati per mese in ordine crescente
ORDER BY mq.month ASC

Statistiche sulle risposte agli argomenti

Livello di complessità: Avanzato

Questa complessa query SQL fornisce un report settimanale sull’attività degli argomenti su un forum Discourse. Suddivide i dati degli argomenti in diverse metriche chiave: il numero di argomenti con almeno una risposta, il numero di argomenti senza risposta, il numero massimo di giorni in cui un argomento è rimasto senza risposta e il tempo medio per la prima risposta.

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

Ecco un riepilogo del funzionamento di questa query:

  • La CTE posts_list seleziona un elenco di tutti i post regolari dalle tabelle posts e topics, ordinati per topic_id e post_number. Assegna inoltre un numero di riga (post_order) a ciascun post all’interno del proprio argomento.
  • La CTE atleast_1_response conta il numero di argomenti regolari con almeno una risposta (cioè posts_count maggiore o uguale a 2) per ciascuna settimana.
  • La CTE no_response conta il numero di argomenti regolari senza risposta (cioè posts_count uguale a 1) per ciascuna settimana.
  • La CTE max_days_without_response calcola il numero massimo di giorni in cui un argomento senza risposta è rimasto senza risposta per ciascuna settimana.
  • La CTE avg_time_first_response calcola il tempo medio per la prima risposta per ciascun argomento in ore, per ciascuna settimana.
  • La clausola SELECT principale unisce quindi queste CTE in base alla settimana e seleziona le colonne pertinenti. I risultati sono ordinati per settimana in ordine decrescente.
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
Spiegazione dettagliata con commenti inline 
-- Crea una tabella temporanea (CTE) di tutti i post regolari, ordinati per topic_id e post_number
WITH posts_list AS (
    SELECT 
        t.id topic_id,  -- ID dell'argomento
        p.post_number,  -- Numero del post
        p.created_at,   -- Data di creazione del post
        -- Assegna un numero di riga a ciascun post all'interno del proprio argomento
        ROW_NUMBER () OVER (
                    PARTITION BY p.topic_id 
                    ORDER BY p.post_number)  AS post_order
    FROM posts p
    -- Unisci con la tabella topics, considerando solo argomenti regolari non eliminati
    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  -- Escludi i post eliminati
        AND t.deleted_at ISNULL  -- Escludi gli argomenti eliminati
        AND t.archetype = 'regular'  -- Considera solo argomenti regolari
    ORDER BY p.topic_id, p.post_number
),
-- Crea una CTE per contare il numero di argomenti regolari con almeno una risposta per ciascuna settimana
atleast_1_response AS (
    SELECT 
        -- Tronca il timestamp created_at alla settimana
        date_trunc('week', t.created_at::date)::date AS "week",
        COUNT(*) AS "topics with atleast one response"
    FROM topics t
    WHERE t.archetype = 'regular'  -- Considera solo argomenti regolari
        AND t.deleted_at ISNULL  -- Escludi gli argomenti eliminati
        AND t.posts_count >= 2  -- Considera solo argomenti con almeno una risposta
    GROUP BY "week"
),
-- Crea una CTE per contare il numero di argomenti regolari senza risposta per ciascuna settimana
no_response AS(
    SELECT 
        -- Tronca il timestamp created_at alla settimana
        date_trunc('week', t.created_at::date)::date AS "week",
        COUNT(*) AS "topics without response"
    FROM topics t
    WHERE t.archetype = 'regular'  -- Considera solo argomenti regolari
        AND t.deleted_at ISNULL  -- Escludi gli argomenti eliminati
        AND t.posts_count = 1  -- Considera solo argomenti senza risposta
    GROUP BY "week"
),
-- Crea una CTE per calcolare il numero massimo di giorni in cui un argomento senza risposta è rimasto senza risposta per ciascuna settimana
max_days_without_response AS(
    SELECT 
        -- Tronca il timestamp created_at alla settimana
        date_trunc('week', t.created_at::date)::date AS "week",
        -- Calcola il numero di giorni dalla data di creazione dell'argomento alla data corrente
        CURRENT_DATE::date-MIN(created_at)::date AS "max days without response"
    FROM topics t
    WHERE t.archetype = 'regular'  -- Considera solo argomenti regolari
        AND t.deleted_at ISNULL  -- Escludi gli argomenti eliminati
        AND t.posts_count = 1  -- Considera solo argomenti senza risposta
    GROUP BY "week"
),
-- Crea una CTE per calcolare il tempo medio per la prima risposta per ciascun argomento in ore, per ciascuna settimana
avg_time_first_response AS (
    SELECT 
        -- Tronca il timestamp created_at alla settimana
        date_trunc('week', pl.created_at::date)::date AS "week",
        -- Calcola il tempo medio per la prima risposta in ore
        EXTRACT(epoch FROM AVG(pl2.created_at-pl.created_at))/3600 AS "avg time first response (h)"
    FROM posts_list pl
    -- Unisci con la CTE posts_list, considerando solo il secondo post in ciascun argomento
    INNER JOIN posts_list pl2 ON pl.topic_id = pl2.topic_id AND pl2.post_order = 2
    WHERE  pl.post_order = 1  -- Considera solo il primo post in ciascun argomento
    GROUP BY "week" 
)

-- Seleziona la settimana, il numero di argomenti senza risposta, il numero massimo di giorni senza risposta, il numero di argomenti con almeno una risposta e il tempo medio per la prima risposta
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
-- Unisci le CTE in base alla settimana
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
-- Ordina per settimana in ordine decrescente
ORDER BY "week" DESC

Questi sono solo alcuni esempi di come puoi utilizzare date_trunc nelle tue query di Data Explorer. Sentiti libero di utilizzare qualsiasi di queste query sul tuo sito e, se hai domande, chiedi pure qui sotto. :slight_smile:

5 Mi Piace
Retrospectively pulling number of users each calendar month