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 i-th 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 i-th 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 i-th 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 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 i-th 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 |