TS_ESTIMATE_MISSING function [Time Series]

Estimates the missing values in a time series and returns them as a new time series, interspersed with the original time series.

TS_ESTIMATE_MISSING (timeseries_expression, method)

OVER (window-spec)

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

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.

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

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

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:

The following SQL statement estimates the value of the data missing from the fourth row:

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: