平均在12个前置月份区间和ISO周数上

Question

我有一个查询，用于在一个时间间隔内计算之前12个月的单位平均值，但我的问题是，这12个月不考虑ISO周1在年初的情况，例如：

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;

基本上，ISO 2020年第1周（实际对应的是 “2019-12-30”）没有被计入到这些计算当中。

有没有一种方法可以说出使用 ISO 周的前12个月以及当前行？

谢谢！

- Matias

Answer 1

这个不是你想要的吗？

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年同一周的行到当前行。

- GMB

Answer 2

这段内容太长了不能作为评论。

我认为这并不容易。问题在于“周”和“年”的差异。在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)

编辑：

我不确定这个方法是否适用于你的使用情况。但是我有一个想法，可能可以实现你想要的功能。那就是将一年的周数以 分数 的形式列举出来。所以拥有 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周并没有清晰的定义，但这可能是一个合理的解释。

- Gordon Linoff

---

谢谢，我唯一的问题是，回到52或53周不会达到2020年第1周，因为它会显示为2019年12月。 - Matias

@Matias……我不完全理解你的评论。在回溯52或53周时，isoyear并不是一个考虑因素。 - Gordon Linoff