0Pricing
Coding Interview Prep · Урок

Округление дат и распределение по интервалам

Группировка по неделям, месяцам и кварталам с помощью DATE_TRUNC и аналогов

«Округление дат и распределение по интервалам» — бесплатный урок Coding Interview Prep на CoddyKit. Это урок 2 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения Coding Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс Coding Interview Prep содержит 4 уроков всего.

Почему спрашивают о группировке дат

«Покажите выручку по неделям» или «активных пользователей по месяцам» — основа собеседований на должность аналитика. Проверяемый навык — свести точные временные метки к более крупному периоду, чтобы строки объединялись в группы.

Ошибка начинающих — извлекать только номер месяца, из-за чего один и тот же месяц разных лет объединяется. Профессиональный ответ — усечение: сопоставить каждую временную метку с началом ее периода.

  • Периоды по неделям, месяцам, кварталам и годам
  • DATE_TRUNC и эквиваленты в разных диалектах
  • Корректная группировка, чтобы графики правильно выстраивались

DATE_TRUNC: основной инструмент

В PostgreSQL функция DATE_TRUNC(unit, ts) обнуляет всё, что имеет более высокую точность, чем указанный период. Усечение до 'month' превращает любую временную метку марта в 2024-03-01 00:00:00.

Возвращаемое значение по-прежнему является временной меткой, поэтому оно сортируется в хронологическом порядке и идеально подходит для группировки. Это самая полезная функция работы с датами для отчетности.

SELECT DATE_TRUNC('month', TIMESTAMP '2024-03-17 14:30:00');
-- 2024-03-01 00:00:00

Группировка выручки по месяцам

Классический разобранный пример. Усеките временную метку до месяца, затем сгруппируйте строки и просуммируйте значения. Поскольку период содержит год, январь 2023 года и январь 2024 года останутся раздельными.

Сортировка по усеченному значению дает чистый временной ряд, готовый для построения графика.

SELECT
  DATE_TRUNC('month', order_ts) AS month,
  SUM(amount)                   AS revenue
FROM orders
GROUP BY 1
ORDER BY 1;

EXTRACT и DATE_TRUNC

Интервьюеры напрямую проверяют, понимаете ли Вы это различие. Обе функции извлекают сведения о периоде, но отвечают на разные вопросы.

  • EXTRACT(MONTH FROM ts) возвращает число 3 для любого марта во всех годах — это полезно для анализа сезонности.
  • DATE_TRUNC('month', ts) возвращает начало конкретного месяца, сохраняя различие между годами, — это полезно для временных рядов.

Если сгруппировать данные по EXTRACT(MONTH ...) для графика месячного тренда, годы незаметно смешаются.

-- Seasonality: which month is busiest on average?
SELECT EXTRACT(MONTH FROM order_ts) AS month_num, COUNT(*)
FROM orders GROUP BY 1 ORDER BY 1;

-- Time series: month-by-month trend (years kept separate)
SELECT DATE_TRUNC('month', order_ts) AS month, COUNT(*)
FROM orders GROUP BY 1 ORDER BY 1;

Недельные периоды и вопрос о понедельнике

Недельная группировка скрывает тонкость, которую любят проверять интервьюеры: когда начинается неделя? В PostgreSQL функция DATE_TRUNC('week', ts) всегда сдвигает значение к понедельнику (недели ISO).

Если бизнесу нужны недели, начинающиеся в воскресенье, необходимо выполнить смещение. Распространенный прием — сдвинуть дату на день назад, усечь ее, а затем сдвинуть вперед.

-- ISO week (Monday start)
SELECT DATE_TRUNC('week', order_ts) AS iso_week FROM orders;

-- Sunday-start week
SELECT DATE_TRUNC('week', order_ts + INTERVAL '1 day') - INTERVAL '1 day'
  AS sunday_week
FROM orders;

Периоды по кварталам

Отчетность по кварталам распространена на должностях, связанных с финансами. DATE_TRUNC('quarter', ts) сопоставляет любую временную метку с первым днем ее квартала: 1 января, 1 апреля, 1 июля или 1 октября.

Чтобы вместо этого обозначить квартал числом, объедините EXTRACT(QUARTER ...) с годом.

SELECT
  DATE_TRUNC('quarter', order_ts)                  AS quarter_start,
  EXTRACT(YEAR FROM order_ts) || '-Q'
    || EXTRACT(QUARTER FROM order_ts)              AS quarter_label,
  SUM(amount)                                      AS revenue
FROM orders
GROUP BY 1, 2
ORDER BY 1;

В MySQL нет DATE_TRUNC

Популярный вопрос о различиях между диалектами: «В MySQL нет DATE_TRUNC — как сгруппировать данные по месяцам?» Универсальный ответ — отформатировать дату с нужной детализацией.

  • DATE_FORMAT(ts, '%Y-%m-01') возвращает начало месяца в виде текста или даты.
  • DATE_FORMAT(ts, '%Y-%m') возвращает сортируемый строковый ключ, например 2024-03.

Для недель в MySQL есть YEARWEEK() с аргументом режима, задающим начало недели.

-- MySQL month bucket
SELECT DATE_FORMAT(order_ts, '%Y-%m-01') AS month, SUM(amount)
FROM orders
GROUP BY 1
ORDER BY 1;

Группировка в SQL Server

Исторически в SQL Server не было прямой функции усечения, поэтому кандидаты использовали DATEFROMPARTS или идиому DATEADD/DATEDIFF. В современных версиях (2022 и новее) появилась функция DATETRUNC.

Классический прием «посчитать единицы от начала эпохи, а затем прибавить их обратно» работает в любой версии, поэтому его стоит знать.

-- Portable SQL Server month truncation
SELECT DATEADD(month, DATEDIFF(month, 0, order_ts), 0) AS month_start
FROM orders;

-- SQL Server 2022+
SELECT DATETRUNC(month, order_ts) AS month_start FROM orders;

Заполнение пропусков во временном ряду

Одно только усечение отбрасывает периоды с нулевым количеством строк: месяц, в котором нет заказов, просто не появится. Интервьюеры проверяют, заметите ли Вы это.

Решение — создать полный календарный ряд периодов и присоединить к нему данные с помощью LEFT JOIN. В PostgreSQL функцию generate_series можно использовать для построения такого ряда.

SELECT
  cal.month,
  COALESCE(SUM(o.amount), 0) AS revenue
FROM generate_series(DATE '2024-01-01', DATE '2024-12-01',
                      INTERVAL '1 month') AS cal(month)
LEFT JOIN orders o
  ON DATE_TRUNC('month', o.order_ts) = cal.month
GROUP BY cal.month
ORDER BY cal.month;

Более глубокий пример: активные пользователи по неделям

Сочетайте группировку по периодам с подсчетом уникальных значений. «Активные пользователи за неделю» означает подсчет уникальных пользователей в каждом недельном периоде — это реальная задача продуктовой аналитики.

Усеките временную метку события до недели, затем примените COUNT(DISTINCT user_id). Упоминание о том, что для отображения недель без активности Вы присоединили бы календарный ряд недель, принесет дополнительные баллы.

SELECT
  DATE_TRUNC('week', event_ts) AS week,
  COUNT(DISTINCT user_id)      AS wau
FROM events
GROUP BY 1
ORDER BY 1;

Группировка по индексированному столбцу

Стоит упомянуть одну оговорку по производительности: применение DATE_TRUNC к столбцу даты внутри условия WHERE может помешать планировщику использовать индекс этого столбца.

В GROUP BY это нормально, но при фильтрации лучше сравнивать исходный столбец с вычисленными границами. Этот шаблон полуоткрытого интервала мы разбирали ранее; здесь он тоже применим.

-- Avoid in WHERE: DATE_TRUNC('month', order_ts) = '2024-03-01'
-- Prefer:
SELECT * FROM orders
WHERE order_ts >= DATE '2024-03-01'
  AND order_ts <  DATE '2024-04-01';

Быстрая проверка

Выберите подходящий инструмент для графика месячного тренда, в котором годы остаются раздельными.

Повторение: усечение и группировка дат

Что следует запомнить:

  • DATE_TRUNC(unit, ts) сопоставляет временные метки с началом периода и сохраняет годы раздельными — это правильный инструмент для временных рядов.
  • EXTRACT возвращает одно число: оно хорошо подходит для анализа сезонности, но объединяет годы.
  • В PostgreSQL недели начинаются с понедельника; при необходимости воскресенья выполните смещение.
  • В MySQL используется DATE_FORMAT; в старых версиях SQL Server — идиома DATEADD(DATEDIFF(...)); в версиях 2022 и новее есть DATETRUNC.
  • Используйте сгенерированный календарный ряд + LEFT JOIN, чтобы показывать пустые периоды, и не применяйте DATE_TRUNC в WHERE, чтобы сохранить возможность использования индекса.

Часто задаваемые вопросы

Урок «Округление дат и распределение по интервалам» бесплатный?

Да — полный текст урока «Округление дат и распределение по интервалам» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс Coding Interview Prep, подпишись на CoddyKit PRO. Курс Coding Interview Prep содержит 4 уроков всего.

Чему я научусь в уроке «Округление дат и распределение по интервалам»?

Группировка по неделям, месяцам и кварталам с помощью DATE_TRUNC и аналогов Ты практикуешь Coding Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать Coding Interview Prep?

Предыдущий опыт не требуется. Coding Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 2 из 4.

Сколько времени занимает урок «Округление дат и распределение по интервалам»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке Coding Interview Prep?

Да. Каждый урок Coding Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. Арифметика дат и интервалы
  2. Округление дат и распределение по интервалам
  3. Разбор и форматирование строк
  4. Часовые пояса и временные метки
← Назад к Coding Interview Prep