4

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

Sithered
  • 481
  • 7
  • 23

1 Answers1

6

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
Thomas Smyth - Treliant
  • 4,993
  • 6
  • 25
  • 36
hellomichibye
  • 4,112
  • 2
  • 23
  • 23
  • Thank you. Can you explain what this line do please: w:-300000 0+\:trade.time – Sithered Oct 21 '16 at 06:57
  • 1
    Yes. it creates the sliding window for every row. which says current trade time minus 300000 milli seconds to 0/- 0 milli seconds (which is the trade time). Print w to the console and you will get an idea. – hellomichibye Oct 21 '16 at 07:25
  • trade.time does not work. I have the following error: nyi. Possibly this error refers to not yet implemented - suggests the operation you are tying to do makes sense but it has not yet been implemented. Is it the same than trade[`time]? – Sithered Oct 21 '16 at 08:15
  • That's strange, when I print w:-5000 0+\:tradeWithDV01[`time], I get two timestamps lists that are exactly the same. Shouldn't there be a 5 sec lag between them? – Sithered Oct 21 '16 at 13:01
  • 1
    what's the type of your list? run type tradeWithDV01[`time] – hellomichibye Oct 21 '16 at 13:09
  • It's timespan (16h) – Sithered Oct 21 '16 at 13:15
  • 1
    timespan has nano seconds precision. time only millis. sou you need to add a few zeros. w:-300000000000 0+\:tradeWithDV01[`time] – hellomichibye Oct 22 '16 at 09:16
  • How would you get 5min moving average at the end of each minute? – Ryan Mar 09 '21 at 21:14