Генерация последовательности дат и generate_series в PostgreSQL

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

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

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

select gs::date
from generate_series('2018-01-31', '2018-05-31', interval '1 month') as gs;

Результат оказался столь же неожиданным, как и логичным. Функция generate_series по честному итерационно сгенерировала последовательность дат по принципу последовательного прибавления сдвига к предыдущему значению. При этом на каждом шаге проверялась корректность и правка полученной даты. 31 февраля не бывает, поэтому дата преобразовалась в 28 февраля и дальнейшее прибавление месяца сбила всю последовательность на 28 число.

UPD. Пояснения после вопросов в комментариях. Вообще изначальная задача стоит шире — группировать данные на произвольные дни месяца. Например, сгруппировать по 20-м числам каждого месяца, по 15-м числам, но с такими датами проблем при генерации не наблюдается.

Интересно как поведет себя операция сложения с несколькими месяцами сразу? Что будет если мы будем прибавлять интервал не итерационно, а "оптом"?

select '2018-01-31'::date +interval '1 mons'
28.02.2018

select '2018-01-31'::date +interval '2 mons'
31.03.2018

В этом случае прибавление производится по честному.
Как применяя этот подход сгенерировать нужные даты?

Если известно количество месяцев, то очень просто:

select '2018-01-31'::date +make_interval(0, i) as gs
from generate_series(0, 4, 1) as i

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

Следующий код представляет собой в некоторой степени макетную плату и не претендует на изящность, первые варианты запросов мы в компании пишем с упором на гибкость и взаимозаменяемость блоков

/* Вводим что-то типа переменных, чтобы в едином месте можно было вводить входные данные, когда нет возможности использовать параметры */
with dates as (
    select '2018-01-31'::date as dt1, '2018-05-31'::date as dt2 
),
/* Вычисляем разницу между датами в "иерархических" единицах */
g_age as (
    select age( (select dt2 from dates), (select dt1 from dates))
),
/* Считаем сколько месяцев в полученной разнице (годы*12 + месяцы) и добавляем +1 месяц на возможную потерю при округлении  */
months as (
    select (extract(year from (select * from g_age))*12 + 
      extract(month from (select * from g_age))+1)::integer
),
/* Количество посчитано, генерируем последовательность и добавляем проверку на выход из первоначального диапазона из-за возможного лишнего месяца, который мы добавили как корректировку округления */

seq as(
  select ((select dt1 from dates) + make_interval(0, gs)) as gs
  from  generate_series (
      0,
      (select * from months),
      1
  ) as gs 
  where ((select dt1 from dates) + make_interval(0, gs)) <= (select dt2 from dates)
)
/* Ну и собственно смотрим что у нас получилось */
select * from seq

Решение получилось достаточно громозким, но рабочим и его достаточно просто интегрировать в другие запросы через механизм with.
Отчет мы реализовали, однако мысль что этот запрос мало того, что громоздкий, так еще и ограничен в своем использовании только шагами по целым месяцам не давал покоя.

Вариант 2.
Спустя время меня осенило, что последовательная генерация дат по сути рекурсивная процедура. Только не в чистом виде, так как в нашем случае расчет следующей даты от предыдущей приводит к первоначальной проблеме. Зато на каждом шаге мы можем увеличивать интервал, прибавляемый к началу нашего периода:

/*  Снова определяем наши псевдопараметры-псевдопеременные, расширив их тип до timestamp */
with recursive dates as (
    select 
           '2018-01-31'::timestamp as dt1, 
           '2018-05-31'::timestamp as dt2,
           interval '1 month' as interval
),
/* Реализуем рекурсивный запрос в котором на каждом шаге увеливается целочисленный счетчик, а каждая следующая дата получается из первоначальной путем прибавления интервала, умноженного на счетчик. Останавливается генерация, когда вновь полученная дата выходит за границу периода*/
pr AS(
    select
        1 as i,
        (select dt1 from dates) as dt
    union

    select 
        i+1 as i, 
        ( (select dt1 from dates) + ( select interval from dates)*i)::timestamp as dt
    from pr
    where ( ((select dt1 from dates) + (select interval from dates)*i)::timestamp) <=(select dt2 from dates)
)

select dt as gs from pr;

Данный запрос корректно работает с любыми входными временными отрезками и интервалами.

Автор: TemaAE

Источник