How do I do a select statement in kdb with fby on a function that takes more than one argument?
What I want is something like this:
t:([] price:1000?1f; sym:`AAPL; qty:3.0; open:0.20);
select from t where open<({exec x wavg y};qty;price) fby sym
without having to do
t:update avgPrice:qty wavg price by sym from t;
t:select from t where avgPrice>open;
EDIT: I found a way to do it without using flip
select from t where open<({[x] exec qty wavg price from x};([]qty;price)) fby sym
Small sample table with variety for testing:
q)t:([] price:15?10f; sym:15?`A`P`L; qty:15?3; open:15?10f);
q)t
price sym qty open
---------------------------
3.614587 P 2 4.329653
8.378739 P 1 0.4506864
0.5421134 L 0 0.3795818
1.635727 A 0 9.475303
6.417787 L 1 0.7403326
6.655573 P 0 9.941467
8.064934 P 0 4.265147
4.316783 A 1 3.447288
9.054219 A 1 4.368897
3.741311 P 1 5.678812
7.319251 A 0 8.729202
4.999804 P 2 9.133432
7.846789 L 0 3.920599
6.721702 L 0 5.564439
7.074448 L 2 3.351027
Your version:
q)select from (update avgPrice:qty wavg price by sym from t) where avgPrice>open
price sym qty open avgPrice
------------------------------------
3.614587 P 2 4.329653 4.891472
8.378739 P 1 0.4506864 4.891472
0.5421134 L 0 0.3795818 6.855561
6.417787 L 1 0.7403326 6.855561
8.064934 P 0 4.265147 4.891472
4.316783 A 1 3.447288 6.685501
9.054219 A 1 4.368897 6.685501
7.846789 L 0 3.920599 6.855561
6.721702 L 0 5.564439 6.855561
7.074448 L 2 3.351027 6.855561
With fby as list of lists:
q)select from t where open<({x[;0] wavg x[;1]};flip (qty;price)) fby sym
price sym qty open
---------------------------
3.614587 P 2 4.329653
8.378739 P 1 0.4506864
0.5421134 L 0 0.3795818
6.417787 L 1 0.7403326
8.064934 P 0 4.265147
4.316783 A 1 3.447288
9.054219 A 1 4.368897
7.846789 L 0 3.920599
6.721702 L 0 5.564439
7.074448 L 2 3.351027
Or sub tables:
q)select from t where open< ({exec qty wavg price from x};([]qty;price)) fby sym
price sym qty open
---------------------------
3.614587 P 2 4.329653
8.378739 P 1 0.4506864
0.5421134 L 0 0.3795818
6.417787 L 1 0.7403326
8.064934 P 0 4.265147
4.316783 A 1 3.447288
9.054219 A 1 4.368897
7.846789 L 0 3.920599
6.721702 L 0 5.564439
7.074448 L 2 3.351027
Check results match:
q)a:delete avgPrice from select from (update avgPrice:qty wavg price by sym from t) where avgPrice>open
q)b:select from t where open<({x[;0] wavg x[;1]};flip (qty;price)) fby sym
q)a~b
1b
q)c:select from t where open< ({exec qty wavg price from x};([]qty;price)) fby sym
q)b~c
1b
fby code.kx.com/q/ref/fby so showing to the left would be a good addition github.com/KxSystems/docs