In Memory Tables

Creating Tables

Tables in q are defined using the ([] ...) syntax. Each column is a name, a colon, and a list; all columns must have the same length:

/ Simple unkeyed table
t: ([]
  sym:   `AAPL`GOOG`MSFT`AAPL;
  price: 182.5 2840.0 310.2 183.1;
  size:  100 50 200 150)

/ Inspect
t

sym  price size
---------------
AAPL 182.5 100
GOOG 2840  50
MSFT 310.2 200
AAPL 183.1 150

In a script, the continuation lines must be indented, as above. At the q prompt, type the whole definition on one line.

Avoid using q keywords such as last, value or count as column names: they cannot be assigned to, and a definition that uses them fails with 'assign.

Keyed Tables

A keyed table is a dictionary where both the keys and the values are tables. Key columns go inside the square brackets:

kt: ([sym:`AAPL`GOOG`MSFT]
     lastPx: 183.1 2841.5 311.0;
     vol:    1500000 300000 800000)

/ Look up a row by key
kt[`AAPL]

lastPx| 183.1
vol   | 1500000

n!t keys an unkeyed table on its first n columns:

1!t    / keyed on sym
2!t    / keyed on sym and price

Meta and Schema

meta shows each column's name (c), type character (t), foreign key (f) and attribute (a):

meta t

c    | t f a
-----| -----
sym  | s
price| f
size | j

type returns a column's type as a short integer — positive for a list:

type t`size     / 7h — a list of longs
type t`price    / 9h — a list of floats

Accessing Columns and Rows

/ Column access
t`sym         / `AAPL`GOOG`MSFT`AAPL
t`price       / 182.5 2840 310.2 183.1

/ Row access by index: a row is a dictionary
t[0]          / `sym`price`size!(`AAPL;182.5;100)

/ Last row
last t

Adding and Removing Columns

/ Create a new table with an extra column
t2: update notional: price * size from t

/ Modify t in place: pass the table name as a symbol
update notional: price * size from `t

/ Remove a column (returns a new table)
delete size from t

Joining Tables

t1: ([] sym:`AAPL`GOOG; price:182.5 2840.0)
t2: ([] sym:`AAPL`GOOG; vol:1000000 200000)

/ Join Each: join corresponding rows, side by side
t1,'t2        / columns sym, price, vol

/ Append rows: only works when both tables have the same columns
t1,t2         / 'mismatch — the columns differ

/ lj: left join against a keyed table
kt2: ([sym:`AAPL`GOOG] sector:`Tech`Tech)
t1 lj kt2     / adds the sector column

See the Joins topic for the full set of joins.

Attributes

Attributes are metadata on a list that tell q how its values are arranged, so it can use faster algorithms. Each one has a precondition, and applying it to data that does not meet it is an error:

st: `sym xasc t                         / sort the table by sym first

update `s#price from `price xasc t      / sorted: values must be in ascending order
update `p#sym from st                   / parted: equal values must be in contiguous blocks
update `g#sym from t                    / grouped: no precondition; q builds a hash index
update `u#sym from ([] sym:`A`B`C)      / unique: every value must be distinct

Applying `s# to the unsorted sym column of t fails with 's-fail, and `u# on a column with repeated values fails with 'u-fail.

Performance note: In a partitioned database, each table is normally sorted by sym within a partition and given the parted attribute (`p#) on sym, so a query such as where sym=`AAPL reads only that symbol's contiguous block rather than the whole column. See the Attributes topic for details.

Upsert

upsert appends rows to an unkeyed table, and on a keyed table updates the row with a matching key or inserts it if the key is new. When building a one-row table to upsert, wrap each value in enlist so that each column is a list:

/ Append to an unkeyed table
`t upsert ([] sym:enlist `FB; price:enlist 350.0; size:enlist 250; notional:enlist 87500.0)

/ Or upsert a single row given as a list of values
`t upsert (`IBM; 150.0; 10; 1500.0)

/ Keyed table: FB is a new key, so it is inserted ...
`kt upsert ([sym:enlist `FB] lastPx:enlist 350.0; vol:enlist 500000)

/ ... and AAPL already exists, so its row is updated
`kt upsert (`AAPL; 184.0; 1600000)

Practical: Building a Tick Table

/ Define an empty table with typed columns
trades: ([]
  time:  `time$();
  sym:   `symbol$();
  price: `float$();
  size:  `long$())

/ Insert rows
`trades insert (12:30:00.000; `AAPL; 182.5; 100)
`trades insert (12:30:01.500; `GOOG; 2840.0; 50)

/ Query
select from trades where sym=`AAPL

time         sym  price size
----------------------------
12:30:00.000 AAPL 182.5 100