TSRunningAvg function

Syntax

TSRunningAvg(value      double precision, 
              num_values integer) 
returns double precision;

TSRunningAvg(value      real, 
              num_values integer) 
returns double precision;

value

The value to include in the running average.

num_values

The number of values to include in the running average, k.

Description

Use the TSRunningAvg function within the Apply function.

A running average is the average of the last k values, where k is supplied by the user. If a value is NULL, the previous value is used. The running average for the first k-1 values is NULL.

The TSRunningAvg function can take parameters that are columns of a time series. Use the same parameter format that the Apply function accepts.

This function runs over a fixed number of elements, not over a fixed length of time; therefore, it might not be appropriate for irregular time series.

Returns

A SMALLFLOAT or DOUBLE PRECISION running average of the last k values.

Example

create row type if not exists stock_bar (
    timestamp datetime year to fraction(5), 
    high real, 
    low real, 
    final real, 
    vol real);

The example uses the following input data:

2011-01-03 00:00:00.00000          3        2      1        3
2011-01-04 00:00:00.00000          2        2      2        3
2011-01-05 00:00:00.00000          2        2      3        3
2011-01-06 00:00:00.00000          2        2               3

Notice the null value for the final column on 2011-01-06.

The SELECT query in the following example returns the closing price from the final column and the 4-day moving average from the stocks in the time series:

select stock_name, Apply('TSRunningAvg($final,4)',
    '2011-01-03 00:00:00.00000'::datetime year to fraction(5),
    '2011-01-06 00:00:00.00000'::datetime year to fraction(5),
    stock_data::TimeSeries(stock_bar))::TimeSeries(one_real)
from first_stocks;

The query returns the following result:

stock_name    HCLTECH
(expression)  origin(2011-01-03 00:00:00.00000), calendar(daycal), container(),
              threshold(20), regular, [(1.000000000000), (1.500000000000), (2.
              000000000000), (2.000000000000)]

The fourth result is the same as the third result because the fourth value in the final column is null.