Given a set of price buckets (of varying sizes) as follows:
buckets:(
1000.0 1000.4 1000.9 1001.6 1002.5 1003.6 1004.9 1006.4 1008.1 1010; // min bucket price
1000.4 1000.9 1001.6 1002.5 1003.6 1004.9 1006.4 1008.1 1010 1012.1 // max bucket price
);
specifying the maximum and minimum prices for a given bucket respectively. How might I group a trades table by these buckets and conduct aggregations on them thereafter. i.e.
q) trades
tid time | size price side
---------------------------| ---------------
0 2020.10.18T19:55:41.554| 40 1000.0 0 // first bucket
1 2020.10.18T20:07:41.554| 83 1000.1 0 // first bucket
2 2020.10.18T18:43:41.554| 30 1000.5 0 // second bucket
3 2020.10.18T19:35:41.554| 102 1000.6 0 // second bucket
4 2020.10.18T19:11:41.554| 16 1000.6 0 // second bucket
5 2020.10.18T21:17:41.554| 29 1000.9 0 // third bucket
6 2020.10.18T22:24:41.554| 67 1001.9 0 // fourth bucket
...
for simplicity's sake I have only shown the aggregation for one side
q) fn[trades;buckets]
bid | tid
---------------
0 | 0 1
1 | 2 3 4
2 | 5
3 | 6
Is there a canonical (efficient) way of achieving this functionality in kdb+/q?
Thanks for your guidance
Use the bin command. You only need the distinct values for your bucket list too.
q)n:1000000
time sym price side
----------------------------
01:35:38.122 CCC 1002.8 0
00:57:13.343 CCC 1003.5 0
02:40:31.958 BBB 1000.3 1
...
q)buckets:1000.0 1000.4 1000.9 1001.6 1002.5 1003.6 1004.9 1006.4 1008.1 1010 1012.1
q)show t2:select time,sym,price,side by Bucket:buckets bin price from t
Bucket| time ..
------| ---------------------------------------------------------------------..
0 | 02:40:31.958 01:56:19.155 03:07:33.543 02:19:32.484 03:19:00.728 02:4..
1 | 00:51:01.325 04:15:50.568 01:20:01.150 01:27:36.835 04:05:26.790 00:4..
Then either ungroup your result to create large table with Bucket column you can reference or index into t2 to get the data for each bucket like:
q)flip t2 0
time sym price side
----------------------------
02:40:31.958 BBB 1000.3 1
01:56:19.155 BBB 1000.1 1
03:07:33.543 BBB 1000 0
q)flip t2 3
time sym price side
----------------------------
02:44:04.837 BBB 1001.8 0
02:47:43.405 CCC 1001.7 1
01:15:08.693 BBB 1002.3 0
Hope this helps. Let us know if it is not what you are after.