Subtract average from timeseries

Fluxlang
1

Hi,
here is what I’m trying to do: subtract an average (or median) value from a time series.

Application: I have a bunch of temperature sensors, where I’m not interested in the absolute value of each individual sensor, but rather the deviation from the average value (per sensor).

I have those two queries, which both work indivually.

t2 = from(bucket: "VIS")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] =~ /${temperatures:regex}/)
  |> mean()
  |> group(columns: ["_measurement"])
  |> yield()

t1 = from(bucket: "VIS")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] =~ /${temperatures:regex}/)
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)

How can I combine the aggregated values and the timeseries ? (I essentially want t1-t2)
I tried |> map but I failed finding the correct syntax/function.
Any advice appreciated !

2

Hi @brainiac,
Welcome to the community! I trust you are doing well. You’re not far off, here is an example query with my data:

var1 = from(bucket: "Jetson")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "exec_jetson_stats")
  |> filter(fn: (r) => r["_field"] == "jetson_CPU1" )
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)


var2 = from(bucket: "Jetson")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "exec_jetson_stats")
  |> filter(fn: (r) => r["_field"] == "jetson_CPU2")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)



union(tables: [var1, var2])
|> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
|> map(fn: (r) => ({ r with deviation: r.jetson_CPU1 - r.jetson_CPU2}))
|> yield(name: "mean")

The part that’s important:

  1. I use a union to merge my two tables into one table.
  2. I then pivot based on the _field key to turn my CPU1 and CPU2 into two separate columns.
  3. As you said I then use the map function to generate a new column based on my desired calculation.
3

Thanks @Jay_Clifford but I think that’s not quite what I wanted.My problem is, that var1 has no “_time” since it’s an aggregate function.
I have reformulated my problem to a minimal example:

import "sampledata"

t1 = sampledata.float()
  |> mean()
  |> yield(name: "_average")

  
t2 = sampledata.float()
  |> yield(name: "_individual")

t2-t1

So what I want is the table that looks like _individual but has subtracted the average value (from t2) based on the tag.
Is this possible ?

4

Hi @brainiac,
Sorry for the miss read. In that case you are looking for a join on the tag:

import "sampledata"

t1 = sampledata.float()
  |> mean()


  
t2 = sampledata.float()



join(
    tables: {t1: t1, t2: t2},
    on: ["tag"],
)
 
 |> map(fn: (r) => ({ r with _value: r._value_t2 - r._value_t1 }))
 |> yield(name: "custom-name")

Screenshot 2021-11-23 at 14.27.29
Screenshot 2021-11-23 at 14.27.293172×676 174 KB

5

Thanks @Jay_Clifford, that worked (with a bit of tweaking)!
Just for reference, I’m posting the solution for my initial Query.
There might be a more elegant way of doing this, but it works flawlessly

t1 = from(bucket: "VIS")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] =~ /${temperatures:regex}/)
  |> mean()
  |> group(columns: ["_measurement"])

t2 = from(bucket: "VIS")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] =~ /${temperatures:regex}/)
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)

join(
    tables: {t1: t1, t2: t2},
    on: ["_measurement"],
)

 |> map(fn: (r) => ({ r with _value: (r._value_t1 - r._value_t2) }))
 |> rename(columns: {_start_t1: "_start", _stop_t1: "_stop", _field_t1: "_field"})
 |> drop(columns:["_value_t1", "_value_t2", "_start_t2", "_stop_t2", "_field_t2"])
 |> group(columns: ["_measurement", "_field", "_start", "_stop"])
 |> yield()