Functional Select Kdb Queries

Functional queries allow specifying column names as symbols to typical q-sql select/exec/delete columns, this is extremely useful when you want to specify column names dynamically. Typically in standard sql developers have concatenated strings to form dynamic queries, as you will see q's functional dynamic queries are much more powerful.

Functional Select
Parse
Functional Exec
Functional Update
Functional Delete

Functional Select

Kdb allows specifying "select c1,c2 by c3 from table where conditions" in a functional format that allows supplying native kdb data structures to specify the arguments. The format is:

Functional Select: ?[ table; whereConditions; groupBy; selectedColumns]

Select columns from a table that meet the whereConditions grouped by given columns

whereConditions: list of where clauses in functional form uses symbols for column names
groupBy: dictionary from new column names that are grouped by to column expressions.
selectedColumns: column expressions

Here are some increasing complex examples of functional select:

q)?[`t; (); 0b; ()] / select from t
a b c
--------------
q 0 6.910318
q 1 1.375553
p 2 4.32439
p 3 0.8958048
q 4 8.185765
p 5 8.372398
q 6 0.01820672

q)whereCon: enlist (<;`b;4);
q)?[ `t; whereCon; 0b; ()]   / select from t where b<4
a b c
-------------
q 0 6.910318
q 1 1.375553
p 2 4.32439
p 3 0.8958048

q)groupBy: enlist[`a]!enlist `a
q)selCols: enlist[`c]!enlist `c
q)?[ `t; (); groupBy; selCols]   / select c by a from t
a| c
-| -------------------------------------
p| 4.32439 0.8958048 8.372398
q| 6.910318 1.375553 8.185765 0.01820672

As you can see the syntax is cryptic and hard to form, particularly the where conditions. Below we will consider parse and how it makes forming functional queries much easier.

Dynamic SQL - Select variable Columns

Below is an example of functions that accept the columns it should return as a parameter symbol list. One method uses string concatenation and the second functional form.

q)getTcolsString:{ c:"," sv string (),x; value 0N!"select ",c," from t"}
q)getTcolsString `a`b
"select a,b from t"
a b
---
q 0
q 1
p 2
p 3
q 4
p 5
q 6

q)getTcols:{ c:(),x; ?[`t; (); 0b; c!c]}
q)getTcols `a`b
a b
---
q 0
q 1
p 2
p 3
q 4
p 5
q 6

The 0N! here is used to output the query string being evaluated for debug purposes. As you can see either method works, however it leaves us vulnerable to users supplying raw qsql and cases where columns have spaces in their names would cause an error.

Parse

The easiest way to find functional form of any query is to parse it. Parse is a builtin q function that returns the parse-tree for a given string. We can use the format that it returns as a guide to constructing our functional select. e.g.

q)t:([] a:8?`a`b; b:til 8; c:8?9.)
q)?[`t; (); 0b; ()]  // simple select all case
a b c
-------------
b 0 1.723948
a 1 5.340811
b 2 0.3740114
a 3 3.464688
b 4 6.814478
b 5 4.779729
a 6 3.952289
a 7 5.364171
q)parse "select max c by a from t where b<5"
?
`t
,,(<;`b;5)
(,`a)!,`a
(,`c)!,(max;`c)

q)?[ `t; enlist (<;`b;5); enlist[`a]!enlist `a; enlist[`c]!enlist (max;`c)]
a| c
-| --------
a| 5.340811
b| 6.814478

The format parse displays at the console is actual K rather than q, so can take a little getting used to. The comma's here e.g. ,,(<;`b;5) generally mean enlist.

Functional Exec

Functional Exec: ?[ table; whereConditions; groupBy; selectedColumns]
is the same as select, but groupBy and selectedColumns are sometimes lists rather than dictionaries, dependent on number of columns.

q)?[ `t; (); (); `c] / exec a single columns values only, returns a list
6.910318 1.375553 4.32439 0.8958048 8.185765 8.372398 0.01820672
q)?[ `t; (); `a; `c] / exec grouped by gives a dictionary from distinct groups
p| 4.32439 0.8958048 8.372398
q| 6.910318 1.375553 8.185765 0.01820672

Functional Update

Functional Update: ![ table; whereConditions; groupBy; updatedColumns]
is the same as select, but uses ! instead. e.g.

q)parse "update d:`myval,c:13 from t"
!
`t
()
0b
`d`c!(,`myval;13)
q)![t; (); 0b; `d`c!(enlist `myval;13)] / update and return
a b c  d
------------
q 0 13 myval
q 1 13 myval
p 2 13 myval
p 3 13 myval
q 4 13 myval
p 5 13 myval
q 6 13 myval


q)t
a b c
--------------
q 0 6.910318
q 1 1.375553
p 2 4.32439
p 3 0.8958048
q 4 8.185765
p 5 8.372398
q 6 0.01820672
q)![`t; (); 0b; `d`c!(enlist `myval;13)] / use backtick to make it actually modify t
`t
q)t
a b c  d
------------
q 0 13 myval
q 1 13 myval
p 2 13 myval
p 3 13 myval
q 4 13 myval
p 5 13 myval
q 6 13 myval

Functional Delete

Functional Delete Columns: ![table; (); 0b; columnList]
Functional Delete Rows: ![table; whereConditions; 0b; columnList]

q)![t; (); 0b; enlist `a] / delete a column
b c
------------
0 6.910318
1 1.375553
2 4.32439
3 0.8958048
4 8.185765
5 8.372398
6 0.01820672
q)![t; enlist (<;`b;4); 0b; `symbol$()] / delete rows
a b c
--------------
q 4 8.185765
p 5 8.372398
q 6 0.01820672

Functional Select Kdb Queries

Contents