Home › Forums › kdb+ › select with combinational conditions › Re: select with combinational conditions
-
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