I have generated this flux query detailed below.
It returns me exactly the answer I want.
I’d like to try to make it quicker. At the moment it is taking ~2sec and in an ideal world, I’d like to get it sub sec. Can anyone see any way I can improve it?
I’ve ready up on making sure filters on fields are applied before tags, that helped a little.
ts1 = from(bucket: “spreadData”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “Spreads”)
|> filter(fn: (r) => r[“_field”] == “SpreadA”)
|> filter(fn: (r) => r[“DoW”] == “Weekday”)
|> filter(fn: (r) => r[“Spread”] == “Type1”)
|> filter(fn: (r) => r[“MBST999”] == “1” or r[“MBST90”] == “1”)
|> filter(fn: (r) => r[“_value”] > -10)
|> keep(columns: [“SpreadDate”, “_value”, “_time”])
|> group(columns: [“SpreadDate”])
|> first()
|> group()
ts2 = from(bucket: “spreadData”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “Spreads”)
|> filter(fn: (r) => r[“_field”] == “SpreadB”)
|> filter(fn: (r) => r[“DoW”] == “Weekday”)
|> filter(fn: (r) => r[“Spread”] == “Type1”)
|> keep(columns: [“SpreadDate”, “_value”, “_time”])
|> group(columns: [“SpreadDate”])
join(tables: {s1: ts1, s2: ts2}, on: [“SpreadDate”], method: “inner”)
|> filter(fn: (r) => r[“_time_s2”] > r[“_time_s1”])
|> map(fn: (r) => ({ r with _value: r._value_s2}))
|> min()
|> group()
|> yield()
