Как развернуть месяцы между двумя датами в SQL

При анализе данных часто требуется детализировать период между начальной и конечной датой. Например, есть таблица с интервалами и общим количеством месяцев, а нужно получить отдельную строку для каждого месяца внутри этого интервала. В статье разберём способ решения такой задачи на чистом SQL с использованием рекурсивного CTE (Common Table Expression).

Исходная задача и данные

Пусть есть таблица periods со столбцами: ID, Дата_начала, Дата_окончания, Кол-во (количество месяцев между датами). Пример данных:

ID | Дата_начала                  | Дата_окончания                | Кол-во
55 | 2024-09-17 11:52:17.000 | 2024-12-17 11:52:17.000 | 3
42 | 2024-06-10 14:52:19.000 | 2024-07-10 14:52:19.000 | 1

Требуется вывести для каждого ID все месяцы, попадающие в интервал, с сохранением исходного количества. Результат должен быть таким:

ID | Дата                                  | Кол-во
55 | 2024-09-17 11:52:17.000 | 3
55 | 2024-10-17 11:52:17.000 | 3
55 | 2024-11-17 11:52:17.000 | 3
42 | 2024-06-10 14:52:19.000 | 1

Решение с помощью рекурсивного CTE

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

Пример SQL-запроса

WITH RECURSIVE date_range AS (
    SELECT 
        ID,
        Дата_начала AS month_date,
        Дата_окончания,
        Кол-во,
        0 AS level
    FROM periods
    UNION ALL
    SELECT 
        dr.ID,
        DATEADD(MONTH, 1, dr.month_date) AS month_date,
        dr.Дата_окончания,
        dr.Кол-во,
        dr.level + 1
    FROM date_range dr
    WHERE DATEADD(MONTH, 1, dr.month_date) <= dr.Дата_окончания
)
SELECT 
    ID,
    month_date AS Дата,
    Кол-во
FROM date_range
ORDER BY ID, month_date;

В этом запросе:

• Базовый шаг - выбираем начальную дату для каждого ID.
• Рекурсивный шаг - прибавляем один месяц, пока не достигнем даты окончания.
• Фильтр - условие DATEADD(MONTH, 1, dr.month_date) <= dr.Дата_окончания останавливает рекурсию.

Альтернативный подход: таблица чисел

Если в вашей СУБД рекурсивные CTE недоступны или нежелательны, можно использовать вспомогательную таблицу чисел (Numbers table). Она содержит последовательность целых чисел от 0 до N. Тогда запрос выглядит так:

SELECT 
    p.ID,
    DATEADD(MONTH, n.num, p.Дата_начала) AS Дата,
    p.Кол-во
FROM periods p
JOIN Numbers n 
    ON n.num >= 0 
    AND n.num < DATEDIFF(MONTH, p.Дата_начала, p.Дата_окончания) + 1
ORDER BY p.ID, Дата;

Таблица чисел должна содержать достаточно значений (максимальное количество месяцев в интервале). Этот способ часто работает быстрее рекурсии на больших объёмах данных.

Особенности для разных СУБД

• Microsoft SQL Server - функция DATEADD и DATEDIFF работают как показано выше.
• PostgreSQL - используйте generate_series: generate_series(Дата_начала, Дата_окончания, INTERVAL '1 month').
• MySQL - рекурсивные CTE доступны с версии 8.0; альтернатива - переменные или таблица чисел.

Заключение

Развернуть месяцы между двумя датами в SQL можно несколькими способами: рекурсивным CTE, таблицей чисел или функцией генерации рядов. Выбор зависит от используемой СУБД и объёма данных. Предложенные запросы помогут получить детализированную таблицу с каждым месяцем интервала и сохранить исходное количество.