# window join {#window-join}

## Syntax {#syntax}

*Window join:*

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

*Prevailing window join:*

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

## Arguments {#arguments}

**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 one or a list of aggregate functions/*rightTable* columns. For details please refer to [Metaprogramming](../../Metaprogramming/functional_meta.md). 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.

## Details {#details}

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](../../../Functions/a/avg.md), [beta](../../../Functions/b/beta.md), [count](../../../Functions/c/count.md), [corr](../../../Functions/c/corr.md), [covar](../../../Functions/c/covar.md), [first](../../../Functions/f/first.md), [last](../../../Functions/l/last.md), [max](../../../Functions/m/max.md), [med](../../../Functions/m/med.md), [min](../../../Functions/m/min.md), [percentile](../../../Functions/p/percentile.md), [std](../../../Functions/s/std.md), [sum](../../../Functions/s/sum.md), [sum2](../../../Functions/s/sum2.md), [var](../../../Functions/v/var.md) [wavg](../../../Functions/w/wavg.md), [kurtosis](../../../Functions/k/kurtosis.md), [prod](../../../Functions/p/prod.md), [skew](../../../Functions/s/skew.md), [stdp](../../../Functions/s/stdp.md), [varp](../../../Functions/v/varp.md), [atImin](../../../Functions/a/atImin.md), [atImax](../../../Functions/a/atImax.md), [firstNot](https://docs.dolphindb.com/en/Functions/f/firstNot.html), [lastNot](https://docs.dolphindb.com/en/Functions/l/lastNot.html)

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.

## Examples {#examples}

```
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;
```

|sym|time|price|
|---|----|-----|
|A|09:56:06|10.6|
|A|09:56:07|10.7|
|B|09:56:06|20.6|

```
t2;
```

|sym|time|bid|offer|volume|
|---|----|---|-----|------|
|A|09:56:01|10.05|10.15|100|
|A|09:56:02|10.15|10.25|300|
|A|09:56:03|10.25|10.35|800|
|A|09:56:04|10.35|10.45|200|
|A|09:56:05|10.45|10.55|600|
|A|09:56:06|10.55|10.65|100|
|A|09:56:07|10.65|10.75|300|
|A|09:56:08|10.75|10.85|800|
|A|09:56:09|10.85|10.95|200|
|A|09:56:10|10.95|11.05|600|
|B|09:56:01|20.05|20.15|100|
|B|09:56:02|20.15|20.25|300|
|B|09:56:03|20.25|20.35|800|
|B|09:56:04|20.35|20.45|200|
|B|09:56:05|20.45|20.55|600|
|B|09:56:06|20.55|20.65|100|
|B|09:56:07|20.65|20.75|300|
|B|09:56:08|20.75|20.85|800|
|B|09:56:09|20.85|20.95|200|
|B|09:56:10|20.95|21.05|600|

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

|sym|time|price|avg\_bid|
|---|----|-----|--------|
|A|09:56:06|10.6|10.3|
|A|09:56:07|10.7|10.4|
|B|09:56:06|20.6|20.3|

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

|sym|time|price|wavg\_bid|wavg\_offer|
|---|----|-----|---------|-----------|
|A|09:56:06|10.6|10.295|10.395|
|A|09:56:07|10.7|10.32|10.42|
|B|09:56:06|20.6|20.295|20.395|

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

|sym|time|price|wavg\_bid|wavg\_offer|
|---|----|-----|---------|-----------|
|A|09:56:06|10.6|10.595|10.695|
|A|09:56:07|10.7|10.645|10.745|
|B|09:56:06|20.6|20.595|20.695|

Calculate the ratio of average bid-offer spread to the average offer price.

``` {#codeblock_alb_scc_bdc}
wj(t1, t2, -5s:0s, <avg(offer-bid)/avg(offer)>, `sym`time);
```

|sym|time|price|avg\_bid\_div|
|---|----|-----|-------------|
|A|09:56:06|10.6|0.0096|
|A|09:56:07|10.7|0.0095|
|B|09:56:06|20.6|0.0049|

Window join is a generalization of asof join:

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

|sym|time|price|bid|offer|
|---|----|-----|---|-----|
|A|09:56:06|10.6|10.55|10.65|
|A|09:56:07|10.7|10.65|10.75|
|B|09:56:06|20.6|20.55|20.65|

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

|sym|time|price|bid|offer|
|---|----|-----|---|-----|
|A|09:56:06|10.6|10.55|10.65|
|A|09:56:07|10.7|10.65|10.75|
|B|09:56:06|20.6|20.55|20.65|

Prevailing window join:

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

|sym|time|bid|offer|volume|
|---|----|---|-----|------|
|A|09:56:01|10.05|10.15|100|
|A|09:56:02|10.15|10.25|300|
|A|09:56:03|10.25|10.35|800|
|A|09:56:07|10.65|10.75|300|
|A|09:56:08|10.75|10.85|800|
|A|09:56:09|10.85|10.95|200|
|A|09:56:10|10.95|11.05|600|
|B|09:56:01|20.05|20.15|100|
|B|09:56:02|20.15|20.25|300|
|B|09:56:03|20.25|20.35|800|
|B|09:56:07|20.65|20.75|300|
|B|09:56:08|20.75|20.85|800|
|B|09:56:09|20.85|20.95|200|
|B|09:56:10|20.95|21.05|600|

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

|sym|time|price|first\_bid|avg\_offer|
|---|----|-----|----------|----------|
|A|09:56:06|10.6|10.65|10.75|
|A|09:56:07|10.7|10.65|10.8|
|B|09:56:06|20.6|20.65|20.75|

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);
```

|sym|time|price|min\_bid|min\_offer|min\_volume|
|---|----|-----|--------|----------|-----------|
|A|09:56:06|10.6|10.05|10.15|100|
|A|09:56:07|10.7|10.15|10.25|100|
|B|09:56:06|20.6|20.05|20.15|100|

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

|sym|time|price|first\_bid|avg\_offer|
|---|----|-----|----------|----------|
|A|09:56:06|10.6|10.25|10.55|
|A|09:56:07|10.7|10.25|10.65|
|B|09:56:06|20.6|20.25|20.55|

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:

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

<table id="table_a54_c2y_21c"><thead><tr><th align="left">

sym

</th><th align="left">

time

</th><th align="left">

price

</th><th align="left">

**last\_bid**

</th><th align="left">

**bid**

</th></tr></thead><tbody><tr><td align="left">

A

</td><td align="left">

09:56:06

</td><td align="left">

10.6

</td><td align="left">

10.45

</td><td align="left">

\[10.05, 10.15, 10.25, 10.35, 10.45\]

</td></tr><tr><td align="left">

A

</td><td align="left">

09:56:07

</td><td align="left">

10.7

</td><td align="left">

10.55

</td><td align="left">

\[10.55\]

</td></tr><tr><td align="left">

B

</td><td align="left">

09:56:06

</td><td align="left">

20.6

</td><td align="left">

20.45

</td><td align="left">

\[20.05, 20.15, 20.25, 20.35, 20.45\]

</td></tr></tbody>
</table>