Window operation and competition

1

Hello,

First of all, I’d like to be able to perform an operation like “variable1 / variable2 / constant”.

depending on the designated time step. In other words, the calculation can be performed by day, month or year. In the script “aggregateWindow(every: 1d” 1d would be a variable on Grafana (1d/1m/1y)
The above calculation must be performed according to this aggregation window.

In addition to this. I have a third variable that must compete with variable 2. If var2 > var3 then variable 2 is retained. If var3 > var2 then variable 3 is retained.
The calculation can be either “var1 / var2 / constant” or “var1 / var3 / constant”.

Voici ce que j’ai fais :

first = from(bucket: "bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "firstmeasurement")
  |> filter(fn: (r) => r["_field"] == "firstfield")
  |> aggregateWindow(every: 1d, fn: sum, createEmpty: false)


second = from(bucket: "GIN")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "secondmeasurement")
  |> filter(fn: (r) => r["_field"] == "secondfield")
  |> aggregateWindow(every: 1d, fn: mean, createEmpty: false)

result = join(tables: {key1: first, key2: second}, on: ["_time", "_field"], method: "inner")
  
result
  |> map(fn: (r) => ({r with _field: r.firld / r.second}))

Avec union :

result = union(tables: [first, second])
  
result
  |> map(fn: (r) => ({r with _field: r.firld / r.second}))

But it’s not what I expected

Thank you in advance.

2

You can create a Grafana dashboard variable that includes the three duration values and use it in your query. The values of the variable will be strings, so you have to cast them to duration values. For example:

|> aggregateWindow(every: duration(v: "${yourVariable}"), fn: sum, createEmpty: false)

Is var3 a field value returned by your query or is it a constant. Since you’re querying two fields, I’ll assume var3 is a constant. In your map() function, you can assign a variable that returns the greater of the two values (var2 vs var3) using the math.mMax() function.

It’s the combination of union() |> pivot() that gives you what you’d expect from a join, but in a much more performant way. Your query would look something like this:

import "math"

// Assuming var 3 is a numeric constant
var3 = 1.0

first =
    from(bucket: "bucket")
        |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
        |> filter(fn: (r) => r["_measurement"] == "firstmeasurement")
        |> filter(fn: (r) => r["_field"] == "firstfield")
        |> aggregateWindow(every: duration(v: "${yourVariable}"), fn: sum, createEmpty: false)

second =
    from(bucket: "GIN")
        |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
        |> filter(fn: (r) => r["_measurement"] == "secondmeasurement")
        |> filter(fn: (r) => r["_field"] == "secondfield")
        |> aggregateWindow(every: duration(v: "${yourVariable}"), fn: sum, createEmpty: false)

result =
    union(tables: [first, second])
        |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")

result
    |> map(
        fn: (r) => {
            denominator = math.mMax(x: r.secondfield, y: var3)

            return {r with _field: "new-field-name", _value: r.firstfield / denominator}
        },
    )
3

Thanks for your answer

After a long search. I’m finally at the end. I just need to get a query that catches two fields and keeps only the highest value for each day.

Here’s the query :

from(bucket: "GIN")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "TheMeasurement")
  |> filter(fn: (r) => r["_field"] == "Firstfield" or r["_field"] == "Secondfield")
  |> aggregateWindow(every: 1d, fn: mean, createEmpty: false, offset: -1s)

And I wish there was only one maximum compound curve left per day.

I tried this

from(bucket: "GIN")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "TheMeasurement")
  |> filter(fn: (r) => r["_field"] == "Firstfield" or r["_field"] == "Secondfield")
  |> aggregateWindow(every: 1d, fn: mean, createEmpty: false, offset: -1s)
  |> group(columns: ["_time"])
  |> max()

But I get this :

image
image1321×372 20.1 KB

I’m not sure it works for what I need to do. Values are not on a time serie

4

Finally I managed to do this :

import "timezone"

option location = timezone.location(name: "Europe/Paris")

first = from(bucket: "GIN")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "TheMeasurement")
  |> filter(fn: (r) => r["_field"] == "FirstField")
  |> aggregateWindow(every: 1d, fn: max, createEmpty: false, offset: -1s)

second = from(bucket: "GIN")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "TheMeasurement")
  |> filter(fn: (r) => r["_field"] == "SecondField")
  |> aggregateWindow(every: 1d, fn: max, createEmpty: false, offset: -1s)

result = join(tables: {chauf: chauf, refroid: refroid}, on: ["_time"])
    |> map(fn: (r) => ({r with max_value: if r._value_first > r._value_second then r._value_first else r._value_second }))
    |> drop(columns: ["_value_first", "_value_second"])
    |> yield(name: "max_values")

result

But I’m not sure it’s the right thing to do and that it works properly.

5

@Yohan, there’s a way you can do this without having to join streams together. Joins are incredibly expensive operations and I avoid them when I can. Since both fields are queried from the same data source, you can query both fields in a single filter() call. You can then pivot them into columns so you can compare them per row. Rather than if/else logic to find the greater of the two values, you can import the math package and use math.mMax:

import "math"
import "timezone"

option location = timezone.location(name: "Europe/Paris")

from(bucket: "GIN")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r["_measurement"] == "TheMeasurement")
    |> filter(fn: (r) => r["_field"] == "FirstField" or r["_field"] == "SecondField")
    |> aggregateWindow(every: 1d, fn: max, createEmpty: false, offset: -1s)
    |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> map(
        fn: (r) => ({r with max_value: math.mMax(x: r.FirstField, y: r.SecondField}),
    )
    |> drop(columns: ["FirstField", "SecondField"])

One question I do have–what the -1s offset in aggregateWindow()?