// requires 64bit e. for 32bit k, replace idx and na below with the commented-out versions / ?/![t;c;b;a] select:{[t;c;b;a]t:tval t;t:col[dekey t;`i_];xkey[b]cols[:[#c;rows[t]{x@&kval[rows[y]x]z}/[_n;t;c];t];b,();!a;a[];#b;#!a::[4=__abs 4:a;.+(a;a,:());~#!a;.+2#,(!t)_dvl b,`i_;a]]} update:{[t;c;b;a]t:dekey@*tval t;u:@[dekey select[u;c;b]@[.+2#,!u:col[t;`i_];!a;:;a[]];_n;,/];xkey[b].[col/[.[t;(f;u`i_);:;u f:!t];g];(g;u`i_);:;u g:(!u)_dvl`i_,!t]} delete:{[t;c;b;a]k::[5=4:t:tval t;();!**t];t:dekey@*t;xkey[k]:[#c;rows[t]i _dvl(i:!*|^t[]){x@&kval[rows[y]x]z}/[_n;t;c];#a;@[_n;f;:;t f:(!t)_dvl(),a];t]} insert:{[t;d]t:tval t;@[t;_n;,;eval[d][]]} upsert:{[t;u]t:tval t;u:tval 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;xkey[k]upsert[.[nil[t]u;(!u;i j);:;u[;j]]]@[u;_n;@[;&~b]]} exec:{[t;c;b;a]:[1=#!r:dekey select[t;c;b]a;*r[];r]} / functions distinct:{:[5=t:4:x:tval x;.+(!x;+?+x[]);?x]} xasc:{[c;t]t:tval t;@[t;_n;@[;t@*c]]} xcol:{[c;t]t:tval t;d:!u:dekey t;xkey[keys t]@.+(c,(#c*:)_ d;u[])} xcols:{[c;t]d:!t:tval t;.+(e;t e:c,d _dvl(),c*:)} fby:{[a;c]f:*a;d:a 1;@[d;g;:;f'd g:=:[5=4:c;+c[];c]]} / joins j_:{[o;t;u]t:tval t;u:tval u;k:!*u;f:(!u:dekey u)_dvl k;l::[5=4:t;0#`;!*t];t:dekey t;xkey[l]o[t;u;k]f} 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]t:tval t;u:tval 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]t:tval t;u:tval u;:[5=4:t;@[nil[t]u;?(!u),!t;,;nil[u;t][]];upsert[t]u]} a_:{[o;t;u;k]t:tval t;u:tval u;a:lj[u]xkey[l:-1_ k,:()]t:dekey t;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*:;(e _sv 1j*(j?/:'u k))?/:(e:1+#:'j)_sv 1j*(j:?:'u k)?/:'t k]} / idx:{[t;u;k]:[1=#k;(?u k)?/:t k*:;(e _sv(j?/:'u k))?/:(e:1+#:'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;{{kval[rows[x]z]y}[x;z]'y}[t;=+t b]'e;()];kval[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} / na:{[t;f](0N;0n;" ";`;)1 2 3 4?/:__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} list:{[e]({x,,y}/;())~2#e} / aggregators sum:+/ max:|/ min:&/ avg:{(+/x)%#x} / operators E:`distinct`xasc`xdesc`xcol`xcols`sum`min`max`avg`lj`ij`pj`ej`ug`aj`aj0`asof`wj`wj1`xkey`dekey`keys F:`select`update`delete`upsert`insert`exec G:,`fby / self KSQL:_d / access get:.: set:{(x;.[. x;();:;y];y)} / evaluation run:{[e]r:eval e;(.`BAGS;r)} tval:{[t]:[(1=#t)&-4=4:(),t;ksql[`get]@*t;iskey t;t;5=4:t;t;(u:*t)_in F;ksql[*u]. 1_ t;eval t]} eval:{[e]:[list e;:[1=#e 2;_f@*e 2;_f'e 2];(1<#e)&(-4=4:e)&~(*e)_in E;ksql[`get][*e]._f'1_ e;(1<#e)&(a:4:*e)_in 4 7;:[a=7;fval[_f]e;ksql[*e]. 1_ e];@e;:[4=4:e;ksql[`get]e;e];(1=#e)&-4=4:*e;*e;_f'e]} kval:{[t;e]:[list e;:[1=#e 2;_f[t]@*e 2;_f[t]'e 2];(1<#e)&(-4=4:e)&~(*e)_in E;_f[t]'e;4=4:e;:[e _in E;ksql@*e;t e];(1<#e)&(a:4:*e)_in 4 7;:[a=7;fval[_f[t]]e;ksql[*e]._f[t]'1_ e];(4=4:**e)*:/e]} fval:{[v;e]:[(:)~f:*e;{.[x;();:;y];z}. ksql[`set][e 1]v e 2;f . v'1_ e]} ksql:{[e].($.`KSQL),".",$e} / xkey, dekey iskey:{[t]:[4:t;0;~2=#t;0;~&/5=4::'t;0;=/(*|^:)'t[;]]} keys:{[t]:[5=4:t:tval t;0#`;!*t]} xkey:{[k;t]t:tval t;:[~#k;t;5=4:t;(.+(k;t k);t _di k@:&(k,:())_lin!t);_f[k]dekey t]} dekey:{[t]:[5=4:t:tval t;t;.,/.:'t]} \ / examples t:@[_n;`f`g`h`i`j;{:[@z;x _draw z;z x _draw#z]}[50];(`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;1));({(+/x)%#x};`i);(+/;(+;`i;`j)))) / 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] / update p:g*11,q:g+1111 by f,g from t where f=`c,g _lin 2 3 4,h>.5 ee:update[t;c;b;.()] / update by f,g from t where f=`c,g _lin 2 3 4,h>.5 ff:update[t;c;();aa] / update p:g*11,q:g+1111 from t where f=`c,g _lin 2 3 4,h>.5 gg:update[t;c;();.()] / update from t where f=`c,g _lin 2 3 4,h>.5 hh:update[t;();b;aa] / update p:g*11,q:g+1111 by f,g from t ii:update[t;();b;.()] / update by f,g from t jj:update[t;();();aa] / update p:g*11,q:g+1111 from t kk:update[t;();();.()] / update from t / all the forms of delete ll:delete[t;c;();.()] / delete from t where f=`c,g _lin 2 3 4,h>.5 mm:delete[t;();();`f`g] / delete f,g from t / all the forms of upsert 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)] v:.+(`f`h`g;(`x`y`z;1.1 2.2 3.3;100 200 300)) oo:upsert[t]v t:xkey[`k;.+(`k`f`g`h;(1 2 3 4 5;10 20 30 40 50;60 70 80 90 100;`a`b`c`d`e))] u:.+(`k`h`f;(3 9 2;`x`yyyyy`z;100 222 300)) pp:upsert[t]u / joins t:.+(`k`f;(1 1 1 4 4 3 3 3 3 3;!10)) u:xkey[`k;.+(`k`g;(1 2 3;`A`B`C))] xxx:lj[t]u yyy:ij[t]u 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:xkey[`k;.+(`k`a`b;(`a`b;10 20;1.1 2.2))] uuu:pj[t]u t:.+(`x`y`z;(`a`b;1 2;`x`y)) u:.+(`x`y`w;(`c`d;3 4;`X`Y)) vvv:uj[t]u t:xkey[`k;.+(`k`v`w;(1 2 3;`a`b`c;10 20 30))] 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))] \ / q-sql examples s:key[.((`s;`s1`s2`s3`s4`s5) (`name;`smith`jones`blake`clark`adams) (`status;20 10 30 20 30) (`city;`london`paris`paris`london`athens))]`s p:key[.((`p;`p1`p2`p3`p4`p5`p6) (`name;`nut`bolt`screw`screw`cam`cog) (`color;`red`green`blue`red`blue`red) (`weight;12 17 17 14 12 19) (`city;`london`paris`rome`london`paris`london))]`p sp:.((`s;`s1`s1`s1`s1`s4`s1`s2`s2`s3`s4`s4`s1) (`p;`p1`p2`p3`p4`p5`p6`p1`p2`p2`p2`p4`p5) (`qty;300 200 400 200 100 100 300 400 200 200 300 400)) a k3 implementation of k4 select, update, delete, upsert and exec ================================================================= 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 old slow buggy idx: idx:{[t;u;k]:[1=#k;(?u k)?/:t k*:;(?i)?/:i:(#:'j)_sv(j:?:'u k)?/:'t k]}