Join two queries even outside the range

,
1

Hello,

My goal is as follows:
To combine two queries (corresponding to consumption data that don’t occur at the same time of year (one consumes the whole year, the other only part of it)), into a single query by adding up the values so that, in grafana, you can use this query to recalculate an expression.
The aim is to obtain a global value for use in an ultimate calculation.

Initially, in Grafana, I wanted to bring my two queries separately and add them together. But one of the queries has no value during a period. During this period, the addition cannot take place.

Here’s what I tried:

import "timezone"

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


value1 = from(bucket: "GIN")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "Consumption")
  |> filter(fn: (r) => r["_field"] == "value1")
  |> group(columns: ["_measurement", "_field"])
  |> aggregateWindow(every: 1d, fn: sum, createEmpty: true, offset: -1s)
  |> fill(value: 0.0)
  |> rename(columns: {_value: "value1_value"})


value2 = from(bucket: "GIN")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "Consumption")
  |> filter(fn: (r) => r["_field"] == "value2")
  |> group(columns: ["_measurement", "_field"])
  |> aggregateWindow(every: 1d, fn: sum, createEmpty: true, offset: -1s)
  |> rename(columns: {_value: "value2_value"})

joined = join(
  tables: {value1: value1, value2: value2},
  on: ["_time"],
  method: "inner"
)

result = joined
  |> map(fn: (r) => ({
      _time: r._time,
      combinedValue: r.value1_value + r.value2_value
    }))


result

Unfortunately, two things went wrong:
1 :

  • On “value1”, I only have values at the beginning of the year.
  • On “value2”, I have values throughout the year

If I set a range of 6 months, the join is done correctly, if I set 30 days, the join is no longer done.
2 :
The values added are not correct.

Also, I need to multiply value1 by 0.6.

Thanks for your help

2

It seems like you need to use the fill function to fill the missing data, something like this in your queries

fill(value: 0.0)

See this for reference: time series - Timeseries (InfluxDB): How to deal with missing data? - Data Science Stack Exchange

3

Hello,

thank you for your interest in my problem.

As you can see on this graph, when the value does not exist it applies 0 over a range of 6 months. It works fine.

image
image1815×725 19.6 KB

Unfortunately, when the range is recent (last 90 days), it doesn’t work anymore.
image

4

Hello,
i don’t know if your query is a true query or a example one.
I can suggest you to drop the two queries and the join to go to only one query (grouping by field as you already do) with a pivot using columnKey on field.

It should solve your problem number 1 who is if i’m right that when there is no data on the “not whole year” data the whole querie don’t work.

For the 2nd problem about not correct value i can’t tell right now anything.

For the multiply of value 1 by 0.6 you can do it directly in the map (something like: (value_1*0.6)+value_2 )