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

    Велопредупреждение

    Данная статья может оказаться сферическим примером велосипедостроения. Если вам известно стандартное или более изящное решение задачи, то буду рад увидеть его в комментариях.


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


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


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


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

    gs
    31.01.2018
    28.02.2018
    28.03.2018
    28.04.2018
    28.05.2018

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


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


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


    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

    gs
    31.01.2018
    28.02.2018
    31.03.2018
    30.04.2018
    31.05.2018

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


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


    /* Вводим что-то типа переменных, чтобы в едином месте можно было вводить входные данные, когда нет возможности использовать параметры */
    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

    gs
    31.01.2018
    28.02.2018
    31.03.2018
    30.04.2018
    31.05.2018

    Решение получилось достаточно громозким, но рабочим и его достаточно просто интегрировать в другие запросы через механизм 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;

    gs
    31.01.2018
    28.02.2018
    31.03.2018
    30.04.2018
    31.05.2018

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

    Поделиться публикацией

    Комментарии 13

      +1
      Вопрос: а почему бы не найти конец календарного месяца банальным вычитанием одного дня из первого дня следующего месяца?
      select gs - interval '1 day'
      from generate_series('2018-02-01', '2018-06-01', interval '1 month') as gs;


      Или, для удобства построения параметров,
      select gs + interval '1 month -1 day'
      from generate_series('2018-01-01', '2018-12-01', interval '1 month') as gs;
        0

        Да, после первого абзаца статьи мне тоже пришла такая же мысль

          0
          Да, в самом ПО для нахождения последних дней месяцев мы эти трюки с вычитаниями повсеместно употребляем.

          Ваше решение хорошее и лаконичное, а голова в его сторону не думала, потому что задача изначально ставилась группировкой не на конец месяца, а по определенным числам. Например, если займ выдан 20 числа, то итоги по платежам подбивать на 20-е числа. Если выдан 31-го, то итоги подбивать на 31-е числа. И вот тут-то обнаружилась проблема.

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

                Под универсальным механизмом я подразумевал генерацию timestamp с любым шагом, хоть по часам, хоть по 1 месяц 2 дня. Именно так работает итоговый запрос.


                Что касается вашего алгоритма генерации последних дней, то это тот же трюк — как задачу последних дней месяцев, свести к первым дням следующих месяцев и шажком назад. Неважно, в конце мы секунду или день отнимем, главное через границу перейти.


                Если пользоваться именно таким подходом для частной задачи, то думаю, все же удобнее воспользоваться generate_series, установив в параметры первые числа месяцев, как привели в примере выше. Вы предлагаете по честному с with recursive решить ту же задачу с тем же трюком, но руками сгенерировать первые числа, вместо вызова одной функции — это длиннее и неочевиднее.

                  +1

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


                  select ('2018-01-28 0:00:00'::timestamp + interval '1 day')::date + interval '1 mon' - interval '1 sec'
                  
                  27.02.2018 23:59:59
                    0
                    Извините, но сначала вы говорили про календарный месяц(последний день месяца), а потом, про шаг от 28 до 28 дня. Это несколько разные задачи. Тогда решаемая задача не до конца очевидна.
                      0
                      Вы правы — мой недочет, что я начал описывать уже частный случай задачи, потому что именно он оказался проблемным в плане результата и собственно над этой частной проблемой внутри общей и работал.

                      После первых комментариев, я внес описание общей задачи в текст, чтобы по возможности остальные читатели в верном контексте восприняли текст.
              0
              Читал и не понимал «что я упускаю, почему нельзя сделать так-же с вычитанием». Я бы именно так и делал.
              +1
              Другого варианта на чистом sql, кроме как рекурсивно прокрутить цикл, сходу в голову не пришло, но Ваше решение можно сделать несколько более изящным:
              with recursive params as (
                  select date('2018-01-31') date1, date('2018-05-31') date2, "interval"('1 month')
              ),
              cycle as (
                  select date1 result, 1 step from params
                  union all
                  select date, step + 1 from params, cycle, date(date1 + interval * step)
                  where date <= date2
              )
              select result from cycle
              
                0
                Ваш пример более изящный и чистый, но сначала ломает голову своим хитрым ходом в избавлении от избыточного кода при «вызове параметров» через секцию from второго запроса в рекурсии. Обычно, после декартова произведения следует нещадная чистка результатов от лишних строк, чего тут нет — но это ровно до того момента, пока не осенит, что умножение с однострочными таблицами.

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

                Но это я про общий случай — в данном запросе вы ловко избавились от мусора.
                  0
                  Это лишь вопрос синтаксиса запроса, по использованию join'ов я с Вами согласен. Join тут не был использован исключительно из соображений компактности. Для конкретно этого случая второй запрос можно заменить на эквивалентный вариант с использованием явных join'ов:
                  select date, step + 1
                  from params
                  cross join cycle
                  join date(date1 + interval * step) on date <= date2
                  

              Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

              Самое читаемое