timedMovingAverage() function

The timedMovingAverage() function calculates the mean of values in a defined time range at a specified frequency.

timedMovingAverage(
  every: 1d,
  period: 5d,
  column: "_value"
)

For each row in a table, timedMovingAverage() returns the average of the current value and all row values in the previous period (duration). It returns moving averages at a frequency defined by the every parameter.

Each color in the diagram below represents a period of time used to calculate an average and the time a point representing the average is returned. If every = 30m and period = 1h:

Parameters

every

(Required) Frequency of time windows.

period

(Required) Length of each averaged time window. A negative duration indicates start and stop boundaries are reversed.

column

Column used to compute the moving average. Defaults to "_value".

tables

Input data. Default is piped-forward data (<-).

Examples

The following examples use generate.from() to generate sample data and illustrate how timedMovingAverage() transforms data.

Calculate a five year moving average every year

import "generate"

timeRange = {start: 2015-01-01T00:00:00Z, stop: 2021-01-01T00:00:00Z}

data = generate.from(
    count: 6,
    fn: (n) => n * n,
    start: timeRange.start,
    stop: timeRange.stop
  )
  |> range(start: timeRange.start, stop: timeRange.stop)

data
  |> timedMovingAverage(every: 1y, period: 5y)

Calculate a seven day moving average every day

import "generate"

timeRange = {start: 2021-01-01T00:00:00Z, stop: 2021-01-08T00:00:00Z}

data = generate.from(
    count: 7,
    fn: (n) => n + n,
    start: timeRange.start,
    stop: timeRange.stop
  )
  |> range(start: timeRange.start, stop: timeRange.stop)

data
  |> timedMovingAverage(every: 1d, period: 7d)

Function definition

timedMovingAverage = (every, period, column="_value", tables=<-) =>
  tables
    |> window(every: every, period: period)
    |> mean(column:column)
    |> duplicate(column: "_stop", as: "_time")
    |> window(every: inf)