Starting from a table with dates in descending order, starting from the current_date:
date
----------
2023-02-03
2023-02-02
2023-02-01
2023-01-31
...
I want to add a column that contains a group number for the rolling month:
date rolling_month
---------- -------------
2023-02-03 1
2023-02-02 1
2023-02-01 1
2023-01-31 1
...
2023-01-04 1
2023-01-03 2
2023-01-02 2
...
2023-01-04 2
2023-01-03 2
2023-01-02 3
...
2022-12-04 3
2022-12-03 3
...
Is this possible? What SQL to use? maybe a window function?
I tried this, but keep getting trouble with the number of days that can be either 28, 29, 30 or 31.
Any suggestions will be appreciated.
--- Update
Here's a similar query, that works for rolling weeks and rolling fortnights:
with dim_date as (
select date::date from generate_series(now()- interval '2 years', now() + interval '2 years', '1 day') as date
)
SELECT
date,
row_number() OVER (ORDER BY date DESC) rev_order,
floor((row_number() OVER (ORDER BY date DESC)-1) / 7) AS rolling_week,
floor((row_number() OVER (ORDER BY date DESC)-1) / 14) AS rolling_fortnight
FROM
dim_date
WHERE
date IS NOT NULL
and date <= CURRENT_DATE
ORDER BY date DESC;
and here's two of my (failing) attempts to crack it:
with dim_date as (
select date::date from generate_series(now()- interval '2 years', now() + interval '2 years', '1 day') as date
)
select
date,
rank() over (
order by date desc
range between interval '1 month' preceding and current row)
from star.dim_date
where
date is not null
and date < current_date
order by date desc;
with dim_date as (
select date::date from generate_series(now()- interval '2 years', now() + interval '2 years', '1 day') as date
)
select
date,
row_number() over (order by date desc) rev_order,
(date - interval '1 month')::date one_month_ago,
date - (date - interval '1 month') as interval,
extract (DAY from (date - (date - interval '1 month'))) as interval_days,
floor((row_number() over (order by date desc)-1) / extract (DAY from (date - (date - interval '1 month')))) as rolling_month
from
dim_date
where
date is not null
and date < current_date
order by date desc LIMIT 1000;
I was thinking that I probably should subtract the duration of the previous month unless the day considered march 30 or if the day considered is the 31st; unless there is a way to use to subtract an interval '1 month'
intelligently perjaps in combination with the range
in the window function.