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. See DATASET example input data table.

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

See also

System Administration Guide: Volume 2 > Using OLAP

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