InfluxDB v2.7.11 subquery

[rossos]

Hello,

How can I make this work for me?

I’m getting a mean values within 1min window with the first query
in order to run a 2nd query on the result to sum up the values with a different time window.

The idea is to get the Wh (watts-hour) result.

t1 = from(bucket: "table")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "watts" and r["_field"] == "value")
  |> aggregateWindow(every: 1m, fn: mean)
  |> map(fn: (r) => ({r with _value: r._value / 60.0}))


from(tables: [t1])
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "watts" and r["_field"] == "value")
  |> aggregateWindow(every: 1h, fn: sum)
  |> yield(name: "value")

InfluxDB v2.7.11

[scott]

A few questions:

• Why are you dividing the result of the the 1m average in t1 by 60.0?

  // ...
  |> map(fn: (r) => ({r with _value: r._value / 60.0}))
  

  This, I guess, gives you the average watts used per second, but I don’t think this is is necessary or even desired for this query.

• Why are you doing 1m averages and then summing them in 1h hour intervals? Why not just sum all the values into 1h hour intervals? Getting the average seems unnecessary.

I guess I just don’t fully understand the result you’re trying to return. From the description you provided, I think the following query should give you what you’re looking for:

t1 = from(bucket: "table")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "watts" and r["_field"] == "value")
  |> aggregateWindow(every: 1h, fn: sum)

[rossos]

Thank you for your reply.

Your questions gave me the answer I was looking for.

@scott

Why are you dividing the result of the the 1m average in t1 by 60.0?

the value on it’s own is Watts-per-minutes, hence I divide by 60min to convert it into Watt-per-hour.

@scott

Why are you doing 1m averages

because of Grafana memory limitations

@scott

guess I just don’t fully understand the result you’re trying to return.

I was trying to do nested queries “SELECT xyz FROM (SELECT abc FROM dfg WHERE 1) WHERE 1” in Flux 2.0 syntax, which apparently is only supported in v1 and v3, but not v2.

the solution to my quest is

from(bucket: "table")
  |> range(start: -15d)
  |> filter(fn: (r) => r["_measurement"] == "watts" and r["_field"] == "value")
  |> aggregateWindow(every: 1d, fn: sum)
  |> map(fn: (r) => ({r with _value: r._value / (60.0 * 60.0)}))
  |> yield(name: "value")