TS_AUTO_ARIMA Function [Aggregate]

Determines parameters of a multiplicative seasonal autoregressive integrated moving average (ARIMA) model, and produces forecasts that incorporate the effects of outliers that have effects that persist beyond the end of the series.

Syntax

TS_AUTO_ARIMA( <time_value>, <timeseries_expression> [ , <max_lag> [ , <critical	> [ , epsilon> [ , <criterion> [ , <confidence> [, <model> [ , <n_predictions>]]]]]]] )
OVER (window-spec)

Licensing Prerequisites

Available only with RAP – The Trading Edition Enterprise.

Parameters

time_value – the time value for each input time-series data point.
timeseries_expression – a numeric expression, generally a column name, containing an element in a time series to be differenced.
max_lag – (optional) represents the maximum lag allowed when fitting an AR(p) model. Must be an integer constant or constant expression. The default is 10.
critical – (optional) critical value used as a threshold for outlier detection. The value must be greater than 0, and must be a double-precision floating point constant or constant expression. The default is 3.0.
epsilon – (optional) positive tolerance value controlling the accuracy of parameter estimates during outlier detection. The value must be a double-precision floating point constant or constant expression. The default is 0.001.
criterion – (optional) the information criterion used for optimum model selection. Must be an integer constant or constant expression valued at 0, 1, or 2:
- 0 – (default) Akaike's Information Criterion (AIC)
- 1 – Akaike's Corrected Information Criterion (AICC)
- 2 – Bayesian Information Criterion (BIC)
confidence – (optional) confidence level for computing forecast confidence limits, taken from the exclusive interval (0, 100). Typical choices for confidence are 90.0, 95.0, and 99.0. Must be a double-precision floating point constant or constant expression. The default is 95.0.
n_predictions – (optional) the number of forecasts requested. Must be a positive integer constant or constant expression. The maximum supported value is 2000. The default is 0.
model – (optional) represents a binary encoded integer array, with a length of four integers, containing the ARIMA values for p, q, s, and d, in that order. If the model argument is specified in the TS_INT_ARRAY function, then the TS_AUTO_ARIMA function determines the p, q, s, and d, values using the method 3 (Specified ARIMA) parameter of the (IMSLS_METHOD, int method) argument of the imsls_d_auto_arima function in the IMSL library. If the model parameter is NULL, then the TS_AUTO_ARIMA function automatically calculates an ARIMA(p,0,0)X(0,d,0) model, which minimizes the specified error criterion. The default is NULL.
window-spec – TS_AUTO_ARIMA is an OLAP function requiring an OVER() clause containing an ORDER BY clause. ROWS or RANGE specifiers are not allowed in the OVER() clause.

Usage

As an OLAP-style aggregate function, TS_AUTO_ARIMA produces a single SQL result—a specially encoded variable-length binary result value. Supporting scalar functions accept the binary composite output value and return individual scalar result values from it.

Since an OLAP-style aggregate function returns one result value per input tuple, the same binary composite result is returned for each row within a partition. If you do not specify a PARTITION BY clause in the OVER clause, use SELECT FIRST to reduce the results down to a single tuple containing the binary composite result. If you do specify a PARTITION BY clause in the OVER clause, use SELECT DISTINCT to eliminate all but one tuple per partition.

IMSL Mapping

Mappings to the parameters of the IMSL C functions in the external VNI library are performed by the TS_AUTO_ARIMA_RESULT supporting scalar functions.

Example

In this example, Sybase IQ computes the AUTO_ARIMA model independently for each of the four stock symbols. The DISTINCT qualifier reduces the set of tuples to one tuple per stock symbol. Finally, one output row is returned containing the stock symbol and all the relevant information describing the AUTO_ARIMA model computed for that stock.

select stock_symbol, TS_AUTO_ARIMA_RESULT_RESIDUAL_SIGMA( auto_arima_res ), 
	TS_AUTO_ARIMA_RESULT_AIC( auto_arima_res), 
	TS_AUTO_ARIMA_RESULT_AICC( auto_arima_res),
	TS_AUTO_ARIMA_RESULT_BIC( auto_arima_res), TS_AUTO_ARIMA_RESULT_FORECAST_VALUE( auto_arima_res, 1),
	TS_AUTO_ARIMA_RESULT_FORECAST_ERROR( auto_arima_res, 1),
TS_AUTO_ARIMA_RESULT_FORECAST_VALUE( auto_arima_res, 2),
	TS_AUTO_ARIMA_RESULT_FORECAST_ERROR( auto_arima_res, 2),

TS_AUTO_ARIMA_RESULT_FORECAST_VALUE( auto_arima_res, 3),

	TS_AUTO_ARIMA_RESULT_FORECAST_ERROR( auto_arima_res, 3),

TS_AUTO_ARIMA_RESULT_MODEL_P( auto_arima_res),
	TS_AUTO_ARIMA_RESULT_MODEL_Q( auto_arima_res),

	TS_AUTO_ARIMA_RESULT_MODEL_S( auto_arima_res),

	TS_AUTO_ARIMA_RESULT_MODEL_D( auto_arima_res)
from ( select distinct
			stock_symbol,
			TS_AUTO_ARIMA(stock_trade_time,
				trade_price,

				1, 3.0, 4.0, 0, 95.0, TS_INT_ARRAY(4, 0,
				1, 0, 3)) 
		over (partition by stock_symbol 
				order by stock_trade_time) 
		as auto_arima_res
from stock_trades 
where stock_symbol in ('XYZ', 'XZZ', 'ZXZ', 'ZZZ') 
) as auto_arima_per_stock

Standards and Compatibility

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