前 12 个月间隔和 ISO 周的平均值

这不是你想要的吗？

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;

假设当前行是 2020 年和第 45 周，这会将 2019 年同一周的行带到当前行。

这太长了，无法发表评论。

我认为没有一种简单的方法可以做到这一点。问题在于“周”和“年”之间的差异。在12个月的时间内，大约有52.2周。所以，你要问的是，有时“12 个月”期间有 52 周，有时有 53 周。

我认为您可以根据过去 52 周进行累积计算，然后使用条件逻辑来包括前 53 周。问题是......我不知道回到 53 周的确切规则是什么。

如果唯一担心的是，在一年的第 53 周，那么应该包括全年，那么这很容易包括在内。其伪代码为：

(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)

编辑：

我不是 100% 确定这是否适用于您的用例。但我有一个想法，可以做你想做的事。也就是说，将一年中的周数列举为一年中的分数。因此，具有 52 周的年份将有一个枚举，而具有 53 周的年份将具有另一个枚举。

这看起来像：

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;

您需要对其进行测试，看看它是否符合您的真正需求。正如我在这个答案的开头所说，“12 个月前”没有明确定义 ISO 周，但这可能是一个合理的解释。