Query for fetching data for windows

Community Support
,
1

https://learninghub.kx.com/forums/topic/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

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 B

Query 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)
3

@laura - This was a format testing reply - can be deleted. Thanks