CODEWITHSUNDEEP
influxdbflux
Malik Rizwan
Malik Rizwan

Reputation: 787

What is the equivalent of SELECT first(column), last(column) in Flux Query Language?

What would be equivalent flux queries for

SELECT first(column) as first, last(column) as last FROM measurement ?

SELECT last(column) - first(column) as column FROM measurement ?

(I am referring to FluxQL, the new query language developed by InfluxData)

There are first() and last() functions but, I am unable to find the example to use both in same query.

These are the documentation for FluxQL for better reference:

https://docs.influxdata.com/flux/v0.50/introduction/getting-started

https://v2.docs.influxdata.com/v2.0/query-data/get-started/

Upvotes: 0

Views: 2741

Answers (1)

gMale
gMale

Reputation: 17895

If you (or someone else who lands here) just wanted the difference between the min and max values you would want the built-in spread function.

The spread() function outputs the difference between the minimum and maximum values in a specified column.

However, you're asking for the difference between the first and last values in a stream and there doesn't seem to be a built-in function for that (probably because most streams are expected to be dynamic in range). To achieve this, you could either write a custom aggregator function like in a similar answer. Or you can join two queries together, then take the difference:

data = from(bucket: "example-bucket") |> range(start: -1d) |> filter(fn: (r) => r._field == "field-you-need")

temp_earlier_number = data |> first() |> set(key: "_field", value: "delta")
temp_later_number = data |> last() |> set(key: "_field", value: "delta")

union(tables: [temp_later_number, temp_earlier_number])
  |> difference()

What this does is create two tables with a field named delta and then join them together, resulting in a table with two rows--one representing the first value and the other representing the last. Then we take the difference between those two rows. If you don't want negative numbers, just be sure to subtract in the correct order for your data (or use math.abs).

Upvotes: 3

Related Questions