Math expression with 2 query influxDB

1

Hi

i’m new on influxDB, i am work with influx + Grafana, and i would like to do math expression, not like below example but more complex, however even this one i can’t do it:

the result on below if the sum per query, not bwetween queries, i want the sum between t1 + t2

anybody can help me please?

import “contrib/tomhollingworth/events”
import “sampledata”

t1=from(bucket: “LORAHOMEDB”)
|> range(start: -1d, stop: 0d)
|> filter(fn: (r) => r[“_measurement”] == “device_frmpayload_data_hum”)
|> filter(fn: (r) => r[“_field”] == “value”)
|> filter(fn: (r) => r[“_value”] >= 55.0)
|> events.duration(unit: 1s)
|> map(fn: (r) => ({ r with _value: float(v: r.duration)/3600.0 }))
|> sum(column: “_value”)
|> yield(name: “number”)

t2=from(bucket: “LORAHOMEDB”)
|> range(start: -1d, stop: 0d)
|> filter(fn: (r) => r[“_measurement”] == “device_frmpayload_data_temp”)
|> filter(fn: (r) => r[“_field”] == “value”)
|> aggregateWindow(every: 1d, fn: mean, createEmpty: false)
|> yield(name: “mean”)

union(tables: [t1, t2])
//|> sum()
|> map(fn: (r) => ({ r with CALCULATION: r._value + r._value}))

2

@cadaval sum() sums all the values in a specified column (default is _value) for each input table. To be able to add values returned from these queries, you need to pivot data so that each value you want to work with exists in a single row. For a pivot to work, you have to have a common value to pivot. Generally, this is _time, but in the case of your query, I don’t think your two streams of data will have common time stamps because one is summing all values in a column (with sum()), while the other is “downsampling” points into 1d averages. Since your query range is only one day, I assume you really only need the mean from the last day, not necessarily windowed aggregates like you’re currently using.

I’m making a lot of assumptions here, but I’m guessing you want t1 and t2 to each return a single scalar value and you want add those values to together. To return a scalar value from each stream, you need to use findColumn() which returns an array of values in a column. You can then reference a value in the array and return a scalar value.

So this is how I’d do what I think you’re trying to do:

import "array"

t1 =
    from(bucket: "LORAHOMEDB")
        |> range(start: -1d, stop: 0d)
        |> filter(fn: (r) => r["_measurement"] == "device_frmpayload_data_hum")
        |> filter(fn: (r) => r["_field"] == "value")
        |> filter(fn: (r) => r["_value"] >= 55.0)
        |> events.duration(unit: 1s)
        |> map(fn: (r) => ({r with _value: float(v: r.duration) / 3600.0}))
        |> sum(column: "_value")
        |> findColumn(fn: (key) => true, column: "_value")

t2 =
    from(bucket: "LORAHOMEDB")
        |> range(start: -1d, stop: 0d)
        |> filter(fn: (r) => r["_measurement"] == "device_frmpayload_data_temp")
        |> filter(fn: (r) => r["_field"] == "value")
        |> mean()
        |> findColumn(fn: (key) => true, column: "_value")

array.from(rows: [{_time: now(), _value: t1[0] + t2[0]}])

This uses array.from() to build an ad-hoc table with a single row that contains the current time and the sum of first values in the t1 and t2 arrays.

3

Hi @scott, this work thanks a lot for help

4

Hi

i would put the above result array in table with time, for try doing a graffic, any help for that. i need use funtion join.tables() ?

thanks
CDV