📅 Создаем календарную таблицу с помощью SQL

Часто бывает, что в работе с базами данных нужен список всех дат за какой-то период. Например, для отчетов, анализа данных или просто, чтобы посмотреть, сколько было дней без продаж. Конечно, можно заморочиться и сделать мегатаблицу со всеми атрибутами (день недели, номер квартала, праздники и т. д.), но иногда нужна простая реализация — обычный список дат за текущий месяц.

В этой статье посте я покажу, как на лету создать такую табличку со всеми датами текущего месяца. Причем рассмотрим это для разных популярных баз данных: MySQL, PostgreSQL, MS SQL Server и Oracle. Главный кайф в том, что вам не придется каждый раз вручную прописывать даты – скрипт сам подстроится под текущий месяц.

Зачем вообще париться с календарной таблицей?

Прежде чем нырнуть в код, давайте быстренько разберемся, зачем это вообще нужно:

1. Заполнить пробелы: Если у вас данные только о днях, когда что-то происходило (например, были продажи), такая таблица поможет найти "пустые" дни без активности (дни с нулевыми продажами).
2. Анализ трендов: Круто для любого анализа, где нужна непрерывная временная шкала, даже если данных не очень много.
3. Упростить JOIN'ы: Можно просто присоединить свои данные к календарной таблице, чтобы легко группировать по частям даты или фильтровать по сплошным диапазонам.
4. Отчеты: Дает полный диапазон дат для отчетов, даже если по каким-то датам данных нет.

Ну что, поехали смотреть решения для каждой СУБД!

MySQL

В MySQL начиная с версии 8.0 используем обычное табличное выражение (CTE) в комбинации с рекурсивным CTE:

Как это работает:

• WITH RECURSIVE dates AS (...): Задаем рекурсивное CTE под названием dates.
• "Якорь" (начало): SELECT DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) – 1 DAY): Тут мы получаем первый день текущего месяца. CURDATE() дает текущую дату, DAYOFMONTH(CURDATE()) – номер дня в месяце (например, 15 для 15 июля), и мы просто вычитаем (день – 1) дней, чтобы попасть на 1-е число.
• "Рекурсия" (продолжение): SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM dates WHERE dt < LAST_DAY(CURDATE()): Эта часть добавляет по одному дню к предыдущей дате (dt), пока не достигнем последнего дня текущего месяца, который мы узнаем с помощью LAST_DAY(CURDATE()).

Выполнить SQL online

PostgreSQL

У PostgreSQL есть просто суперудобная функция generate_series(), которая идеально подходит для нашей задачи.

PostgreSQL песочница

Как это работает:

• GENERATE_SERIES(start, stop, step): Генерирует последовательность значений.
• DATE_TRUNC('month', CURRENT_DATE): "Обрезает" текущую дату до начала месяца, давая нам первый день.
• (DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month' – INTERVAL '1 day')::date: Вычисляем последний день текущего месяца. Для этого мы идем к началу следующего месяца (+ INTERVAL '1 month'), а затем отнимаем один день (- INTERVAL '1 day'), чтобы получить последний день текущего месяца.
• '1 day': Говорит, что каждый шаг в серии должен быть равен одному дню.
• ::date: Превращает полученную метку времени в обычную дату, чтобы вывод был чище.

🐘 Руководство по SQL для начинающих. Часть 1: создание базы данных, таблиц и установка связей между таблицами

Пошаговая инструкция по установке, настройке и наполнению базы данных PostgreSQL с помощью pgAdmin и SQL-запросов.

MS SQL Server 2022

SQL Server предлагает несколько способов генерировать последовательности. Начиная с SQL Server 2022, функция GENERATE_SERIES позволяет делать это очень прямолинейно, похоже на PostgreSQL, но она работает с числами, которые мы потом превращаем в даты. Для старых версий или если вам так удобнее, рекурсивные CTE тоже отлично подойдут.

Выполнить SQL

Как это работает:

• GENERATE_SERIES(start, stop, step): Эта функция создает последовательность чисел от start до stop с шагом step. Если step не указан, по умолчанию он равен 1.
• DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0): Считает первый день текущего месяца. Это наша "отправная точка".
• DAY(EOMONTH(GETDATE())) – 1: EOMONTH(GETDATE()) дает последний день текущего месяца. DAY() извлекает номер дня (например, 31 для 31 июля). Вычитаем 1, чтобы получить максимальное число для генерации в нашей серии (от 0 до количество_дней_в_месяце – 1). Например, если в месяце 31 день, мы хотим получить числа от 0 до 30.
• DATEADD(day, value, ...): Для каждого value, которое сгенерирует GENERATE_SERIES (это будут 0, 1, 2, ... до количество_дней_в_месяце – 1), мы добавляем это количество дней к нашей "отправной точке". Так мы и получаем каждую дату месяца.

Legacy MS SQL Server

Как это работает:

• WITH Dates AS (...): Объявляем рекурсивный CTE под названием Dates.
• "Якорь" (начало): SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS dt: Это такой стандартный прием в SQL Server, чтобы получить первый день текущего месяца.DATEDIFF(month, 0, GETDATE()): Считает количество "границ месяцев", пересеченных между 0 (который SQL Server понимает как 1 января 1900 года) и GETDATE().DATEADD(month, ..., 0): Добавляет это количество месяцев к 0, и вуаля – получаем первый день текущего месяца.
• DATEDIFF(month, 0, GETDATE()): Считает количество "границ месяцев", пересеченных между 0 (который SQL Server понимает как 1 января 1900 года) и GETDATE().
• DATEADD(month, ..., 0): Добавляет это количество месяцев к 0, и вуаля – получаем первый день текущего месяца.
• "Рекурсия" (продолжение): SELECT DATEADD(day, 1, dt) FROM Dates WHERE DATEPART(month, DATEADD(day, 1, dt)) = DATEPART(month, GETDATE()): Добавляем по одному дню к dt до тех пор, пока месяц следующей даты остается текущим.
• OPTION (MAXRECURSION 366): Важная штука для рекурсивных CTE в SQL Server. Устанавливает максимальное количество раз, сколько может выполниться рекурсивная часть. 366 — это безопасное число, чтобы покрыть все возможные дни в году (включая високосные).

💻 Библиотека программиста

Больше полезных материалов вы найдете на нашем телеграм-канале «Библиотека программиста»

Oracle

В Oracle есть мощная конструкция CONNECT BY LEVEL, которую часто используют для генерации последовательностей.

Заключение

Как видите, хоть синтаксис и отличается в разных базах данных, основная идея генерации ряда дат остается похожей. А с появлением GENERATE_SERIES в SQL Server 2022, современные версии SQL все больше упрощают эту задачу. Понимание этих приемов — суперважно для того, чтобы эффективно работать с данными и делать крутые отчеты в SQL. Выбирайте тот метод, который лучше подходит для вашей конкретной СУБД и её версии.

Надеюсь, было полезно! Если есть вопросы или другие прикольные способы, пишите в комментах!