InfluxDB Flux - Getting last and first values as a column

Question

I am trying to create two new columns with the first and last values using the last() and first() functions. However the function isn’t working when I try to map the new columns. Here is the sample code below. Is this possible using Flux?

from(bucket: "bucket")

  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "price_info")
  |> filter(fn: (r) => r["_field"] == "price")
  |> map(fn: (r) => ({r with  
  open: last(float(v: r._value)),
  close: first(float(v: r._value)),
  })

No sorry. I have not been able to figure this out. I also asked in InfluxDB forums but no response. I went back to using influxQL where this is supported. — MBasith, Apr 03 '21 at 23:44

score 2 · Answer 1 · answered Jul 03 '21 at 14:50

I am not answering directly to the question, however it might help. I wanted to perform some calculation between first and last, here is my method, I have no idea if it is the right way to do.

The idea is to create 2 tables, one with only the first value and the other with only the last value, then to perform a union between both.

data = from(bucket: "bucket")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "plop")

l = data
|> last()
|> map(fn:(r) => ({ r with _time: time(v: "2011-01-01T01:01:01.0Z") }))

f = data
|> first()
|> map(fn:(r) => ({ r with _time: time(v: "2010-01-01T01:01:01.0Z") }))

union(tables: [f, l])
|> sort(columns: ["_time"])
|> difference()

For an unknown reason I have to set wrong date, just to be able to sort values and take into account than first is before last.

You saved my day with this solution, thanks. – DigitalWM Sep 28 '22 at 17:59 — DigitalWM, Sep 28 '22 at 17:59

score 2 · Answer 2 · answered Dec 30 '21 at 17:42

Just a quick thank you. I was struggeling with this as well. This is my code now:

First = from(bucket: "FirstBucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "mqtt_consumer")
  |> filter(fn: (r) => r["topic"] == "Counters/Watermeter 1")
  |> filter(fn: (r) => r["_field"] == "Counter")
  |> first()
  |> yield(name: "First")

Last = from(bucket: "FirstBucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "mqtt_consumer")
  |> filter(fn: (r) => r["topic"] == "Counters/Watermeter 1")
  |> filter(fn: (r) => r["_field"] == "Counter")
  |> last()
  |> yield(name: "Last")

union(tables: [First, Last])
|> difference()

score 0 · Answer 3 · answered Oct 26 '22 at 06:05

Simple answer is to use join (You may also use old join, when using "new" join remember to import "join")

Example:

import "join"

balance_asset_gen = from(bucket: "telegraf")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "balance")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)

balance_asset_raw = from(bucket: "telegraf")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "balance_raw")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)

// In my example I merge two data sources but you may just use 1 data source
balances_merged = union(tables: [balance_asset_gen, balance_asset_raw])
    |> group(columns:["_time"], mode:"by")
    |> sum()

f = balances_merged |> first()
l = balances_merged |> last()

// Watch out, here we assume we work on single TABLE (we don't have groups/one group)
join.left(
    left: f,
    right: l,
    on: (l, r) => l.my_tag == r.my_tag, // pick on what to merge e.g. l._measurement == r._measurement 
    as: (l, r) => ({
                    _time: r._time,
                    _start: l._time,
                    _stop: r._time,
                    _value: (r._value / l._value), // we can calculate new field
                    first_value: l._value,
                    last_value: r._value,
                    }),
    )
    |> yield()

InfluxDB Flux - Getting last and first values as a column

3 Answers3