twindow

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

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.

excludedPeriod is a pair of time values (of TIME, NANOTIME, MINUTE, and SECOND type) representing the start and end time of the period which is excluded from the calculation. When the excludedPeriod is set, the input T cannot contain the time range specified by excludedPeriod and must be of TIMESTAMP, NANOTIMESTAMP, TIME, and NANOTIME types. Note that excludedPeriod must be within a calendar day and cannot be longer than the value of (24 - range).

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 is set to 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 T_i in T, the window range is [T_i+d1, T_i+d2].

   • T is a vector of temporal type: range has the precision of T by default. For element T_i in T, the window range is [temporalAdd(T_i, d1, unit), temporalAdd(T_i, 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 T_i in T, the window range is [temporalAdd(T_i, d1), temporalAdd(T_i, 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 (T_i - window, T_i] or (temporalAdd(T_i, -window), T_i] 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.

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)
// output
[-5, -5, , -1, -8, -8, -8]

When prevailing = true, 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)
// output
[5, 5, ,-8, -8, -8, -8]

twindow(func=max, funcArgs=x, T=t, range=0d:3d)
// output
[5, 5, , 4, 4, 4, 4]

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

twindow(func=max, funcArgs=x, T=t, range=0M:3M)
// output
[5, 5, 4, 4, 4, 4, 4]

twindow(func=max, funcArgs=x, T=t, range=0M:3M, prevailing=true)
// output
[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)
// output
[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

The following example specifies the excludedPeriod parameter. The closing hours from 11:30 to 13:00 will be excluded from the calculation.

excludedPeriod=(11:30:00:13:00:00)
ts=timestamp(2023.11.01T11:21:00+1..500) join timestamp(2023.11.01T13:00:00+1..500)
t=table(ts, rand(10.0,size(ts)) as price)

// with excludedPeriod specified
res1=select ts, twindow(func=avg, T=ts, funcArgs=price, range=-1m:0m, excludedPeriod=excludedPeriod) from t
// without excludedPeriod specified
res2=select ts, twindow(func=avg, T=ts, funcArgs=price, range=-1m:0m) from t

// results for two situations
select * from res1 where ts between timestamp(2023.11.01T13:00:00) and timestamp(2023.11.01T13:01:00)

select * from res2 where ts between timestamp(2023.11.01T13:00:00) and timestamp(2023.11.01T13:01:00)