window join

Window join:

wj(leftTable, rightTable, window, aggs, matchingCols, [rightMatchingCols])

Prevailing window join:

pwj(leftTable, rightTable, window, aggs, matchingCols, [rightMatchingCols])

leftTable and rightTable are the tables to be joined. The right table cannot be a DFS table.

window is a pair of integers indicating the left bound and the right bound (both are inclusive) of the window relative to the records in the left table.

aggs is metacode or a tuple of metacode indicating an aggregate function or a list of aggregate functions. For details please refer to the section of Metaprogramming.The parameters of these aggregate functions must be numeric columns of the right table.

matchingCols is a string scalar/vector indicating matching columns.

rightMatchingCols is a string scalar/vector indicating all the matching columns in rightTable . This optional argument must be specified if at least one of the matching columns has different names in leftTable and rightTable . The joining column names in the result will be the joining column names from the left table.

Window join is a generalization of asof join. For each row in leftTable, window join applies aggregate functions on a matching interval of rows in rightTable .

Similar to asof join, if there is only 1 joining column, the window join function assumes the right table is sorted on the joining column. If there are multiple joining columns, the window join function assumes the right table is sorted on the last joining column within each group defined by the other joining columns. The right table does not need to be sorted by the other joining columns. If these conditions are not met, unexpected results may be returned. The left table does not need to be sorted.

If window=w1:w2, for each row in leftTable with the value of the last column in matchCols equal to t, find the rows in rightTable with the value of the last column in matchCols between (t+w1) and (t+w2) conditional on all other columns in matchCols are matched, then apply aggs to the selected rows in rightTable .

The differences between wj and pwj are:

• If rightTable doesn't have a matching value for t+w1 (the left boundary of the window), wj will treat it as a NULL element in the window, whereas pwj will include the last value before t+w1 in the window.

• If rightTable has multiple matching values for t+w1, wj will include all of them while pwj will only include the last row.

The following aggregate functions in window join are optimized for better performance:

avg, beta, count, corr, covar, first, last, max, med, min, percentile, std, sum, sum2, var wavg, kurtosis, prod, skew, stdp, varp, atImin, atImax

Note: When specifying atImax or atImin in parameter aggs of window join functions, if there are multiple identical extreme values in a window, the last record with extreme value is used for calculation by default.

t1 = table(`A`A`B as sym, 09:56:06 09:56:07 09:56:06 as time, 10.6 10.7 20.6 as price)
t2 = table(take(`A,10) join take(`B,10) as sym, take(09:56:00+1..10,20) as time, (10+(1..10)\10-0.05) join (20+(1..10)\10-0.05) as bid, (10+(1..10)\10+0.05) join (20+(1..10)\10+0.05) as offer, take(100 300 800 200 600, 20) as volume);
t1;

t2;

wj(t1, t2, -5s:0s, <avg(bid)>, `sym`time);

wj(t1, t2, -5:-1, <[wavg(bid,volume), wavg(offer,volume)]>, `sym`time);

t3=t2
t3.rename!(`time, `second)
wj(t1, t3, -2:2, <[wavg(bid,volume), wavg(offer,volume)]>, `sym`time, `sym`second);

Window join is a generalization of asof join:

wj(t1, t2, -100:0, <[last(bid) as bid, last(offer) as offer]>, `sym`time);

select sym, time, price, bid, offer from aj(t1, t2, `sym`time);

Prevailing window join:

delete from t2 where 09:56:04<=time<=09:56:06;
t2;

wj(t1, t2, -1:1, <[first(bid), avg(offer)]>, `sym`time);

Dynamically pass values to aggs in tuple format.

aggs = array(ANY, 3)   //Specify aggs as a tuple with metacode
aggs[0] = <min(bid)>
aggs[1] = <min(offer)>
aggs[2] = <min(volume)>
wj(t1, t2, -5s:0s, aggs, `sym`time);

pwj(t1, t2, -1:1, <[first(bid), avg(offer)]>, `sym`time);

In the example above, for sym "A" at 09:56:06 in the left table, window join uses the row of sym "A" at 09:56:07 in the right table to calculate first(bid) and avg(offer), whereas prevailing window join uses the rows of sym "A" at 09:56:03 and 09:56:07 in the right table.