Using the lag function to find a moving average in SQL

sql

Solution

Don't use the `lag()` function. There is a build in moving average function. Well, almost:

select t.*, avg(sales) over (order by t range between 12 preceding and current row
from table t;

The problem is that this will produce an average for the first 11 months. To prevent that:

select t.*,
       (case when row_number() over (order by t) >= 12
             then avg(sales) over (order by t range between 12 preceding and current row
        end) as MovingAvg
from table t;

Note that the syntax `rows between` instead of `range between` would be very similar for this query.

Problem

I need to find a moving average for the previous 12 rows. I need to have my result set look like this. ``` t Year Month Sales MovingAverage 1 2010 3 20 NULL 2 2010 4 22 NULL 3 2010 5 24 NULL 4 2010 6 25 NULL 5 2010 7 23 NULL 6 2010 8 26 NULL 7 2010 9 28 NULL 8 2010 10 26 NULL 9 2010 11 29 NULL 10 2010 12 27 NULL 11 2011 1 28 NULL 12 2011 2 30 NULL 13 2011 3 27 25.67 14 2011 4 29 26.25 15 2011 5 26 26.83 ``` For row 13 I need to average rows 1 to 12 and have the result returned in row 13 column MovingAverage. Rows 1-12 have a MovingAverage of NULL because there should be at least 12 previous rows for the calculation. Rows t, Year, Month, and Sales already exist. I need to create the MovingAverage row. I am using postgreSQL but the syntax should be very similar.

Original source