Sliding window on time in KDB/Q
aggregate, aggregate-functions, kdb, q-lang, sliding-window
Solution
do you want to aggregate to fixed time buckets `by` and `xbar` are your friend: http://code.kx.com/q/ref/arith-integer/#xbar
trade: ([] time:`time$(10:00 10:01 10:03 10:07 10:09); price:`float$(12.1 12.6 12.4 12.4 12.9); size:`int$(5 6 10 34 2))
select last price, sum size by 5 xbar time.minute from trade
minute| price size
------| ----------
10:00 | 12.4 21
10:05 | 12.9 36
if you want to go back 5 minutes in time for every row a window join is what your are looking for: http://code.kx.com/q/ref/joins/#wj-wj1-window-join
w:-300000 0+\:trade.time
wj1[w;`time;trade;(trade;(last;`price);(sum;`size))]
time price size
-----------------------
10:00:00.000 12.1 5
10:01:00.000 12.6 11
10:03:00.000 12.4 21
10:07:00.000 12.4 44
10:09:00.000 12.9 36
Problem
There are some functions in Q/KDB that let us aggregate on a sliding window (msum, mavg, etc.). But these functions takes the number of previous rows into account. I'd like a function that would aggregate on a sliding window but with time instead of number of rows. For example on the last 5 minutes. Do such functions exist? If not, how can I design it? I don't want to use a while loop, as it will slow down my program too much because of the huge amount of data. Thank you for your help