I have been struggling to do something that seems quite simple: having a 1-minute resolution data of X, I want to:
- compute a 2h moving average of X.
select moving_average("X", 120) from meas_Ywork fine.
- as I might want to show a year’s worth of this data, I would like to aggregate the results, using groupby and mean, e.g.
select mean("MA") from (select moving_average("X", 120) as "MA" from meas_Y) group by time(1h)
This returns an error:
ERR: aggregate function required inside the call to moving_average.
Now, when I move the mean() call inside the moving_average, like so:
select moving_average("MEAN", 120) from (select mean("X") as "MEAN" from meas_Y group by time(1h) it works but does something else than intended - points are first aggregated by time, and then the moving average is calculated - this time from 120 hours, instead of 120 minutes.
This is actually intended to work with Grafana’s $__interval in the GROUP BY, therefore I cannot just change the second argument to moving_average accordingly.
Is there a way of calculating time-aggregates of moving_average, as opposed to moving_average() of aggregates?