-
Query for fetching data for windows
I have a table “orders” which looks like this:
sym | transactTime | orderId | price | qty
This table will only have the data for 1 day.
now I am provided with two lengths(timespan values) length1: 20 mins , length2: 10 mins
This will be used to create windows, let’s say
window 1:
start:00:00:00:000000000
end: start + length1
window 2:
start: end of window 1 + length2
end: start + length1
this will go on till end=23:59:59:999999999
I need to fetch the data for each sym, for each window.
Can someone help with this?
-
2 Replies
-
You could make windows like so:
q)start:0D q)duration:1D q)length1:0D00:20 q)length2:0D00:10 q)windows:{flip (0;y-1)+\:y*til `long$x div y}'[duration;(length1;length2)] q)(first;last)@\:windows 0 0D00:00:00.000000000 0D00:19:59.999999999 0D23:40:00.000000000 0D23:59:59.999999999 q)(first;last)@\:windows 1 0D00:00:00.000000000 0D00:09:59.999999999 0D23:50:00.000000000 0D23:59:59.999999999 q)t:([] time:100?1D;sym:100?`A`B`C) time sym ------------------------ 0D11:37:38.441374450 A 0D16:23:13.911677598 A 0D03:40:38.809463381 C 0D12:50:31.973013281 B 0D10:58:33.142468035 C 0D00:11:36.601854264 BQuery for small tables for each sym for each windows:
syms:exec distinct sym from t q){[x;y;z] select from x where sym=y,time within z}[t](.)/:syms cross enlist each windows 0 +`time`sym!(`timespan$();`symbol$()) +`time`sym!(,0D03:19:44.361107647;,`A) +`time`sym!(0D03:20:29.103351384 0D03:28:31.211699992;`A`A) ... +`time`sym!(,0D20:47:50.484825968;,`B) +`time`sym!(,0D21:14:37.927203476;,`B) +`time`sym!(0D21:39:42.773356139 0D21:37:00.400066673;`B`B) +`time`sym!(`timespan$();`symbol$()) +`time`sym!(,0D22:09:45.092769563;,`B) -
@laura – This was a format testing reply – can be deleted. Thanks
Log in to reply.