![TS_BOX_COX_XFORM function [Time Series]](arrow-left.gif){kind=icon}
![TS_ESTIMATE_MISSING function [Time Series]](arrow-right.gif){kind=icon}
This function is available only with RAP – The Trading Edition Enterprise.
Differences a seasonal or nonseasonal time series.
TS_DIFFERENCE (timeseries_expression, period1 [, period2 [, ...period 10] ]) OVER (window-spec)
timeseries_expression A numeric expression, generally a column name, containing an element in a time series to be differenced.
period1 ... period10 Each period is an integer expression containing the period in which the time series is to be differenced. You must specify at least one period, and you can specify up to 10 periods.
window-spec TS_DIFFERENCE 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.
For each element in the time series, TS_DIFFERENCE returns the corresponding calculated differenced value for the time series; it calls the function imsls_d_difference in the IMSL libraries.
The arguments of TS_DIFFERENCE map to the IMSL library function imsls_d_difference as follows:
params = imsls_d_difference(n_objs, z[], n_differences, periods [], 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.
n_differences Maps to the period arguments defined in TS_DIFFERENCE.
period An array of the period arguments defined in TS_DIFFERENCE.
For detailed information on how the function imsls_d_difference performs time series calculations, see IMSL C 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_DIFFERENCE function, and the data values returned by the function. This example uses the following table (called DATASET) as its input data. The DATASET table contains 50 rows of time series data:
rownum |
data |
|---|---|
1 |
0.315523 |
2 |
0.485859 |
3 |
0.676886 |
4 |
1.97381 |
5 |
2.77555 |
6 |
2.73657 |
7 |
2.64233 |
8 |
4.26118 |
9 |
3.13641 |
10 |
4.16566 |
11 |
2.95952 |
12 |
2.14504 |
13 |
1.98799 |
14 |
0.805859 |
15 |
0.833405 |
16 |
2.29075 |
17 |
1.30045 |
18 |
0.467122 |
19 |
-0.170107 |
20 |
-0.256657 |
21 |
-0.382597 |
22 |
-0.505511 |
23 |
-1.90147 |
24 |
-0.981688 |
25 |
-1.43116 |
26 |
-1.39389 |
27 |
-2.34823 |
28 |
-2.91122 |
29 |
-0.927423 |
30 |
-0.044383 |
31 |
-0.389648 |
32 |
0.545008 |
33 |
0.614096 |
34 |
0.364668 |
35 |
1.16043 |
36 |
-0.654063 |
37 |
0.616094 |
38 |
2.00875 |
39 |
1.86696 |
40 |
2.80171 |
41 |
3.78422 |
42 |
4.11499 |
43 |
2.77188 |
44 |
4.00312 |
45 |
4.21298 |
46 |
5.00413 |
47 |
4.74498 |
48 |
4.89621 |
49 |
3.93273 |
50 |
4.31592 |
The following SQL statement differences data from the data column:
SELECT TS_DIFFERENCE(data,1) OVER (ORDER BY ROWNUM rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS res FROM DATASET
Sybase IQ returns 50 rows:
res |
|---|
NULL |
0.170336 |
0.191027 |
1.29692 |
0.801743 |
-0.038988 |
-0.09424 |
1.61886 |
-1.12477 |
1.02925 |
-1.20614 |
-0.814478 |
-0.157049 |
-1.18213 |
0.027546 |
1.45734 |
-0.990302 |
-0.833325 |
-0.637229 |
-0.08655 |
-0.12594 |
-0.122914 |
-1.39596 |
0.919785 |
-0.449474 |
0.037273 |
-0.954345 |
-0.562983 |
1.98379 |
0.88304 |
-0.345265 |
0.934656 |
0.069088 |
-0.249428 |
0.795766 |
-1.8145 |
1.27016 |
1.39266 |
-0.141794 |
0.934752 |
0.982506 |
0.330772 |
-1.34311 |
1.23124 |
0.209869 |
0.791146 |
-0.259155 |
0.15124 |
-0.963484 |
0.383186 |
The first row of results is NULL because the IMSL library
returned a not a number (NaN) value for that
row.
Chapter 2, “Using OLAP” in the System Administration Guide: Volume 2
IMSL Numerical Library User’s Guide: Volume 2 of 2 C Stat Library
