sql - Oracle SQL:获取过去3个月的平均值并添加其他列

我在Oracle中有下表，我需要从该表中创建一个 View 以计算过去3个月在其他列中的平均得分。

Name      YearMonth     Score
Vince     201507         97
Vince     201508         95
Vince     201509         94
Vince     201510         91
Vince     201511         98
Vince     201512         95
Vince     201501         93

预期产量:

Name      YearMonth     Score   Average
Vince     201507         97
Vince     201508         95
Vince     201509         94      95.33 ((97+95+94)/3)
Vince     201510         91      93.33 ((95+94+91)/3)
Vince     201511         98      94.33 ((94+91+98)/3)
Vince     201512         95      94.67 ((91+98+95)/3)
Vince     201501         93      95.33 ((98+95+93)/3)

如何使用SQL做到这一点？感谢您的帮助

最佳答案

select name, year_month, score,
       (score +
        lag(score, 1) over (partition by name, year_month order by score) +
        lag(score, 2) over (partition by name, year_month order by score)) / 3 average
  from my_table