How to calculate moving avg over time?-CodePudding

I have data of sensors coming anytime. I want to group them by sensors and calculate the average over X minutes or hours. I tried to do my own but did not work. You can check the fiddle [here][1].

In short, I have to show sensors wise moving average data against X minutes or hours. [1]: http://sqlfiddle.com/#!17/e3101/14

Thanks

CodePudding user response：

It's unclear to me what output you are after, but maybe you are looking for a range condition for the window function:

select r.sensor_id,
       r.reading,
       r."timestamp",
       AVG(r.reading) OVER (partition by r.sensor_id 
                            ORDER BY r."timestamp" 
                            range between interval '5 minute' PRECEDING 
                                      AND CURRENT ROW) as avg_reading
from sensor_readings r 
order by r.sensor_id, r."timestamp"

Online example

CodePudding user response：

Instead of the group by clause, you need the partition clause. Try if this works.

Select sensor_id, 
       reading, 
       date_trunc('hour', "timestamp") AS sensor_reading_hour,
       AVG(reading) OVER(PARTITION BY sensor_id ORDER BY date_trunc('hour', "timestamp")) as avg_reading
from sensor_readings