window

Syntax

window(func, funcArgs, range)

Arguments

func is an aggregate function.

funcArgs is the argument(s) of func. It is a tuple if there are more than one parameter of func.

range is a pair of integers or duration values (both boundaries are inclusive).

Note: If range is of DURATION type, funcArgs must be an indexed matrix or an indexed series.

Details

Apply func over a sliding window of funcArgs. Each element in funcArgs corresponds to a window that is determined by 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 is set to d1:d2, the windows are determined based on the following rules:

  1. When funcArgs is a vector, range must be a pair of integers. For the ith element in funcArgs, the corresponding window contains elements at position [i+d1, i+d2].

  2. When funcArgs is an indexed series or indexed matrix:

  • If funcArgs is indexed by time, for fi (the ith element in the index of funcArgs), the corresponding window contains elements at index [temporalAdd(fi, d1), temporalAdd(fi, d2)].

  • If funcArgs is indexed by integral values, range must also be integral. For fi (the ith element in the index of funcArgs), the corresponding window contains elements at index [fi+d1, fi+d2].

Compared with the moving function, the window function has a more flexible window. moving can be roughly considered as a special case of window, where the right boundary of the range parameter is 0. However, please note the following differences:

  1. When the window is based on element counts, moving returns null when the number of windowed elements does not satisfy the minPeriods, whereas window does not have a minimum count requirement.

  2. When the window is based on time, the left boundary of the window of moving is exclusive and the right boundary is inclusive; whereas both boundaries of the window of window are inclusive. In this example:

    Suppose a window with the size of "3d" slides over an index of DATETIME type to apply calculation. For the point "2022.01.05T09:00:00" in the index, the range of the corresponding window in moving is (2022.01.02T09:00:00,2022.01.05T09:00:00], whereas it's [2022.01.03T09:00:00,2022.01.05T09:00:00] in window (with the range parameter specified as "-2d:0d").

Examples

funcArgs is a vector. For the ith element of x, the range of the window is [i+1,i+3].

x = 5 4 NULL -1 2 4
window(min, x, 1:3)
// output
[-1, -1, -1, 2, 4, ]

y = 4.8 9.6 7.1 3.3 5.9 2.7
window(corr, (x, y), 1:3)
// output
[1, 1, -0.623, -1, , ]

funcArgs is a series indexed by time. The range of the window is [temporalAdd(ti 1d), temporalAdd(ti, 3d)] where ti is the i-th element of t.

t = 2021.01.02 2021.01.05 2021.01.06 2021.01.09 2021.01.10 2021.01.12
x1 = indexedSeries(t, x)
window(min, x1, 1d:3d)
label col1
2021.01.02 4
2021.01.05
2021.01.06 -1
2021.01.09 2
2021.01.10 4
2021.01.12

funcArgs is a matrix indexed by time. The range of the window is [temporalAdd(ti, 1d), temporalAdd(ti, 3d)], where ti is the ith element of t.

t= 2021.01.02 2021.01.05  2021.01.06  2021.01.09 2021.01.10 2021.01.12
m=matrix(5 4 NULL -1 2 4, 3 2 8 1 0 5)
m1=m.rename!(t, `a`b).setIndexedMatrix!()
window(min, m1, 1d:3d)
label a b
2021.01.02 4 2
2021.01.05 8
2021.01.06 -1 1
2021.01.09 2 0
2021.01.10 4 5
2021.01.12
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 *, window(avg, t1.time.indexedSeries(t1.price), 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