sql - 甲骨文 SQL : Getting average for past 3 month and add additional column

Question

我在 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 来完成它？谢谢你的帮助

Answer 1

你可以用一个窗口函数来做到这一点:

select name, 
       yearmonth, 
       score,
       avg(score) over (order by to_date(yearmonth, 'yyyymm') range between interval '3' month preceding and current row) as average
from scores;

以上假定 yearmonth 是一个 varchar 列，否则 to_date() 将不起作用。

这不是完全您的示例输出，因为前两行的平均值等于该行的分数(因为这两行没有前 3 个月)。如果你真的需要这些平均值为空，你可以这样做:

select name, 
       yearmonth, 
       score,
       case 
         when row_number() over (order by to_date(yearmonth, 'yyyymm')) > 2 then
             avg(score) over (order by to_date(yearmonth, 'yyyymm') range between interval '3' month preceding and current row) 
         else null -- not really necessary, just for clarity
       end as average
from scores;