Calculating duration of boolean events using Flux

Fluxlang
6

Thanks @Jarifed

I tried many, MANY combinations of different ideas (joins, unions, pivots, etc.)…so many that it has all become a blur. BUT, in the end, pieces of your scripts helped me figure it out. Here is the final query. I am pretty sure there is a more efficient way to calculate the percent “on” (or true") for a machine, but this works for now.

t1 = from(bucket: "AMPdata")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "SingleRotaryFurnaceElementData")
  |> filter(fn: (r) => r["EquipNumber"] == "8")
  |> filter(fn: (r) => r["MeasType"] == "actual")
  |> filter(fn: (r) => r["_field"] == "Feedrate")
  |> map(fn: (r) => ({ r with _value: if r._value >= 1 then
                "true"
            else
                "false",
        }),
    )
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> filter(fn: (r) => r["Feedrate"] == "false")
  |> count(column:"Feedrate")

t2 = from(bucket: "AMPdata")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "SingleRotaryFurnaceElementData")
  |> filter(fn: (r) => r["EquipNumber"] == "8")
  |> filter(fn: (r) => r["MeasType"] == "actual")
  |> filter(fn: (r) => r["_field"] == "Feedrate")
  |> map(fn: (r) => ({ r with _value: if r._value >= 1 then
                "true"
            else
                "false",
        }),
    )
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> filter(fn: (r) => r["Feedrate"] == "true")
  |> count(column:"Feedrate")

result = join(tables: {t1: t1, t2: t2}, on: ["EquipNumber"])
 |> map(fn: (r) => ({r with percent_true: float(v:r.Feedrate_t2) / (float(v:r.Feedrate_t2)+ float(v:r.Feedrate_t1) ) }))
 |> keep(columns: ["percent_true"])
 |> yield(name: "percent_running")
1 Like