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 T_i in T, the range of the corresponding window is [T_i - window + 1,T_i ].

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

Please see tmFunctions for the parameters and windowing logic.

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 ms

As 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)

The following example specifies multiple funcArgs parameters to calculate the minimum value of bond prices after reaching their peak within a 5-minute rolling window.

data_time = 2023.01.01T00:00:00 + 0..5 * 3600000  
last_price_bond = 100 + rand(10.0, 10) 

defg getMinPrice(data_time, last_price_bond){
        maxPriceTime = atImax(last_price_bond, data_time)
        maxPrice = max(last_price_bond)
        newPriceList = min(iif(data_time<maxPriceTime, maxPrice, last_price_bond))
        return min(newPriceList)
}
calTime = "5m"
tmoving(getMinPrice, data_time, [data_time, last_price_bond], duration(calTime))