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.

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 one or a list of aggregate functions/rightTable columns. For details please refer to Metaprogramming. If an aggregate function is specified, its parameters must be numeric columns of the right table. If a rightTable column is specified, the results for each window will be output in the form of array vectors.

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.

Standard windows (i.e., window = w1:w2):

The windows over the right table are determined by the current timestamp in the left table and the specified parameter window. Suppose the current timestamp in the left table is t, and window is set to w1:w2, then the corresponding window in the right table consists of records with timestamps in [t+w1, t+w2]. The function applies aggs to the selected rows in rightTable and returns the result for each window.

Special windows (i.e., window = 0:0):

The special window is only supported for wj.

The windows over the right table are determined by the current timestamp in the left table and its previous timestamp. Suppose the current timestamp in the left table is t and the previous timestamp is t0, then the corresponding window in the right table consists of records with timestamps in [t0, t).

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.

Example for window = 0:0:

wj(t1, t2, 0:0, <[last(bid), bid]>, `sym`time)