Data Analysis
qSQL
The full qSQL query language — select, exec, update, delete, fby, and advanced aggregations.
qSQL Syntax
qSQL is q's built-in query syntax. It resembles SQL, but operates on q's column-oriented tables, both in memory and on disk. The general shape of a select is:
select [columns] [by groups] from table [where conditions]SELECT
t: ([]
sym: `AAPL`GOOG`MSFT`AAPL`GOOG;
price: 182.5 2840.0 310.2 183.1 2845.0;
size: 100 50 200 150 75;
side: `buy`sell`buy`buy`sell)
/ Select all columns
select from t
/ Select specific columns
select sym, price from t
/ Rename column
select sym, mid: price from t
/ Computed column
select sym, notional: price * size from tWHERE Clauses
/ Single condition
select from t where sym=`AAPL
/ Multiple conditions (AND — comma-separated)
select from t where sym=`AAPL, price > 182.0
/ IN clause
select from t where sym in `AAPL`GOOG
/ String matching
select from t where sym like "AA*"
/ Range
select from t where price within 180.0 185.0Important: Comma-separated where conditions are applied left to right, and each condition is evaluated only on the rows that passed the previous ones. Put the most restrictive condition first for better performance.
BY Clause (GROUP BY)
/ Count trades per symbol
select count i by sym from t
/ Average price and total size per symbol
select avg price, sum size by sym from tThe result is a keyed table, keyed on the by column:
sym | price size
----| -----------
AAPL| 182.8 250
GOOG| 2842.5 125
MSFT| 310.2 200EXEC
exec returns a list or dictionary rather than a table:
/ Returns a list (one column)
exec sym from t / `AAPL`GOOG`MSFT`AAPL`GOOG
/ Returns a dict (with by clause)
exec sum size by sym from t
/ `AAPL`GOOG`MSFT!250 125 200UPDATE
/ Add or modify a column (returns new table)
update notional: price * size from t
/ Modify in place (use backtick)
update notional: price * size from `t
/ Conditional update
update price: price * 1.01 from t where side=`buyDELETE
/ Delete rows matching condition
delete from t where sym=`GOOG
/ Delete a column
delete size from t
/ Both operate in-place with backtick prefix
delete from `t where sym=`GOOGfby — Filter By (Aggregation in WHERE)
fby applies a group aggregation in the where clause, enabling row-level filtering against group-level statistics:
/ Select trades where price is above the symbol's average
select from t where price > (avg;price) fby sym
/ Trades larger than the median size for that symbol
select from t where size > (med;size) fby sym
/ Most recent trade per symbol (common pattern)
select from t where i = (last;i) fby symAggregation Functions
/ Common aggregations used in select/by
select
n: count i,
total: sum size,
avg_p: avg price,
vwap: size wavg price, / volume-weighted average
hi: max price,
lo: min price,
rng: max[price]-min price
by sym from tsym | n total avg_p vwap hi lo rng
----| -------------------------------------
AAPL| 2 250 182.8 182.86 183.1 182.5 0.6
GOOG| 2 125 2842.5 2843 2845 2840 5
MSFT| 1 200 310.2 310.2 310.2 310.2 0Joins in qSQL
/ Add a reference table
ref: ([sym:`AAPL`GOOG`MSFT] sector:`Tech`Tech`Tech; mktcap:3000000 1900000 2800000)
/ lj: left join (keyed table on right)
select from t lj ref
/ aj: as-of join (time-series, inexact match)
quotes: ([]
time: 12:30:00.000 12:30:01.000 12:30:02.000;
sym: `AAPL`AAPL`AAPL;
bid: 182.4 182.5 182.3;
ask: 182.6 182.7 182.5)
trades2: ([]
time: 12:30:00.500 12:30:01.750;
sym: `AAPL`AAPL;
price: 182.5 182.4)
/ Join each trade to the last quote before it
aj[`sym`time; trades2; quotes]Functional Form
Every qSQL expression has an equivalent functional form. This is useful for building queries programmatically:
/ Parse qSQL to see functional form
parse "select avg price by sym from t"
/ (?;`t;();(,`sym)!,`sym;(,`price)!,(avg;`price))
/ Equivalent functional call
?[t; (); (enlist`sym)!enlist`sym; (enlist`price)!enlist(avg;`price)]
/ Dynamic select — columns as a list variable
c: `sym`price
?[t; (); 0b; c!c]