CREATE TYPE date_and_rn AS (
d date,
rn int
);
CREATE OR REPLACE FUNCTION reduce(previous_state date_and_rn, current_row date, max_interval interval) RETURNS date_and_rn AS
$$
SELECT CASE
-- still within the max range, we simply increment rn
WHEN ((current_row - (previous_state).d) || ' days')::interval <= max_interval THEN ((previous_state).d, (previous_state).rn + 1)::date_and_rn
-- new group, starting from the current row
ELSE (current_row, 1)::date_and_rn
END;
$$
IMMUTABLE
LANGUAGE SQL;
CREATE OR REPLACE FUNCTION finalizer(output date_and_rn) RETURNS int AS
$$
SELECT (output).rn;
$$
IMMUTABLE
STRICT
LANGUAGE SQL;
CREATE AGGREGATE not_a_good_name(date, interval)
(
SFUNC = reduce,
STYPE = date_and_rn,
FINALFUNC = finalizer
);
WITH values(x) AS (
VALUES (1), (2), (3), (5), (6)
), as_dates(d) AS (
SELECT ('2020-01-01'::date + (x || ' months')::interval)::date
FROM values
)
SELECT d, not_a_good_name(d, '3 months'::interval) OVER (ORDER BY d)
FROM as_dates
;
/* OUTPUT
2020-02-01;1
2020-03-01;2
2020-04-01;3
2020-06-01;1
2020-07-01;2
*/