René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Rolling Average with Oracle analytical functions

This little example will demonstrate how to use Oracle's analytical functions to get the rolling average. First you have to create and load a table that contains each month's average temprature in Edinburgh in the years 1764-1820. The script to do that can be found here.
After filling this table, use this statement to find the average temperature over the last 12 Months for each month in the result set: 
select
  avg_temp,
  month, year,
  to_char(avg(avg_temp)
    OVER (order by year,month rows between 11 preceding and current row),
  '99D9')
    as avg_year_temp from scottish_weather;
The (shortened) result is: 
   57,2          8       1812  46,5
   53,3          9       1812  46,4
   47,3         10       1812  46,0
  39,81         11       1812  45,7
  34,91         12       1812  45,6
  35,62          1       1813  45,5
  39,72          2       1813  45,6
  43,33          3       1813  46,1
  44,85          4       1813  46,4
   49,3          5       1813  46,5
   55,9          6       1813  46,5
   59,3          7       1813  46,7
   57,5          8       1813  46,7
   53,4          9       1813  46,7
   44,3         10       1813  46,5
  37,72         11       1813  46,3
  37,31         12       1813  46,5
   26,5          1       1814  45,8
  35,19          2       1814  45,4
The second and third columns are the month and year. The first column is the average temperature for this month; the last column is the average of the current and last 11 months average temperatures.