Moving average calculation on same period of time

InfluxDB 1 InfluxQL
, ,
1

Hi there,

I got a situation where I want to calculate the moving average.
All the existing moving average functionality looks like this :
MOVING_AVERAGE(MEAN(“field”), N ) … GROUP BY time(interval)
Where N is the number of points before the current calculated number to include in the calculation of the moving average.

But what I want to calculate is the moving average on a certain time of the day during the week.
So for example: MOVING_AVERAGE(MEAN(“field”) , N , P ) … GROUP BY time(interval)
Where N is the same as above
But P is the period between those points.

For example P = 1w , to calculate the moving average based on averages on the same timebucket N weeks before. ( For example timebucket : Monday 10:00-10:30)

Does somebody knows how to fix this with querying Influx?

2

Hello @Cas_Altenburg,
Welcome!
Yes I know how to do that with Flux :slight_smile:
You can use the date package to filter for specific times:

I believe your query would look something like:


  data
    |> map(fn: (r) => ({ r with dateWeek: date.weekDay(t: r._time), dateHr: date.hour(t: r._time), dateMin: date.minute(t: r._time), dateSec: date.second(t: r._time)}))
    |> filter(fn: (r) => r.dateWeek == 1)
    |> filter(fn: (r) => r.dateHr == 9)
    |> filter(fn: (r) => r.dateMin >= 0 and r.dateMin <= 30)

To filter for that schedule. I also included the seconds in the mapping but you don’t need that to just reduce the values to monday from 10:00-10:30
All those function return values with ranges starting at 0.

3

Thanks @Anaisdg !
I never worked with Flux only InfluxQL, but I see now Flux has a lot more features than InfluxQL does.