Usando DATE_TRUNC para agregación de datos

Comunidad Datos e informes
1

La función date_trunc es una herramienta poderosa en SQL. Permite truncar un valor TIMESTAMP o INTERVAL según una parte de fecha especificada, lo que la convierte en una función invaluable cuando se desea agrupar o agregar datos basándose en un período de tiempo concreto.

Sintaxis

La sintaxis de la función date_trunc es la siguiente:

date_trunc('date_part', field)
  • date_part: Es una cadena que especifica la parte de la fecha o el timestamp al que se debe truncar. Puede ser uno de los siguientes valores:
    • millennium
    • century
    • decade
    • year
    • quarter
    • month
    • week
    • day
    • hour
    • minute
    • second
    • milliseconds
    • microseconds
  • field: Es el timestamp o intervalo que se va a truncar.

Ejemplos de uso en consultas DE

Veamos algunos ejemplos de consultas que utilizan date_trunc:

Recuento de nuevos temas por mes

Nivel de complejidad: Principiante

Esta consulta SQL se utiliza para contar el número de temas creados en cada mes en la base de datos de Discourse.

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

En esta consulta, date_trunc('month', created_at)::DATE trunca el timestamp created_at al mes y luego lo convierte a un tipo de fecha para su visualización, agrupando efectivamente los temas por el mes en que fueron creados.

La función count(id) cuenta entonces el número de temas creados en cada mes. Los resultados se ordenan por mes en orden descendente, por lo que el mes más reciente aparecerá primero.

Resultados de ejemplo:

month count
2023-09-01 1
2023-08-01 6
2023-07-01 10
Explicación detallada con comentarios en línea 
-- Seleccionar el mes en que se creó el tema y el recuento de temas
SELECT 
    -- Truncar el timestamp 'created_at' al mes y convertirlo a fecha
    -- Esto agrupa los temas por el mes en que fueron creados
    date_trunc('month', created_at)::DATE AS month,
    -- Contar el número de temas creados en cada mes
    count(id)
-- Desde la tabla 'topics'
FROM topics
-- Agrupar los resultados por mes
GROUP BY month
-- Ordenar los resultados por mes en orden descendente
-- Esto significa que el mes más reciente aparecerá primero
ORDER BY month DESC

Total acumulado de usuarios

Nivel de complejidad: Intermedio

Esta consulta proporcionará un informe semanal de los registros de usuarios en un foro de Discourse, junto con un total acumulado de usuarios. Utiliza una cláusula WITH para crear un conjunto de resultados temporal (daily_signups) y luego selecciona de ese 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

A continuación se detalla el funcionamiento de esta consulta:

  • La cláusula WITH crea un conjunto de resultados temporal llamado daily_signups. Este conjunto contiene el número de registros de usuarios para cada semana entre las fechas de inicio y fin especificadas por los parámetros :start_date y :end_date.
  • Dentro del conjunto de resultados daily_signups, date_trunc('week', u.created_at)::date trunca el timestamp created_at a la semana y luego lo convierte a fecha. Esto agrupa efectivamente a los usuarios por la semana en que se registraron.
  • Count(id) cuenta entonces el número de usuarios que se registraron en cada semana.
  • En la sentencia SELECT principal, SUM(Signups) OVER (ORDER BY Date) calcula un total acumulado de usuarios. La cláusula OVER (ORDER BY Date) especifica que la suma debe calcularse sobre las filas ordenadas por fecha, por lo que proporciona una suma acumulada de registros hasta cada fecha.
  • Los resultados se ordenan entonces por fecha en orden ascendente.

Resultados de ejemplo:

date signups total_users
2013-01-28 20 20.0
2013-02-04 2136 2156.0
2013-02-11 442 2598.0
Explicación detallada con comentarios en línea 
-- Definir parámetros para las fechas de inicio y fin
-- [params]
-- date :start_date = 2000-01-01
-- date :end_date

-- Crear una expresión de tabla común (CTE) para contar el número de registros de usuarios cada semana
WITH daily_signups AS(
SELECT
    -- Truncar el timestamp 'created_at' a la semana y formatearlo como cadena de fecha
    to_char(date_trunc('week', u.created_at)::date,'YYYY-MM-DD') AS Date,
    -- Contar el número de usuarios que se registraron
    Count (id) as Signups
FROM users u
WHERE
    -- Incluir solo usuarios que se registraron entre las fechas de inicio y fin
    u.created_at::date BETWEEN :start_date::date AND :end_date::date
GROUP BY Date
)

-- Seleccionar la fecha, el número de registros y el total acumulado de registros
SELECT
    Date, 
    Signups, 
    -- Calcular el total acumulado de registros
    SUM(Signups) OVER (ORDER BY Date) as total_users
FROM daily_signups
-- Ordenar los resultados por fecha en orden ascendente
ORDER BY Date Asc

Número de preguntas resueltas y no resueltas por mes

Nivel de complejidad: Intermedio / Requiere el plugin Discourse Solved

Esta consulta proporcionará un informe mensual del número de preguntas resueltas y no resueltas en un foro de Discourse. Esta consulta asume que todos los temas de un sitio pueden ser resueltos.

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

En esta consulta, la CTE monthly_questions cuenta el número total de preguntas (temas) creados cada mes. La CTE solved_questions cuenta el número de preguntas marcadas como resueltas cada mes contando el número de id de la tabla user_actions con action_type = 15.

La sentencia SELECT principal calcula entonces el número de preguntas no resueltas restando el número de preguntas resueltas del número total de preguntas. Los resultados se ordenan por mes en orden ascendente, por lo que el mes más antiguo aparecerá primero.

Resultados de ejemplo:

month total_questions solved unsolved
2023-07-01 10 3 7
2023-08-01 6 0 6
2023-09-01 1 1 0
Explicación detallada con comentarios en línea 
-- [params]
-- date :start_date = 2000-01-01
-- date :end_date

-- Crear una CTE para contar el número total de preguntas (temas) creados cada mes
WITH monthly_questions AS (
    SELECT
        -- Truncar el timestamp 'created_at' al mes
        date_trunc('month', created_at)::DATE AS month,
        -- Contar el número de temas creados en cada mes
        COUNT(id) as total_questions
    FROM topics
    WHERE
        -- Incluir solo temas creados entre las fechas de inicio y fin
        created_at::date BETWEEN :start_date::date AND :end_date::date
    GROUP BY month
),
-- Crear una CTE para contar el número de preguntas marcadas como resueltas cada mes
solved_questions AS (
    SELECT
        -- Truncar el timestamp 'created_at' al mes
        date_trunc('month', created_at)::DATE AS month,
        -- Contar el número de preguntas resueltas en cada mes
        COUNT(id) as solved
    FROM user_actions
    WHERE
        -- Incluir solo acciones realizadas entre las fechas de inicio y fin
        created_at::date BETWEEN :start_date::date AND :end_date::date
        -- Considerar solo acciones donde el tipo de acción es 15 (indicando una pregunta resuelta)
        AND action_type = 15
    GROUP BY month
)

-- Seleccionar el mes, el número total de preguntas, el número de preguntas resueltas y el número de preguntas no resueltas
SELECT
    mq.month, 
    mq.total_questions, 
    -- Si no hay preguntas resueltas en un mes, mostrar 0
    COALESCE(sq.solved, 0) as solved,
    -- Restar el número de preguntas resueltas del número total de preguntas para obtener el número de preguntas no resueltas
    mq.total_questions - COALESCE(sq.solved, 0) as unsolved
FROM monthly_questions mq
-- Unir las CTEs 'monthly_questions' y 'solved_questions' por el mes
LEFT JOIN solved_questions sq ON mq.month = sq.month
-- Ordenar los resultados por mes en orden ascendente
ORDER BY mq.month ASC

Estadísticas de respuestas a temas

Nivel de complejidad: Avanzado

Esta compleja consulta SQL proporciona un informe semanal sobre la actividad de los temas en un foro de Discourse. Desglosa los datos de los temas en varias métricas clave: el número de temas con al menos una respuesta, el número de temas sin respuesta, el número máximo de días que un tema ha estado sin respuesta y el tiempo promedio hasta la primera respuesta.

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

A continuación se presenta un resumen del funcionamiento de esta consulta:

  • La expresión de tabla común (CTE) posts_list selecciona una lista de todas las publicaciones regulares de las tablas posts y topics, ordenadas por topic_id y post_number. También asigna un número de fila (post_order) a cada publicación dentro de su tema.
  • La CTE atleast_1_response cuenta el número de temas regulares con al menos una respuesta (es decir, posts_count es mayor o igual a 2) para cada semana.
  • La CTE no_response cuenta el número de temas regulares sin respuesta (es decir, posts_count igual a 1) para cada semana.
  • La CTE max_days_without_response calcula el número máximo de días que un tema sin respuesta ha permanecido sin respuesta para cada semana.
  • La CTE avg_time_first_response calcula el tiempo promedio hasta la primera respuesta para cada tema en horas, para cada semana.
  • La sentencia SELECT principal une estas CTEs por la semana y selecciona las columnas relevantes. Los resultados se ordenan por semana en orden descendente.
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
Explicación detallada con comentarios en línea 
-- Crear una tabla temporal (CTE) de todas las publicaciones regulares, ordenadas por topic_id y post_number
WITH posts_list AS (
    SELECT 
        t.id topic_id,  -- ID del tema
        p.post_number,  -- Número de publicación
        p.created_at,   -- Fecha de creación de la publicación
        -- Asignar un número de fila a cada publicación dentro de su tema
        ROW_NUMBER () OVER (
                    PARTITION BY p.topic_id 
                    ORDER BY p.post_number)  AS post_order
    FROM posts p
    -- Unir con la tabla de temas, considerando solo temas regulares que no han sido eliminados
    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  -- Excluir publicaciones eliminadas
        AND t.deleted_at ISNULL  -- Excluir temas eliminados
        AND t.archetype = 'regular'  -- Considerar solo temas regulares
    ORDER BY p.topic_id, p.post_number
),
-- Crear una CTE para contar el número de temas regulares con al menos una respuesta para cada semana
atleast_1_response AS (
    SELECT 
        -- Truncar el timestamp created_at a la 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'  -- Considerar solo temas regulares
        AND t.deleted_at ISNULL  -- Excluir temas eliminados
        AND t.posts_count >= 2  -- Considerar solo temas con al menos una respuesta
    GROUP BY "week"
),
-- Crear una CTE para contar el número de temas regulares sin respuesta para cada semana
no_response AS(
    SELECT 
        -- Truncar el timestamp created_at a la semana
        date_trunc('week', t.created_at::date)::date AS "week",
        COUNT(*) AS "topics without response"
    FROM topics t
    WHERE t.archetype = 'regular'  -- Considerar solo temas regulares
        AND t.deleted_at ISNULL  -- Excluir temas eliminados
        AND t.posts_count = 1  -- Considerar solo temas sin respuesta
    GROUP BY "week"
),
-- Crear una CTE para calcular el número máximo de días que un tema sin respuesta ha permanecido sin respuesta para cada semana
max_days_without_response AS(
    SELECT 
        -- Truncar el timestamp created_at a la semana
        date_trunc('week', t.created_at::date)::date AS "week",
        -- Calcular el número de días desde la fecha de creación del tema hasta la fecha actual
        CURRENT_DATE::date-MIN(created_at)::date AS "max days without response"
    FROM topics t
    WHERE t.archetype = 'regular'  -- Considerar solo temas regulares
        AND t.deleted_at ISNULL  -- Excluir temas eliminados
        AND t.posts_count = 1  -- Considerar solo temas sin respuesta
    GROUP BY "week"
),
-- Crear una CTE para calcular el tiempo promedio hasta la primera respuesta para cada tema en horas, para cada semana
avg_time_first_response AS (
    SELECT 
        -- Truncar el timestamp created_at a la semana
        date_trunc('week', pl.created_at::date)::date AS "week",
        -- Calcular el tiempo promedio hasta la primera respuesta en horas
        EXTRACT(epoch FROM AVG(pl2.created_at-pl.created_at))/3600 AS "avg time first response (h)"
    FROM posts_list pl
    -- Unir con la CTE posts_list, considerando solo la segunda publicación de cada tema
    INNER JOIN posts_list pl2 ON pl.topic_id = pl2.topic_id AND pl2.post_order = 2
    WHERE  pl.post_order = 1  -- Considerar solo la primera publicación de cada tema
    GROUP BY "week" 
)

-- Seleccionar la semana, el número de temas sin respuesta, el número máximo de días sin respuesta, el número de temas con al menos una respuesta y el tiempo promedio hasta la primera respuesta
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
-- Unir las CTEs por la 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
-- Ordenar por semana en orden descendente
ORDER BY "week" DESC

Estos son solo algunos ejemplos de cómo puedes utilizar date_trunc en tus consultas de Data Explorer. Siéntete libre de usar cualquiera de estas consultas en tu sitio, y si tienes alguna pregunta, no dudes en plantearla a continuación. :slight_smile:

5 Me gusta
Retrospectively pulling number of users each calendar month