CODEWITHSUNDEEP
kdb+
Utsav
Utsav

Reputation: 5918

Optimize a kdb query which used update more than once

We have a table:

q)t:([] id:("ab";"cd";"ef";"gh";"ij"); refid:("";"ab";"";"ef";""); typ:`BUY`SELL`BUY`SELL`BUY)
q)t
id   refid typ
---------------
"ab" ""    BUY
"cd" "ab"  SELL
"ef" ""    BUY
"gh" "ef"  SELL
"ij" ""    BUY

Now our requirement is to add a column named 'event' to the table which is marked as 'N' if id of BUY type matches with refid of SELL type and refid is not null else mark event as 'Y'.
I have written below query which works perfectly fine but has a scope of optimization. 

Desired Output:  
id   refid typ  event
---------------------
"ab" ""    BUY  N
"cd" "ab"  SELL N
"ef" ""    BUY  N
"gh" "ef"  SELL N
"ij" ""    BUY  Y
 Query used: 
q)update event:`N from (update event:?[([]id) in (select id:refid from t where typ=`SELL, not refid like "");`N;`Y] from t) where typ=`SELL, not refid like ""

Please help me optimize above query.

Upvotes: 0

Views: 136

Answers (1)

Dunny
Dunny

Reputation: 170

You could try something like this which works for the date you have provided

q)update eve:?[(typ=`BUY) &(not any(`$id)=/:`$refid);`Y;`N] from t
id   refid typ  eve
-------------------
"ab" ""    BUY  N
"cd" "ab"  SELL N
"ef" ""    BUY  N
"gh" "ef"  SELL N
"ij" ""    BUY  Y

Upvotes: 2

Related Questions