![TS_AUTO_UNI_AR function [Time Series]](arrow-left.gif){kind=icon}
![TS_DIFFERENCE function [Time Series]](arrow-right.gif){kind=icon}
This function is available only with RAP – The Trading Edition Enterprise.
Performs a forward or inverse Box-Cox power transformation.
TS_BOX_COX_XFORM (timeseries_expression, power [, shift [, inverse] ]) OVER (window-spec)
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, IQ performs an inverse transformation. If 0 or null, 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.
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.
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, 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 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_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:
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:
res |
|---|
8 |
27 |
7 |
61 |
79.5 |
2 |
30 |
16 |
53 |
75.3 |
12 |
57 |
9 |
Chapter 2, “Using OLAP” in the System Administration Guide: Volume 2
IMSL Numerical Library User’s Guide: Volume 2 of 2 C Stat Library
