Aggregation Mean excluding by a specific Tag

Hi everyone,

Is there any possibility to make the mean of the values excluding a specific tag?
I want to make the mean of the values with different values of the Tag “Type” for example

Thanks beforehand

Hi @JMU,
You can use an inverse filter like so:

  |> filter(fn: (r) => r["tag_name"] != "######")

Here is an example:

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" or r["_field"] == "jetson_CPU2" or r["_field"] == "jetson_CPU3")
  |> filter(fn: (r) => r["host"] != "jetson-desktop")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")

Hi Jay,

I guess the result is gonna be two separate values. Value 1 for “Jetson_CPU1” and Value2 for “Jetson_CPU2”. What I want is to get the mean of the values in one row: a value who make the mean for CPU1 and CPU2 together.

I don’t know if I’ve explained myself well. So sorry

Hi @JMU,
Okay my apologies, that was a little different to your original query which was to exclude a specific tag.

So what you could do is group() by the measurement which would put all fields within one table:

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" or r["_field"] == "jetson_CPU2" or r["_field"] == "jetson_CPU3")
  |> group(columns: ["_measurement"], mode:"by")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")

When you run an aggregation it will find the mean of all fields within the time window. If you want to find the mean per row then you will have to do something like this with a pivot:

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" or r["_field"] == "jetson_CPU2" or r["_field"] == "jetson_CPU3")
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with mean: (r.jetson_CPU1 + r.jetson_CPU2 + r.jetson_CPU3) / 3.0   }))
  |> yield(name: "mean")


This is what appears after your first query is applied (“group”).

|> group(columns: [“MEDICION”], mode:“by”)

The TAG MEDICION is what I wanted to “disable” to get one single value.

Hi @JMU,
Don’t group by the tag Medicion if this is the tag you want to disable. Group by the measurement instead.

  |> group(columns: ["_measurement"], mode:"by")