How to translate the following SQL to flux language?

soulmachine · March 30, 2021, 8:57pm

Previously I have a Grafana panel with the following SQL query:

SELECT abs(sum("volume_buy")-sum("volume_sell"))/sum("volume") FROM "kline_ext" WHERE $timeFilter GROUP BY time(5m), "pair" fill(0)'

Now I’m upgrading to InfluxDB 2, I’m struggling to rewrite the SQL above into flux language.

Any suggestions? Thanks a lot

soulmachine · March 30, 2021, 11:48pm

I found a very close answer:

from(bucket: "my-bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "kline_ext")
  |> filter(fn: (r) => r["_field"] == "volume_sell" or r["_field"] == "volume_buy")
  |> group(columns: ["_measurement", "_field", "pair"])
  |> aggregateWindow(every: 5m, fn: sum, createEmpty: false)
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with _value: (r.volume_buy - r.volume_sell)/(r.volume_buy + r.volume_sell) }))
  |> drop(columns: ["_start", "_stop", "volume_sell", "volume_buy"])