How to use multiple arguments in kdb where is the request?

Question

I want to select a maximum of items from a table over the next 5, 10, 30 minutes, etc. I suspect this is not possible with multiple elements in the where clause. Using both normal < and </: fails. My code / request is below:

 `select max price from dat where time</: (09:05:00; 09:10:00; 09:30:00)` 

Any ideas what I'm doing wrong here? The idea is to get the maximum price for each row in the next 5, 10, 30 ... minutes of time in this row, and not just the 3 maximum prices in the entire table.

 select max price from dat where time</: time+\:(5 10 30) 

This will not work, but should give a general idea.

To clarify, I want to calculate the maximum price at 5, 10, 30 minute intervals from the time [i] of each row of the table. Therefore, for each table, the maximum price is within x + 5, x + 10, x + 30 minutes, where x is the time record in this row.

Answer 1

It works, but takes a lot of time. For 20 thousand records, ~ 20 seconds, too much !. Any way to make it faster

  dat: update tmlst: time+\:mtf*60 from dat; dat[`pxs]: {[x;y] {[x; ts] raze flip raze {[x;y] select min price from x where time<y}[x] each ts }[x; y`tmlst]} [dat] each dat; 

Answer 2

You can try something like this:

 select c1:max price[where time <09:05:00],c2:max price[where time <09:10:00],c3:max price from dat where time< 09:30:00 

You can parameterize this query as you like. Therefore, if you have a list of times, l: 09: 05: 00 09:10:00 09:15:00 09:20:00 ... You can create a function using the functional request form above to work for different lengths l, something like:

 q)f:{[t]?[dat;enlist (<;`time;max t);0b;(`$"c",/:string til count t)!flip (max;flip (`price;flip (where;((<),/:`time,/:t))))]} q)fl 

You can expand f to perform different functions instead of max, work for different tables, etc.

Answer 3

this creates a dictionary of steps to display time on your buckets:

 q)-1_select max price by(`s#{((neg w),x)!x,w:(type x)$0W}09:05:00 09:10:00 09:30:00)time from dat 

you can also ab use wj :

 q)wj[{(prev x;x)}09:05:00 09:10:00 09:30:00;`time;([]time:09:05:00 09:10:00 09:30:00);(delete sym from dat;(max;`price))] 

if all your buckets are the same size, this is much simpler:

 q)select max price by 300 xbar time from dat where time<09:30:00 / 300-second (5-min) buckets