Re: select with combinational conditions

[rocuinneagain]

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