Урок 12.2 · Время чтения: ~11 мин
Этот урок посвящен практическому использованию функций даты и времени в SQL для аналитики. Вы узнаете, как извлекать периоды из временных полей, агрегировать данные по дням и месяцам, считать интервалы между событиями и строить рабочие отчеты на основе временных метрик. К концу урока вы сможете уверенно анализировать динамику данных во времени на примере базы Sakila.
Практическое использование функций даты и времени для анализа данных
В предыдущем уроке мы разобрали практическую обработку строк. Теперь переходим к другому типу данных, который постоянно встречается в реальных задачах: дате и времени.
В аналитике недостаточно просто вывести payment_date или rental_date. Обычно нужно ответить на практические вопросы: как меняется активность по дням, в какие часы больше транзакций, сколько времени проходит между арендой и возвратом, есть ли сезонные пики.
Зачем в аналитике нужны функции даты и времени
Почти любой отчет имеет временное измерение. Даже если бизнес-вопрос звучит как «сколько продаж» или «сколько клиентов», на практике обычно нужно добавить контекст времени: за день, неделю, месяц, квартал или конкретный период.
Функции даты и времени помогают:
- извлекать нужную гранулярность (день, месяц, час);
- агрегировать метрики по времени;
- сравнивать периоды между собой;
- рассчитывать длительность процессов;
- находить аномальные всплески и провалы.
Базовые функции, которые чаще всего применяют
В MySQL для практической аналитики особенно полезны:
DATE()- получить только дату изDATETIME;YEAR(),MONTH(),DAY()- выделить части даты;HOUR()- анализ активности по часам;DATE_FORMAT()- формирование удобной временной метки;TIMESTAMPDIFF()- расчет интервала между двумя моментами времени;DATEDIFF()- разница в днях.
Ниже разберем рабочие сценарии на данных Sakila.
Агрегация платежей по дням
Первый практический сценарий - посмотреть, как меняется объем платежей по дням.
SELECT
DATE(payment_date) AS payment_day,
COUNT(*) AS payments_count,
SUM(amount) AS total_amount
FROM payment
GROUP BY DATE(payment_date)
ORDER BY payment_day;
Результат: вы получаете дневную динамику количества платежей и суммы выручки.
Такой отчет полезен как базовый слой для мониторинга активности и поиска резких изменений.
Сравнение месяцев через DATE_FORMAT
Когда нужна более компактная аналитика, данные агрегируют по месяцам.
SELECT
DATE_FORMAT(payment_date, '%Y-%m') AS year_month,
COUNT(*) AS payments_count,
ROUND(SUM(amount), 2) AS revenue
FROM payment
GROUP BY DATE_FORMAT(payment_date, '%Y-%m')
ORDER BY year_month;
Примечание: формат %Y-%m удобен для сортировки и визуализации в BI-инструментах.
Если требуется только номер месяца без года, лучше не терять контекст: одинаковые месяцы разных лет смешаются в одну группу.
Анализ активности по часам
Частый практический вопрос: в какие часы пользователи совершают больше действий.
SELECT
HOUR(payment_date) AS payment_hour,
COUNT(*) AS payments_count,
ROUND(SUM(amount), 2) AS total_amount
FROM payment
GROUP BY HOUR(payment_date)
ORDER BY payment_hour;
Результат: вы видите распределение активности по часам суток.
Это полезно для планирования нагрузок, расписания рассылок и настройки операционных процессов.
Расчет длительности аренды
Функции времени часто применяются для анализа жизненного цикла событий. В Sakila можно измерить, сколько часов проходит между арендой и возвратом.
SELECT
rental_id,
rental_date,
return_date,
TIMESTAMPDIFF(HOUR, rental_date, return_date) AS rental_duration_hours
FROM rental
WHERE return_date IS NOT NULL
ORDER BY rental_duration_hours DESC
LIMIT 10;
Результат: запрос показывает самые длительные завершенные аренды.
Для сводной картины лучше агрегировать длительность по среднему и медиане (если СУБД поддерживает соответствующие функции).
Практический отчет: среднее время возврата по дням недели
Теперь объединим временные функции и агрегацию в одном прикладном запросе.
SELECT
DAYOFWEEK(rental_date) AS week_day,
COUNT(*) AS rentals_count,
ROUND(AVG(TIMESTAMPDIFF(HOUR, rental_date, return_date)), 2) AS avg_return_hours
FROM rental
WHERE return_date IS NOT NULL
GROUP BY DAYOFWEEK(rental_date)
ORDER BY week_day;
Результат: вы получаете среднюю длительность аренды по дню недели.
Такой отчет помогает заметить паттерны пользовательского поведения и скорректировать операционные правила под разные дни.
Сравнение текущего и предыдущего периодов
В реальной аналитике важно не только считать метрики, но и сравнивать периоды. Даже простой вариант с двумя диапазонами уже дает полезный сигнал.
SELECT
CASE
WHEN payment_date >= '2005-07-01' AND payment_date < '2005-08-01' THEN 'period_1'
WHEN payment_date >= '2005-08-01' AND payment_date < '2005-09-01' THEN 'period_2'
END AS period_label,
COUNT(*) AS payments_count,
ROUND(SUM(amount), 2) AS revenue
FROM payment
WHERE payment_date >= '2005-07-01'
AND payment_date < '2005-09-01'
GROUP BY period_label
ORDER BY period_label;
Примечание: такой подход легко масштабируется на неделю-к-неделе, месяц-к-месяцу и квартал-к-кварталу.
Практические рекомендации
- Всегда определяйте нужную гранулярность заранее: день, неделя, месяц или час.
- Для стабильной сортировки периодов используйте формат, который сортируется лексикографически (
YYYY-MM). - Явно фильтруйте незавершенные события (
return_date IS NOT NULL) при расчете интервалов. - Проверяйте часовой пояс источника данных, если анализируете активность по часам.
- Для сравнений периодов используйте четкие границы
>=и<, чтобы избежать перекрытий.
Ключевые выводы этого урока:
- Функции даты и времени в SQL необходимы для реальной аналитики динамики и сезонности.
DATE,DATE_FORMAT,HOUR,TIMESTAMPDIFFиDATEDIFFпокрывают большинство практических задач.- Гранулярность времени напрямую влияет на смысл итоговой метрики.
- Анализ интервалов между событиями помогает измерять эффективность процессов.
- Даже простое сравнение периодов дает ценный аналитический сигнал для принятия решений.
Часто задаваемые вопросы
Почему лучше использовать диапазон >= начало и < конец, а не BETWEEN?
Потому что такой формат дает однозначные и неперекрывающиеся интервалы, особенно для DATETIME. Это снижает риск двойного учета на границах периода.
Когда применять DATE_FORMAT, а когда YEAR() и MONTH()?
DATE_FORMAT удобен для готовых отчетных ключей (например, 2025-08). YEAR() и MONTH() полезны, когда нужна отдельная логика по году и месяцу или дополнительные вычисления.
Что чаще всего ломает анализ по времени?
Типичные проблемы: смешение часовых поясов, неверная гранулярность, незавершенные записи (NULL в return_date) и нечеткие границы периода.
Вопросы для собеседования
Как вы объясните разницу между DATEDIFF() и TIMESTAMPDIFF()?
DATEDIFF() возвращает разницу в днях между датами. TIMESTAMPDIFF() позволяет выбрать единицу измерения (часы, минуты, дни и т.д.) и подходит для более точного анализа временных интервалов.
Почему важно правильно выбрать гранулярность времени в отчете?
Потому что гранулярность определяет интерпретацию результата: дневная аналитика показывает операционные колебания, месячная - тренд. Неверный уровень агрегации может скрыть важные паттерны.
Как бы вы проверили корректность отчета по времени перед публикацией?
Я бы проверил: границы периода, часовой пояс, обработку NULL, отсутствие перекрытий интервалов, и сопоставил итоговые суммы с контрольной выборкой по сырым данным.
В следующем уроке мы перейдем к техникам трансформации данных для анализа и посмотрим, как комбинировать временные и условные вычисления в одном запросе.