Multiple Aggregation Projections in fluxql

Hi,

Trying to recreate some of the basics in Flux compared to InfluxQL is quite a stretch at present.

I can’t work out how to ask for multiple projections.

select mean(temp) as temp_mean, count(temp) as num_points from my_measurement

I’m not sure how to go about this in Flux.

Pointers appreciated.

Thanks

Rob

Hello @robshep952,
Welcome! Great question.
One way is to:

count = from(bucket: "cats-and-dogs")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "bunny" or r["_measurement"] == "cats")
  |> filter(fn: (r) => r["_field"] == "young")
  |> count(column: "_value")

mean = from(bucket: "cats-and-dogs")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "bunny" or r["_measurement"] == "cats")
  |> filter(fn: (r) => r["_field"] == "young")
  |> mean(column: "_value")

join(tables: {mean: mean, count: count}, on: ["_time", "_field"], method: "inner")

However, I feel like there should be a way to do this without joins. I’m looking into it and I’ll get back to you if learn anything. Thank you!

There are a few things they could do.
Union and pivot two streams:

  |> range(start: -1d)
  |> filter(fn: (r) => r._field == "temp")
temp_mean = data |> mean() |> set(key: "_field", as: "temp_mean")
num_points = data |> count |> set(key: "_field", as: "num_points")
union(tables: [temp_mean, num_points])
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  1. Use reduce to create a custom aggregate that does both at the same time:
data = from(bucket: "example-bucket")
  |> range(start: -1d)
  |> filter(fn: (r) => r._field == "temp")
  |> reduce(
    identity: {num_points: 0, sum:0.0, temp_mean:0.0}
    fn: (r) => ({
      num_points: accumulator.num_points + 1,
      sum: r._value + accumulator.sum,
      temp_mean: (accumulator.sum + r._value) / (float(v: num_points + 1))
    })
  )
  |> drop(columns: ["sum"])