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 can be 0/false (default), 1/true, or 2, indicating how duplicate values at window boundaries are handled. The specific windowing rules for each value are introduced in Details.

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)].

When the window boundary matches multiple duplicates, the prevailing parameter determines whether those duplicates are included in the window.

• If prevailing = 0/false, the window includes all duplicates.
• If prevailing = 1/true, the calculation window includes the last record of duplicates at the left boundary and all duplicates at the right boundary.
• If prevailing = 2,
  • When d1 is 0, the window starts at the current record, excluding prior duplicates while including all duplicates at the right boundary.
  • When d2 is 0, the window ends at the current record, excluding following duplicates while including duplicates at the left boundary.
  • Note that prevailing and excludedPeriod cannot be set simultaneously.

Compared with the tmoving function, twindow has more flexible windows. Thetmoving function can be considered roughly as a special case of twindow, where the right boundary of the range parameter is 0 and prevailing is set to 0. However, when the window is measured by time, 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.

The following examples show different windowing rules when the window boundary matches multiple duplicates.

• When prevailing = 0, the calculation window includes all duplicates.

  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

  sym	time	price	twindow_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

  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 = 1, the calculation window only includes the last record with the identical value.

  twindow(func=min,funcArgs=x,T=t,range=0:2,prevailing=1)
  // output
  [5, 5, , -1, -8, -8, -8]

• When prevailing = 2 and the range's left/right boundary is 0, the calculation window starts at the current record, excluding prior/following duplicates.

  twindow(func=min,funcArgs=x,T=t,range=0:2,prevailing=2)
  // output
  [-5, 5, , -1, -8, -8, -8]
  
  twindow(func=min,funcArgs=x,T=t,range=-2:0,prevailing=2)
  // output
  [-5, -5, , -1, -1, 2, -8]

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
select * from res1 where ts between timestamp(2023.11.01T13:00:00) and timestamp(2023.11.01T13:01:00)
// without excludedPeriod specified
res2=select ts, twindow(func=avg, T=ts, funcArgs=price, range=-1m:0m) from t
select * from res2 where ts between timestamp(2023.11.01T13:00:00) and timestamp(2023.11.01T13:01:00)