Difference between two streams

InfluxDB 2
#1

Hello,

I’m extracting two data streams to measure my electrical consumption and the production of my solar panels. I would like to make the difference between these two values.

Here is my current QL:

Pv=from(bucket: "jeedom2/autogen")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "Consommation Totale O" or r["_measurement"] == "Consommation Totale 1")
  |> filter(fn: (r) => r["_field"] == "Shelly 3EM")
  |> aggregateWindow(every: 1h, fn: max, createEmpty: false)
  |> difference()
  |> group()
  |> aggregateWindow(every: 1h, fn: sum, createEmpty: false)
  |> yield(name: "Conso")

Conso=from(bucket: "pv/autogen")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "2120198970" or r["_measurement"] == "2120199320" or r["_measurement"] == "2120199323")
  |> filter(fn: (r) => r["_field"] == "energy")
  |> aggregateWindow(every: 1h, fn: max, createEmpty: false)
  |> difference(nonNegative: true)
  |> group()
  |> aggregateWindow(every: 1h, fn: sum, createEmpty: false)
  |> yield(name: "Pv")

How do i create the difference between the Pv streams and the Conso Streams.

Thanks

#2

Up…

Any ideas how to solve this ?

thanks

#3

Hello @ced2flux,
Thanks for boosting this. Sometimes questions slip through the cracks :confused:
You would want to perform a join() and then perform a map()

In order to write the query for you though I need a little bit more information.
Are you hoping to: “Shelly 3EM” - “energy” for all measurements?
Or “Shelly 3EM”“Consommation Totale O” - “energy”“2120198970” and then some other differences for example?

I’ll assume that you want to do the former (please let me know if my assumption is wrong) “Shelly 3EM” - “energy”
https://docs.influxdata.com/flux/v0.x/stdlib/experimental/join/

import "experimental"

experimental.join(
  left: Pv
  right: Conso,
  fn: (left, right) => ({
    left with
    Pv_value: left._value,
    Conso_value: right._value,
    diff: left._value - right._value
    _time: left._time
  })
)
|> yield(name:"diff")

Or you could use join and map():
https://docs.influxdata.com/flux/v0.x/stdlib/universe/join/
https://docs.influxdata.com/flux/v0.x/stdlib/universe/map/

join(
  tables: {Pv: Pv, Conso: Conso},
  on: ["_time", "_start", "_stop"],
  method: "inner"
)
|> map(fn: (r) => ({diff: r._value_Pv - r._value_Conso }))
|> yield(name:"diff")

Please let me know if this isn’t what you’re looking for