How to calculate OHLCV data from tick data in influxdb?

Question

I have some tick data store in influxdb, protocol line like below, (Note: lastPrice is realtime price at that time, totalVolume is cumulative trade quantity from the begining of the day)

tick,contract=FU2301 lastPrice=10.2,totalVolume=100
tick,contract=FU2301 lastPrice=10.1,totalVolume=110
tick,contract=FU2301 lastPrice=10.3,totalVolume=150
...
tick,contract=FU2301 lastPrice=9.8,totalVolume=290

I am trying to calculate 1 minus OHLCV from tick data. (Note: the volume here is the trade quantity of every minus)

time measurement contract open high low close volume
----
xxxx tick        FU2301   10.2 10.3 9.0 9.3   10
xxxx tick        FU2301   10.2 10.3 9.1 9.2   40
xxxx tick        FU2301   10.1 10.4 9.0 9.1   20

so my flux script like below, but it didn't work expected.

data = from(bucket: "mk_data_test")
  |> range(start: -12h, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "tick")
  |> filter(fn: (r) => r["contract"] == "FU2301")
  |> window(every: 1m)

volumeData = data |> filter(fn: (r) => r["_field"] == "totalVolume")
priceData = data |> filter(fn: (r) => r["_field"] == "lastPrice")

max = priceData |> max() |> set(key: "_field", value: "max")
min = priceData |> min() |> set(key: "_field", value: "min")
open = priceData |> first() |> set(key: "_field", value: "open")
close = priceData |> last() |> set(key: "_field", value: "close")
// this expression didn't work excepted
volume = volumeData |> first() |> difference() |> set(key: "_field", value: "volume")

union(tables: [max, min, open, close, volume])
  |> pivot(rowKey: ["_start"], columnKey: ["_field"], valueColumn: "_value")

Answer 1

Are you struggled with the volume dataset's row number? By default, "for each input table with n rows, difference() outputs a table with n - 1 rows."

If you want to align volume dataset with max, min, open and close, you could turn on the keepFirst flag:

volume = volumeData |> difference(keepFirst: true) |> set(key: "_field", value: "volume")

See more details here.