sql - 带天数的 Redshift SQL 窗口函数 frame_clause

Question

我正在尝试对 Redshift 中的数据集执行窗口函数，使用天数和前几行的间隔。示例数据:

date        ID      score
3/1/2017    123     1
3/1/2017    555     1
3/2/2017    123     1
3/3/2017    555     3
3/5/2017    555     2

最近 3 个分数的平均分数的 SQL 窗口函数:

select
      date,
      id,
      avg(score) over 
         (partition by id order by date rows 
              between preceding 3 and 
                      current row) LAST_3_SCORES_AVG,
from DATASET

结果:

date        ID      LAST_3_SCORES_AVG
3/1/2017    123     1
3/1/2017    555     1
3/2/2017    123     1
3/3/2017    555     2
3/5/2017    555     2

问题是我想要过去 3 天的平均分数(移动平均值)，不是最后三个测试。我已经查看了 Redshift 和 Postgre 文档，但似乎找不到任何方法。

期望的结果:

date        ID      3_DAY_AVG
3/1/2017    123     1
3/1/2017    555     1
3/2/2017    123     1
3/3/2017    555     2
3/5/2017    555     2.5

任何方向将不胜感激。

Answer 1

您可以使用 lag() 并显式计算平均值。

select t.*,
       (score +
        (case when lag(date, 1) over (partition by id order by date) >=
                   date - interval '2 day'
              then lag(score, 1) over (partition by id order by date)
              else 0
         end) +
        (case when lag(date, 2) over (partition by id order by date) >=
                   date - interval '2 day'
              then lag(score, 2) over (partition by id order by date)
              else 0
         end)
        )
       ) /
       (1 +
        (case when lag(date, 1) over (partition by id order by date) >=
                   date - interval '2 day'
              then 1
              else 0
         end) +
        (case when lag(date, 2) over (partition by id order by date) >=
                   date - interval '2 day'
              then 1
              else 0
         end)
       )
from dataset t;