Данная статья может оказаться сферическим примером велосипедостроения. Если вам известно стандартное или более изящное решение задачи, то буду рад увидеть его в комментариях.
Однажды на одном из проектов нам понадобилось составить отчет по финансовым операциям за период с группировкой промежуточных итогов на конец месяца.
Задача в общем-то простая, определить требуемые периоды внутри большого интервала, привязать каждую операцию к подходящему периоду, сгруппировать и сложить сумму.
Для генерации периодов внутри интервала я привычно взял функцию 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 число.
Интересно как поведет себя операция сложения с несколькими месяцами сразу? Что будет если мы будем прибавлять интервал не итерационно, а «оптом»?
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 |
Данный запрос корректно работает с любыми входными временными отрезками и интервалами.
ссылка на оригинал статьи https://habr.com/post/421969/
Добавить комментарий