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:
-
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].
-
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:
-
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. -
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] inwindow
(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 |