TS_OUTLIER_IDENTIFICATION Function [Aggregate]

Syntax

TS_OUTLIER_IDENTIFICATION (timeseries_expression, p_value, q_value, s_value, d_value, [, delta_value[, critical_value]])

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.
• p_value – an integer containing the p-portion of the autoregressive integrated moving average (ARIMA) (p, 0, q)x(0, d, 0)s model that the outlier free series follows.
• q_value – an integer containing the q-portion of the ARIMA (p, 0, q)x(0, d, 0)s model that the outlier free series follows.
• s_value – an integer containing the s-portion of the ARIMA (p, 0, q)x(0, d, 0)s model that the outlier free series follows.
• d_value – an integer containing the d-portion of the ARIMA (p, 0, q)x(0, d, 0)s model that the outlier free series follows.
• delta_value – (optional) a double precision float value containing the dampening effect parameter used in detecting a temporary change outlier. The integer must be greater than 0 and less than 1. The default value is 0.7.
• critical_value – (optional) a double-precision float value used as a threshold for outlier detection. The default is 3.0.
• window-spec – TS_OUTLIER_IDENTIFICATION 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

This function returns an outlier-free time series. TS_OUTLIER_IDENTIFICATION calls the function imsls_d_ts_outlier_identification in the IMSL libraries.

IMSL Mapping

The arguments of TS_OUTLIER_IDENTIFICATION map to the IMSL library function imsls_d_ts_outlier_identification as follows:

params = imsls_d_ts_outlier_identification(n_objs, model[], z[], 0);

• n_objs – contains the number of rows in the current window frame.
• model – an array containing the TS_OUTLIER_IDENTIFICATION arguments p_value, s_value, q_value, d_value: model[0] = p_value; model[1] = s_value; model[2] = q_value; model[3] = d_value;
• z[] – contains the value of timeseries_expression for the current window frame.

If delta_value is non-null, the arguments of TS_OUTLIER_IDENTIFICATION map to the IMSL library function imsls_d_ts_outlier_identification as follows:

params = imsls_d_ts_outlier_identification(n_objs, model[], z[], IMSL_DELTA, delta_value, 0);

If critical_value is non-null, the arguments of TS_OUTLIER_IDENTIFICATION map to the IMSL library function imsls_d_ts_outlier_identification as follows:

params = imsls_d_ts_outlier_identification(n_objs, model[], z[],		IMSL_CRITICAL, critical_value, 0);

If both delta_value and critical_value are non-null, the arguments of TS_OUTLIER_IDENTIFICATION map to the IMSL library function imsls_d_ts_outlier_identification as follows:

params = imsls_d_ts_outlier_identification(n_objs, model[], z[], 		IMSL_DELTA, delta_value, IMSL_CRITICAL, critical_value, 0);

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

Example

This example shows a SQL statement containing the TS_OUTLIER_IDENTIFICATION function and the data values returned by the function. This example uses the example input data table (called DATASET) as its input data.

select ts_outlier_identification(data,1,1,1,1,0.7,3.0) over (order by rownum rows between unbounded preceding and unbounded following) as res FROM DATASET

Sybase IQ returns 50 rows:

Standards and Compatibility

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