Flux Multiple Query Join

Fluxlang
1

Hi
I am facing a problem with my query and can’t get my head around it.

I’m trying to get some values between a start and a stop and i should have the last value before the start and the first value after the stop also… and i’m trying to achieve this with just one query and one return…

It looks like this:

from(bucket:"data")
|> range(start:1629349200,stop:1629352800)
|> filter(fn:(r) => r._measurement == "trend_history" and r._field == "nValue")
|> filter(fn: (r) => r["nTrendId"] == "10003")
|> last()
|> yield(name: "10003_0")

from(bucket:"data")
|> range(start:1629352800,stop:1629381600)
|> filter(fn:(r) => r._measurement == "trend_history" and r._field == "nValue")
|> filter(fn: (r) => r["nTrendId"] == "10003")
|> yield(name: "10003_1")

from(bucket:"data")
|> range(start:1629378000,stop:1629385200)
|> filter(fn:(r) => r._measurement == "trend_history" and r._field == "nValue")
|> filter(fn: (r) => r["nTrendId"] == "10003")
|> first()
|> yield(name: "10003_2")

with this query, i get 1 return with 3 csv in it. But i like to get only one csv like return…
so that i only need:

|> yield(name: "10003")

I hope someone understands what i’m trying to achieve

Thank you in advance

2

Hello @Donoli,

I’m trying to get some values between a start and a stop and i should have the last value before the start and the first value after the stop also… and i’m trying to achieve this with just one query and one return…

Can you just query slightly outside of the start and stop? And then find the first value outside of your start and stop?
Something like:

import "date"
data = from(bucket: "noaa")
  |> range(start:(1627653004 - 1000 ), stop: ( 1627655004 + 1000)) 
  |> filter(fn: (r) => r["_measurement"] == "average_temperature")
  |> filter(fn: (r) => r["_field"] == "degrees")
  |> filter(fn: (r) => r["location"] == "santa_monica")
//   |> yield(name: "data exxtended start and stop boundary")

first = data 
  |> filter(fn: (r) => r._time < time(v:1627653004000000000) )
  |> last()
  |> findRecord(fn: (key) => true, idx: 0)
//   |> yield(name: "first value before start") 

last = data
  |> filter(fn: (r) => r._time > time(v:1627655004000000000))
  |> limit(n: 1) 
  |> findRecord(fn: (key) => true, idx: 0)
//   |> yield(name: "last value after stop")    

time_before_start = first._time 
time_after_stop = last._time 

from(bucket: "noaa")
  |> range(start:time_before_start, stop: time_after_stop) 
  |> filter(fn: (r) => r["_measurement"] == "average_temperature")
  |> filter(fn: (r) => r["_field"] == "degrees")
  |> filter(fn: (r) => r["location"] == "santa_monica")

Does that do what you expect?
Here’s the annotated CSV:

#group,false,false,true,true,false,false,true,true,true
#datatype,string,long,dateTime:RFC3339,dateTime:RFC3339,dateTime:RFC3339,double,string,string,string
#default,data exxtended start and stop boundary,,,,,,,,
,result,table,_start,_stop,_time,_value,_field,_measurement,location
,,0,2021-07-30T13:33:24Z,2021-07-30T14:40:04Z,2021-07-30T13:35:14.035840903Z,70,degrees,average_temperature,santa_monica
,,0,2021-07-30T13:33:24Z,2021-07-30T14:40:04Z,2021-07-30T13:41:14.035840903Z,81,degrees,average_temperature,santa_monica
,,0,2021-07-30T13:33:24Z,2021-07-30T14:40:04Z,2021-07-30T13:47:14.035840903Z,84,degrees,average_temperature,santa_monica
,,0,2021-07-30T13:33:24Z,2021-07-30T14:40:04Z,2021-07-30T13:53:14.035840903Z,76,degrees,average_temperature,santa_monica
,,0,2021-07-30T13:33:24Z,2021-07-30T14:40:04Z,2021-07-30T13:59:14.035840903Z,85,degrees,average_temperature,santa_monica
,,0,2021-07-30T13:33:24Z,2021-07-30T14:40:04Z,2021-07-30T14:05:14.035840903Z,71,degrees,average_temperature,santa_monica
,,0,2021-07-30T13:33:24Z,2021-07-30T14:40:04Z,2021-07-30T14:11:14.035840903Z,76,degrees,average_temperature,santa_monica
,,0,2021-07-30T13:33:24Z,2021-07-30T14:40:04Z,2021-07-30T14:17:14.035840903Z,89,degrees,average_temperature,santa_monica
,,0,2021-07-30T13:33:24Z,2021-07-30T14:40:04Z,2021-07-30T14:23:14.035840903Z,74,degrees,average_temperature,santa_monica
,,0,2021-07-30T13:33:24Z,2021-07-30T14:40:04Z,2021-07-30T14:29:14.035840903Z,84,degrees,average_temperature,santa_monica
,,0,2021-07-30T13:33:24Z,2021-07-30T14:40:04Z,2021-07-30T14:35:14.035840903Z,71,degrees,average_temperature,santa_monica

So you can use the csv.from() function if you want to test my script on your machine.

3

Hi Anaisdg

Thank you for your answer. Unfortunately i can’t say if the data before or after my query start/stop are just one minute, one hour or a whole year in distant. I send now three requests to get this data. But i will keep your answer in the back of my head.

Thank you again for your fast and helpful answer

4

@Donoli,
Sure no problem.
You might also find the timeShift() function useful timeShift() function | Flux 0.x Documentation

5

I think you can use the union() function. You’d assign each of the queries to a data stream, like data1 =from… data2=from… etc. without the yield function at the end, and then use the function union(tables: [data1, data2, data3]). See the example here:
union() function | InfluxDB OSS 2.0 Documentation (influxdata.com)