KDB+/q: how to implement an xbar aggregation after an xgroup aggregation on kdb table

Question

I'm trying to run an xbar aggregation on trade data after an xgroup aggregation however I can't get seem to get it right:

I am trying to take a table of the following format (consisting of trades):

time                    side amount price  exchange
------------------------------------------------
2019.08.22T12:01:04.389 sell 54     9953.5 exchange1
2019.08.22T12:01:05.034 sell 205    9953.5 exchange1
2019.08.22T12:01:05.754 sell 150    9953.5 exchange1
2019.08.22T12:01:06.375 sell 516    9953.5 exchange1
2019.08.22T12:01:07.044 sell 100    9953.5 exchange1
2019.08.22T12:01:07.691 sell 1500   9953.5 exchange1
2019.08.22T12:01:08.393 sell 300    9953.5 exchange1
2019.08.22T12:01:09.005 sell 2254   9953.5 exchange2
2019.08.22T12:01:09.625 sell 500    9957.5 exchange2
2019.08.22T12:01:10.448 sell 5330   9953.5 exchange2
2019.08.22T12:01:11.065 sell 260    9953.5 exchange2
2019.08.22T12:01:11.701 sell 38     9953.5 exchange2
2019.08.22T12:01:12.404 sell 44     9953.5 exchange2
2019.08.22T12:01:12.974 sell 41     9953.5 exchange2

on one hand I would like to use xbar to group them into time buckets of 5 minutes i.e.

select price, amount by 5 xbar time.minute from trades

and on the other I am trying to group them by side and exchange i.e.

exchangeside xgroup trades

I am looking for the best method to combine the above 2 methods such that I have 4 groups bucketed/windowed/aggregated by time i.e.

exchange1 sell time1 price1 amt1
               time2 price2 amt2
exchange1 buy  time1 ...
               time2 ...
exchange2 sell time1 ...
               time2 ...
exchange2 buy  time1 ...
               time2 ...

etc.

How would one succinctly achieve this? Thanks

Could you please provide an example table others could use, and give an example of your desired result — Callum Biggs
– Callum Biggs, Commented Aug 22, 2019 at 14:13

terrylynch · Accepted Answer · 2019-08-22 14:36:45Z

2

If you're trying to aggregate over 15min buckets with groupings then you can do it in the by clause:

trades:([]exch:100?`P`Q;sym:100?`IBM`MSFT;side:100?`B`S;time:asc 0D10:20+0D00:01*100?100;price:100?100.;size:100?1000);

q)select avg price, sum size by exch,side,sym,15 xbar time.minute from trades
exch side sym  minute| price    size
---------------------| -------------
P    B    IBM  10:30 | 34.14991 369
P    B    IBM  10:45 | 46.46884 1204
P    B    IBM  11:15 | 30.9058  1106
P    B    IBM  11:30 | 22.88752 1196
P    B    IBM  11:45 | 12.47049 494
...

edited Aug 22, 2019 at 14:36

answered Aug 22, 2019 at 14:25

terrylynch

14k1 gold badge15 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

James Over a year ago

Thanks ... really straight forward solution.

terrylynch Over a year ago

Ok, edited my answer to be more specific to your edited question

Callum Biggs Over a year ago

To build on this, you cannot use dot notation for temporal conversion in the select specification of a Q-SQL query because that is reserved for resolving linked columns

terrylynch Over a year ago

Hi, you could achieve that with a followup update to calculate sums, e.g.

update rolling:sums size by exch,side,sym from select avg price, sum size by exch,side,sym,15 xbar time.minute from trades

terrylynch Over a year ago

There isn't really any clean way to do it as a single select statement because it's an aggregation (rolling sum) of an aggregation (sum over minute buckets). I think you'll have to stick to storing the intermediary result

|

Collectives™ on Stack Overflow

KDB+/q: how to implement an xbar aggregation after an xgroup aggregation on kdb table

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related