@roba You could use reduce()
to create a custom function that returns the min, max, and mean of tables in the input stream, then use that to calculate the min, max, and mean values of the flow and temperature. The credit count would be a just a straight sum with some column duplication to give the data something to join on. You would then join the three streams of tables together. It’ll save you a couple lines and from having to read the data so many times in a single query:
minMaxMean = (tables=<-) =>
tables
|> reduce(
identity: {count: 0.0, sum: 0.0, min: 0.0, max: 0.0, mean: 0.0},
fn: (r, accumulator) => ({
count: accumulator.count + 1.0,
sum: r._value + accumulator.sum,
min: if accumulator.count == 0.0 then r._value else if r._value < accumulator.min then r._value else accumulator.min,
max: if accumulator.count == 0.0 then r._value else if r._value > accumulator.max then r._value else accumulator.max,
mean: (r._value + accumulator.sum) / (accumulator.count + 1.0)
})
)
|> drop(columns: ["count", "sum"])
|> duplicate(column: "_stop", as: "_time")
baseData = from(bucket: "mybucket")
|> range(start: startTime, stop: stopTime)
|> filter(fn: (r) => r.measurement == "MyMeasurement")
temperature = baseData
|> filter(fn: (r) => r._field == "temperature")
|> minMaxMean()
flow = baseData
|> filter(fn: (r) => r._field == "flow")
|> minMaxMean()
credits = baseData
|> filter(fn: (r) => r._field == "credit")
|> set(key: "_field", value:"total_credits")
|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
|> sum()
|> duplicate(column: "_stop", as: "_time")
join1 = join(tables: {temperature: temperature, flow: flow}, on: ["_time"])
join(tables: {join1: join1, credits: credits})