PostgreSQL: медленно работает with recursive

Question

Всем привет. Таблица имеет такой вид

Подписка	Начало подписки	Конец подписки
Один	01-01-2021	15-03-2021
Два	07-02-2021	07-05-2021
Три	30-03-2021	15-09-2021

мне нужен запрос, который выводит количество подписок, которые "зацепляют" месяц хоть на один день. Условно:

Месяц	Количество подписок
Январь 2021	1
Февраль 2021	2
Март 2021	3
Апрель 2021	2
Май 2021	2
Июнь 2021	1
Ибль 2021	1
Август 2021	1
Сентябрь 2021	1

я этого добился с помощью WITH RECURSIVE, по сути каждую итерацию брал границы нового месяца и выбирал подписки WHERE 'Начало подписки' < 'Конца месяца' AND 'Конец подписки' > 'Начало месяца'. Все это как я сказал работает, но работает медленно, вплоть до того, что мне этот запрос на 20к подписок и периода на 4 года (брал по месяцу) пришлось разделить на 3 части по полтора года а потом склеить результаты UNION'ом. Может кто знает другой подход, пошустрее?

Answer 1

Рекурсия, как сказал выше @Akina, в Вашей задаче не очень нужна. Но можно рассмотреть пример с рекурсией. Думаю, он будет побыстрее Вашего текущего варианта.

with recursive r1 as(
  select 1 l
     ,date_trunc('month',"Начало подписки") mm
     ,"Подписка","Начало подписки","Конец подписки"
  from test
  union all
  select l+1 l
     ,date_trunc('month',mm+ interval '1 month') mm
     ,"Подписка","Начало подписки","Конец подписки"
  from r1
  where date_trunc('month',mm+ interval '1 month')<="Конец подписки"
  and l<10
)
select * from r1

Результат рекурсивного запроса далее можно группировать и получить нужный результат. Надо ли дополнительно сделать JOIN с generate_series для полного периода - смотрите сами.

l	mm	Подписка	Начало подписки	Конец подписки
1	2021-01-01 00:00:00+00	Один	2021-01-01	2021-03-15
2	2021-02-01 00:00:00+00	Один	2021-01-01	2021-03-15
3	2021-03-01 00:00:00+00	Один	2021-01-01	2021-03-15
1	2021-02-01 00:00:00+00	Два	2021-02-07	2021-05-07
2	2021-03-01 00:00:00+00	Два	2021-02-07	2021-05-07
3	2021-04-01 00:00:00+01	Два	2021-02-07	2021-05-07
4	2021-05-01 00:00:00+01	Два	2021-02-07	2021-05-07
1	2021-03-01 00:00:00+00	Три	2021-03-30	2021-09-15
2	2021-04-01 00:00:00+01	Три	2021-03-30	2021-09-15
3	2021-05-01 00:00:00+01	Три	2021-03-30	2021-09-15
4	2021-06-01 00:00:00+01	Три	2021-03-30	2021-09-15
5	2021-07-01 00:00:00+01	Три	2021-03-30	2021-09-15
6	2021-08-01 00:00:00+01	Три	2021-03-30	2021-09-15
7	2021-09-01 00:00:00+01	Три	2021-03-30	2021-09-15

Группировку нужно делать по расчетному полю mm

select mm,count(*) cnt
from r1
group by mm
order by mm;

Результат

mm	cnt
2021-01-01 00:00:00+00	1
2021-02-01 00:00:00+00	2
2021-03-01 00:00:00+00	3
2021-04-01 00:00:00+01	2
2021-05-01 00:00:00+01	2
2021-06-01 00:00:00+01	1
2021-07-01 00:00:00+01	1
2021-08-01 00:00:00+01	1
2021-09-01 00:00:00+01	1

Upd1. Условие and l<10 в запросе - только для отладки, ограничить рекурсию при ошибке в запросе.

БЛОГ НА HUSL

PostgreSQL: медленно работает with recursive

Ответы (1 шт):