Average on 12 preceding months interval and ISO Weeks

user_4020890 0
Viewed 295

I have the following query to get an average of units on the preceding 12 months in an interval but my problem is that the 12 preceding months is not taking into account ISO week 1 in the year, say this example:

SELECT
    *,
   avg(units) OVER (                                             
       ORDER BY to_date(year::text || '-' || week::text, 'IYYY-IW')     
       RANGE between interval '12 months' preceding and current row)
FROM
   rolling_year_table 
   order by year,week;

Basically ISO week 1 2020 (which is actually '2019-12-30' is not taken into account in the calculations.

Is there a way to say 12 months preceding and current row but using ISO weeks?

Thanks,

date-arithmetic window-functions datetime postgresql sql
2 Answers

Doesn't this do what you want?

select ry.*,
   avg(units) over (                                             
       order by year * 100 + week
       range between 100 preceding and current row
from rolling_year_table 
order by year,week;

Say current row is year 2020 and week 45, this will take rows from the same week in 2019 to the current row.

user_106767160

This is too long for a comment.

I don't think there is an easy way to do this. The problem is the discrepancy between "week" and "year". There are, about 52.2 weeks in a 12 month period. So, what you are asking is that sometimes the "12 month" period have 52 weeks and sometimes it has 53 weeks.

I think you could do a cumulative calculation based on the past 52 weeks and then use condition logic to include the 53rd week previous. The problem is . . . I don't know what the exact rules are for going back 53 weeks.

If the only concern is that in the 53rd week of a year then the entire year should be included, then that would be pretty easy to include. The pseudo code for that would be:

(case when isoweek = 53
      then avg() over (. . . range between '53 week' preceding and current row)
      else avg() over (. . . range between '52 week' preceding and current row)
 end)

EDIT:

I'm not 100% sure if this will work for your use-case. But I have an idea that might do what you want. That is to enumerate the weeks of the year as fractions of the year. So years with 52 weeks would have one enumeration and years with 53 weeks would have another.

This would look like:

select . . .,
       avg(units) over (order by year + (isoweek - 1) / weeks_in_year
                        range between 1 preceding and current row
                       )
from (select t.*,
             extract(isoyear from dte) as isoyear,
             extract(week from dte) as isoweek,
             greatest(extract(week from date_trunc('year', dte) + interval '1 year - 1 day'), 53) as weeks_in_year
f      from t
     ) t;

You would need to test this to see if it does what you really want. As I say at the beginning of this answer "12 months ago" is not clearly defined for ISO weeks, but this may be a reasonable interpretation.

user_11440350
Related