My query has to ignore all data before 2023-08-06T17:33:36.76Z (when STATUS is equal 4).
I tried to join by time (Grafana can do this, but I can’t reproduce this join by time on flux language), tried a full join, outer join, but stucked on join.
Anyone can help me advance on this topic?
I wanna create a state timeline for STATUS values if TYPE is 4.
there are 2 ways to accomplish this, the performance may vary significatively.
Pivot
from(bucket: "real-data")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "STATUS" or r["_measurement"] == "TYPE")
|> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
// I suggest not forgetting the windows to make your query efficient. choose the every parameter you preffer I.E. 10s
|> pivot(rowKey:["_time"], columnKey: ["_measurement"], valueColumn: "_value")
|> filter(fn: (r) => r["TYPE"] == 4)
|> yield(name: "last")
Join
The Key to make Join work is that you should have 2 different queries and the grouping should be the same.
import "join"
Status =
from(bucket: "real-data")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
// I suggest not forgetting the windows to make your query efficient. choose the every parameter you preffer I.E. 10s
|> filter(fn: (r) => r["_measurement"] == "STATUS" )
|> group()
Type =
from(bucket: "real-data")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false) // I suggest not forgetting the windows to make your query efficient. choose the every parameter you preffer I.E. 10s
|> filter(fn: (r) => r["_measurement"] == "TYPE" )
|> filter(fn: (r) => r["_value"] == 4 )
|> group()
Result =
join.left(
left: Type,
right: Status,
on: (l, r) => l._time == r._time,
as: (l, r) => ({l with _valule2: r._value}),
)
|> yield(name: "last")
