learnkdb
Back to Attributes in the Tick Architecture

Attributes · Attributes in the Tick Architecture · Practice Question

For a Window Join or As-Of Join, What Attribute Is Required?

Sign in to track your progress

Question

What attributes and sort order does KX recommend for the right-hand (lookup) table of an as-of join (aj) or a window join (wj)? Why, and what happens if the table is not laid out that way?

Solution

quote: ([] sym:`MSFT`AAPL`AAPL`MSFT; time:09:30:00 09:30:00 09:30:04 09:30:01; bid:410.0 185.1 185.3 410.2)
trade: ([] sym:`AAPL`MSFT; time:09:30:05 09:30:02; qty:100 200)

/ In memory: sort by sym then time, and put the grouped attribute on sym
quote: `sym`time xasc quote
update `g#sym from `quote

aj[`sym`time; trade; quote]

For a window join, KX recommends the parted attribute on sym instead (see below):

quote: update `p#sym from `sym`time xasc quote
w: -00:00:05 00:00:00 +\: trade`time
wj[w; `sym`time; trade; (quote; (avg; `bid))]

Explanation

For each row of trade, aj finds the row in quote with the same sym and the latest time at or before the trade's time. To do that quickly it needs to (1) find each symbol's rows in quote, and (2) search those rows by time.

As-of join (aj). KX's reference for aj recommends:

  • In memory: the grouped attribute (g#) on the first join column (sym), with time sorted within each sym.
  • On disk: the parted attribute (p#) on sym, with time sorted within each sym. KX notes that on disk, g# does not help.

The sorted attribute (s#) on time is not part of this: after sorting by sym then time, the time column as a whole is not in ascending order, so s# could not be applied to it. KX warns that departing from the recommended layout incurs a severe performance penalty.

Window join (wj, wj1). KX's reference for wj states that the lookup table should be sorted by sym then time, with the parted attribute on sym, and that window joins over multiple symbols should only be used with that kind of schema — with the grouped attribute typical of an RDB, the results are undefined.

If the data is not sorted by time within each sym, the as-of search can return the wrong row, because it assumes the times it is searching are in order. Sort the lookup table before joining.