Functional Query Functions
So, five months ago I said I’d cover functional query utility functions “next time”. Well, next time is finally here. Sorry for the wait; hope it was worth it.
Before I begin, though, two quick notes:
- Functional queries have no performance advantage. They are identical to
qsql
queries in speed and memory usage. - Funcitonal queries are hard to maintain. Even using the techniques described here, functional queries are still harder to read and understand than
qsql
queries.
qsql
is incapable of accomplishing the task at hand. (Typically, this involves dynamically choosing columns.)As mentioned previously, getting the parse trees of the
c
, b
, and a
arguments to functional queries right is quite tricky. One technique I frequently use to make this easier is to use utility functions to abstract away as much of that complexity as possible. In their most basic form, they simply generate (composable) elements of the relevant structures:For example:q)c:{parse["select from t",$[count x;" where ",x;""]]. 2 0} q)b:{parse["select",$[count x;" by ",x;""]," from t"]3} q)a:{parse["select ",x," from t"]4}
In practice, of course, these functions should be used to express inq)t:([]x:1 2 3 4;y:1 1 2 3;z:7 8 9 10) q)select sum x by y from t where x<>1 y| x -| - 1| 2 2| 3 3| 4 q)?[t;c"x<>1";b"y";a"sum x"] y| x -| - 1| 2 2| 3 3| 4 q)
qsql
whatever parts of a query are so expressible, while reserving the actual parse trees for the parts of the query that need them:More specialized utilities can also be written, once their need becomes apparent. One particularly difficult thing to express in functional form is a multi-column fby:q)show each t{?[x;c"x<>1";b"y";(enlist y)!enlist(sum y)]}/:`x`z; y| x -| - 1| 2 2| 3 3| 4 y| z -| -- 1| 8 2| 9 3| 10 q)
The primary problem is that the parse of this query contains the instructions for assembling a table literal out of its individual pieces. Here’s the functional equivalent, written all the way out:q)t2::update k:`a`a`b`c from t q)select from t2 where i=(last;i)fby([]y;k) x y z k -------- 2 1 8 a 3 2 9 b 4 3 10 c q)parse"select from t2 where i=(last;i)fby([]y;k)" ? `t2 ,,(=;`i;(k){@[(#y)#x[0]0#x 1;g;:;x[0]'x[1]g:.=y]};(enlist;last;`i);(+:;(!;,`y`k;(enlist;`y;`k))))) 0b () q)
Using a utility function, it can instead be written much more simply:q)?[t2;enlist(=;`i;(fby;(enlist;last;`i);(flip;(!;enlist`y`k;(enlist;`y;`k)))));0b;()] x y z k -------- 2 1 8 a 3 2 9 b 4 3 10 c q)
Judicious application of these techniques (not to mention judicious application of functional querying in the first place) can make code much more readable.q)fbyx:{.[parse["select from t where ",x,"fby c"]. 2 0 0;2 2;:;(flip;(!;enlist(),y;(enlist,y)))]} q)?[t2;enlist fbyx["i=(last;i)";`y`k];0b;()] x y z k -------- 2 1 8 a 3 2 9 b 4 3 10 c q)
Labels: article