How to convert InfluQL to Flux with GROUP BY and WHERE

Fluxlang
, ,
1

I am making an IoT Dashboard where I want to visualize data from a machine. Now I want to make the step to Influx 2.0, but the new Flux language is different, and I cannot figure this problem out. In Grafana I have the following InfluxQL query:

SELECT sum("execution_duration") / sum("interval_duration") as "Availability" FROM "data-oee" WHERE $timeFilter GROUP BY time($time_interval)

Now I want to execute this query as a Flux query, I’ve made an query but it doesn’t give me the right results:

from(bucket: "Dashboard")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "oee-data")
  |> filter(fn: (r) => r["machine"] == "ilv")
  |> filter(fn: (r) => r["_field"] == "no_production_duration" or r["_field"] == "production_duration")
  |> cumulativeSum(columns: ["_field"])
  |> aggregateWindow(every: 1h, fn: sum, createEmpty: false)
  |> yield(name: "sum")

I want to sum all the values that have the _field name “execution_duration” and divide it by the sum of all the values from the field name “interval_duration” by doing this I get a list of timestamps with some kind of availability value that I want to visualize.

The data in my InfluxDB:

notraw
notraw1302×172 11 KB

I hope you guys know how to fix this…

2

Hi Tom. I am sure there is a way to write a query to do what you want, but another approach might be to just make 2 queries (one for the sum of “execution_duration” and another for the sum of “interval_duration”) and then do a Transformation in Grafana:

image
image1253×627 45.1 KB