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.

Contents

  1. Functional Select
  2. Parse
  3. Functional Exec
  4. Functional Update
  5. 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:

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.

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.

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.

Functional Update

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

Functional Delete

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

See Also