tmoving
Syntax
tmoving(func, T, funcArgs, window, [excludedPeriod])
Arguments
func is a function.
T is a non-strictly increasing vector of temporal or integral type. It cannot contain NULL values.
funcArgs are the parameters of func. They can be vectors/dictionaries/tables. It is a tuple if there are more than one parameter of func, and all parameters must have the same size.
window is a scalar of positive integer or DURATION type indicating the size of the sliding window. For each element Ti in T, the range of the corresponding window is [Ti - window + 1,Ti ].
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 - window).Details
Apply the function/operator to a sliding window of the given objects.
The tmoving template always returns a vector with the same number of
elements as the number of rows in the input arguments.
Each of the built-in tm-functions such as tmsum, tmcount and tmavg is optimized for its specific use case. Therefore,
they have much better performance than the tmoving template.
Examples
date=2021.08.01 2021.08.02 2021.08.02 2021.08.02 2021.08.03 2021.08.04 2021.08.05 2021.08.06 2021.08.09 2021.08.10 2021.08.14
value=1..11
t = table(date,value)
timer(100) select date, value, tmoving(avg,date,value,3d) from t;
// output
Time elapsed: 1.995 ms
timer(100) select date, value, tmavg(date, value, 3d) from t;
// output
Time elapsed: 0.997 msAs shown in the output, when the window slides to 2021.08.09, the range is [2021.08.07, 2021.08.08, 2021.08.09]. As the values in 2021.08.07 and 2021.08.08 are missing, they do not participate in the calculation.
| date | value | tmoving_sum |
|---|---|---|
| 2021.08.01 | 1 | 1 |
| 2021.08.02 | 2 | 1.5 |
| 2021.08.02 | 3 | 2 |
| 2021.08.02 | 4 | 2.5 |
| 2021.08.03 | 5 | 3 |
| 2021.08.04 | 6 | 4 |
| 2021.08.05 | 7 | 6 |
| 2021.08.06 | 8 | 7 |
| 2021.08.09 | 9 | 9 |
| 2021.08.10 | 10 | 9.5 |
| 2021.08.14 | 11 | 11 |
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, tmoving(func=avg, T=ts, funcArgs=price, window=1m, excludedPeriod=excludedPeriod) from t
// without excludedPeriod specified
res2=select ts, tmoving(func=avg, T=ts, funcArgs=price, window=1m) 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)