InfluxDB: keep min/max values while downsampling

Question

I am pretty new to InfluxDB. I have an application that collects data with 20kHz, which is written to the database.

At some point I need to query/show this data. Currently I achieve this with:

from(bucket: "test-bucket")
   |> range(start: 0; stop: now())
   |> filter(fn: (r) => r["_field"] == "sensorValue")
   |> aggregateWindow(every: 1s, fn: mean, createEmpty: false
   |> yield(name: "mean")

The problem I do have is the mean function. I do have data, which might jump up and down quite fast. So calculating the mean value of each window is not good for me here, because it smoothes the data to much.

What I want to achieve is to get the min and max value (and its corresponding timestamp) from every window.

For example if the 'window' has the following data:

value: 10 11 10 12 13 27 10 2 10 11
time:   0  1  2  3  4  5  6 7  8  9

I want to get for max (27, 5) and for min (2,7). Is there any function doing this? Or even better, is there any other downsampling function that keeps local extreme values?

What I have tried so far:

data = from(bucket: "test-bucket")
   |> range(start: 0; stop: now())
   |> filter(fn: (r) => r["_field"] == "sensorValue")

minData = data
   |> aggregateWindow(every: 1s, fn: min, createEmpty: false)

maxData = data
   |> aggregateWindow(every: 1s, fn: max, createEmpty: false)

 |> yield()

This will draw 2 series. Is there any chance to merge them? Or is my approach just completly wrong?

score 0 · Answer 1 · answered Nov 18 '22 at 07:49

There are two ways to do aggregateWindow for multiple functions.

One is to use reduce() to create a custom aggregate function that performs different aggregations. The problem here is that reduce() can't be pushed down and will be less performant.

The other is to create separate streams for each aggregation, AND union them together, and then pivot the data. Sample code is as below:

   data = from(bucket: "test-bucket")
       |> range(start: 0; stop: now())
       |> filter(fn: (r) => r["_field"] == "sensorValue")

   minData = data
       |> aggregateWindow(every: 1s, fn: min, createEmpty: false)

   maxData = data
       |> aggregateWindow(every: 1s, fn: max, createEmpty: false)

   union(tables: [minData, maxData])
       |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")

InfluxDB: keep min/max values while downsampling

1 Answers1