Aggregation first/last with reduce

Fluxlang
1

Hi
I try to get multiple aggregation (sum, avg, min, max, first, last), so I found the function reduce. Works well, but I’m puzzled about first and last. It seems that count begins from last record, so I get last with accumulator.count == 0

Example:
from(bucket: “”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == “VM”)
|> filter(fn: (r) => r["_field"] == “vRAM”)
|> group(columns: [“name”])
|> reduce(identity: {
sum: 0.0, count: 0, avg: 0.0, min: 0.0, max: 0.0, first: 0.0, last: 0.0
},
fn: (r, accumulator) => ({
sum: accumulator.sum + r._value,
count: accumulator.count + 1,
avg: (accumulator.sum + r._value) / float(v: accumulator.count + 1),
min: if accumulator.count == 0 then r._value else if r._value < accumulator.min then r._value else accumulator.min,
max: if accumulator.count == 0 then r._value else if r._value > accumulator.max then r._value else accumulator.max,
first: if accumulator.count == accumulator.count then r._value else accumulator.first,
last: if accumulator.count == 0 then r._value else accumulator.last,
}),
)

Has somebody experience about first / last with reduce? Thanks in advance.

23.07.21:
Sometimes, sort-order of “_time” is in disorder, that’s why first/last was wrong. So I have to add “|> sort(columns:[”_time"])".

This happens if the device is not continuous available. This is working well now:
from(bucket: “”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == “VM”)
|> filter(fn: (r) => r["_field"] == “vRAM”)
|> group(columns: [“name”])
|> sort(columns:["_time"])
|> reduce(identity: {
sum: 0.0, count: 0, avg: 0.0, min: 0.0, max: 0.0, first: 0.0, last: 0.0
},
fn: (r, accumulator) => ({
sum: accumulator.sum + r._value,
count: accumulator.count + 1,
avg: (accumulator.sum + r._value) / float(v: accumulator.count + 1),
min: if accumulator.count == 0 then r._value else if r._value < accumulator.min then r._value else accumulator.min,
max: if accumulator.count == 0 then r._value else if r._value > accumulator.max then r._value else accumulator.max,
first: if accumulator.count == 0 then r._value else accumulator.first,
last: if accumulator.count == accumulator.count then r._value else accumulator.last,
}),
)

2

Hi and welcome.

could you wrap your code in </> code blocks , not doing so makes it hard to read.

Can you create a simpler example that demonstrates the issue/unexpected behavior.

1 Like
3

Hello @AlainFricker,
It’s also worth mentioning that reduce() really shines for custom functions.
Otherwise you should consider using the following functions

min() 
max()
last()
count()
sum()

and invoking multiple yields instead to preserve pushdown patterns and increase performance.
You can read about pushdown patterns here:

(hurdle two ) Top 5 Hurdles for Intermediate Flux Users and Resources for Optimizing Flux | InfluxData

and here: