TemaAE Aug 31 2018 at 20:45

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

4 min

32K

PostgreSQL*

+10

Comments 13

Melkij Aug 31 2018 at 21:29

Вопрос: а почему бы не найти конец календарного месяца банальным вычитанием одного дня из первого дня следующего месяца?

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;

gecube Aug 31 2018 at 21:56

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

TemaAE Sep 1 2018 at 05:49

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

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

При написании статьи я описывал уже вырожденный случай задачи, хотя она изначально стоит шире.

TemaAE Sep 1 2018 at 06:01

Добавил уточнения по общей формулировке задачи в статью, спасибо

boodda Sep 2 2018 at 22:17

У вас там же таймштамп, добавляете к пришедшей дате день, обнуляете время, прибавляете месяц и отнимаете секунду. это должно быть универсальным решением

TemaAE Sep 3 2018 at 06:14

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

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

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

TemaAE Sep 3 2018 at 06:36

Если же вы имели ввиду, что ваш метод работает для любых месячных итераций дат, а не только последних дней, то он не сработает, к примеру, для генерации 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

boodda Sep 3 2018 at 08:27

Извините, но сначала вы говорили про календарный месяц(последний день месяца), а потом, про шаг от 28 до 28 дня. Это несколько разные задачи. Тогда решаемая задача не до конца очевидна.

TemaAE Sep 3 2018 at 10:38

Вы правы — мой недочет, что я начал описывать уже частный случай задачи, потому что именно он оказался проблемным в плане результата и собственно над этой частной проблемой внутри общей и работал.

После первых комментариев, я внес описание общей задачи в текст, чтобы по возможности остальные читатели в верном контексте восприняли текст.

Estagus Sep 2 2018 at 06:34

Читал и не понимал «что я упускаю, почему нельзя сделать так-же с вычитанием». Я бы именно так и делал.

eranthis Sep 8 2018 at 05:53

Другого варианта на чистом 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

TemaAE Sep 8 2018 at 06:13

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

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

Но это я про общий случай — в данном запросе вы ловко избавились от мусора.

eranthis Sep 8 2018 at 11:29

Это лишь вопрос синтаксиса запроса, по использованию join'ов я с Вами согласен. Join тут не был использован исключительно из соображений компактности. Для конкретно этого случая второй запрос можно заменить на эквивалентный вариант с использованием явных join'ов:

select date, step + 1
from params
cross join cycle
join date(date1 + interval * step) on date <= date2

Show the best of all time