I want to generate a query where there are two columns one is time and second is a particular conc. Based upon last 24 hour of data I want to calculate 24 hour average of conc. In the below table, if suppose I have a data for past 24 hour it will be calculated like (conc conc.... nthconc)/count. The dates will be moving forward like 8/11, 9/11, 10/11 and so on. This query will be kept in Grafana for conc visualisation with time.For information, if I use avg function in sql the value shown in avgvalue is same as conc Can anybody help me to write this query.
| Time | conc | avg (my-output) | Output wanted(conc) |
|---|---|---|---|
| 2021-11-07 18:47:00 | 1 | 1 | 24 hour average |
| 2021-11-07 18:48:00 | 1 | 2 | 24 hour average |
| .... | |||
| 2021-11-08 18:47:00 | 5 | 5 | 24 hour average |
| 2021-11-08 18:48:00. (Get the 24 hour average) |
CodePudding user response：
Seems that you need a window function (see the manual 3.5. Window functions, 4.2.8. Window Function Calls, 9.22. Window Functions). Then you can try this :
SELECT avg(conc) OVER (ORDER BY Time RANGE BETWEEN '1 DAY' PRECEDING AND CURRENT ROW)
FROM your_table