-1

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

Volatil3
  • 14,253
  • 38
  • 134
  • 263
  • What exactly is the output you want? –  Dec 19 '21 at 08:07
  • @a_horse_with_no_name Data is keep coming from sensors, if the time duration is given, for example, 3 minutes of 10 minutes, it creates a window of that X Minutes of records and average them. For example data cam,e at 11:25, 11:26,11:27,11:28, 11:29 and 11:30, it is 5 minutes, whatever the average became for last 5 minutes it should show it. – Volatil3 Dec 19 '21 at 09:19
  • 1
    Please **[edit]** your question and add the expected output based on your sample data (as formatted text, no screen shots please) –  Dec 19 '21 at 09:21
  • [Minimal, complete sample code](//stackoverflow.com/help/mcve) and data should be included in the [question itself](//meta.stackoverflow.com/q/254428/90527). Note that adding a link to a live example *in addition* to having code in the question is welcomed, but it shouldn't take its place. – outis Dec 21 '21 at 09:43

2 Answers2

2

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

  • Didn't know window functions were so powerful. – Zakaria Dec 19 '21 at 08:15
  • Can you explain this? I guess `PRECEDING and CURRENT ROW` is checking current and last row only and averaging out it but how is it applying `5 minute` here? Pls explain this – Volatil3 Dec 19 '21 at 09:26
  • 1
    Not it operates on an interval, not on the number of rows. That's the difference between `rows between` and `range between`. See [here](https://modern-sql.com/blog/2019-02/postgresql-11#over) for an introduction –  Dec 19 '21 at 09:31
  • @a_horse_with_no_name so is it averaging out based on rows appear in last 5 minutes? – Volatil3 Dec 19 '21 at 09:37
  • @a_horse_with_no_name I think this answer of yours doing what I am looking for but it is too complex for me to understand https://stackoverflow.com/a/13820529/275002 – Volatil3 Dec 19 '21 at 10:16
0

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
GoonerForLife
  • 631
  • 2
  • 5