CODEWITHSUNDEEP
kdb+
James
James

Reputation: 1260

KDB+: How to retrieve the rows immediately before and after a given row that conform to a specific logic?

Given the following table

time    kind    counter    key1    value
----------------------------------------
1       1       1          1       1
2       0       1          1       2
3       0       1          2       3
5       0       1          1       4
5       1       2          2       5
6       0       2          3       6
7       0       2          2       7
8       1       3          3       8
9       1       4          3       9

How would one select the value in the first row immediately after and immediately before each row of kind 1 ordered by time where the key1 value is the same in both instances .i.e:

time      value       prevvalue     nextvalue
---------------------------------------------
1         1           0n            2
5         5           3             7
8         8           6             0n
9         9           6             0n

Here are some of the things I have tried, though to be honest I have no idea how to canonically achieve something like this in q whereby the prior value has a variable offset to the current row?

select 
     prev[value],
     next[value],
     by key1 where kind<>1

 
     update 0N^prevval,0N^nextval from update prevval:prev value1,nextval:next value1 by key1 from table

Some advice or a pointer on how to achieve this would be great! Thanks

Upvotes: 0

Views: 1108

Answers (1)

Caitlin Galway
Caitlin Galway

Reputation: 141

I was able to use the following code to return a table meeting your requirements. If this is correct, the sample table you have provided is incorrect, otherwise I have misunderstood the question.

q)table:([] time:1 2 3 5 5 6 7 8 9;kind:1 0 0 0 1 0 0 1 1;counter:1 1 1 1 2 2 2 3 4;key1:1 1 2 1 2 3 2 3 3;value1:1 2 3 4 5 6 7 8 9)  

q)tab2:update 0N^prevval,0N^nextval from update prevval:prev value1,nextval:next value1 by key1 from table

q)tab3:select from tab2 where kind=1
  time value1 prevval nextval
  ---------------------------
   1    1              2
   5    5      3       7
   8    8      6       9
   9    9      8

The update statement in tab2:

  update 0N^prevval,0N^nextval from update prevval:prev value1,nextval:next value1 by key1 from table

is simply adding 2 columns onto the original table with the previous and next values for each row. 0^ is filling the empty fields with nulls.

The select statement in tab3:

tab3:select from tab2 where kind=1

is filtering tab2 for rows where kind=1.

The final select statement:

select time,value1,prevval,nextval from tab3

is selecting the rows you want to be returned in the final result.

Hope this answers your question.

Thanks, Caitlin

Upvotes: 2

Related Questions