Usando DATE_TRUNC para Agregação de Dados

[SaraDev (Sara Devlaeminck)]

A função date_trunc é uma ferramenta poderosa em SQL. Ela permite truncar um valor TIMESTAMP ou INTERVAL com base em uma parte de data especificada, tornando-se uma função inestimável quando você deseja agregar ou agrupar dados com base em um período de tempo específico.

Sintaxe

A sintaxe da função date_trunc é a seguinte:

date_trunc('date_part', field)

• date_part: Esta é uma string que especifica a parte da data ou do timestamp para a qual truncar. Pode ser um dos seguintes valores:
  • millennium
  • century
  • decade
  • year
  • quarter
  • month
  • week
  • day
  • hour
  • minute
  • second
  • milliseconds
  • microseconds
• field: Este é o timestamp ou intervalo a ser truncado.

Exemplos de Uso em Consultas do DE

Vamos analisar algumas consultas de exemplo que utilizam date_trunc:

Contagem de Novos Tópicos por Mês

Nível de Complexidade: Iniciante

Esta consulta SQL é usada para contar o número de tópicos criados em cada mês no banco de dados do Discourse.

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

Nesta consulta, date_trunc('month', created_at)::DATE truncar o timestamp created_at para o mês e depois o converte para exibir uma data, agrupando efetivamente os tópicos pelo mês em que foram criados.

A função count(id) então conta o número de tópicos criados em cada mês. Os resultados são ordenados por mês em ordem decrescente, de modo que o mês mais recente aparecerá primeiro.

Resultados de Exemplo:

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

Explicação Detalhada com Comentários Inline

-- Seleciona o mês em que o tópico foi criado e a contagem de tópicos
SELECT 
    -- Trunca o timestamp 'created_at' para o mês e o converte para uma data
    -- Isso agrupa os tópicos pelo mês em que foram criados
    date_trunc('month', created_at)::DATE AS month,
    -- Conta o número de tópicos criados em cada mês
    count(id)
-- Da tabela 'topics'
FROM topics
-- Agrupa os resultados pelo mês
GROUP BY month
-- Ordena os resultados pelo mês em ordem decrescente
-- Isso significa que o mês mais recente aparecerá primeiro
ORDER BY month DESC

Total Acumulado de Usuários

Nível de Complexidade: Intermediário

Esta consulta fornecerá um relatório semanal de inscrições de usuários em um fórum Discourse, juntamente com um total acumulado de usuários. Ela usa uma cláusula WITH para criar um conjunto de resultados temporário (daily_signups) e, em seguida, seleciona a partir desse conjunto.

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

Aqui está uma análise de como esta consulta funciona:

• A cláusula WITH cria um conjunto de resultados temporário chamado daily_signups. Este conjunto contém o número de inscrições de usuários para cada semana entre as datas de início e fim especificadas pelos parâmetros :start_date e :end_date.
• Dentro do conjunto de resultados daily_signups, date_trunc('week', u.created_at)::date trunca o timestamp created_at para a semana e depois o converte para uma data. Isso agrupa efetivamente os usuários pela semana em que se inscreveram.
• Count(id) então conta o número de usuários que se inscreveram em cada semana.
• Na declaração SELECT principal, SUM(Signups) OVER (ORDER BY Date) calcula um total acumulado de usuários. A cláusula OVER (ORDER BY Date) especifica que a soma deve ser calculada sobre as linhas ordenadas por data, fornecendo assim uma soma cumulativa de inscrições até cada data.
• Os resultados são então ordenados por data em ordem crescente.

Resultados de Exemplo:

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

Explicação Detalhada com Comentários Inline

-- Define parâmetros para as datas de início e fim
-- [params]
-- date :start_date = 2000-01-01
-- date :end_date

-- Cria uma expressão de tabela comum (CTE) para contar o número de inscrições de usuários a cada semana
WITH daily_signups AS(
SELECT
    -- Trunca o timestamp 'created_at' para a semana e o formata como uma string de data
    to_char(date_trunc('week', u.created_at)::date,'YYYY-MM-DD') AS Date,
    -- Conta o número de usuários que se inscreveram
    Count (id) as Signups
FROM users u
WHERE
    -- Inclui apenas usuários que se inscreveram entre as datas de início e fim
    u.created_at::date BETWEEN :start_date::date AND :end_date::date
GROUP BY Date
)

-- Seleciona a data, o número de inscrições e o total acumulado de inscrições
SELECT
    Date, 
    Signups, 
    -- Calcula o total acumulado de inscrições
    SUM(Signups) OVER (ORDER BY Date) as total_users
FROM daily_signups
-- Ordena os resultados por data em ordem crescente
ORDER BY Date Asc

Número de Perguntas Resolvidas e Não Resolvidas por Mês

Nível de Complexidade: Intermediário / Requer o Plugin Discourse Solved

Esta consulta fornecerá um relatório mensal do número de perguntas resolvidas e não resolvidas em um fórum Discourse. Esta consulta assume que todos os tópicos em um site podem ser resolvidos.

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

Nesta consulta, a CTE monthly_questions conta o número total de perguntas (tópicos) criados a cada mês. A CTE solved_questions conta o número de perguntas marcadas como resolvidas a cada mês, contando o número de id da tabela user_actions com action type = 15.

A declaração SELECT principal então calcula o número de perguntas não resolvidas subtraindo o número de perguntas resolvidas do número total de perguntas. Os resultados são ordenados por mês em ordem crescente, de modo que o mês mais antigo aparecerá primeiro.

Resultados de Exemplo:

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

Explicação Detalhada com Comentários Inline

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

-- Cria uma CTE para contar o número total de perguntas (tópicos) criados a cada mês
WITH monthly_questions AS (
    SELECT
        -- Trunca o timestamp 'created_at' para o mês
        date_trunc('month', created_at)::DATE AS month,
        -- Conta o número de tópicos criados em cada mês
        COUNT(id) as total_questions
    FROM topics
    WHERE
        -- Inclui apenas tópicos criados entre as datas de início e fim
        created_at::date BETWEEN :start_date::date AND :end_date::date
    GROUP BY month
),
-- Cria uma CTE para contar o número de perguntas marcadas como resolvidas a cada mês
solved_questions AS (
    SELECT
        -- Trunca o timestamp 'created_at' para o mês
        date_trunc('month', created_at)::DATE AS month,
        -- Conta o número de perguntas resolvidas em cada mês
        COUNT(id) as solved
    FROM user_actions
    WHERE
        -- Inclui apenas ações tomadas entre as datas de início e fim
        created_at::date BETWEEN :start_date::date AND :end_date::date
        -- Considera apenas ações onde o tipo de ação é 15 (indicando uma pergunta resolvida)
        AND action_type = 15
    GROUP BY month
)

-- Seleciona o mês, o número total de perguntas, o número de perguntas resolvidas e o número de perguntas não resolvidas
SELECT
    mq.month, 
    mq.total_questions, 
    -- Se não houver perguntas resolvidas em um mês, exibe 0
    COALESCE(sq.solved, 0) as solved,
    -- Subtrai o número de perguntas resolvidas do número total de perguntas para obter o número de perguntas não resolvidas
    mq.total_questions - COALESCE(sq.solved, 0) as unsolved
FROM monthly_questions mq
-- Junta as CTEs 'monthly_questions' e 'solved_questions' pelo mês
LEFT JOIN solved_questions sq ON mq.month = sq.month
-- Ordena os resultados pelo mês em ordem crescente
ORDER BY mq.month ASC

Estatísticas de Respostas a Tópicos

Nível de Complexidade: Avançado

Esta complexa consulta SQL fornece um relatório semanal sobre a atividade de tópicos em um fórum Discourse. Ela divide os dados dos tópicos em várias métricas-chave: o número de tópicos com pelo menos uma resposta, o número de tópicos sem resposta, o número máximo de dias que um tópico ficou sem resposta e o tempo médio até a primeira resposta.

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

Aqui está um resumo de como esta consulta funciona:

• A expressão de tabela comum (CTE) posts_list seleciona uma lista de todas as postagens regulares das tabelas posts e topics, ordenadas por topic_id e post_number. Ela também atribui um número de linha (post_order) a cada postagem dentro de seu tópico.
• A CTE atleast_1_response conta o número de tópicos regulares com pelo menos uma resposta (ou seja, posts_count é maior ou igual a 2) para cada semana.
• A CTE no_response conta o número de tópicos regulares sem resposta (ou seja, posts_count igual a 1) para cada semana.
• A CTE max_days_without_response calcula o número máximo de dias que um tópico sem resposta ficou sem resposta para cada semana.
• A CTE avg_time_first_response calcula o tempo médio até a primeira resposta para cada tópico em horas, para cada semana.
• A declaração SELECT principal então junta essas CTEs pela semana e seleciona as colunas relevantes. Os resultados são ordenados por semana em ordem 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

Explicação Detalhada com Comentários Inline

-- Cria uma tabela temporária (CTE) de todas as postagens regulares, ordenadas por topic_id e post_number
WITH posts_list AS (
    SELECT 
        t.id topic_id,  -- ID do tópico
        p.post_number,  -- Número da postagem
        p.created_at,   -- Data de criação da postagem
        -- Atribui um número de linha a cada postagem dentro de seu tópico
        ROW_NUMBER () OVER (
                    PARTITION BY p.topic_id 
                    ORDER BY p.post_number)  AS post_order
    FROM posts p
    -- Junta com a tabela de tópicos, considerando apenas tópicos regulares que não foram excluídos
    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  -- Exclui postagens excluídas
        AND t.deleted_at ISNULL  -- Exclui tópicos excluídos
        AND t.archetype = 'regular'  -- Considera apenas tópicos regulares
    ORDER BY p.topic_id, p.post_number
),
-- Cria uma CTE para contar o número de tópicos regulares com pelo menos uma resposta para cada semana
atleast_1_response AS (
    SELECT 
        -- Trunca o timestamp created_at para a semana
        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 apenas tópicos regulares
        AND t.deleted_at ISNULL  -- Exclui tópicos excluídos
        AND t.posts_count >= 2  -- Considera apenas tópicos com pelo menos uma resposta
    GROUP BY "week"
),
-- Cria uma CTE para contar o número de tópicos regulares sem resposta para cada semana
no_response AS(
    SELECT 
        -- Trunca o timestamp created_at para a semana
        date_trunc('week', t.created_at::date)::date AS "week",
        COUNT(*) AS "topics without response"
    FROM topics t
    WHERE t.archetype = 'regular'  -- Considera apenas tópicos regulares
        AND t.deleted_at ISNULL  -- Exclui tópicos excluídos
        AND t.posts_count = 1  -- Considera apenas tópicos sem resposta
    GROUP BY "week"
),
-- Cria uma CTE para calcular o número máximo de dias que um tópico sem resposta ficou sem resposta para cada semana
max_days_without_response AS(
    SELECT 
        -- Trunca o timestamp created_at para a semana
        date_trunc('week', t.created_at::date)::date AS "week",
        -- Calcula o número de dias da data de criação do tópico até a data atual
        CURRENT_DATE::date-MIN(created_at)::date AS "max days without response"
    FROM topics t
    WHERE t.archetype = 'regular'  -- Considera apenas tópicos regulares
        AND t.deleted_at ISNULL  -- Exclui tópicos excluídos
        AND t.posts_count = 1  -- Considera apenas tópicos sem resposta
    GROUP BY "week"
),
-- Cria uma CTE para calcular o tempo médio até a primeira resposta para cada tópico em horas, para cada semana
avg_time_first_response AS (
    SELECT 
        -- Trunca o timestamp created_at para a semana
        date_trunc('week', pl.created_at::date)::date AS "week",
        -- Calcula o tempo médio até a primeira resposta em horas
        EXTRACT(epoch FROM AVG(pl2.created_at-pl.created_at))/3600 AS "avg time first response (h)"
    FROM posts_list pl
    -- Junta com a CTE posts_list, considerando apenas a segunda postagem em cada tópico
    INNER JOIN posts_list pl2 ON pl.topic_id = pl2.topic_id AND pl2.post_order = 2
    WHERE  pl.post_order = 1  -- Considera apenas a primeira postagem em cada tópico
    GROUP BY "week" 
)

-- Seleciona a semana, o número de tópicos sem resposta, o número máximo de dias sem resposta, o número de tópicos com pelo menos uma resposta e o tempo médio até a primeira resposta
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
-- Junta as CTEs pela semana
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
-- Ordena por semana em ordem decrescente
ORDER BY "week" DESC

Estes são apenas alguns exemplos de como você pode usar date_trunc em suas consultas do Data Explorer. Sinta-se à vontade para usar qualquer uma dessas consultas em seu site e, se tiver alguma dúvida, por favor, pergunte abaixo.