0

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")
Heo
  • 33
  • 1
  • 5

1 Answers1

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.

Munin
  • 1,576
  • 2
  • 19
  • I have tried to add **keepFirst**, but it still prints nothing. What I want is take the first totalVolume in every 1 minus, and calculate difference of them. – Heo Oct 24 '22 at 13:14
  • You might need to get the first value and assign it to a variable then put it this value in the fill function since keepFirst will assign a null value to the first record. – Munin Oct 25 '22 at 08:27
  • Yes, you are right, difference() just produce n-1 records unless I add keepFirst argument and using a value replace the default null value. And I found difference function can only work within a window, can't work across different window. I should unwindw before using difference function ` volumeData |> first() |> window(every: inf) |> difference(keepFirst: true) |> .... ` – Heo Oct 26 '22 at 07:52