Как в Postgresql получить данные с разбивкой по месяцам?

Question

[ChygyN]

Всем привет
Есть таблица с данными о подписках, у записей есть поле "подписка от" и "подписка до", написал запрос, который выдает подписки на текущий месяц, причем по таким условиям:

• подписка от <= конец текущего месяца
  
• подписка до >= начало текущего месяца
  

то есть когда период подписки "цепляет" текущий месяц (см. приложенную схему)

Теперь мне нужна выгрузка за предыдущий год так же разбивка по месяцам. Я та понял тут надо использовать WITH RECURSIVE, но пока не удается написать правильный запрос. Пытался написать что-то типа такого

WITH RECURSIVE r AS (
    -- стартовая часть рекурсии (т.н. "anchor")
    SELECT 
        1 AS i,
	current_date AS days
    
    UNION 
    
    -- рекурсивная часть 
    SELECT 
        i+1 AS i, 
        current_date - interval '1 month' * i as days
    FROM r
    WHERE i < 12
)
SELECT * FROM r;

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

ERROR: recursive query "r" column 2 has type date in non-recursive term but type timestamp without time zone overall
LINE 5: current_date AS days
^
HINT: Cast the output of the non-recursive term to the correct type.

Не понимаю.
И сразу вдогонку вопросы:

1. в якорной части мы же пишем только скажем так начальные счетчики, а в рекурсивной пишем запрос, чтоб условия на основе этих счетчиков соблюдалось, или нужно там прописать первую итерацию, и последующие будут объединяться с тем, что в якорной получилось?
   
2. Можно сделать так, чтобы значения счетчиков не отображалось (не присутствовало в SELECT)?
   

Comments

[unfilled]

1. current_date имеет тип date, а current_date - interval '1 month' * i - timestamp without time zone. Нужно явно привести типы
2. можно, достаточно написать имена только нужных столбцов в финальном селекте

[ChygyN]

unfilled,
1) спасибо большое, помогло
2) а тут накладочка, или я неправильно твою мысль оформил. В моем примере, который я нагло стащил с Хабра и переделал под себя, счетчик (i) инициализируется в селекте, и чтобы объединить с рекурсивной частью в ее селекте так же должен присутствовать i

[Akina]

А зачем городить огород с рекурсией, когда есть generate_series?

[unfilled]

ChygyN, технически, "счётчик" не нужен, если вы знаете как остановиться без него. Часто встречается задача с "иерархией подчинения" - там как раз остановка без счётчика, он используется только для наглядности:

create table employees (emp_id int, manager_id int);

insert into employees 
 	select 1 emp_id, 2 manager_id
	union all select 2, 3
	union all select 3, 4
	union all select 4, null
	union all select 5, 3
	union all select 6, 4;

with recursive r as (
	select
		emp_id, manager_id, 0 as lvl
	from employees
	
	union all 
	
	select
		r.emp_id, head.manager_id, r.lvl + 1
	from r
	join employees head on r.manager_id = head.emp_id
	where head.manager_id is not null
)
select * from r
where emp_id = 1;