Использование DATE_TRUNC для агрегации данных

Создание сообщества Данные и отчетность
1

Функция date_trunc — это мощный инструмент в SQL. Она позволяет обрезать значение TIMESTAMP или INTERVAL до указанного компонента даты, что делает её незаменимой при агрегации или группировке данных по определённому временному интервалу.

Синтаксис

Синтаксис функции date_trunc выглядит следующим образом:

date_trunc('date_part', field)
  • date_part: строка, указывающая компонент даты или временной метки, до которого нужно обрезать значение. Возможные значения:
    • millennium
    • century
    • decade
    • year
    • quarter
    • month
    • week
    • day
    • hour
    • minute
    • second
    • milliseconds
    • microseconds
  • field: временная метка или интервал, который нужно обрезать.

Примеры использования в запросах Data Explorer

Рассмотрим несколько примеров запросов, использующих date_trunc:

Количество новых тем по месяцам

Уровень сложности: Начинающий

Этот SQL-запрос используется для подсчёта количества тем, созданных в каждом месяце в базе данных Discourse.

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

В этом запросе выражение date_trunc('month', created_at)::DATE обрезает временную метку created_at до начала месяца, а затем приводит её к типу DATE для отображения в виде даты. Это позволяет сгруппировать темы по месяцам их создания.

Функция count(id) подсчитывает количество тем, созданных в каждом месяце. Результаты сортируются по месяцам в порядке убывания, поэтому самый последний месяц будет первым.

Пример результатов:

month count
2023-09-01 1
2023-08-01 6
2023-07-01 10
Подробное объяснение с комментариями внутри кода 
-- Выбираем месяц создания темы и количество тем
SELECT 
    -- Обрезаем временную метку 'created_at' до месяца и приводим к типу DATE
    -- Это группирует темы по месяцам их создания
    date_trunc('month', created_at)::DATE AS month,
    -- Подсчитываем количество тем, созданных в каждом месяце
    count(id)
-- Из таблицы 'topics'
FROM topics
-- Группируем результаты по месяцу
GROUP BY month
-- Сортируем результаты по месяцу в порядке убывания
-- Это означает, что самый последний месяц будет первым
ORDER BY month DESC

Кумулятивное общее количество пользователей

Уровень сложности: Средний

Этот запрос предоставляет еженедельный отчёт о регистрации пользователей на форуме Discourse, а также накопительный итог числа пользователей. Он использует подзапрос WITH для создания временного набора результатов (daily_signups), а затем выбирает данные из этого набора.

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

Вот подробное объяснение работы этого запроса:

  • Подзапрос WITH создаёт временный набор результатов с именем daily_signups. Этот набор содержит количество регистраций пользователей за каждую неделю между датами начала и конца, указанными параметрами :start_date и :end_date.
  • Внутри набора результатов daily_signups выражение date_trunc('week', u.created_at)::date обрезает временную метку created_at до начала недели, а затем приводит её к типу DATE. Это позволяет сгруппировать пользователей по неделям их регистрации.
  • Count(id) подсчитывает количество пользователей, зарегистрировавшихся в каждую неделю.
  • В основном операторе SELECT выражение SUM(Signups) OVER (ORDER BY Date) вычисляет накопительный итог числа пользователей. Клауза OVER (ORDER BY Date) указывает, что сумма должна вычисляться по строкам, отсортированным по дате, что даёт накопительную сумму регистраций на каждую дату.
  • Результаты сортируются по дате в порядке возрастания.

Пример результатов:

date signups total_users
2013-01-28 20 20.0
2013-02-04 2136 2156.0
2013-02-11 442 2598.0
Подробное объяснение с комментариями внутри кода 
-- Определяем параметры для дат начала и конца
-- [params]
-- date :start_date = 2000-01-01
-- date :end_date

-- Создаём общее табличное выражение (CTE) для подсчёта количества регистраций пользователей каждую неделю
WITH daily_signups AS(
SELECT
    -- Обрезаем временную метку 'created_at' до недели и форматируем её как строку даты
    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

Количество решённых и нерешённых вопросов по месяцам

Уровень сложности: Средний / Требуется плагин Discourse Solved

Этот запрос предоставляет ежемесячный отчёт о количестве решённых и нерешённых вопросов на форуме Discourse. Предполагается, что все темы на сайте могут быть помечены как решённые.

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

В этом запросе CTE monthly_questions подсчитывает общее количество вопросов (тем), созданных каждый месяц. CTE solved_questions подсчитывает количество вопросов, помеченных как решённые каждый месяц, путём подсчёта id из таблицы user_actions с action_type = 15.

Основной оператор SELECT затем вычисляет количество нерешённых вопросов, вычитая количество решённых вопросов из общего количества вопросов. Результаты сортируются по месяцам в порядке возрастания, поэтому самый ранний месяц будет первым.

Пример результатов:

month total_questions solved unsolved
2023-07-01 10 3 7
2023-08-01 6 0 6
2023-09-01 1 1 0
Подробное объяснение с комментариями внутри кода 
-- [params]
-- date :start_date = 2000-01-01
-- date :end_date

-- Создаём CTE для подсчёта общего количества вопросов (тем), созданных каждый месяц
WITH monthly_questions AS (
    SELECT
        -- Обрезаем временную метку 'created_at' до месяца
        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
),
-- Создаём CTE для подсчёта количества вопросов, помеченных как решённые, каждый месяц
solved_questions AS (
    SELECT
        -- Обрезаем временную метку 'created_at' до месяца
        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
        -- Учитываем только действия с типом 15 (что означает решённый вопрос)
        AND action_type = 15
    GROUP BY month
)

-- Выбираем месяц, общее количество вопросов, количество решённых и нерешённых вопросов
SELECT
    mq.month, 
    mq.total_questions, 
    -- Если в месяце нет решённых вопросов, отображаем 0
    COALESCE(sq.solved, 0) as solved,
    -- Вычитаем количество решённых вопросов из общего количества, чтобы получить количество нерешённых
    mq.total_questions - COALESCE(sq.solved, 0) as unsolved
FROM monthly_questions mq
-- Присоединяем CTE 'monthly_questions' и 'solved_questions' по месяцу
LEFT JOIN solved_questions sq ON mq.month = sq.month
-- Сортируем результаты по месяцу в порядке возрастания
ORDER BY mq.month ASC

Статистика ответов на темы

Уровень сложности: Продвинутый

Этот сложный SQL-запрос предоставляет еженедельный отчёт о активности тем на форуме Discourse. Он разбивает данные по темам на несколько ключевых метрик: количество тем с хотя бы одним ответом, количество тем без ответа, максимальное количество дней, прошедших без ответа по теме, и среднее время до первого ответа.

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

Вот краткое описание работы этого запроса:

  • CTE posts_list выбирает список всех обычных постов из таблиц posts и topics, отсортированный по topic_id и post_number. Также каждой записи внутри темы присваивается номер строки (post_order).
  • CTE atleast_1_response подсчитывает количество обычных тем с хотя бы одним ответом (т. е. posts_count больше или равно 2) для каждой недели.
  • CTE no_response подсчитывает количество обычных тем без ответа (т. е. posts_count равно 1) для каждой недели.
  • CTE max_days_without_response вычисляет максимальное количество дней, прошедших без ответа по теме без ответа, для каждой недели.
  • CTE avg_time_first_response вычисляет среднее время до первого ответа для каждой темы в часах для каждой недели.
  • Основной оператор SELECT объединяет эти CTE по неделе и выбирает соответствующие столбцы. Результаты сортируются по неделе в порядке убывания.
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
Подробное объяснение с комментариями внутри кода 
-- Создаём временную таблицу (CTE) всех обычных постов, отсортированную по topic_id и post_number
WITH posts_list AS (
    SELECT 
        t.id topic_id,  -- 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
    -- Присоединяем таблицу topics, учитывая только обычные темы, которые не удалены
    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
),
-- Создаём CTE для подсчёта количества обычных тем с хотя бы одним ответом для каждой недели
atleast_1_response AS (
    SELECT 
        -- Обрезаем временную метку created_at до недели
        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"
),
-- Создаём CTE для подсчёта количества обычных тем без ответа для каждой недели
no_response AS(
    SELECT 
        -- Обрезаем временную метку created_at до недели
        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"
),
-- Создаём CTE для вычисления максимального количества дней, прошедших без ответа по теме без ответа, для каждой недели
max_days_without_response AS(
    SELECT 
        -- Обрезаем временную метку created_at до недели
        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"
),
-- Создаём CTE для вычисления среднего времени до первого ответа для каждой темы в часах, для каждой недели
avg_time_first_response AS (
    SELECT 
        -- Обрезаем временную метку created_at до недели
        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
    -- Присоединяем CTE posts_list, учитывая только второй пост в каждой теме
    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
-- Присоединяем CTE по неделе
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

Это лишь несколько примеров использования date_trunc в ваших запросах Data Explorer. Не стесняйтесь использовать эти запросы на своём сайте, и если у вас возникнут вопросы, задавайте их ниже. :slight_smile:

5 лайков
Retrospectively pulling number of users each calendar month