/ ?/![t;c;b;a] select:{[t;c;b;a]t:col[dekey t;`i_];key[cols[:[#c;rows[t]{x@&eval[rows[y]x]z}/[_n;t;c];t];b,();!a;a[];#b;#!a::[4=__abs 4:a;.+(a;a,:());a]]]b} update:{[t;c;b;a]u:@[dekey select[u;c;b]@[.+2#,!u:col[t;`i_];!a;:;a[]];_n;,/];key[.[col/[.[t;(f;u`i_);:;u f:!t];g];(g;u`i_);:;u g:(!u)_dvl`i_,!t]]b} delete:{[t;c;b;a]k::[5=4:k;();!*t];t:dekey t;key[:[#c;rows[t]i _dvl(i:!*|^t[]){x@&eval[rows[y]x]z}/[_n;t;c];#a;@[_n;f;:;t f:(!t)_dvl(),a];t]]k} upsert:{[t;u]if[5=4:t;:@[t;_n;,;++@[u;m;:;na[t]m:(!t)_dvl!u]@!t]];k:!*t;t:dekey t;j:&b:(*|^t[])>i:idx[u;t]k;key[upsert[.[nil[t]u;(!u;i j);:;u[;j]]]@[u;_n;@[;&~b]]]k} / joins j:{[o;t;u]k:!*u;f:(!u:dekey u)_dvl k;l::[5=4:t;0#`;!*t];t:dekey t;key[o[t;u;k]f]l} lj:j[{[t;u;k;f]@[t;f;:;at[u;f]idx[t;u]k]}] ij:j[{[t;u;k;f]@[tin[t;u]j;f;:;in[u;u;f]j:idx[t;u]k]}] pj:j[{[t;u;k;f].[t;(f;tu[t;u]k);+;u f]}] ej:{[k;t;u]a:?int[b:+t k]c:+u k;t:@[t;_n;@[;&b _lin a]];u:@[u;_n;@[;&c _lin a]];m:eq[u k]t k;u:@[u;_n;,/(+/'m)#'];@[u;f;:;@[t f:(!t)_dvl!u;_n;,/(+/'+m)#']]} uj:{[t;u]:[5=4:t;@[nil[t]u;?(!u),!t;,;nil[u;t][]];upsert[t]u]} a:{[o;t;u;k]a:lj[u]key[t:dekey t]l:-1_ k,:();b:dekey select[a;();l;?((!t),!u)_dvl l];i:t[m][tu[t;b]l](|/&~>)'b m:*|k;c:@[b;(!b)_dvl l;{y@'x}[i]];d:o[(l;m;i);u]c;uj[d]@[t;_n;@[;&~t[`s]_lin c`s]]} aj0:a[{@[z;x 1;:;y[x 1;=+y x 0]@'x 2]}] aj:a[{z}] asof:{[t;d]{@[x;_n;@[;tu[x;y;!y]]]}[aj[t;d;!d];d]_di!d} wj:{[w;c;t;p]} wj1:{[w;c;t;p]} / subfunctions idx:{[t;u;k]:[1=#k;(?u k)?/:t k*:;(?i)?/:i:(#:'j)_sv(j:?:'u k)?/:'t k]} col:{[t;f]@[t;f;:;!*|^t[]]} rows:{[t;i]@[t;_n;@[;i]]} cols:{[t;b;a;e;j;k]:[~j|k;t;@[_n;b,a;:;:[j;:[#*u:t b;+?+u;u],:[k;{{eval[rows[x]z]y}[x;z]'y}[t;=+t b]'e;()];eval[t]'e]]]} at:{[t;f;i](t[f],'na[t]f)@\:i} na:{[t;f](0N;0n;" ";`;0Nj;)1 2 3 4 64?/:__abs 4::'t f} in:{[t;u;f;i]t[f;i@&i<*|^u[]]} tin:{[t;u;i].+(!t;in[t;u;_n]i)} int:{_dvl/(x;(x;y))} nil:{[t;u]@[t;f;:;(*|^t[])#'na[u]f:(!u)_dvl!t]} eq:{[a;b]:[0>4:a;a=\:b;(+a)~/:\:+b]} tu:{[t;u;f](+t f)?/:+u f} nub:{[t].+(!t;+?+t[])} / aggregators sum:+/ max:|/ min:&/ avg:{(+/x)%#x} / evaluate parse-tree eval:{[t;e]:[4=4:e;t e;(1<#e)&(4:*e)_in 4 7;e[0]._f[t]'1_ e;((1=#e)&(4:**e)_in 4 7)*:/e]} / key, dekey, rekey key:{[t;k]:[~#k;t;5=4:t;(.+(k;t k);t _di k@:&(k,:())_lin!t);key[dekey t]k]} dekey:{[t]:[5=4:t;t;.,/.:'t]} rekey:{[t;k]key[dekey t]k} / examples t:@[_n;`f`g`h`i`j;{:[@z;x _draw z;z x _draw#z]}[10];(`a`b`c`d`e;7;0;0;0)] / where-clause, by-clause, calculated columns c:((=;`f;,`c);(_lin;`g;2 3 4);(>;`h;.5)) b:`f`g a:.+(`p`q`r`s`i_;((+/;`i);(+/;(+;`i;1));({(+/x)%#x};`i);(+/;(+;`i;`j));`i_)) / all the forms of select d:select[t;c;b;a] / select p:sum i,q:sum i+1,r:avg i,s:sum i+j by f,g from t where f=`c,g _lin 2 3 4,h>.5 e:select[t;c;b;.()] / select by f,g from t where f=`c,g _lin 2 3 4,h>.5 f:select[t;c;();a] / select p:sum i,q:sum i+1,r:avg i,s:sum i+j from t where f=`c,g _lin 2 3 4,h>.5 g:select[t;c;();.()] / select from t where f=`c,g _lin 2 3 4,h>.5 h:select[t;();b;a] / select p:sum i,q:sum i+1,r:avg i,s:sum i+j by f,g from t i:select[t;();b;.()] / select by f,g from t j:select[t;();();a] / select p:sum i,q:sum i+1,r:avg i,s:sum i+j from t k:select[t;();();.()] / select from t aa:.+(`p`q;((*;`g;11);(+;`g;1111))) / all the forms of update dd:update[t;c;b;aa] ee:update[t;c;b;.()] ff:update[t;c;();aa] gg:update[t;c;();.()] hh:update[t;();b;aa] ii:update[t;();b;.()] jj:update[t;();();aa] kk:update[t;();();.()] / all the forms of delete ll:delete[t;c;();()] mm:delete[t;();();`f`g] nn:delete[t;();();()] / all the forms of upsert v:.+(`f`h`g;(`x`y`z;1.1 2.2 3.3;100 200 300)) oo:upsert[t]v t:key[.+(`k`f`g`h;(1 2 3 4 5;10 20 30 40 50;60 70 80 90 100;`a`b`c`d`e));`k] u:.+(`k`h`f;(3 9 2;`x`yyyyy`z;100 222 300)) pp:upsert[t]u / joins x:.+(`k`f;(1 1 1 4 4 3 3 3 3 3;!10)) y:key[.+(`k`g;(1 2 3;`A`B`C))]`k xxx:lj[x]y yyy:ij[x]y t:.+(`k`c`q;(1 2 2 3 4;10 11 20 30 40;`x`xx`y`z`w)) u:.+(`k`c`v;(2 2 2 4 5;200 300 301 400 500;2 3 31 4 5)) zzz:ej[`k;t]u t:.+(`k`a`b`c;(`c`b`a;100 200 300;10. 20. 30.;1 2 3)) u:key[.+(`k`a`b;(`a`b;10 20;1.1 2.2));`k] uuu:pj[t]u t:.+(`x`y`z;(`a`b;1 2;`x`y`z)) u:.+(`x`y`w;(`c`d;3 4;`X`Y`Z)) vvv:uj[t]u t:key[.+(`k`v`w;(1 2 3;`a`b`c;10 20 30))]`k u:.+(`k`w`x;(3 4;300 400;3.3 4.4)) www:uj[t]u t:.+(`t`s`q;(1 3 4;`m`i`g;100 200 150)) u:.+(`t`s`p;(0 1 1 2;`i`m`m`i;100 99 101 98)) k:`s`t aaa:aj[t;u]k bbb:aj0[t;u]k t:.+(`t`s`q`p;(1 3 4;`m`i`g;100 200 150;45 160 55)) ccc:asof[t;.+(`s`t;(`m`i;1 3))] \ a k3 implementation of k4 select, update, delete, upsert and eval ================================================================= select, update, delete ---------------------- k4 contains three powerful primitives for table selection and update, both with and without aggregation: ?[t;c;b;a] / select by from where ![t;c;b;a] / update by from where ![t;c;();()] / delete from where ![t;();();a] / delete from t is a table. c is a list of parse-trees of row-constraints. b is a vector of grouping columns. a is a dictionary of parse-trees of column-calculations. for example, the k4 q-expression: d:select p:sum i,q:sum i+1,r:avg i,s:sum i+j by f,g from t where f=`c,g in 2 3 4,h>.5 can be coded in k3 this way: c:((=;`f;,`c);(_lin;`g;2 3 4);(>;`h;.5)) b:`f`g a:.+(`p`q`r`s;((+/;`i);(+/;(+;`i;1));({(+/x)%#x};`i);(+/;(+;`i;`j)))) d:select[t;c;b;a] where t is a table with columns f, g, h, i, and j. nb: t~update[t;();();.()]~select[t;();();.()]~delete[t;();();()] parse-tree structure -------------------- row-constraints: c is a list (c1;..;cn) which means: c1 & .. & cn, i.e. &&/eval'c. column-calculations: a is a dictionary where each column !a will be assigned the result of evaluating each a[]. parse-trees are evaluated recursively: (f;a1;..;an) means: apply n-adic function or symbol f to arguments a1 .. an. atomic symbols are interpreted as columns of the table t being operated on: (=;`f;10) means: t[`f] = 10. (>;`f;`g) means: t[`f] > t[`g]. enlisted symbols and functions are data: (=;`f;,`g) means: t[`f] = `g. (_lin;`f;,`a`b`c) means: t[`f] _lin `a`b`c. ,(=;`f;`g) means: a list (e.g. update[t;c;b;.,(`foo;,(=;`f;`g))]). the logic of 'eval': eval:{[t;e]:[4=4:e ;t e (1<#e)&(4:*e)_in 4 7 ;e[0]._f[t]'1_ e ((1=#e)&(4:**e)_in 4 7)*:/e]} spelled out: if e = symbol then t e else if e = (function or symbol;..) then apply e[0] to eval'1_ e else *e or e as e = ,function or symbol or not notes ----- *0: i_ is a reserved column name for index *1: cols uses a variant of {x y z x}/ to progressively reduce the domain of the restriction. *2: cols uses the less-efficient f't[`f]@=t b for aggregation. this is about 4 x slower than the more complex method using self-linking with _sv encoding. cf. http://www.nsl.com/k/t.k. *3: the k4 functional form for a query without aggregation uses the boolean atom 0b in the 'b' position instead of (). *4: for additional information, see the section "functional forms" in jeffrey borror's Q FOR MORTALS. sasha katsman's fby: e.g. select rowid from table where 50<(sum;f2) fby f1 fby:{[test;fun;f1;f2]&((?f1)?/:f1)_lin &test[fun'f2@=f1]} x 1 2 3 1 2 3 1 2 3 y 10 20 30 11 12 13 44 45 3 fby[50<;+/;x;y] 0 1 3 4 6 7 fby[5<;+/;x;y] 0 1 2 3 4 5 6 7 8 Or, unordered: fby1:{[test;fun;f1;f2] ,/g@&test[fun'f2@g:=f1]} fby1[50<;+/;x;y] 0 3 6 1 4 7