Mean of moving_average, not moving_average of mean - is it possible in InfluxDB?

1

Hello,

I have been struggling to do something that seems quite simple: having a 1-minute resolution data of X, I want to:

  1. compute a 2h moving average of X.
    Queries like select moving_average("X", 120) from meas_Y work fine.
  2. 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?

2

Hello @tpietruszka,

Try checking your parentheses? I had the same error until I fixed mine. The following query worked for me in chronograf and through the CLI:
SELECT mean("MA") from (SELECT moving_average("usage_system",120) as "MA" FROM "telegraf"."autogen"."cpu" WHERE time > '2019-05-20T14:00:00Z' AND "cpu"='cpu-total') GROUP BY time(1h)

55%20AM
Screen Shot 2019-05-21 at 11.32.55 AM.jpg1318×310 107 KB

3

@Anaisdg thank you very much for your response.

I have tried your exact query, just changing the name of the database, and still ERR: aggregate function required inside the call to moving_average

What version of InfluxDB are you using? The server I am using is 1.6.0 - maybe there is some issue that got fixed later?

4

@tpietruszka,
I am using 1.7.6.