TS_BOX_COX_XFORM Function [Aggregate]

Syntax

TS_BOX_COX_XFORM (timeseries_expression, power [, shift [, inverse] ]) 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.
power – a double-precision floating-point value representing an exponent parameter in the Box-Cox power transformation.
shift – (optional) a double-precision floating-point value representing a shift parameter. The value must satisfy the relation: min(timeseries)+shift>0. Shift defaults to 0.0.
inverse – (optional) a tinyint value; if set to 1, Sybase IQ performs an inverse transformation. If 0 or null, Sybase IQ performs a forward transformation. The default value is 0.
window-spec – TS_BOX_COX_XFORM is an OLAP function requiring an OVER () clause with an unbounded window. TS_BOX_COX_XFORM does not support value-based windows; for example, you cannot use a range specifier in the OVER () clause.

Usage

TS_BOX_COX_XFORM returns the corresponding calculated transformed value for each element in the time series; it calls the function imsls_d_box_cox_transform in the IMSL libraries.

IMSL Mapping

The arguments of TS_BOX_COX_XFORM map to the IMSL library function imsls_d_box_cox_transform as follows:

params = imsls_d_box_cox_transform(n_objs, z[], power,		IMSLS_SHIFT, shift [, IMSLS_INVERSE], 0);

n_objs – contains the number of rows in the current window frame.
z[] – contains the value of timeseries_expression for the current window frame.
power – maps to the user-defined aggregate function argument power.
shift – maps to the user-defined aggregate function argument shift.
IMSLS_INVERSE – if the user-defined aggregate function argument inverse is 1, Sybase IQ calls the Box-Cox transform with IMSLS_INVERSE, otherwise this argument is left out of the function call.

For detailed information on how the function imsls_d_box_cox_transform 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_BOX_COX_XFORM function, and the data values returned by the function. This example uses the following table (called BOX_COX_XFORM_DATASET) as its input data. The BOX_COX_XFORM_DATASET table contains 13 rows of time series data:

Input Data Table BOX_COX_XFORM_DATASET
rownum	data
1	7
2	26
3	6
4	60
5	78.5
6	1
7	29
8	15
9	52
10	74.3
11	11
12	56
13	8

The following SQL statement returns the Box-Cox power transformation from the data column:

SELECT TS_BOX_COX_XFORM(data,1.0,1.0,0) OVER (ORDER BY rownum ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS res FROM BOX_COX_XFORM_DATASET

Sybase IQ returns the following 13 rows:

Values Returned from TS_BOX_COX_XFORM
res
8
27
7
61
79.5
2
30
16
53
75.3
12
57
9

Standards and compatibility

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