aj [c1…cn; t1; t2] aj0 [c1…cn; t1; t2] ajf [c1…cn; t1; t2] ajf0[c1…cn; t1; t2]
t1is a table
t2is a simple table
c1…cnis a symbol list of column names, common to
t2, and of matching type
cnis of a sortable type (typically time)
returns a table with records from the left-join of
In the join, columns
c1…cn-1 are matched for equality, and the last value of
cn (most recent time) is taken.
For each record in
t1, the result has one record with the items in
- if there are matching records in
t2, the items of the last (in row order) matching record are appended to those of
- otherwise the remaining columns are null.
q)t:(time:10:01:01 10:01:03 10:01:04;sym:`msft`ibm`ge;qty:100 200 150) q)t time sym qty ----------------- 10:01:01 msft 100 10:01:03 ibm 200 10:01:04 ge 150 q)q:(time:10:01:00 10:01:00 10:01:00 10:01:02;sym:`ibm`msft`msft`ibm;px:100 99 101 98) q)q time sym px ----------------- 10:01:00 ibm 100 10:01:00 msft 99 10:01:00 msft 101 10:01:02 ibm 98 q)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
There is no requirement for any of the join columns to be keys but the join will be faster on keys.
aj0 return different times in their results:
aj boundary time from t1 aj0 actual time from t2
Since V3.6 2018.05.18
ajf0 behave as V2.8
aj0, i.e. they fill from LHS if RHS is null. e.g.
q)t0:(time:2#00:00:01;sym:`a`b;p:1 1;n:`r`s) q)t1:(time:2#00:00:01;sym:`a`b;p:0 1) q)t2:(time:2#00:00:00;sym:`a`b;p:1 0N;n:`r`s) q)t0~ajf[`sym`time;t1;t2] 1b
Order of search columns
Ensure the first argument to
aj, the columns to search on, is in the correct order, e.g.
`sym`time. Otherwise you’ll suffer a severe performance hit.
If the resulting time value is to be from the quote (actual time) instead of the (boundary time) from trade, use
aj0 instead of
aj should run at a million or two trade records per second; whether the tables are mapped or not is irrelevant. However, for speed:
Departure from this incurs a severe performance penalty.
Note that, on disk, the
g# attribute does not help.
Select the virtual partition column only if you need it. It is fabricated on demand, which can be slow for large partitions.
In memory, there is no need to select from
t2. Irrespective of the number of records, use, e.g.:
aj[`sym`time;select … from trade where …;quote]
aj[`sym`time;select … from trade where …; select … from quote where …]
In contrast, on disk you must map in your splay or day-at-a-time partitioned database:
aj[`sym`time;select … from trade where …;select … from quote]
aj[`sym`time;select … from trade where …; select … from quote where date = …]
where constraints are used, the columns will be copied instead of mapped into memory, slowing down the join.
If you are using a database where an individual day’s data is spread over multiple partitions the on-disk
p# will be lost when retrieving data with a constraint such as
In this case you will have to reduce the number of quotes retrieved by applying further constraints – or by re-applying the attribute.