How do I optimise this query?

Fluxlang
,
1

It seems that either I am doing this wrong (likely) or FluxLang is way more verbose than InfluxQL. How can I convert this InfluxQL query:

SELECT mean("Current") FROM "APCUps" WHERE $timeFilter GROUP BY time($__interval), "Name" fill(null)

to FluxLang better than what I did below (below works, but it is much longer and runs slightly slower):

  from(bucket: "power_highres")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r._measurement == "APCUps" and r._field == "Current")
    |> group(columns: ["Name"])
    |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> aggregateWindow(column: "Current", every: v.windowPeriod, fn: (column, tables=<-) => tables |> mean(column:column))
    |> map(fn: (r) => ({_time: r._time, _value: r.Current, Name: r.Name }))
2

Flux can tend to be a bit more verbose than InfluxQL, but in this case I think it’s possible to make this query more concise.

You could remove pivot from the query, since you are selecting just one field. Typically pivot us used to put several fields into the same table.

A more concise query would look like this:

  from(bucket: "power_highres")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r._measurement == "APCUps" and r._field == "Current")
    |> group(columns: ["Name"])
    |> aggregateWindow(every: v.windowPeriod, fn: mean)

This will leave you with a table with columns _time, _value and Name, where the contents of _value are the values in the Current field.

1 Like
3

Thanks for that - I could swear I tried it the first time like that but I could not see the Name value… This works. If I may - I have a similar but slightly different query that I tried to convert and it works, but it is a lot slower and much more verbose. In thise case I need the map as I need to perform a calculation first. Any ideas how to make this better? I tried the simpler non-pivot approach but do not get values out:

from(bucket: "power_highres")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) =>
    r._measurement == "APCUps" and
    (r._field == "LoadPerc" or r._field == "TotalActivePower")
  )
  |> group(columns: ["Name"])
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with _value: float(v: r.LoadPerc)  / 100.0 * float(v: r.TotalActivePower) }))
  |> aggregateWindow(column: "_value", every: v.windowPeriod, fn: (column, tables=<-) => tables |> mean(column:column))
  |> keep(columns: ["_time", "_value", "Name"])
4

You could have the map after the aggregate. In this way you do the math on a smaller number of rows. This introduces an approximation. You have to consider if it is acceptable for your application.