Functional Selects/Updates in kdb+

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’:

[code]
\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
[/code]

Functional Selects

A simple select from p with some criteria:

[code]
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
[/code]

Now lets look at the parse tree for this query:

[code]
q)parse "select from p where city=`london"
?
`p
,,(=;`city;,`london)
0b
()
[/code]

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

[code]
?[tablename;(select criteria);(grouping criteria);(columns)]
[/code]

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:

[code]
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
[/code]

So if we want to add e.g. column selection:

[code]
q)select name,color,weight from p where city=`london
name color weight
——————
nut red 12
screw red 14
cog red 19
[/code]

The parse tree looks like:

[code]
q)parse"select name,color,weight from p where city=`london"
?
`p
,,(=;`city;,`london)
0b
`name`color`weight!`name`color`weight
[/code]

In this case we have added a dictionary mapping selected columns to their output names:

[code]
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
[/code]

Similarly, we can change the select criteria:

[code]
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
[/code]

Which produces the following parse tree:

[code]
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
[/code]

This is only a small modification to the original functional select:

[code]
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
[/code]

Functional Updates

Functional updates follow an almost identical form, but use the ! operator, e..g

[code]
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)
[/code]

This parse tree maps to:

[code]
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
[/code]

Now if we want to update the output from a select, e.g. a simple grouped update:

[code]
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
[/code]

The parse tree for this looks like:

[code]
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)
[/code]

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):

[code]
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
[/code]

However it may be easier to read if we store the select in its own variable:

[code]
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
[/code]

And then refer to the select thus:

[code]
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
[/code]