[InfluxQL to Flux] show the calculated column in graph

Fluxlang
,
1

I am trying to migrate influx from 1.8 to 2.2, but I got a problem for one InfluxQL.
The original influxQL is

SELECT mean("markPrice") AS "markPrice", mean("realPrice") AS "expectedPrice",  mean("markPrice")-mean("realPrice") AS "timeValue"  FROM "mydb"."autogen"."MoveData" WHERE time > now() - 24h AND time < now() AND "type"='DAYMove' GROUP BY time(:interval:) FILL(null)

It’s a simple query and the graph will show 3 lines, one is calculated with mean(“markPrice”)-mean(“realPrice”)

Then I tried to rewrite with flux, but can’t figure out how to draw the calculated line in graph.

WX20220516-150850@2x
WX20220516-150850@2x2168×1330 253 KB

I’m new for FLUX, and I have read the document, but still not find out the answer.

2

use Pivot and map() function | Flux 0.x Documentation function to calculate it.

first add this line after line no 6

pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")

then

|> map(fn: (r) =>
    ({
            r with
            _value: (r.markPrice - r.realPrice)
  
  }))

It will calculate mean(markPrice) - mean(realPrice)

1 Like
3

thanks for your reply. I tried this before I post this thread. My problem is how display all the 3 lines in the graph, after run the updated script, only one line is plotted.

WX20220516-170828@2x
WX20220516-170828@2x1904×1564 112 KB 

from(bucket: "mydb/autogen")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "MoveData")
  |> filter(fn: (r) => r["_field"] == "markPrice" or r["_field"] == "realPrice")
  |> filter(fn: (r) => r["type"] == "DAYMove")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) =>
      ({
              r with
              _value: (r.markPrice - r.realPrice)
    
    }))
  |> yield(name: "mean")
4

I want to show something like is

111
1112182×814 131 KB

5

Write two queries

query1 = from(bucket: "mydb/autogen")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "MoveData")
  |> filter(fn: (r) => r["_field"] == "markPrice" or r["_field"] == "realPrice")
  |> filter(fn: (r) => r["type"] == "DAYMove")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) =>
      ({
              r with
              _value: (r.markPrice - r.realPrice)
    
    }))
  |> yield(name: "mean")


query2 = from(bucket: "mydb/autogen")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "MoveData")
  |> filter(fn: (r) => r["_field"] == "markPrice" or r["_field"] == "realPrice")
  |> filter(fn: (r) => r["type"] == "DAYMove")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
 
  |> yield(name: "last")

It will plot all three graphs.

1 Like