• kdb+

  select with combinational conditions

  Posted by powerpeanuts on May 29, 2023 at 12:00 am

  Hi, is there a way to make select statements with multiple conditions in combinations?

  For example, for the table below:

  q)flip `date`data!(2023.05.20 2023.05.20 2023.05.20 2023.05.19 2023.05.19 2023.05.19;`a`c`b`b`b`c) 
  date      data 
  --------------- 
  2023.05.20 a 
  2023.05.20 c 
  2023.05.20 b 
  2023.05.19 b 
  2023.05.19 b 
  2023.05.19 c

   

  I would like to select rows with

  (date=2023.05.20 and data in `a`b) or

  (date=2023.05.19 and data in enlist `b)

  i.e. the results should be

   

  date      data 
  --------------- 
  2023.05.20 a 
  2023.05.20 b 
  2023.05.19 b 
  2023.05.19 b

   

  I have a list of these dates/data combinations, is there an easy way to do such filter?

  i.e.

  2023.05.20 `a`b

  2023.05.19 enlist `b

  2023.05.18 `c`d`a

  2023.05.17 `d`a

  …

   

  Thanks.

   

  powerpeanuts replied 8 months, 2 weeks ago 4 Members · 3 Replies
• 3 Replies

• 

  rocuinneagain

  Member

  May 29, 2023 at 12:00 am

  If you went directly in to writing what you ask:

   

  q)t:flip `date`data!(2023.05.20 2023.05.20 2023.05.20 2023.05.19 2023.05.19 2023.05.19;`a`c`b`b`b`c) 
  q)select from t where any (and[date=2023.05.20;data in `a`b];and[date=2023.05.19;data in enlist `b])
  date data 
  --------------- 
  2023.05.20 a 
  2023.05.20 b 
  2023.05.19 b 
  2023.05.19 b 
  
  q)parse "select from t where any (and[date=2023.05.20;data in `a`b];and[date=2023.05.19;data in enlist `b])" 
  ? 
  `t 
  ,,(max$["b"];(enlist;(&;(=;`date;2023.05.20);(in;`data;,`a`b));(&;(=;`date;2023.05.19);(in;`data;(enlist;,`b))))) 
  0b 
  () 
  q)?[`t;enlist(any;(enlist;(and;(=;`date;2023.05.20);(in;`data;enlist `a`b));(and;(=;`date;2023.05.19);(in;`data;(enlist;enlist`b)))));0b;()] 
  date data 
  --------------- 
  2023.05.20 a 
  2023.05.20 b 
  2023.05.19 b 
  2023.05.19 b 
  
  q)f:((2023.05.20;`a`b);(2023.05.19;enlist `b);(2023.05.18;`c`d`a);(2023.05.17;`d`a)) 
  q)?[`t;(enlist(any;enlist,{(and;(=;`date;x 0);(in;`data;enlist x 1))}each f));0b;()] 
  date data 
  --------------- 
  2023.05.20 a 
  2023.05.20 b 
  2023.05.19 b 
  2023.05.19 b

   

  Much better if the data is on disk and partitioned by date would be to iterate over each date and not each filter:

   

  q)f:flip `date`syms!flip f 
  date syms 
  ----------------- 
  2023.05.20 `a`b 
  2023.05.19 ,`b 
  2023.05.18 `c`d`a 
  2023.05.17 `d`a 
  
  q)raze {select from t where date=x`date,data in x`data} peach 0!select distinct raze data by date from f 
  date data 
  --------------- 
  2023.05.19 b 
  2023.05.19 b 
  2023.05.20 a 
  2023.05.20 b

   

• 

  eohara_kdb

  Member

  May 29, 2023 at 12:00 am

  Assuming your initial table can grow to a large size, probably best to only query it once to get the dates and symbols you’re interested in:

   

  q)l:((2023.05.20;`a`b);(2023.05.19;enlist `b);(2023.05.18;`c`d`a);(2023.05.17;`d`a)); 
  q)tab:flip `date`data!(2023.05.20 2023.05.20 2023.05.20 2023.05.19 2023.05.19 2023.05.19;`a`c`b`b`b`c); 
  q)subset:select from tab where date in l[;0],data in raze l[;1]; 
  q)raze{select from y where date=x[0],data in x[1]}[;subset]each l 
  date      data 
  --------------- 
  2023.05.20 a 
  2023.05.20 b 
  2023.05.19 b 
  2023.05.19 b

   

  There’s probably a more efficient way but this should do it

• 

  unterrainer_ale

  Member

  May 30, 2023 at 12:00 am

  Something worth adding to the previous answers: If you can transform your filter into a table then the select is much easier. To illustrate

  q)t:flip `date`data!(2023.05.20 2023.05.20 2023.05.20 2023.05.19 2023.05.19 2023.05.19;`a`c`b`b`b`c) 
  q)t 
  date      data 
  --------------- 
  2023.05.20 a 
  2023.05.20 c 
  2023.05.20 b 
  2023.05.19 b 
  2023.05.19 b 
  2023.05.19 c 
  
  q)select from t where ([] date;data) in ([] date:2023.05.20 2023.05.20 2023.05.19;data:`a`b`b) 
  date      data 
  --------------- 
  2023.05.20 a 
  2023.05.20 b 
  2023.05.19 b 
  2023.05.19 b 
  
  q)filter:([] date:2023.05.20 2023.05.19 2023.05.18 2023.05.17; data:(`a`b;enlist `b;`c`d`a;`d`a)) 
  q)filter 
  date        data 
  ----------------- 
  2023.05.20 `a`b 
  2023.05.19 ,`b 
  2023.05.18 `c`d`a 
  2023.05.17 `d`a 
  
  q)ungroup filter 
  date      data 
  --------------- 
  2023.05.20 a 
  2023.05.20 b 
  2023.05.19 b 
  2023.05.18 c 
  2023.05.18 d 
  2023.05.18 a 
  2023.05.17 d 
  2023.05.17 a 
  
  q)t1:ungroup filter 
  q)select from t where ([] date;data) in t1 
  date      data 
  --------------- 
  2023.05.20 a 
  2023.05.20 b 
  2023.05.19 b 
  2023.05.19 b

Log In to Reply