Moving Average based on Timestamps in PostgreSQL

Question

I wanted to perform moving average through timestamps. I have two columns: Temperature and timestamps (time-date) and I want to perform the moving average based on every 15 minutes successive temperature observations. In other words, selecting data to perform the average based on 15 minutes time interval. Moreover, it is possible to have different number of observations for different time sequences. I meant all the window sizes are equal (15 minutes) but it is possible to have different number of observations in each window. For example: For a first window we have to calculate the average of n observation and for second window calculate the average of the observation for n+5 observation. Data Sample: ``` ID Timestamps Temperature 1 2007-09-14 22:56:12 5.39 2 2007-09-14 22:58:12 5.34 3 2007-09-14 23:00:12 5.16 4 2007-09-14 23:02:12 5.54 5 2007-09-14 23:04:12 5.30 6 2007-09-14 23:06:12 5.20 7 2007-09-14 23:10:12 5.39 8 2007-09-14 23:12:12 5.34 9 2007-09-14 23:20:12 5.16 10 2007-09-14 23…

Accepted Answer

Assuming you want to restart the rolling average after each 15 minute interval:

```
select id, 
       temp,
       avg(temp) over (partition by group_nr order by time_read) as rolling_avg
from (       
  select id, 
         temp,
         time_read, 
         interval_group,
         id - row_number() over (partition by interval_group order by time_read) as group_nr
  from (
    select id, 
           time_read, 
           'epoch'::timestamp + '900 seconds'::interval * (extract(epoch from time_read)::int4 / 900) as interval_group,
           temp
    from readings
  ) t1
) t2
order by time_read;
```

It is based on Depesz's solution to group by "time ranges":

Here is an SQLFiddle example: http://sqlfiddle.com/#!1/0f3f0/2

Moving Average based on Timestamps in PostgreSQL

Solution

Problem