0

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.

4
  • Use DENSE_RANK()
    – Akina
    Feb 3 at 14:26
  • I don't understand the logic. Why is 2023-02-01 the same month as 2023-01-31? And why is 2023-01-04 a different month than 2023-01-03? Feb 3 at 14:48
  • 2023-01-31 lies in the interval (2023-02-03 - interval '1 month'), that's why it's in the same group. 2023-01-03 is the first date of the new group
    – Bart Jonk
    Feb 3 at 14:59
  • Did you try with row_number()
    – user580950
    Feb 3 at 21:17

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy