Генерирование данных в PostgreSQL

Задача: Рекурсивно поднимаем статистику по количеству аккаунтов на указанную дату.
При этом у аккаунта есть только период с-по.
Основная задача — сгенерировать даты.

Сложный способ — рекурсия:

WITH RECURSIVE stat_( inputdate ) AS (
		SELECT COUNT(*) AS total, curdate 
		FROM account 
		WHERE expired > inputdate AND inputdate = NOW() - INTERVAL '1 MONTH'
	UNION ALL
		SELECT COUNT(*) AS total, ( inputdate + INTERVAL '1 DAY' ) AS curdate
		FROM stat_, account
		WHERE expired > curdate AND curdate = NOW() + INTERVAL '1 MONTH'

)
SELECT * FROM stat_

WITH RECURSIVE stat_( inputdate ) AS (

SELECT COUNT(*) AS total, curdate

FROM account

WHERE expired > inputdate AND inputdate = NOW() - INTERVAL '1 MONTH'

UNION ALL

SELECT COUNT(*) AS total, ( inputdate + INTERVAL '1 DAY' ) AS curdate

FROM stat_, account

WHERE expired > curdate AND curdate = NOW() + INTERVAL '1 MONTH'

)

SELECT * FROM stat_

Очень простой способ — создаём виртуалную таблицу:

 SELECT 'now'::text::date + s.a AS day, account.id, count(account.id) AS count
   FROM generate_series((-120), 0) s(a)

1 2	SELECT 'now'::text::date + s.a AS day, account.id, count(account.id) AS count FROM generate_series((-120), 0) s(a)

Генерирование данных в PostgreSQL

Similar Posts

LEAVE A COMMENT Отменить ответ