Chapter 7 of 8

Time series: joins, temporal types, bars and attributes

This is what the engine is for. Everything in this chapter assumes two tables: trades and quotes, the shape every tick workload has.

trades: ([] sym: `a`b`a; time: 3 4 9; px: 100 200 300.0; sz: 10 20 30)
quotes: ([] sym: `a`a`b`a; time: 1 5 2 8; bid: 10 11 20 12.0)

The join family

join	what it does
`lj[t;kt]`	left join against a keyed table
`ij[t;kt]`	inner join
`uj[x;y]`	union join, the column-wise concatenation
`pj[t;kt]`	plus join, which adds matching numeric columns
`ej[c;x;y]`	equi join on named columns
`aj[c;x;y]`	as-of join, the important one
`wj[w;c;t;(q;a..)]`	window join, aggregating over a window per row

As-of join

aj answers "what was the prevailing quote at the moment of each trade". The last column named is the time key; the ones before it are the matching keys:

trades: ([] sym: `a`b`a; time: 3 4 9; px: 100 200 300.0; sz: 10 20 30)
quotes: ([] sym: `a`a`b`a; time: 1 5 2 8; bid: 10 11 20 12.0)
aj[`sym`time; trades; quotes]

Each trade keeps its own columns and gains the quote columns as they stood at or before its timestamp, within its own symbol. That is the whole operation: one line, running a native C kernel rather than a loop.

Window join

Where aj takes the single prevailing row, wj aggregates everything inside a window around each row (plus the quote in force when it opens, like q; wj1 leaves that one out), for example the average quoted bid in the two seconds before each trade:

trades: ([] sym: `a`b`a; time: 3 4 9; px: 100 200 300.0; sz: 10 20 30)
quotes: ([] sym: `a`a`b`a; time: 1 5 2 8; bid: 10 11 20 12.0)
w: +(trades`time)-\:2 0        / the window, as (begins;ends)
wj[w; `sym`time; trades; (quotes; (avg; `bid))]

The window is two rows, not one pair per row

wj wants (begins;ends), a 2-item list of vectors, so the + matters. Without it you hand it one (begin;end) pair per row and get a length-mismatch error pointing into wjK.

How times are stored

Temporal columns are numeric underneath: a date is an integer count of days, a time is an integer count of milliseconds since midnight. That is what lets sorting and the attributes work on them unchanged:

tm: 09:30:00.000       / a time literal
hh tm                  / 9   -- hour
mm tm                  / 30  -- minute of the hour
sec tm                 / 0   -- second of the minute
minute tm              / 570 -- minutes since midnight
stime tm               / "09:30:00.000"

Time lists

09:30:00.000 09:30:40.000 is a time list, as in q, and since 2.9.0 it's a typed vector that prints and reads back as one. ptime parses strings: ptime'("09:30:00.000"; "09:30:40.000"). Underneath a time is the millisecond count, and the accessors accept either.

It is sec, not ss

q spells the seconds accessor time.ss. In Amber ss is already string-search, so the accessor is sec. This is the kind of difference collected in the language reference's dialect notes.

Bars

times: ptime'("09:30:00.000"; "09:30:40.000"; "09:31:10.000")
minbar[1; times]       / bucket into 1-minute bars
xbar[5; 0 3 7 12 18]   / generic bucketing: 0 0 5 10 15

Bucketing plus a by-clause is the whole of OHLCV, in one query, at any bar width:

tr: ([] sym: `a`a`b; time: ptime'("09:30:00.000";"09:30:40.000";"09:31:10.000"); px: 10 11 20.0; sz: 1 2 3)
select o: first px, h: max px, l: min px, c: last px, v: sum sz by sym, time: minbar[1; time] from tr

Attributes

An attribute is a promise about a column's current contents that lets the engine pick a better algorithm. They are set in C and read with `at:

attribute	promise	what it buys
`s sorted	ascending	binary search: O(log n) find, and as-of joins
`u unique	no duplicates	hash-like membership
`p parted	equal values are contiguous	O(1) slice per distinct value
`g grouped	any order	an index from value to positions

v: asc 1 5 3 9 7        / asc sets `s for you
attr v                  / `s
v ? 7                   / binary search, not a scan
`s#v                    / set sorted explicitly (q's way, since 2.7)
`p#v                    / set parted

The promise is yours to keep

An attribute is not checked on every write. Setting `s on an unsorted column makes lookups return wrong answers rather than slow ones. Let asc and xasc set it for you where you can.