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

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

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

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

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

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

MySQL

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

        WITH RECURSIVE dates AS (
    SELECT
        DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY) AS dt -- Первый день текущего месяца
    UNION ALL
    SELECT
        DATE_ADD(dt, INTERVAL 1 DAY)
    FROM
        dates
    WHERE
        dt < LAST_DAY(CURDATE()) -- Последний день текущего месяца
)
SELECT
    dt AS calendar_date
FROM
    dates;
    

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

• 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(), которая идеально подходит для нашей задачи.

        SELECT
    GENERATE_SERIES(
        DATE_TRUNC('month', CURRENT_DATE), -- Первый день текущего месяца
        (DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month' - INTERVAL '1 day')::date, -- Последний день текущего месяца
        '1 day'
    )::date AS calendar_date;
    

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: Превращает полученную метку времени в обычную дату, чтобы вывод был чище.

MS SQL Server 2022

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

        SELECT
    DATEADD(day, value, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) AS calendar_date
FROM
    GENERATE_SERIES(0, DAY(EOMONTH(GETDATE())) - 1);
    

Выполнить 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 (
    SELECT
        DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS dt -- Первый день текущего месяца
    UNION ALL
    SELECT
        DATEADD(day, 1, dt)
    FROM
        Dates
    WHERE
        DATEPART(month, DATEADD(day, 1, dt)) = DATEPART(month, GETDATE())
)
SELECT
    dt AS calendar_date
FROM
    Dates
OPTION (MAXRECURSION 366); -- Ставим лимит рекурсии, 366 хватит даже для високосного года
    

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

• 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, которую часто используют для генерации последовательностей.

        SELECT
    TRUNC(SYSDATE, 'MM') + LEVEL - 1 AS calendar_date
FROM
    dual
CONNECT BY
    TRUNC(SYSDATE, 'MM') + LEVEL - 1 <= LAST_DAY(SYSDATE);
    

Заключение

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

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