CODEWITHSUNDEEP
sqloracle-database
Ianthe
Ianthe

Reputation: 5709

Oracle SQL : Getting average for past 3 month and add additional column

I have below table in Oracle and i need to create a view from this table to calculate of the average score for the past 3 months in additional column.

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

Expected output:

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)

How can I do it using SQL? Thank you for the help

Upvotes: 2

Views: 2187

Answers (2)

Dmitriy
Dmitriy

Reputation: 5565

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

Upvotes: 2

user330315
user330315

Reputation:

You can do this with a window function:

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;

The above assumes yearmonth is a varchar column, otherwise the to_date() wouldn't work.

This is not exactly what your sample output is, because the first two rows will have an average that is equal to the row's score (because there are no 3 previous months for those two rows). If you really do need those averages to be null, you can do something like this:

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;

Upvotes: 4

Related Questions