Calculate the mean of temperature just when humectation > 1?

Hi all

i would like that anybody can help me how i can calculate the mean of temperature of one query with result of other query.
so i need some help for Calculate the mean of temperature just when humectation > 1

let below my queries, thanks a lot in advance

from(bucket: “LORAHOMEDB”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “humectação”)
|> filter(fn: (r) => r[“_field”] == “value”)
|> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: true)
|> yield(name: “last”)

from(bucket: “LORAHOMEDB”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “temperaturaoutdoor”)
|> filter(fn: (r) => r[“_field”] == “value”)
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: “mean”)

@cadaval I think this will give you what you’re looking for:

humectação = from(bucket: "LORAHOMEDB")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r["_measurement"] == "humectação")
    |> filter(fn: (r) => r["_field"] == "value")
    |> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: true)

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

union(tables: [humectação, temperaturaoutdoor])
    |> pivot(rowKey:["_time"], columnKey: ["_meausurement"], valueColumn: "_value")
    |> filter(fn: (r) => r["humectação"] > 1.0)
    |> mean(column: "temperaturaoutdoor")

Dear scott

this work thanks a lot, once again for help.
unfortunatly to me this did not solve my issue, because i need calculate de number hours with humectação and multiplicate per avg temperatura, so i try doing the following.

import “array”
import “contrib/tomhollingworth/events”

t1 =
from(bucket: “LORAHOMEDB”)
|> range(start: -1d, stop: 0d)
|> filter(fn: (r) => r[“_measurement”] == “humectação”)
|> aggregateWindow(every: 15m, fn: last, createEmpty: true)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> fill(usePrevious: true)
|> events.duration(unit: 1s)
|> map(
fn: (r) => ({r with _value:
if r._value >= 1.0 then (float(v: r.duration) / 3600.0)
else
0.0}),
)
|> sum(column: “_value”)
|> findColumn(fn: (key) => true, column: “_value”)

humectação = from(bucket: “LORAHOMEDB”)
|> range(start: -1d, stop: 0d)
|> filter(fn: (r) => r[“_measurement”] == “humectação”)
|> filter(fn: (r) => r[“_field”] == “value”)
|> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: true)

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

t2=
union(tables: [humectação, temperaturaoutdoor])
|> pivot(rowKey:[“_time”], columnKey: [“_measurement”], valueColumn: “_value”)
|> filter(fn: (r) => r[“humectação”] == 1.0)
|> mean(column: “temperaturaoutdoor”)
|> findColumn(fn: (key) => true, column: “_value”)

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

i think the issue is column: “temperaturaoutdoor”) it is different of , column: “_value”)…, and don’t know how solve it.
once again i need your help thanks in a advance

cadaval

Hi

just to inform that i solve the problem doing the following

import “array”
import “contrib/tomhollingworth/events”

humectação = from(bucket: “LORAHOMEDB”)
|> range(start: -1d, stop: 0d)
|> filter(fn: (r) => r[“_measurement”] == “humectação”)
|> filter(fn: (r) => r[“_field”] == “value”)
|> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: true)

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

t1 = union(tables: [humectação, temperaturaoutdoor])
|> pivot(rowKey:[“_time”], columnKey: [“_measurement”], valueColumn: “_value”)
|> filter(fn: (r) => r[“humectação”] == 1.0)
|> mean(column: “temperaturaoutdoor”)
|> findColumn(fn: (key) => true, column: “temperaturaoutdoor”)

h1 = from(bucket: “LORAHOMEDB”)
|> range(start: -1d, stop: 0d)
|> filter(fn: (r) => r[“_measurement”] == “humectação”)
|> filter(fn: (r) => r[“_field”] == “value”)
|> aggregateWindow(every: 15m, fn: last, createEmpty: true)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> fill(usePrevious: true)
|> events.duration(unit: 1s)
|> map(
fn: (r) => ({r with _value:
if r._value >= 1.0 then (float(v: r.duration) / 3600.0)
else
0.0}),
)
|> sum(column: “_value”)
|> findColumn(fn: (key) => true, column: “_value”)

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

once again thanks a lot for all support

Cadaval (CDV)