Sample Header Ad - 728x90

Create a group number for rolling month

0 votes
0 answers
67 views
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.
Asked by Bart Jonk (357 rep)
Feb 3, 2023, 02:13 PM
Last activity: Feb 4, 2023, 01:44 PM