Aggregating Best Bid/Offer and Quantity By Time

Community Support
1

Hi guys

If I have a query such as the following:

select price, qty, count i by (60000000000j xbar timestamp), mkt, side from tab

That returns a result set such as:

time                                            mkt         side     price                                      qty

2011.08.24D23:59:00.000000000MKTB    1.04595 1.04605 1.046071000000 2000000 3000000

2011.08.24D23:59:00.000000000MKTS    1.04635 1.04645 1.046552000000 1000000 1000000

…

How can I sweep the result set for the best price and corresponding quantity by time (Noting that price and qty are lists)?

E.g. in the first row, for “B”, the best price is 1.04595 (min) and the corresponding quantity is 1000000 (i.e. the corresponding entry in the quantity list)

For row 2 (“S”) , the best price is 1.04655 and the corresponding quantity entry is 1000000 …

Im struggling to find a way to do this elegantly for both cases without having to sweep through the table multiple times - anyone got a nice way to do this?

Cheers

– R

2

Might not?be??a good way but i can think something like that

select (((side=B)*1)+(side=S)*(-1)) #’ price from t:( side:(B,S); price:((1;1.05);(1.08;1.1)))

Prashant

3

A sample table:

q)b:select p,q by (0D00:05 xbar t),s from (t:2013.10.01+100?0D01;p:100?100;q:100?1000;s:100?BS)

q)b

t s| p q

-------------------------------| ------------------------------------------------------

2013.10.01D00:00:00.000000000 B| 50 11 74 62 42 4 454 279 885 328 544 157

2013.10.01D00:00:00.000000000 S| 87 92 36 48 64 47 939 122 51 210 963 107

2013.10.01D00:05:00.000000000 B| 43 94 46 253 234 787

2013.10.01D00:05:00.000000000 S| 13 24 28 75 665 454 903 184

2013.10.01D00:10:00.000000000 B| 71 16 288 612

2013.10.01D00:10:00.000000000 S| 75 30 44 13 26 2 977 359 78 531

2013.10.01D00:15:00.000000000 B| 75 37 25 1 362 269 814 92

2013.10.01D00:15:00.000000000 S| 13 53 249 579

2013.10.01D00:20:00.000000000 B| 86 25 37 84 4 33 147 525 20 351 321 425

The following query should return what you asked for:

q)select t,s,bestp:p@'bi,bestq:q@‘bi from update bi:s {1#y?$[x=`B;min y;max y]}’ p from b

t s bestp bestq

-------------------------------------------

2013.10.01D00:00:00.000000000 B 4 157

2013.10.01D00:00:00.000000000 S 92 122

2013.10.01D00:05:00.000000000 B 43 253

2013.10.01D00:05:00.000000000 S 75 184

2013.10.01D00:10:00.000000000 B 16 612

2013.10.01D00:10:00.000000000 S 75 2

2013.10.01D00:15:00.000000000 B 1 92

2013.10.01D00:15:00.000000000 S 53 579

Huy

4

You can try a functional select this way:

?[tab;();`time`mkt`side!((xbar;60000000000j;`time);`mkt;`side);`price`qty!{(x;parse"where price='?[side in `B;min price;max price]")}@/:`pirce`qty]

its the same as:

select price where price='?[side in B;min price;max price], qty where price='?[side in B;min price;max price] by (60000000000j xbar time), mkt, side from tab