Join Prevailing Quotes to Trades with aj

Question

Given the following tables t and q, representing trades and quotes, join the prevailing px price from the q table to each record in table t.

t:([] time:10:01:01 10:01:03 10:01:04; sym:`msft`ibm`ge; qty:100 200 150)
q:([] time:10:01:00 10:01:00 10:01:00 10:01:02; sym:`ibm`msft`msft`ibm; px:100 99 101 98)

Solution

aj[`sym`time;t;q]

time     sym  qty px
---------------------
10:01:01 msft 100 101
10:01:03 ibm  200 98
10:01:04 ge   150

Explanation

aj (as-of join) finds, for every row of t, the most recent matching row in q at or before that row's time — exactly like asof, but applied across a whole table of lookups at once rather than one dictionary at a time.

aj[`sym`time;t;q]

joins on sym (exact match) and time (as-of match): for the msft row at 10:01:01, both msft quotes in q (at 10:01:00, prices 99 and 101) are at or before that time, and the later one — 101 — wins, since it's the most recent. The ge row finds no sym match in q at all, so its px is left empty.

Why row order inside q matters: aj finds matches with a binary search, and binary search only works correctly on sorted data. The final column in the join list (here, time) is the one searched this way, and it must be sorted ascending within each group defined by the earlier columns (here, within each sym). q does not check or enforce this — if time isn't sorted within a sym group, aj won't raise an error, it will simply return wrong results silently.

The columns before the last one (sym here) are used for exact matching, narrowing the search down to one symbol's rows; the binary search for the as-of time match is then performed only within that narrowed subset. This is why correctness depends on time being sorted per sym, not necessarily across the whole table.