Functional qSQL

The functional forms of delete, exec, select and update are particularly useful for programmatically-generated queries, such as when column names are dynamically produced.

Performance

The q interpreter parses delete, exec, select, and update into their equivalent functional forms, so there is no performance difference.

The functional forms are

![t;c;b;a]              /update and delete

?[t;i;p]                /simple exec

?[t;c;b;a]              /select or exec
?[t;c;b;a;n]            /select up to n records
?[t;c;b;a;n;(g;cn)]     /select up to n records sorted by g on cn

where:

t

is a table, or the name of a table as a symbol atom.

c

is the Where phrase, a list of constraints.

Every constraint in c is a parse tree representing an expression to be evaluated; the result of each being a boolean vector. The parse tree consists of a function followed by a list of its arguments, each an expression containing column names and other variables. (Represented by symbols; distinguish actual symbol constants by enlisting them.) The function is applied to the arguments, producing a boolean vector that selects the rows. The selection is performed in the order of the items in c, from left to right: only rows selected by one constraint are evaluated by the next.

b

is the By phrase, one of:

• the general empty list ()
• boolean atom: 0b for no grouping; 1b for distinct
• a symbol atom or list naming table column/s
• a dictionary of group-by specifications

The domain of dictionary b is a list of symbols that are the key names for the grouping. Its range is a list of column expressions (parse trees) whose results are used to construct the groups. The grouping is ordered by the domain items, from major to minor.

a

is the Select phrase, one of:

• the general empty list ()
• a symbol atom: the name of a table column
• a parse tree
• a dictionary of select specifications (aggregations)

The domain of dictionary a is a list of symbols containing the names of the produced columns. (QSQL query templates assign default column names in the result, but here each result column must be named explicitly.)

Each item of its range is an evaluation list consisting of a function and its argument/s, each of which is a column name or another such result list. For each evaluation list, the function is applied to the specified value/s for each row and the result is returned. The evaluation lists are resolved recursively when operations are nested.

i

is a list of indexes

p

is a parse tree

n

is a non-negative integer or infinity, indicating the maximum number of records to be returned

g

is a unary grade function

Call by name

Columns in a, b and c appear as symbols.

To distinguish symbol atoms and vectors from columns, enlist them.

q)t:([] c1:`a`b`a`c`a`b`c; c2:10*1+til 7; c3:1.1*1+til 7)

q)select from t where c2>35,c1 in `b`c
c1 c2 c3
---------
c  40 4.4
b  60 6.6
c  70 7.7

q)?[t; ((>;`c2;35);(in;`c1;enlist[`b`c])); 0b; ()]
c1 c2 c3
---------
c  40 4.4
b  60 6.6
c  70 7.7

Note above that

• the columns c1 and c2 appear as symbol atoms
• the symbol vector `b`c appears as enlist[`b`c]

Use enlist to create singletons to ensure appropriate entities are lists.

Different types of a and b return different types of result for Select and Exec.

           | b
a          | bool    ()         sym/s   dict
-----------|----------------------------------------
()         | table    dict       -       keyed table
sym        | -        vector     dict    dict
parse tree | -        vector     dict    dict
dict       | table    vector/s   table   table 

? Select

?[t;c;b;a]

Where t, c, b, and a are as above, returns a table.

q)show t:([]n:`x`y`x`z`z`y;p:0 15 12 20 25 14)
n p
----
x 0
y 15
x 12
z 20
z 25
y 14

q)select m:max p,s:sum p by name:n from t where p>0,n in `x`y
name| m  s
----| -----
x   | 12 12
y   | 15 29

select

Following is the equivalent functional form. Note the use of enlist to create singletons, ensuring that appropriate entities are lists.

q)c: ((>;`p;0);(in;`n;enlist `x`y))
q)b: (enlist `name)!enlist `n
q)a: `m`s!((max;`p);(sum;`p))
q)?[t;c;b;a]
name| m  s
----| -----
x   | 12 12
y   | 15 29

Degenerate cases

• For no constraints, make c the empty list
• For no grouping make b a boolean 0b
• For distinct rows make b a boolean 1b
• To produce all columns of t in the result, make a the empty list ()

select from t is equivalent to functional form ?[t;();0b;()].

Select distinct

For special case select distinct specify b as 1b.

q)t:([] c1:`a`b`a`c`b`c; c2:1 1 1 2 2 2; c3:10 20 30 40 50 60)

q)?[t;(); 1b; `c1`c2!`c1`c2]        / select distinct c1,c2 from t
c1 c2
-----
a  1
b  1
c  2
b  2

Rank 5

Limit result rows

?[t;c;b;a;n]

Returns as for rank 4, but where n is

• an integer or infinity, only the first n rows, or the last if n is negative
• a pair of non-negative integers, up to n[1] rows starting with row n[0]

q)show t:([] c1:`a`b`c`a; c2:10 20 30 40)
c1 c2
-----
a  10
b  20
c  30
a  40

q)?[t;();0b;();-2]                   / select[-2] from t
c1 c2
-----
c  30
a  40

q)?[t;();0b;();1 2]                 / select[1 2] from t
c1 c2
-----
b  20
c  30

Rank 6

Limit result rows and sort by a column

?[t;c;b;a;n;(g;cn)]

Returns as for rank 5, but where

• g is a unary grading function
• cn is a column name as a symbol atom

sorted by g on column cn.

q)?[t; (); 0b; `c1`c2!`c1`c2; 0W; (idesc;`c1)]
c1 c2
-----
c  30
b  20
a  10
a  40

Q for Mortals §9.12.1 Functional select

? Exec

A simplified form of Select that returns a list or dictionary rather than a table.

?[t;c;b;a]

The constraint specification c (Where) is as for Select.

q)show t:([] c1:`a`b`c`c`a`a; c2:10 20 30 30 40 40; 
    c3: 1.1 2.2 3.3 3.3 4.4 3.14159; c4:`cow`sheep`cat`dog`cow`dog)
c1 c2 c3      c4
-------------------
a  10 1.1     cow
b  20 2.2     sheep
c  30 3.3     cat
c  30 3.3     dog
a  40 4.4     cow
a  40 3.14159 dog

exec

No grouping

b is the general empty list.

b   a      result
--------------------------------------------------------------
()  ()     the last row of t as a dictionary
()  sym    the value of that column
()  dict   a dictionary with keys and values as specified by a

q)?[t; (); (); ()]                          / exec last c1,last c2,last c3 from t
c1| `a
c2| 40
c3| 3.14159
c4| `dog

q)?[t; (); (); `c1]                         / exec c1 from t
`a`b`c`c`a`a

q)?[t; (); (); `one`two!`c1`c2]             / exec one:c1,two:c2 from t
one| a  b  c  c  a  a
two| 10 20 30 30 40 40

q)?[t; (); (); `one`two!(`c1;(sum;`c2))]    / exec one:c1,two:sum c2 from t
one| `a`b`c`c`a`a
two| 170

Group by column

b is a column name. The result is a dictionary.

Where a is a column name, in the result

• the keys are distinct values of the column named in b
• the values are lists of corresponding values from the column named in a

q)?[t; (); `c1; `c2]     / exec c2 by c1 from t
a| 10 40 40
b| ,20
c| 30 30

Where a is a dictionary, in the result

• the key is a table with a single anonymous column containing distinct values of the column named in b
• the value is a table with columns as defined in a

q)?[t; (); `c1; enlist[`c2]!enlist`c2]      / exec c2:c2 by c1 from t
 | c2
-| --------
a| 10 40 40
b| ,20
c| 30 30

q)?[t; (); `c1; `two`three!`c2`c3]          / exec two:c2,three:c3 by c1 from t
 | two      three
-| ------------------------
a| 10 40 40 1.1 4.4 3.14159
b| ,20      ,2.2
c| 30 30    3.3 3.3

q)?[t;();`c1;`m2`s3!((max;`c2);(sum;`c3))]  / exec m2:max c2,s3:sum c3 by c1 from t
 | m2  s3
-| -----------
a| 40  8.64159
b| 20  2.2
c| 30  6.6

Group by columns

b is a list of column names.

Where a is a column name, returns a dictionary in which

• the key is the empty symbol
• the value is the value of the column/s specified in a

q)?[t; (); `c1`c2; `c3]
| 1.1 2.2 3.3 3.3 4.4 3.14159

q)?[t; (); `c1`c2; `c3`c4!((max;`c3);(last;`c4))]
| c3  c4
| -------
| 4.4 dog

Group by a dictionary

b is a dictionary. Result is a dictionary in which the key is a table with columns as specified by b and

b     a     result value
-----------------------------------------------------
dict  ()    last records of table that match each key
dict  sym   corresponding values from the column in a
dict  dict  values as defined in a

q)?[t; (); `one`two!`c1`c2; ()]
one two| c1 c2 c3      c4
-------| -------------------
a   10 | a  10 1.1     cow
a   40 | a  40 3.14159 dog
b   20 | b  20 2.2     sheep
c   30 | c  30 3.3     dog
q)/ exec last c1,last c2,last c3,last c4 by one:c1,two:c2 from t

q)?[t; (); enlist[`one]!enlist(string;`c1); ()]
one | c1 c2 c3      c4
----| -------------------
,"a"| a  40 3.14159 dog
,"b"| b  20 2.2     sheep
,"c"| c  30 3.3     dog
q)/ exec last c1,last c2,last c3,last c4 by one:string c1 from t

q)?[t; (); enlist[`one]!enlist `c1; `c2]     / exec c2 by one:c1 from t
one|
---| --------
a  | 10 40 40
b  | ,20
c  | 30 30

q)?[t; (); `one`four!`c1`c4; `m2`s3!((max;`c2);(sum;`c3))]
one four | m2 s3
---------| ----------
a   cow  | 40 5.5
a   dog  | 40 3.14159
b   sheep| 20 2.2
c   cat  | 30 3.3
c   dog  | 30 3.3

Q for Mortals §9.12.2 Functional exec

? Simple Exec

?[t;i;p]

Where t is not partitioned, another form of Exec.

q)show t:([]a:1 2 3;b:4 5 6;c:7 9 0)
a b c
-----
1 4 7
2 5 9
3 6 0

q)?[t;0 1 2;`a]
1 2 3
q)?[t;0 1 2;`b]
4 5 6
q)?[t;0 1 2;(last;`a)]
3
q)?[t;0 1;(last;`a)]
2
q)?[t;0 1 2;(*;(min;`a);(avg;`c))]
5.333333

! Update

![t;c;b;a]

update

Arguments t, c, b, and a are as for Select.

q)show t:([]n:`x`y`x`z`z`y;p:0 15 12 20 25 14)
n p
----
x 0
y 15
x 12
z 20
z 25
y 14

q)select m:max p,s:sum p by name:n from t where p>0,n in `x`y
name| m  s
----| -----
x   | 12 12
y   | 15 29

q)update p:max p by n from t where p>0
n p
----
x 0
y 15
x 12
z 25
z 25
y 15

q)c: enlist (>;`p;0)
q)b: (enlist `n)!enlist `n
q)a: (enlist `p)!enlist (max;`p)

q)![t;c;b;a]
n p
----
x 0
y 15
x 12
z 25
z 25
y 15

The degenerate cases are the same as in Select.

Q for Mortals §9.12.3 Functional update

! Delete

A simplified form of Update

![t;c;0b;a]

delete

One of c or a must be empty, the other not. c selects which rows will be removed. a is a symbol vector with the names of columns to be removed.

q)t:([]c1:`a`b`c;c2:`x`y`z)

q)/following is: delete c2 from t
q)![t;();0b;enlist `c2]
c1
--
a
b
c

q)/following is: delete from t where c2 = `y
q)![t;enlist (=;`c2; enlist `y);0b;`symbol$()]
c1 c2
-----
a  x
c  z

Q for Mortals §9.12.4 Functional delete

---

qSQL
Q for Mortals §9.12 Functional Forms
Functional Query Functions