TS_ESTIMATE_MISSING Function [Aggregate]

Syntax

TS_ESTIMATE_MISSING (timeseries_expression, method)

OVER (window-spec)

Licensing prerequisites

Available only with RAP – The Trading Edition Enterprise.

Parameters

• timeseries_expression – a numeric expression, generally a column name, containing an element in a time series to be differenced. If a null-value is provided, it is assumed to reflect a gap in the time series, the value of which will be computed by the function.
• method – (optional) An integer specifying the method to use when determining missing values:
  
  

  • 0 (default) – estimates the missing time series observations in a gap by the median of the last four time series values before and the first four values after the gap.

  • 1 – uses a cubic spline interpolation method to estimate missing values. Here, the interpolation is again done over the last four time series values before and the first four values after the gap.

  • 2 – assumes that the time series before the gap can be well described by an AR(1) process.

  • 3 – uses an AR(p) model to estimate missing values by a one-step-ahead forecast.

• window-spec – TS_ESTIMATE_MISSING is an OLAP function requiring an OVER () clause with an unbounded window. This function does not support value-based windows; for example, you cannot use a range specifier in the OVER () clause.

Usage

Use TS_ESTIMATE_MISSING to estimate any missing equidistant time points using one of the four estimation methods. TS_ESTIMATE_MISSING calls the function imsls_d_estimate_missing in the IMSL libraries

You cannot use TS_ESTIMATE_MISSING if more than two consecutive NULL values exist in your set of timepoints. If the first or last two values in the set of timepoints are NULL, the function returns NULL.

IMSL mapping

The arguments of TS_ESTIMATE_MISSING map to the IMSL library function imsls_d_estimate_missing as follows:

params = imsls_d_estimate_missing(n_objs, tpoints[], z[], method, 0);

• n_objs – contains the number of rows in the current window frame.
• tpoints – an array of indexes for specifying missing values in a sequence of timepoints.
• z[] – the accumulated timeseries_expression, obtained during calls to next_value.
• method – maps to the method argument defined in TS_ESTIMATE_MISSING.

For detailed information on how the function imsls_d_estimate_missing performs time series calculations, see IMSL C Numerical Library User’s Guide: Volume 2 of 2 C Stat Library.

Example

This example shows an input data table, a SQL statement containing the TS_ESTIMATE_MISSING function, and the data values returned by the function. This example uses the following table (called EST_MISSING_DATASET) as its input data. The EST_MISSING_DATASET table contains nine rows of time series data:

SELECT ts_estimate_missing(data,0) OVER (order by rownum rows between unbounded preceding and unbounded following) AS res FROM EST_MISSING_DATASET

Sybase IQ returns the following nine rows, replacing the NULL value with 1.0278:

Standards and compatibility

• SQL – ISO/ANSI SQL compliant
• Sybase – not compatible with SQL Anywhere or Adaptive Server Enterprise

See also

System Administration Guide: Volume 2 > Using OLAP

IMSL C Numerical Library User’s Guide: Volume 2 of 2 C Stat Library