Moving Average in PostgreSQL

Assume that you have the table mytable with the following fields:

• Date
• Store_ID
• Sales

And you would like to get the Moving Average of 5 days by Store_ID. Let’s see how we can do it in PostgreSQL.

select Date, Store_ID, Sales,
avg(Sales) over (partition by Store_ID order by Date rows BETWEEN 4 PRECEDING and current row) as MA_Sales
from mytable
order by Date, Store_ID

Notice that this query will return the MA, even if we have less than 5 observations in the window. So, if we have one observation, it will return this value, if we have two observations it will return the average of two and so on until we reach the 5 observations where it will adapt the rolling window of 5 observations.

If we want to return null when the rolling window contains less than 5 observations we can do it as follows:

select Date, Store_ID, Sales,
case
    when count(*) over (partition by Store_ID order by Date rows BETWEEN 4 PRECEDING and current row)>4 then
avg(Sales) over (partition by Store_ID order by Date rows BETWEEN 4 PRECEDING and current row) 
else null
    end as MA_Sales
from mytable
order by Date, Store_ID