Functional selects/updates are a relatively trick topic in kdb+ – mainly as the syntax takes a lot of getting used to. They are normally required when there are some dynamic elements in e.g. column selection or grouping criteria.
They are pretty well covered in Q For Mortals, but I wanted to add a couple of examples…combining functional select and update for example:
To start, load the sample tables in sp.q..we will use the table called ‘p’:
\l sp.q
q)p
p | name color weight city w
--| ----------------------------
p1| nut red 12 london 91
p2| bolt green 17 paris 91
p3| screw blue 17 rome 91
p4| screw red 14 london 91
p5| cam blue 12 paris 91
p6| cog red 19 london 91
Functional Selects
A simple select from p with some criteria:
q)select from p where city=`london
p | name color weight city w
--| ----------------------------
p1| nut red 12 london 91
p4| screw red 14 london 91
p6| cog red 19 london 91
Now lets look at the parse tree for this query:
q)parse "select from p where city=`london"
?
`p
,,(=;`city;,`london)
0b
()
Now in order to convert this to a functional select, we need to turn this parse tree into an executable statement using the ? operator.
The basic form of the ? operator is
?[tablename;(select criteria);(grouping criteria);(columns)]
The parse tree above gives us each of the four elements in the right order – we just need to convert them to a valid functional syntax. For the example above this translates to:
q)?[p;enlist (=;`city;enlist `london);0b;()]
p | name color weight city w
--| ----------------------------
p1| nut red 12 london 91
p4| screw red 14 london 91
p6| cog red 19 london 91
So if we want to add e.g. column selection:
q)select name,color,weight from p where city=`london
name color weight
------------------
nut red 12
screw red 14
cog red 19
The parse tree looks like:
q)parse"select name,color,weight from p where city=`london"
?
`p
,,(=;`city;,`london)
0b
`name`color`weight!`name`color`weight
In this case we have added a dictionary mapping selected columns to their output names:
q)?[p;enlist (=;`city;enlist `london);0b;(`name`color`weight)!(`name`color`weight)]
name color weight
------------------
nut red 12
screw red 14
cog red 19
Similarly, we can change the select criteria:
q)select name,color,weight from p where city in `london`paris
name color weight
------------------
nut red 12
bolt green 17
screw red 14
cam blue 12
cog red 19
Which produces the following parse tree:
q)parse"select name,color,weight from p where city in `london`paris"
?
`p
,,(in;`city;,`london`paris)
0b
`name`color`weight!`name`colour`weight
This is only a small modification to the original functional select:
q)?[p;enlist (in;`city;enlist `london`paris);0b;(`name`color`weight)!(`name`color`weight)]
name color weight
------------------
nut red 12
bolt green 17
screw red 14
cam blue 12
cog red 19
Functional Updates
Functional updates follow an almost identical form, but use the ! operator, e..g
q)update w:sum weight by city from p
p | name color weight city w
--| ----------------------------
p1| nut red 12 london 45
p2| bolt green 17 paris 29
p3| screw blue 17 rome 17
p4| screw red 14 london 45
p5| cam blue 12 paris 29
p6| cog red 19 london 45
q)parse "update w:sum weight by city from p"
!
`p
()
(,`city)!,`city
(,`w)!,(sum;`weight)
This parse tree maps to:
q)![p;();(enlist `city)!enlist `city;(enlist `w)!enlist (sum;`weight)]
p | name color weight city w
--| ----------------------------
p1| nut red 12 london 45
p2| bolt green 17 paris 29
p3| screw blue 17 rome 17
p4| screw red 14 london 45
p5| cam blue 12 paris 29
p6| cog red 19 london 45
Now if we want to update the output from a select, e.g. a simple grouped update:
q)update w:sum weight by color from select name,color,weight from p where city in `london`paris
name color weight w
---------------------
nut red 12 45
bolt green 17 17
screw red 14 45
cam blue 12 12
cog red 19 45
The parse tree for this looks like:
q)parse"update w:sum weight by color from select name,color,weight from p where city in `london`paris"
!
(?;`p;,,(in;`city;,`london`paris);0b;`name`color`weight!`name`color`weight)
()
(,`color)!,`color
(,`w)!,(sum;`weight)
This parse tree looks complex, but the main complexity comes from the nested functional select within.
We could explicitly write the entire function (nested select and update):
q)![?[p;enlist (in;`city;enlist `london`paris);0b;`name`color`weight!`name`color`weight];();(enlist `color)!enlist `color;(enlist `w)!enlist (sum;`weight)]
name color weight w
---------------------
nut red 12 45
bolt green 17 17
screw red 14 45
cam blue 12 12
cog red 19 45
However it may be easier to read if we store the select in its own variable:
q)sel::?[p;enlist (in;`city;enlist `london`paris);0b;`name`color`weight!`name`color`weight]
q)sel
name color weight
------------------
nut red 12
bolt green 17
screw red 14
cam blue 12
cog red 19
And then refer to the select thus:
q)![sel;();(enlist `color)!enlist `color;(enlist `w)!enlist (sum;`weight)]
name color weight w
---------------------
nut red 12 45
bolt green 17 17
screw red 14 45
cam blue 12 12
cog red 19 45