KDB:selecting data "around" time of certain events

Question

Consider a huge table of market data T. I am particularly interested in rows where Status=`SSS.

However, in addition to the rows given by (select from T where Status=`SSS), I also would like to select the 10 records that come both immediately before and after these rows. (Note that in some cases, these intervals may overlap). What is an efficient way to do this?

Note that I tried something like this below, and it nearly crashed my port and hogged up all the memory.

select from
update diff:min each abs i-(count i)# enlist (exec distinct x from select from
(update x:i from T) where Status=`SSS),where diff<10

score 1 · Answer 1 · edited Dec 11 '17 at 07:54

1

Here is another solution with little modification in WooiKent answer. But it gives some improvement over time and space.

select from t where i in distinct raze (-10+til 21)+\:(exec i from t where sym=`CC)

For WooiKent sample table:

\ts select from t where i in distinct raze (-10+til 21)+\:(exec i from t where sym=`CC)
113 77595968j

edited Dec 11 '17 at 07:54

Thomas Smyth - Treliant

4,993
6
25
36

answered Jun 25 '14 at 05:31

Rahul

3,914
1
14
25

I am not sure why but I have also got some improvement due to exec statement. – Rahul Jun 25 '14 at 10:07
I think if you use `\:`, you only iterate `+` operator `1+2*n` times. Using `/:` will iterate number of times depending how many records are there that match `CC. – WooiKent Lee Jun 25 '14 at 12:34
Yes, I think you are right there. It is due to no.of iterations. – Rahul Jun 25 '14 at 15:40

score 0 · Answer 2 · answered Jun 24 '14 at 15:27

q)n:1000000
q)t:update `g#sym from`time xasc([]time:n?.z.t;sym:n?`AA`BB`CC;side:n?`buy`sell;price:10+n?1.0;size:1000*n?10)
q)t
time         sym side price    size
-----------------------------------
00:00:00.014 BB  sell 10.40464 7000
00:00:00.052 AA  sell 10.42747 1000
00:00:00.063 BB  buy  10.9406  7000
00:00:00.085 AA  sell 10.23984 7000
00:00:00.105 CC  buy  10.06752 7000
00:00:00.127 AA  sell 10.83174 1000
00:00:00.141 AA  sell 10.29591 8000
00:00:00.167 BB  sell 10.75681 2000
00:00:00.232 CC  buy  10.56052 1000
00:00:00.234 AA  sell 10.16642 7000
00:00:00.281 BB  buy  10.58453 7000
00:00:00.284 BB  buy  10.08245 2000
00:00:00.338 AA  sell 10.4551  1000
00:00:00.455 BB  buy  10.13024 8000
00:00:00.463 CC  sell 10.43779 5000
00:00:00.477 CC  buy  10.5226  0
00:00:00.535 CC  sell 10.59109 7000
00:00:00.671 AA  sell 10.90785 4000
00:00:00.702 CC  sell 10.60891 9000
00:00:00.704 BB  buy  10.30173 8000
..
q){select from t where i in raze(til[1+2*x]-x)+/:where sym in `CC}1
time         sym side price    size
-----------------------------------
00:00:00.085 AA  sell 10.23984 7000
00:00:00.105 CC  buy  10.06752 7000
00:00:00.127 AA  sell 10.83174 1000
00:00:00.167 BB  sell 10.75681 2000
00:00:00.232 CC  buy  10.56052 1000
00:00:00.234 AA  sell 10.16642 7000
00:00:00.455 BB  buy  10.13024 8000
00:00:00.463 CC  sell 10.43779 5000
00:00:00.477 CC  buy  10.5226  0
00:00:00.535 CC  sell 10.59109 7000
00:00:00.671 AA  sell 10.90785 4000
00:00:00.702 CC  sell 10.60891 9000
00:00:00.704 BB  buy  10.30173 8000
00:00:00.716 CC  buy  10.00173 5000
00:00:00.753 BB  sell 10.04301 4000
00:00:01.188 BB  sell 10.86634 8000
00:00:01.210 CC  buy  10.0534  3000
00:00:01.231 BB  buy  10.28736 3000
00:00:01.725 AA  sell 10.25753 5000
00:00:01.783 CC  buy  10.38823 6000
q)\ts {select from t where i in raze(til[1+2*x]-x)+/:where sym in `CC}1
96 37749856
q)\ts {select from t where i in raze(til[1+2*x]-x)+/:where sym in `CC}10
195 154503136

Not sure how big your table is, but this current table is about 28MB in memory... — WooiKent Lee, Jun 24 '14 at 15:31
Awesome! Now the next (very related) question. How do I select all observations within 15 seconds of Status=`SSS? — bigO6377, Jun 24 '14 at 20:18

score 0 · Answer 3 · answered Jun 27 '14 at 13:14

Here's an attempt at addressing your question about records that are in the -7 and 7 seconds surrounding the trade (I assumed you counted the trade time as 0, and those combined made up 15 seconds, but you could change accordingly).

Since you are looking at ranges, there might be overlaps, so there is no point in checking a bunch of ranges, but rather the ranges that will encompass everything you need. So we create a helper function that merges ranges of time. Note that it assumes a) that your starting point is < that you ending point in each range, b) that you have fed in the ranges by ascending starting point. This can definitely be improved

//borken up into 2 lines for stackoverflow formatting /ease of read
mergeranges:{(enlist first fw){
   (neg[d]_x),enlist @[y;0;:;(first y;first p) d:first[y] within p:last x]
   }/1_fw:flip x}

So for example if we had the following ranges

[0, 2]
      [3, 4]
          [4, 6]

we really should just check [0 2] and [3 6]

q)mergeranges (0 3 4; 2 4 6)
0 2
3 6

q)\S 1
q)t:([]time:til 100;status:100?10; px:100?1.)
q)-7 7+\:exec time from t where status=0
10 27 32 35 36 43 68 70
24 41 46 49 50 57 82 84
q)mergeranges -7 7+\:exec time from t where status=0
10 24
27 57
68 84
q)5#select from t where any time within/:mergeranges -7 7+\:exec time from t where status=0
time status px
----------------------
10   1      0.1634704
11   5      0.7766767
12   6      0.8928093
13   6      0.6203577
14   3      0.07747125

KDB:selecting data "around" time of certain events

3 Answers3

Linked