CODEWITHSUNDEEP
postgresql-9.1moving-average
Farhad Irani
Farhad Irani

Reputation: 115

Moving average filter in postgresql

I have a query that computes the moving average in a table over the last 7 days. My table has two columns date_of_data which is date type and is a date series with one day interval and val which is float.

with B as
(SELECT   date_of_data, val
FROM mytable
group by date_of_data
order by date_of_data)
select
    date_of_data,val, avg(val) over(order by date_of_data rows 7 preceding)mean7
from B
order by date_of_data;

I want to compute a moving filter for 7 days. It means that for every row , the moving window would contain the last 3 days, the row itself and 3 succeeding rows.I cannot find a command to take into account the succeeding rows. Can anybody help me on this?

Upvotes: 4

Views: 3511

Answers (1)

user330315
user330315

Reputation:

Try this:

select date_of_data, 
       val, 
       avg(val) over(order by date_of_data ROWS BETWEEN 3 preceding AND 3 following) as mean7
from mytable
order by date_of_data;

Upvotes: 11

Related Questions