Chapter 6 of 8

qSQL: querying tables

Everything so far has been the K layer. qSQL is a surface syntax on top of it: you type select ... by ... from ... where ... and the line is rewritten into the array code from the previous chapters before it runs.

That matters in two ways. You can always drop back down to the array level, and anything you can express as an array operation can go inside a query.

trades: ([] sym: `a`b`a`b`a; px: 10 20 30 40 50.0; sz: 1 2 3 4 5)

select

select from trades
select sym, px from trades
select from trades where px > 25
select from trades where px > 25, sz < 5        / comma = AND
select avg px by sym from trades                / grouped -> keyed table
select s: sum sz, n: # px by sym from trades    / named aggregates
select vwap: wavg[sz; px] by sym from trades

Bare column names become column lookups

Inside a query, px means "the px column of the row set being considered", so wavg[sz;px] is just the library call you already know, applied to two columns. There is no separate expression language.

Sorted and limited selects

Since 2.2 the bracket clause gives you ordering and a row limit, in q's order: where, then by/select, then sort, then limit.

select[3] from trades             / first 3 rows
select[-2] from trades            / last 2 rows
select[>px] from trades           / sorted by px descending
select[<px] from trades           / ... ascending
select[2;>px] from trades         / sort, THEN take 2
select[<sym;>px] from trades      / two keys; the first listed is primary
select[2;>s] s: sum px by sym from trades   / works on a grouped result too

exec, update, delete

exec px from trades where sym = `a      / a plain vector, not a table
exec px, sz from trades                 / several columns -> a dict of vectors
update px: px * 1.01 from trades where sym = `a
update z: px * sz from trades           / add a computed column
delete from trades where sz < 2         / drop rows
delete sz from trades                   / drop a column

On a table value these return a new table, so assign it if you want to keep it. On a name, update … from `trades and delete from `trades where … change the table in place, as in q.

fby — an aggregate broadcast back over rows

fby computes an aggregate per group and aligns it back to every row of that group, which is what you want for "rows above their group's average":

select from trades where px = (max; px) fby sym     / each sym's top-priced rows
select from trades where px > (avg; px) fby sym
select sym, m: (max; px) fby sym from trades        / ... or as a column
fby[(sum; trades`sz); trades`sym]                   / the functional form

The group specification may be several columns, written as a list or as a table:

trades: ([] sym: `a`b`a`b`a; ex: `X`X`Y`Y`X; px: 10 20 30 40 50.0; sz: 1 2 3 4 5)
fby[(max; trades`px); (trades`sym; trades`ex)]
select from trades where px = (max; px) fby (sym; ex)

The functional forms

Every template has a function underneath it. Reach for these when the query itself is computed:

t: ([] sym: `a`b`a; px: 10 20 30.0; sz: 1 2 3)
qwhere[t; (t`px) > 15]                       / select rows by a mask
qselect[t; (,`v) ! ,{x[`px] * x`sz}]         / computed columns
qby[t; `sym; (,`tot) ! ,{sum x`sz}]          / group aggregate -> keyed table
sel"select from t where px > 15"              / the string form of the template

Where the template does and does not work

Bare select is a line-level rewrite installed by the standard library. It works at the prompt, and in any file you pull in with \l from a running session.

It does not work in a file run as ./amber script.k, because that runs with no standard library loaded at all, and neither the rewrite nor sel nor qwhere exists there. Load the library first:

\l amber.k
\l std.k
\l qsql.k

t: ([] sym: `a`b; px: 1 2.0)
select from t where px > 1