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.
