Add weight by second in DolphinDB wj function

Question

I want to join two tables with DolphinDB window join. The window is -10:-1, meaning the window size is 10 seconds. My question is the number of values in a window is random and I want to add weights by time.

For example, the weighted coefficients range from 1 to 10, if there is no data at the 2nd second in the window, then the weighted coefficients are (1, 3, 4, 5, 6, 7, 8, 9, 10). How to add the weight to the calculation?

Answer 1

You can define a user-defined aggregate function in DolphinDB and call it in window join. Use function wavg to obtain the time difference as the weighted coefficients for calculation.

t1 = table(`A`A`B as sym, 09:56:06 09:56:07 09:56:06 as time, 10.6 10.7 20.6 as price)
t2 = table(take(`A,10) join take(`B,10) as sym, take(09:56:00+1..10,20) as time, (10+(1..10)\10-0.05) join (20+(1..10)\10-0.05) as bid, (10+(1..10)\10+0.05) join (20+(1..10)\10+0.05) as offer, take(100 300 800 200 600, 20) as volume);
t1;
defg wg(time,price){
return wavg(price,time-min(time)+1)
}
wj(t1, t2, -5s:0s, <wg(time,bid)>, `sym`time);