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.
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 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 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)
[1, 1, 1, 2, 4, ]
$ y = 4.8 9.6 7.1 3.3 5.9 2.7
$ window(corr, (x, y), 1:3)
[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 ith 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 