twindow

Syntax

twindow(func, funcArgs, T, range, [prevailing=false])

Arguments

func is an aggregate function.

funcArgs is the argument(s) of func. If func has multiple parameters, funcArgs is a tuple.

T is a non-strictly increasing vector of integers or temporal type.

range is a data pair of INT or DURATION type (both boundaries are inclusive).

prevailing is a BOOLEAN. The default value is false. When prevailing = true, the rules to determine the left boundary of the window is the same as applied in pwj。

First release

1.30.16/2.00.4

Details

Apply func over a sliding window of funcArgs. Each element in funcArgs corresponds to a window that is determined by T and range. The result has the same dimension as that of funcArgs (If funcArgs is a tuple, the result has the same dimension as that of each element in the tuple).

Suppose range = [d1:d2], the windows are determined based on the following rules:

1. When range is an integral pair:

T is a vector of integral type: For element Ti in T, the window range is [Ti+d1, Ti+d2].

T is a vector of temporal type: range has the precision of T by default. For element Ti in T, the window range is [temporalAdd(Ti, d1, unit), temporalAdd(Ti, d2, unit)], where unit indicates the precision of T.

2. When range is a duration pair, T can only be a vector of temporal type. For element Ti in T, the window range is [temporalAdd(Ti, d1), temporalAdd(Ti, d2)].

Compared with the tmoving function, twindow has more flexible windows. tmoving can be considered roughly as a special case of twindow, where the right boundary of the range parameter is 0. However, when the window is measured by time, note the following differences regarding how to determine window boundaries:

1. With twindow, when the right boundary of the window matches multiple identical values, all records with the identical value participate in the calculation. When the left boundary of the window matches multiple identical values, if prevailing = true, only the last record with the identical value participates in the calculation; if prevailing = false, all records with the identical value participate in the calculation.

2. With the tm functions, the range of the window is (Ti - window, Ti ] or (temporalAdd(Ti, -window), Ti] where the left boundary is exclusive. The current record is included as the last element in the corresponding window, regardless of whether the following records have identical values.

Examples

When prevailing = false, if the left boundary of the window matches multiple identical values, the calculation window includes all records with the identical value.

$ t = 2021.01.02 2021.01.02 2021.01.06 2021.03.09 2021.03.10 2021.03.12 2021.03.12
$ x = -5 5 NULL -1 2 4 -8
$ twindow(func=min,funcArgs=x,T=t,range=0:2)
[-5, -5, , -1, -8, -8, -8]

When prevailing = false, if the left boundary of the window matches multiple identical values, the calculation window only includes the last record with the identical value.

$ twindow(func=min, funcArgs=x, T=t, range=0:3, prevailing=true)
[5, 5, ,-8, -8, -8, -8]

$ twindow(func=max, funcArgs=x, T=t, range=0d:3d)
[5, 5, , 4, 4, 4, 4]
$ twindow(func=max, funcArgs=x, T=t, range=0:3, prevailing=true)
[5, 5, , 4, 4, -8, -8]

$ twindow(func=max, funcArgs=x, T=t, range=0M:3M)
[5, 5, 4, 4, 4, 4, 4]
$ twindow(func=max, funcArgs=x, T=t, range=0M:3M, prevailing=true)
[5, 5, 4, 4, 4, -8, -8]

$ y = 4.8 9.6 7.1 3.3 5.9 2.7 6.9
$ twindow(func=corr, funcArgs=(x,y), T=t, range=0:3)
[1, 1,  , -0.685, -0.7893, -1, -1]


$ t1 = table(`A`A`B`B`C`C as sym, 09:56:03 09:56:07 09:56:02 09:56:05 09:56:04 09:56:06 as time, 10.6 10.7 20.6 11.6 11.7 19.6 as price)
$ select *, twindow(func=avg, funcArgs=t1.price, T=t1.time, range=2s:4s) from t1 context by sym

sym	time	price	window_avg
A	09:56:03	10.6	10.7
A	09:56:07	10.7
B	09:56:02	20.6	11.6
B	09:56:05	11.6
C	09:56:04	11.7	19.6
C	09:56:06	19.6