Release 8.1.5
A67294-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Time Series User's Guide Release 8.1.5 A67294-01 |
|
The Oracle8i Time Series library consists of:
Calendar functions are mainly used by product developers, such as ISVs, to develop new time series functions and to administer and modify calendars.
Time series and time scaling functions and the administrative tools procedures are used mainly by application developers.
Syntax notes:
select CAST(TimeSeries.ExtractTable(close) AS ORDTNumTab) select cast(TIMESERIES.extracttable(close) as ordtnumtab) select cast(TiMeSeRiEs.eXtRaCtTaBlE(ClosE) As ordtNUMtab)
All time series and time scaling functions accept both references and instances as parameters. (For example, an ORDTNumSeriesIOTRef parameter could also be ORDTNumSeries.) All time series functions return instances. Thus, if you nest functions, such as Cmax(Cmax(...), ...), the innermost nesting accepts a reference and returns an instance, and any other functions in the nesting accept an instance and return an instance.
For an explanation of the reference-based interface, see Section 2.7.2.
ORDSYS.TimeSeries.Cavg(
[tsname VARCHAR2,]
ts ORDSYS.ORDTNumSeriesIOTRef
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given a time series, returns an ORDTNumSeries with each element containing the cumulative average up to and including the corresponding element in the input ORDTNumSeries.
Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.
The input time series.
Starting date within the time series for which the cumulative average is to be computed. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the cumulative average is to be computed. If endDate is specified, startDate must also be specified.
Only non-null values are considered in computing the cumulative average.
An exception is returned if one or more of the following conditions are true:
If startDate and endDate are specified, the time series is trimmed to the date range before the cumulative average is computed.
Return the cumulative average of the closing price of stock ACME for November 1996:
SELECT to_char(tstamp) tstamp, value FROM tsquick ts, TABLE (CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.Cavg(ts.close,to_date('01-NOV-96','DD-MON-YY'), to_date('30-NOV-96','DD-MON-YY')) ) AS ORDSYS.ORDTNumTab)) t WHERE ts.ticker='ACME';
This example might produce the following output:
TSTAMP VALUE --------- ---------- 01-NOV-96 59 04-NOV-96 59.5 05-NOV-96 60 06-NOV-96 60.5 07-NOV-96 61 08-NOV-96 61.5 11-NOV-96 62 12-NOV-96 62.5 13-NOV-96 63 14-NOV-96 63.5 15-NOV-96 64 18-NOV-96 64.5 19-NOV-96 65 20-NOV-96 65.5 21-NOV-96 66 22-NOV-96 66.5 25-NOV-96 67 26-NOV-96 67.5 27-NOV-96 68 29-NOV-96 68.5 20 rows selected.
ORDSYS.TimeSeries.Cmax(
[tsname VARCHAR2,]
ts ORDSYS.ORDTNumSeriesIOTRef
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given a time series, returns an ORDTNumSeries with each element containing the cumulative maximum up to and including the corresponding element in the input ORDTNumSeries.
Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.
The input time series.
Starting date within the time series for which the cumulative maximum is to be returned. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the cumulative maximum is to be returned. If endDate is specified, startDate must also be specified.
Only non-null values are considered in determining the cumulative maximum.
An exception is returned if one or more of the following conditions are true:
If startDate and endDate are specified, the time series is trimmed to the date range before the cumulative maximum is computed.
Return the cumulative maximum of the closing price of stock ACME for November 1996:
SELECT to_char(tstamp) tstamp, value FROM tsquick ts, TABLE (CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.Cmax(ts.close,to_date('01-NOV-96','DD-MON-YY'), to_date('30-NOV-96','DD-MON-YY')) ) AS ORDSYS.ORDTNumTab)) t WHERE ts.ticker='ACME';
This example might produce the following output. (Note that this output reflects the simplified artificial data in the usage demo database, where the closing price rises one point each day.)
TSTAMP VALUE --------- ---------- 01-NOV-96 59 04-NOV-96 60 05-NOV-96 61 06-NOV-96 62 07-NOV-96 63 08-NOV-96 64 11-NOV-96 65 12-NOV-96 66 13-NOV-96 67 14-NOV-96 68 15-NOV-96 69 18-NOV-96 70 19-NOV-96 71 20-NOV-96 72 21-NOV-96 73 22-NOV-96 74 25-NOV-96 75 26-NOV-96 76 27-NOV-96 77 29-NOV-96 78 20 rows selected.
ORDSYS.TimeSeries.Cmin(
[tsname VARCHAR2,]
ts ORDSYS.ORDTNumSeriesIOTRef
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given a time series, returns an ORDTNumSeries with each element containing the cumulative minimum up to and including the corresponding element in the input ORDTNumSeries.
Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.
The input time series.
Starting date within the time series for which the cumulative minimum is to be returned. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the cumulative minimum is to be returned. If endDate is specified, startDate must also be specified.
Only non-null values are considered in determining the cumulative minimum.
An exception is returned if one or more of the following conditions are true:
If startDate and endDate are specified, the time series is trimmed to the date range before the cumulative minimum is computed.
Return the cumulative minimum of the closing price of stock ACME for November 1996:
SELECT to_char(tstamp) tstamp, value FROM tsquick ts, TABLE (CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.Cmin(ts.close,to_date('01-NOV-96','DD-MON-YY'), to_date('30-NOV-96','DD-MON-YY')) ) AS ORDSYS.ORDTNumTab)) t WHERE ts.ticker='ACME';
This example might produce the following output. (Note that this output reflects the simplified artificial data in the usage demo database, where the closing price rises one point each day.)
TSTAMP VALUE --------- ---------- 01-NOV-96 59 04-NOV-96 59 05-NOV-96 59 06-NOV-96 59 07-NOV-96 59 08-NOV-96 59 11-NOV-96 59 12-NOV-96 59 13-NOV-96 59 14-NOV-96 59 15-NOV-96 59 18-NOV-96 59 19-NOV-96 59 20-NOV-96 59 21-NOV-96 59 22-NOV-96 59 25-NOV-96 59 26-NOV-96 59 27-NOV-96 59 29-NOV-96 59 20 rows selected.
ORDSYS.TimeSeries.Cprod(
[tsname VARCHAR2,]
ts ORDSYS.ORDTNumSeriesIOTRef
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given a time series, returns an ORDTNumSeries with each element containing the cumulative product of multiplication up to and including the corresponding element in the input ORDTNumSeries.
Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.
The input time series.
Starting date within the time series for which the cumulative product is to be computed. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the cumulative product is to be computed. If endDate is specified, startDate must also be specified.
Only non-null values are considered in computing the cumulative product.
An exception is returned if one or more of the following conditions are true:
If startDate and endDate are specified, the time series is trimmed to the date range before the cumulative product is computed.
Return the cumulative product of the daily volume of stock ACME for the first four trading days of November 1996. (This example is presented merely to illustrate the function; the results of this query have no practical value for financial analysis.)
SELECT to_char(tstamp) tstamp, value FROM tsquick ts, TABLE (CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.Cprod(ts.volume,to_date('01-NOV-96','DD-MON-YY'), to_date('06-NOV-96','DD-MON-YY')) ) AS ORDSYS.ORDTNumTab)) t WHERE ts.ticker='ACME';
This example might produce the following output:
TSTAMP VALUE --------- ---------- 01-NOV-96 1000 04-NOV-96 1000000 05-NOV-96 1000000000 06-NOV-96 1.0000E+12 4 rows selected.
ORDSYS.TimeSeries.Csum(
[tsname VARCHAR2,]
ts ORDSYS.ORDTNumSeriesIOTRef
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given a time series, returns an ORDTNumSeries with each element containing the cumulative sum up to and including the corresponding element in the input ORDTNumSeries.
Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.
The input time series.
Starting date within the time series for which the cumulative sum is to be computed. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the cumulative sum is to be computed. If endDate is specified, startDate must also be specified.
Only non-null values are considered in computing the cumulative sum.
An exception is returned if one or more of the following conditions are true:
If startDate and endDate are specified, the time series is trimmed to the date range before the cumulative sum is computed.
Return the cumulative sum of the daily volume of stock ACME for November 1996:
SELECT to_char(tstamp) tstamp, value FROM tsquick ts, TABLE (CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.Csum(ts.volume,to_date('01-NOV-96','DD-MON-YY'), to_date('30-NOV-96','DD-MON-YY')) ) AS ORDSYS.ORDTNumTab)) t WHERE ts.ticker='ACME';
This example might produce the following output:
TSTAMP VALUE --------- ---------- 01-NOV-96 1000 04-NOV-96 2000 05-NOV-96 3000 06-NOV-96 4000 07-NOV-96 5000 08-NOV-96 6000 11-NOV-96 7000 12-NOV-96 8000 13-NOV-96 9000 14-NOV-96 10000 15-NOV-96 11000 18-NOV-96 12000 19-NOV-96 13000 20-NOV-96 14000 21-NOV-96 15000 22-NOV-96 16000 25-NOV-96 17000 26-NOV-96 18000 27-NOV-96 19000 29-NOV-96 20000 20 rows selected.
Approach 1:
ORDSYS.TimeSeries.DeriveExceptions(
or
ORDSYS.TimeSeries.DeriveExceptions(
Approach 1A:
ORDSYS.TimeSeries.DeriveExceptions(
inputCal IN ORDSYS.ORDTCalendar,
DateTab IN ORDSYS.ORDTDateTab
[, startDate DATE [, endDate DATE]]
) RETURN ORDSYS.ORDTCalendar;
Approach 2:
ORDSYS.TimeSeries.DeriveExceptions(
series1 ORDTNumSeriesIOTRef,
series2 ORDTNumSeriesIOTRef
[, startDate DATE [, endDate DATE]]
) RETURN ORDSYS.ORDTCalendar;
or
ORDSYS.TimeSeries.DeriveExceptions(
series1 ORDTVarchar2SeriesIOTRef,
series2 ORDTVarchar2SeriesIOTRef
[, startDate DATE [, endDate DATE]]
) RETURN ORDSYS.ORDTCalendar;
Derives calendar exceptions from a time series (Approach 1), a calendar and a table of dates (Approach 1A), or two time series (Approach 2).
The time series whose calendar is to be used as the basis for the returned calendar and whose timestamps are to be used to populate the off- and on-exceptions lists of the returned calendar.
Starting date within the time series for which the exceptions are to be derived. If startDate is not specified, it is the minDate of the calendar and endDate is the maxDate of the calendar.
Ending date within the time series for which the exceptions are to be derived. If endDate is specified, startDate must also be specified. If startDate is specified and endDate is not specified, endDate is the maxDate of the calendar.
The calendar that contains no exceptions and for which exceptions are to be derived.
The table of dates that includes all dates in the time series (for example, all dates on which stock XYZ traded).
The "reference" time series that contains no exceptions and all valid timestamps from the calendar (for example, all Monday through Friday dates within the date range of the calendar).
The time series that contains the timestamps to be used in deriving the exceptions for the resulting calendar (for example, all dates on which stock XYZ traded).
Approach 1 is the most convenient method. You specify a time series (for example, daily closing prices of stock XYZ) that has an associated calendar. A calendar is returned that is defined on the same pattern and frequency as the calendar for the input time series, and the exceptions lists of the returned calendar are populated to be consistent with the time series data.
Approach 1A is a variation of Approach 1 in which you specify a calendar and a table of the desired timestamps (for example, dates on which stock XYZ traded).
Approach 2 involves creating a time series (series1) that in effect functions as a calendar, and then using a second time series (series2) with desired timestamps to populate the exceptions lists. Approach 2 offers a performance advantage if you need to derive exceptions for many calendars based on many time series.
See Section 2.2.5 for a detailed explanation of the approaches to using this function.
See Section 3.8 for examples of the approaches to using this function.
ORDSYS.TimeSeries.Display(
Displays various information (see the description of the ts parameter) using DBMS_OUTPUT routines.
The object to be displayed. Because the function is overloaded, this parameter can be any of the following data types:
Optional message text to be included in the display heading ("Timeseries dump for <mesg>").
Use the SET SERVEROUTPUT ON statement to view the output of the Display function. However, the default display buffer of 2000 bytes is often too small to display a large time series. In such cases you must use the ENABLE procedure of the DBMS_OUTPUT package to specify a larger display buffer size. For example:
DBMS_OUTPUT.ENABLE(1000000);
You should use Display only for development and debugging. Specify a display buffer larger than 2000 only when necessary, because the display buffer uses shared system resources, and a large value might affect the performance of other users.
Because the Display function uses DBMS_OUTPUT routines, it is subject to the limitations of these routines. These limitations include the following:
Display the output for a query that returns the 10 highest closing prices for stock ACME for the month of November 1996:
SET SERVEROUTPUT ON DECLARE tmp INTEGER; BEGIN SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.TSMaxN(close,10, to_date('11011996','MMDDYYYY'), to_date('11301996','MMDDYYYY'))) INTO tmp FROM TSDEV.stockdemo_ts WHERE ticker ='ACME'; END; /
This example might produce the following output:
Tab Data: ----------------------------- Date Value 29-NOV-96 78 27-NOV-96 77 26-NOV-96 76 25-NOV-96 75 22-NOV-96 74 21-NOV-96 73 20-NOV-96 72 19-NOV-96 71 18-NOV-96 70 15-NOV-96 69 -----------------------------
The preceding example works from both SQL*Plus and the Server Manager (svrmgrl) prompt. The following version of the example works from the Server Manager prompt but not from SQL*Plus:
SET SERVEROUTPUT ON SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.TSMaxN(close,10, to_date('11011996','MMDDYYYY'), to_date('11301996','MMDDYYYY'))) FROM TSDEV.stockdemo_ts WHERE ticker ='ACME';
See the TSMaxN function for an example that returns the same information, but that uses a subquery instead of the Display function.
ORDSYS.TimeSeries.DisplayValTS(
validFlag IN INTEGER,
outMessage IN VARCHAR2,
loDateTab IN ORDSYS.ORDTDateTab,
hiDateTab IN ORDSYS.ORDTDateTab,
impreciseDateTab IN ORDSYS.ORDTDateTab,
duplicateDateTab IN ORDSYS.ORDTDateTab,
extraDateTab IN ORDSYS.ORDTDateTab,
missingDateTab IN ORDSYS.ORDTDateTab,
mesg IN VARCHAR2
);
Displays the results returned by the ValidateTS function.
The return value from the ValidateTS function.
The diagnostic returned by the ValidateTS function.
A table of dates before the starting date of the calendar associated with the time series.
A table of dates after the starting date of the calendar associated with the time series.
A table of the imprecise dates found in the time series.
A table of the duplicate dates (dates that appear more than once in the time series).
A table of dates that are included in the time series but that should be excluded based on the calendar definition (for example, a Saturday timestamp that is in a Monday-Friday calendar and that is not an on-exception).
A table of dates that are excluded from the time series but that should be included based on the calendar definition (for example, a Wednesday date that is not a holiday in a Monday-Friday calendar and for which there is no data). Such dates can be considered as "holes" in the time series.
Optional message.
This procedure is intended to be used with the ValidateTS function. See the information on ValidateTS in this chapter.
The DisplayValTS procedure uses the DBMS_OUTPUT package. See the Usage information for the Display function for limitations relating to the use of DBMS_OUTPUT.
Use the IsValidTS and ValidateTS functions and the DisplayValTS procedure with an invalid time series:
SET SERVEROUTPUT ON ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS'; DECLARE numTS ORDSYS.ORDTNumSeries; tempVal integer; retIsValid integer; retValTS integer; loDateTab ORDSYS.ORDTDateTab := NULL; hiDateTab ORDSYS.ORDTDateTab := NULL; impDateTab ORDSYS.ORDTDateTab := NULL; dupDateTab ORDSYS.ORDTDateTab := NULL; extraDateTab ORDSYS.ORDTDateTab := NULL; missingDateTab ORDSYS.ORDTDateTab := NULL; outMesg varchar2(2000); BEGIN -- Set the buffer size DBMS_OUTPUT.ENABLE(100000); -- -- NOTE: Here, an instance of the time series is materialized -- so that it could be modified to generate an invalid time series. -- SELECT ORDSYS.TIMESERIES.GetSeries(ts.open) INTO numTS FROM tsdev.stockdemo_ts ts WHERE ts.ticker = 'ACME'; -- Example of validating a valid time series. SELECT ordsys.timeseries.display(numTS, 'A VALID TIME SERIES') INTO tempVal FROM dual; retIsValid := ORDSYS.TIMESERIES.IsValidTS(numTS); retValTS := ORDSYS.TIMESERIES.ValidateTS(numTS, outMesg, loDateTab, hiDateTab, impDateTab, dupDateTab, extraDateTab, missingDateTab); DBMS_OUTPUT.PUT_LINE('Value returned by IsValid = ' || retIsValid); DBMS_OUTPUT.PUT_LINE('Value returned by ValidateTS = ' || retValTS); ORDSYS.TIMESERIES.DisplayValTS(retValTS, outMesg, loDateTab, hiDateTab, impDateTab, dupDateTab, extraDateTab, missingDateTab, 'Testing DisplayValTS'); DBMS_OUTPUT.NEW_LINE; -- For illustration let us first create an invalid timeseries. -- -- Here we are adjusting the calendar's minDate and maxDate to avoid -- getting a huge list of missing dates. -- numTS.cal.minDate := TO_DATE('10/28/1996'); numTS.cal.maxDate := TO_DATE('01/05/1997'); -- Add Dates Before numTS.cal.minDate numTS.series(10).tstamp := numTS.cal.minDate - 1; numTS.series(11).tstamp := numTS.cal.minDate - 2; -- Add Dates Beyond numTS.cal.maxDate numTS.series(12).tstamp := numTS.cal.maxDate + 1; numTS.series(13).tstamp := numTS.cal.maxDate + 2; -- Add some null timestamps numTS.series(14).tstamp := NULL; numTS.series(15).tstamp := NULL; -- Add some imprecise dates (some are duplicated) numTS.series(17).tstamp := numTS.series(16).tstamp + 1/24; numTS.series(18).tstamp := numTS.series(16).tstamp + 15/24; -- Add some duplicate timestamps numTS.series(19).tstamp := numTS.series(18).tstamp; numTS.series(21).tstamp := numTS.series(20).tstamp; -- Add some extra dates in the middle numTS.series(37).tstamp := TO_DATE('12/28/1996'); numTS.series(36).tstamp := TO_DATE('12/29/1996'); -- Add some holes at the end numTS.series(numTS.series.count).tstamp := TO_DATE('01/04/1997'); -- Example of validating an invalid time series. SELECT ordsys.timeseries.display(numTS, 'AN INVALID TIME SERIES') INTO tempVal FROM dual; retIsValid := ORDSYS.TIMESERIES.IsValidTS(numTS); retValTS := ORDSYS.TIMESERIES.ValidateTS(numTS, outMesg, loDateTab, hiDateTab, impDateTab, dupDateTab, extraDateTab, missingDateTab); DBMS_OUTPUT.PUT_LINE('Value returned by IsValid = ' || retIsValid); DBMS_OUTPUT.PUT_LINE('Value returned by ValidateTS = ' || retValTS); ORDSYS.TIMESERIES.DisplayValTS(retValTS, outMesg, loDateTab, hiDateTab, impDateTab, dupDateTab, extraDateTab, missingDateTab, 'Testing DisplayValTS'); END; /
This example might produce the following output:
A VALID TIME SERIES : Name = OPEN ACME Calendar Data: Calendar Name = BUSINESS-96 Frequency = 4 (day) MinDate = 11/01/1996 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0,1,1,1,1,1,0 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : 11/28/1996 00:00:00 12/25/1996 00:00:00 Series Data: ----------------------------- Date Value 11/01/1996 00:00:00 59 11/04/1996 00:00:00 60 11/05/1996 00:00:00 61 11/06/1996 00:00:00 62 11/07/1996 00:00:00 63 11/08/1996 00:00:00 64 11/11/1996 00:00:00 65 11/12/1996 00:00:00 66 11/13/1996 00:00:00 67 11/14/1996 00:00:00 68 11/15/1996 00:00:00 69 11/18/1996 00:00:00 70 11/19/1996 00:00:00 71 11/20/1996 00:00:00 72 11/21/1996 00:00:00 73 11/22/1996 00:00:00 74 11/25/1996 00:00:00 75 11/26/1996 00:00:00 76 11/27/1996 00:00:00 77 11/29/1996 00:00:00 78 12/02/1996 00:00:00 79 12/03/1996 00:00:00 80 12/04/1996 00:00:00 81 12/05/1996 00:00:00 82 12/06/1996 00:00:00 83 12/09/1996 00:00:00 84 12/10/1996 00:00:00 85 12/11/1996 00:00:00 86 12/12/1996 00:00:00 87 12/13/1996 00:00:00 88 12/16/1996 00:00:00 89 12/17/1996 00:00:00 90 12/18/1996 00:00:00 91 12/19/1996 00:00:00 92 12/20/1996 00:00:00 93 12/23/1996 00:00:00 94 12/24/1996 00:00:00 95 12/26/1996 00:00:00 96 12/27/1996 00:00:00 97 12/30/1996 00:00:00 98 12/31/1996 00:00:00 99 ----------------------------- Value returned by IsValid = 1 Value returned by ValidateTS = 1 DisplayValTS: Testing DisplayValTS: TS-SUC: the input time series is a valid time series AN INVALID TIME SERIES : Name = OPEN ACME Calendar Data: Calendar Name = BUSINESS-96 Frequency = 4 (day) MinDate = 10/28/1996 00:00:00 MaxDate = 01/05/1997 00:00:00 patBits: 0,1,1,1,1,1,0 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : 11/28/1996 00:00:00 12/25/1996 00:00:00 Series Data: ----------------------------- Date Value 11/01/1996 00:00:00 59 11/04/1996 00:00:00 60 11/05/1996 00:00:00 61 11/06/1996 00:00:00 62 11/07/1996 00:00:00 63 11/08/1996 00:00:00 64 11/11/1996 00:00:00 65 11/12/1996 00:00:00 66 11/13/1996 00:00:00 67 10/27/1996 00:00:00 68 10/26/1996 00:00:00 69 01/06/1997 00:00:00 70 01/07/1997 00:00:00 71 72 73 11/22/1996 00:00:00 74 11/22/1996 01:00:00 75 11/22/1996 15:00:00 76 11/22/1996 15:00:00 77 11/29/1996 00:00:00 78 11/29/1996 00:00:00 79 12/03/1996 00:00:00 80 12/04/1996 00:00:00 81 12/05/1996 00:00:00 82 12/06/1996 00:00:00 83 12/09/1996 00:00:00 84 12/10/1996 00:00:00 85 12/11/1996 00:00:00 86 12/12/1996 00:00:00 87 12/13/1996 00:00:00 88 12/16/1996 00:00:00 89 12/17/1996 00:00:00 90 12/18/1996 00:00:00 91 12/19/1996 00:00:00 92 12/20/1996 00:00:00 93 12/29/1996 00:00:00 94 12/28/1996 00:00:00 95 12/26/1996 00:00:00 96 12/27/1996 00:00:00 97 12/30/1996 00:00:00 98 01/04/1997 00:00:00 99 ----------------------------- Value returned by IsValid = 0 Value returned by ValidateTS = 0 DisplayValTS: Testing DisplayValTS: TS-WRN: the input time series has errors. See the message for details message output by validateTS: TS-ERR: the input time series is unsorted TS-ERR: the time series has null timestamps TS-ERR: the time series has timestamps < calendar minDate (refer LoDateTab) TS-ERR: the time series has timestamps > calendar maxDate (refer HiDateTab) TS-ERR: the time series has imprecise timestamps (refer impreciseDateTab) TS-ERR: the time series has duplicate timestamps (refer DuplicateDateTab) list of dates < calendar minDate - lowDateTab : 10/26/1996 00:00:00 10/27/1996 00:00:00 list of dates > calendar maxDate - hiDateTab : 01/06/1997 00:00:00 01/07/1997 00:00:00 list of imprecise dates - impreciseDateTab : 11/22/1996 01:00:00 11/22/1996 15:00:00 list of duplicate dates - duplicateDateTab : 11/22/1996 15:00:00 11/29/1996 00:00:00 ExtraDateTab : 12/28/1996 00:00:00 12/29/1996 00:00:00 01/04/1997 00:00:00 MissingDateTab : 10/28/1996 00:00:00 10/29/1996 00:00:00 10/30/1996 00:00:00 10/31/1996 00:00:00 11/14/1996 00:00:00 11/15/1996 00:00:00 11/18/1996 00:00:00 11/19/1996 00:00:00 11/20/1996 00:00:00 11/21/1996 00:00:00 11/25/1996 00:00:00 11/26/1996 00:00:00 11/27/1996 00:00:00 12/02/1996 00:00:00 12/23/1996 00:00:00 12/24/1996 00:00:00 12/31/1996 00:00:00 01/01/1997 00:00:00 01/02/1997 00:00:00 01/03/1997 00:00:00
ORDSYS.TimeSeries.ExtractCal(
or
ORDSYS.TimeSeries.ExtractCal(
Given a time series, returns a calendar that is the same as the calendar on which the time series is based.
The input time series.
The function returns a calendar that has the same starting and ending timestamps, pattern, frequency, and exceptions (on- and off-) as the calendar on which the specified time series is based.
An exception is returned if the time series (ts) is null.
Return a calendar that matches the one on which the time series for the ACME ticker is based:
SET SERVEROUTPUT ON ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS'; DECLARE dummyval INTEGER; BEGIN SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.ExtractCal(ts.open), 'ExtractCal Results') INTO dummyval FROM TSDEV.stockdemo_ts ts WHERE ts.ticker='ACME'; END; /
This example might produce the following output:
ExtractCal Results : Calendar Name = BUSINESS-96 Frequency = 4 (day) MinDate = 11/01/1996 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0,1,1,1,1,1,0 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : 11/28/1996 00:00:00 12/25/1996 00:00:00
ORDSYS.TimeSeries.ExtractDate(
or
ORDSYS.TimeSeries.ExtractDate(
Given an element in a time series, returns the date.
The time series element for which you want the date.
The time series element must first be identified, such as by using the GetNthElement function.
An exception is returned if the time series element (cell) is null.
Return the date associated with the tenth element in a specified time series:
SELECT to_char( ORDSYS.TimeSeries.ExtractDate( ORDSYS.TimeSeries.GetNthElement(open, 10)), 'MM/DD/YYYY HH24:MI:SS') FROM TSDEV.stockdemo_ts WHERE ticker = 'ACME';
This example might produce the following output:
TO_CHAR(ORDSYS.TIME ------------------- 11/14/1996 00:00:00 1 row selected.
ORDSYS.TimeSeries.ExtractTable(
or
ORDSYS.TimeSeries.ExtractTable(
Given a time series, returns the time series table (ORDTNumTab or ORDTVarchar2Tab) associated with the time series.
The input time series.
The function returns the time series table (ORDTNumTab or ORDTVarchar2Tab) associated with the time series.
An exception is returned if the time series (ts) is null.
Return the closing prices for stock ACME:
SELECT * FROM the (SELECT CAST(ORDSYS.TimeSeries.ExtractTable(ts.close) as ORDSYS.ORDTNumTab) FROM TSDEV.stockdemo_ts ts WHERE ts.ticker='ACME');
This example might produce the following output:
TSTAMP VALUE --------- ---------- 01-NOV-96 59 04-NOV-96 60 05-NOV-96 61 ... ... 31-DEC-96 99 41 rows selected.
ORDSYS.TimeSeries.ExtractValue(
or
ORDSYS.TimeSeries.ExtractValue(
Given an element in a time series, returns the value stored in it.
The time series element for which you want the value.
The time series element must first be identified, such as by using the GetNthElement function.
An exception is returned if the time series element (cell) is null.
Return the value of the tenth opening price in the stockdemo_ts table:
SELECT ORDSYS.TimeSeries.ExtractValue( ORDSYS.TimeSeries.GetNthElement(open, 10)) FROM TSDEV.stockdemo_ts WHERE ticker = 'ACME';
This example might produce the following output:
ORDSYS.TIM ---------- 68 1 row selected.
ORDSYS.TimeSeries.Fill(
[tsname VARCHAR2,]
ts ORDSYS.ORDTNumSeriesIOTRef
[, fill_type INTEGER]
) RETURN ORDSYS.ORDTNumSeries;
Given a time series and optionally a fill type, returns a time series in which values for missing dates are inserted. A missing date is a date that is defined by the calendar and within the time series bounds, but that is not in the current time series.
Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.
The input time series.
One of the following integers indicating how missing values are to be filled:
If fill_type is omitted, 0 is assumed.
The function inserts timestamps and associated values for timestamps that are included in a calendar but for which no entries exist in the time series.
The fill_type parameter lets you choose the manner in which missing values will be defaulted. For example, assume that data for 30-Jan-1997 (Thursday) is missing from a time series and that it should be included because this date is within the calendar definition. Assume the following closing prices for stock XYZ:
The following table shows the closing price that would be inserted for 30-Jan-1997 with each of the fill_type parameter values:
| fill_type | Closing Price for 30-Jan-1997 |
|---|---|
|
0 |
null |
|
1 |
49 |
|
2 |
50 |
Some potential uses for this function include:
For example, you may want to compare prices for a stock that trades on several stock exchanges, where the exchanges have different trading days.
For example, earnings per share (EPS) is computed quarterly, and stocks trade daily. To compute a price-earnings (PE) ratio, earnings per share is first converted to a daily time series using forward repeat. Then, the daily PE ratio is calculated by dividing the daily price time series value by the corresponding daily EPS time series value.
An exception is returned if the specified fill_type value is not 0, 1, or 2.
Return a time series illustrating each fill_type value:
SET SERVEROUTPUT ON ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS'; -- For illustrating Fill we need a time series with missing dates. -- In the following example, the time series 'FOO' has some missing dates -- (07-DEC-1996 and 08-DEC-1996). Also, note that the calendar associated -- with 'FOO' has an 'all one' pattern. -- DECLARE tstCal ORDSYS.ORDTCalendar; ts ORDSYS.ordtnumseries := ORDSYS.ordtnumseries( 'FOO', ORDSYS.ORDTCalendar( 0, 'FOO CALENDAR', 4, ORDSYS.ORDTPattern( ORDSYS.ORDTPatternBits(1,1,1,1,1,1,1), TO_DATE('01/07/1996')), TO_DATE('01/01/1996'), TO_DATE('01/01/1997'), ORDSYS.ORDTExceptions(), ORDSYS.ORDTExceptions() ), ORDSYS.ordtnumtab( ORDSYS.ordtnumcell(TO_DATE('12/02/1996'), 1), ORDSYS.ordtnumcell(TO_DATE('12/03/1996'), 2), ORDSYS.ordtnumcell(TO_DATE('12/04/1996'), 3), ORDSYS.ordtnumcell(TO_DATE('12/05/1996'), 4), ORDSYS.ordtnumcell(TO_DATE('12/06/1996'), 5), ORDSYS.ordtnumcell(TO_DATE('12/09/1996'), 6), ORDSYS.ordtnumcell(TO_DATE('12/10/1996'), 7), ORDSYS.ordtnumcell(TO_DATE('12/11/1996'), 8), ORDSYS.ordtnumcell(TO_DATE('12/12/1996'), 9), ORDSYS.ordtnumcell(TO_DATE('12/13/1996'), 10)) ); dummyval INTEGER; BEGIN -- Generate a time series by from XCORP's high (repeat forward). SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.Fill(ts, 1), 'Fill Forward') INTO dummyval FROM dual; -- Generate a time series by from XCORP's high (repeat backward). SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.Fill(ts, 2), 'Fill Backward') INTO dummyval FROM dual; -- Generate a time series by from XCORP's high (null fill). SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.Fill(ts, 0), 'Null Fill') INTO dummyval FROM dual; END; /
This example might produce the following output:
Fill Forward : Calendar Data: Calendar Name = FOO CALENDAR Frequency = 4 (day) MinDate = 01/01/1996 00:00:00 MaxDate = 01/01/1997 00:00:00 patBits: 1,1,1,1,1,1,1 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : Series Data: ----------------------------- Date Value 12/02/1996 00:00:00 1 12/03/1996 00:00:00 2 12/04/1996 00:00:00 3 12/05/1996 00:00:00 4 12/06/1996 00:00:00 5 12/07/1996 00:00:00 5 12/08/1996 00:00:00 5 12/09/1996 00:00:00 6 12/10/1996 00:00:00 7 12/11/1996 00:00:00 8 12/12/1996 00:00:00 9 12/13/1996 00:00:00 10 ----------------------------- Fill Backward : Calendar Data: Calendar Name = FOO CALENDAR Frequency = 4 (day) MinDate = 01/01/1996 00:00:00 MaxDate = 01/01/1997 00:00:00 patBits: 1,1,1,1,1,1,1 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : Series Data: ----------------------------- Date Value 12/02/1996 00:00:00 1 12/03/1996 00:00:00 2 12/04/1996 00:00:00 3 12/05/1996 00:00:00 4 12/05/1996 00:00:00 4 12/06/1996 00:00:00 5 12/07/1996 00:00:00 6 12/08/1996 00:00:00 6 12/09/1996 00:00:00 6 12/10/1996 00:00:00 7 12/11/1996 00:00:00 8 12/12/1996 00:00:00 9 12/13/1996 00:00:00 10 ----------------------------- Null Fill : Calendar Data: Calendar Name = FOO CALENDAR Frequency = 4 (day) MinDate = 01/01/1996 00:00:00 MaxDate = 01/01/1997 00:00:00 patBits: 1,1,1,1,1,1,1 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : Series Data: ----------------------------- Date Value 12/02/1996 00:00:00 1 12/03/1996 00:00:00 2 12/04/1996 00:00:00 3 12/05/1996 00:00:00 4 12/06/1996 00:00:00 5 12/07/1996 00:00:00 12/08/1996 00:00:00 12/09/1996 00:00:00 6 12/10/1996 00:00:00 7 12/11/1996 00:00:00 8 12/12/1996 00:00:00 9 12/13/1996 00:00:00 10 -----------------------------
ORDSYS.TimeSeries.First(
Given a time series, returns the first element in it.
The input time series.
A null is returned if the time series (ts) is empty.
An exception is returned if the time series (ts) is null.
Return the first timestamp and opening price for stock ACME in the stockdemo_ts time series:
SET SERVEROUTPUT ON ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS'; DECLARE dummyval INTEGER; BEGIN SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.First(ts.open), 'First Results') INTO dummyval FROM TSDEV.stockdemo_ts ts WHERE ts.ticker='ACME'; END; /
This example might produce the following output:
First Results : Timestamp : 11/01/1996 00:00:00 Value : 59
ORDSYS.TimeSeries.FirstN(
[tsname VARCHAR2,]
ts ORDSYS.ORDTNumSeriesIOTRef,
NumValues NUMBER
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given a time series and a number of elements (NumValues) to return, returns the first NumValues elements in the time series.
Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.
The input time series.
Number of elements from the beginning of the time series to be returned.
Starting date within the time series for which NumValues elements are to be returned. If startDate is specified, endDate must also be specified.
Ending date within the time series for which NumValues elements are to be returned. If endDate is specified, startDate must also be specified.
The function returns a time series populated with the first NumValues cells from the input time series (ts). The calendar of the output time series is the same as that of the input time series.
An exception is returned if the time series (ts) is null, if NumValues is zero (0) or negative, or if endDate is earlier than startDate.
If startDate and endDate are specified, the time series is trimmed to the date range before the first NumValues cells are returned.
Return the first 10 timestamps and opening prices in the time series for stock ACME.:
SET SERVEROUTPUT ON ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS'; DECLARE dummyval INTEGER; BEGIN SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.FirstN(ts.open, 10), 'FirstN Results') INTO dummyval FROM TSDEV.stockdemo_ts ts WHERE ts.ticker='ACME'; END; /
This example might produce the following output:
FirstN Results : Calendar Data: Calendar Name = BUSINESS-96 Frequency = 4 (day) MinDate = 11/01/1996 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0,1,1,1,1,1,0 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : 11/28/1996 00:00:00 12/25/1996 00:00:00 Series Data: ----------------------------- Date Value 11/01/1996 00:00:00 59 11/04/1996 00:00:00 60 11/05/1996 00:00:00 61 11/06/1996 00:00:00 62 11/07/1996 00:00:00 63 11/08/1996 00:00:00 64 11/11/1996 00:00:00 65 11/12/1996 00:00:00 66 11/13/1996 00:00:00 67 11/14/1996 00:00:00 68 -----------------------------
ORDSYS.TimeSeries.GetDatedElement (
Given a time series and a date, returns the time series element for that date.
The input time series.
Positive integer specifying the date of the element to be returned.
The function returns the cell from the input time series (ts) at the specified date (target_date). If there is no data in ts at target_date, the function returns a null.
An exception is returned if the time series (ts) is null.
Return the timestamp and opening price for 26-Nov-1996 for stock ACME:
SET SERVEROUTPUT ON ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS'; DECLARE dummyval INTEGER; tstDate date; BEGIN -- Get the cell for 26-NOV-1996 from ACME's open and display it tstDate := TO_DATE('11/26/1996'); SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.GetDatedElement(ts.open, tstDate), 'GetDatedElement Results') INTO dummyval FROM TSDEV.stockdemo_ts ts WHERE ts.ticker='ACME'; END; /
This example might produce the following output:
GetDatedElement Results : Timestamp : 11/26/1996 00:00:00 Value : 76
ORDSYS.TimeSeries.GetNthElement
(ts ORDSYS.ORDTNumSeriesIOTRef,
target_index INTEGER
[,startDate DATE, endDate DATE]
) RETURN ORDSYS.ORDTNumCell;
Given a time series, a number (target_index), and optionally a date range, returns the Nth element (element whose position corresponds to target_index) in the specified time series, or within the date range if one is specified.
The input time series.
Positive integer specifying the position of the element to be returned.
Starting date within the time series to which target_index is to be applied. If target_index = 1, the function returns the element for startDate. If startDate is specified, endDate must also be specified.
Ending date within the time series to which target_index is to be applied. If endDate is specified, startDate must also be specified.
An exception is returned if one or more of the following conditions are true:
Return the tenth opening price for stock ACME:
SELECT ORDSYS.TimeSeries.ExtractValue( ORDSYS.TimeSeries.GetNthElement(open, 10)) FROM TSDEV.stockdemo_ts WHERE ticker = 'ACME';
This example might produce the following output:
ORDSYS.TIM ---------- 68 1 row selected.
ORDSYS.TimeSeries.GetSeries(
or
ORDSYS.TimeSeries.GetSeries(
Given a reference to a time series of references (ORDTNumSeriesIOTRef or ORDTVarchar2SeriesIOTRef), returns a time series instance (ORDTNumSeries or ORDTVarchar2Series).
Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.
The input time series.
The function materializes the input time series.
An exception is returned if the time series (ts) is null.
Return an instance of a specified time series (opening prices for stock ACME):
SET SERVEROUTPUT ON ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS'; DECLARE dummyval INTEGER; BEGIN SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.GetSeries(ts.open), 'GetSeries Results') INTO dummyval FROM TSDEV.stockdemo_ts ts WHERE ts.ticker='ACME'; END; /
This example might produce the following output:
GetSeries Results : Name = OPEN ACME Calendar Data: Calendar Name = BUSINESS-96 Frequency = 4 (day) MinDate = 11/01/1996 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0,1,1,1,1,1,0 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : 11/28/1996 00:00:00 12/25/1996 00:00:00 Series Data: ----------------------------- Date Value 11/01/1996 00:00:00 59 11/04/1996 00:00:00 60 11/05/1996 00:00:00 61 11/06/1996 00:00:00 62 11/07/1996 00:00:00 63 11/08/1996 00:00:00 64 11/11/1996 00:00:00 65 11/12/1996 00:00:00 66 11/13/1996 00:00:00 67 11/14/1996 00:00:00 68 11/15/1996 00:00:00 69 11/18/1996 00:00:00 70 11/19/1996 00:00:00 71 11/20/1996 00:00:00 72 11/21/1996 00:00:00 73 11/22/1996 00:00:00 74 11/25/1996 00:00:00 75 11/26/1996 00:00:00 76 11/27/1996 00:00:00 77 11/29/1996 00:00:00 78 12/02/1996 00:00:00 79 12/03/1996 00:00:00 80 12/04/1996 00:00:00 81 12/05/1996 00:00:00 82 12/06/1996 00:00:00 83 12/09/1996 00:00:00 84 12/10/1996 00:00:00 85 12/11/1996 00:00:00 86 12/12/1996 00:00:00 87 12/13/1996 00:00:00 88 12/16/1996 00:00:00 89 12/17/1996 00:00:00 90 12/18/1996 00:00:00 91 12/19/1996 00:00:00 92 12/20/1996 00:00:00 93 12/23/1996 00:00:00 94 12/24/1996 00:00:00 95 12/26/1996 00:00:00 96 12/27/1996 00:00:00 97 12/30/1996 00:00:00 98 12/31/1996 00:00:00 99 -----------------------------
ORDSYS.TimeSeries.IsValidTS(
or
ORDSYS.TimeSeries.IsValidTS(
Returns 1 if the time series is valid and 0 if the time series is invalid.
The input time series.
A time series is invalid if one or more of the following conditions are true:
Contrast this function with ValidateTS, which checks whether a time series is valid, and if the time series is not valid, generates a diagnostic message and tables with timestamps that are causing the time series to be invalid.
Use the IsValidTS and ValidateTS functions and the DisplayValTS procedure with an invalid time series:
SET SERVEROUTPUT ON ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS'; DECLARE numTS ORDSYS.ORDTNumSeries; tempVal integer; retIsValid integer; retValTS integer; loDateTab ORDSYS.ORDTDateTab := NULL; hiDateTab ORDSYS.ORDTDateTab := NULL; impDateTab ORDSYS.ORDTDateTab := NULL; dupDateTab ORDSYS.ORDTDateTab := NULL; extraDateTab ORDSYS.ORDTDateTab := NULL; missingDateTab ORDSYS.ORDTDateTab := NULL; outMesg varchar2(2000); BEGIN -- Set the buffer size DBMS_OUTPUT.ENABLE(100000); -- -- NOTE: Here, an instance of the time series is materialized -- so that it could be modified to generate an invalid time series. -- SELECT ORDSYS.TIMESERIES.GetSeries(ts.open) INTO numTS FROM tsdev.stockdemo_ts ts WHERE ts.ticker = 'ACME'; -- Example of validating a valid time series. SELECT ordsys.timeseries.display(numTS, 'A VALID TIME SERIES') INTO tempVal FROM dual; retIsValid := ORDSYS.TIMESERIES.IsValidTS(numTS); retValTS := ORDSYS.TIMESERIES.ValidateTS(numTS, outMesg, loDateTab, hiDateTab, impDateTab, dupDateTab, extraDateTab, missingDateTab); DBMS_OUTPUT.PUT_LINE('Value returned by IsValid = ' || retIsValid); DBMS_OUTPUT.PUT_LINE('Value returned by ValidateTS = ' || retValTS); ORDSYS.TIMESERIES.DisplayValTS(retValTS, outMesg, loDateTab, hiDateTab, impDateTab, dupDateTab, extraDateTab, missingDateTab, 'Testing DisplayValTS'); DBMS_OUTPUT.NEW_LINE; -- For illustration let us first create an invalid timeseries. -- -- Here we are adjusting the calendar's minDate and maxDate to avoid -- getting a huge list of missing dates. -- numTS.cal.minDate := TO_DATE('10/28/1996'); numTS.cal.maxDate := TO_DATE('01/05/1997'); -- Add Dates Before numTS.cal.minDate numTS.series(10).tstamp := numTS.cal.minDate - 1; numTS.series(11).tstamp := numTS.cal.minDate - 2; -- Add Dates Beyond numTS.cal.maxDate numTS.series(12).tstamp := numTS.cal.maxDate + 1; numTS.series(13).tstamp := numTS.cal.maxDate + 2; -- Add some null timestamps numTS.series(14).tstamp := NULL; numTS.series(15).tstamp := NULL; -- Add some imprecise dates (some are duplicated) numTS.series(17).tstamp := numTS.series(16).tstamp + 1/24; numTS.series(18).tstamp := numTS.series(16).tstamp + 15/24; -- Add some duplicate timestamps numTS.series(19).tstamp := numTS.series(18).tstamp; numTS.series(21).tstamp := numTS.series(20).tstamp; -- Add some extra dates in the middle numTS.series(37).tstamp := TO_DATE('12/28/1996'); numTS.series(36).tstamp := TO_DATE('12/29/1996'); -- Add some holes at the end numTS.series(numTS.series.count).tstamp := TO_DATE('01/04/1997'); -- Example of validating an invalid time series. SELECT ordsys.timeseries.display(numTS, 'AN INVALID TIME SERIES') INTO tempVal FROM dual; retIsValid := ORDSYS.TIMESERIES.IsValidTS(numTS); retValTS := ORDSYS.TIMESERIES.ValidateTS(numTS, outMesg, loDateTab, hiDateTab, impDateTab, dupDateTab, extraDateTab, missingDateTab); DBMS_OUTPUT.PUT_LINE('Value returned by IsValid = ' || retIsValid); DBMS_OUTPUT.PUT_LINE('Value returned by ValidateTS = ' || retValTS); ORDSYS.TIMESERIES.DisplayValTS(retValTS, outMesg, loDateTab, hiDateTab, impDateTab, dupDateTab, extraDateTab, missingDateTab, 'Testing DisplayValTS'); END; /
This example might produce the following output:
A VALID TIME SERIES : Name = OPEN ACME Calendar Data: Calendar Name = BUSINESS-96 Frequency = 4 (day) MinDate = 11/01/1996 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0,1,1,1,1,1,0 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : 11/28/1996 00:00:00 12/25/1996 00:00:00 Series Data: ----------------------------- Date Value 11/01/1996 00:00:00 59 11/04/1996 00:00:00 60 11/05/1996 00:00:00 61 11/06/1996 00:00:00 62 11/07/1996 00:00:00 63 11/08/1996 00:00:00 64 11/11/1996 00:00:00 65 11/12/1996 00:00:00 66 11/13/1996 00:00:00 67 11/14/1996 00:00:00 68 11/15/1996 00:00:00 69 11/18/1996 00:00:00 70 11/19/1996 00:00:00 71 11/20/1996 00:00:00 72 11/21/1996 00:00:00 73 11/22/1996 00:00:00 74 11/25/1996 00:00:00 75 11/26/1996 00:00:00 76 11/27/1996 00:00:00 77 11/29/1996 00:00:00 78 12/02/1996 00:00:00 79 12/03/1996 00:00:00 80 12/04/1996 00:00:00 81 12/05/1996 00:00:00 82 12/06/1996 00:00:00 83 12/09/1996 00:00:00 84 12/10/1996 00:00:00 85 12/11/1996 00:00:00 86 12/12/1996 00:00:00 87 12/13/1996 00:00:00 88 12/16/1996 00:00:00 89 12/17/1996 00:00:00 90 12/18/1996 00:00:00 91 12/19/1996 00:00:00 92 12/20/1996 00:00:00 93 12/23/1996 00:00:00 94 12/24/1996 00:00:00 95 12/26/1996 00:00:00 96 12/27/1996 00:00:00 97 12/30/1996 00:00:00 98 12/31/1996 00:00:00 99 ----------------------------- Value returned by IsValid = 1 Value returned by ValidateTS = 1 DisplayValTS: Testing DisplayValTS: TS-SUC: the input time series is a valid time series AN INVALID TIME SERIES : Name = OPEN ACME Calendar Data: Calendar Name = BUSINESS-96 Frequency = 4 (day) MinDate = 10/28/1996 00:00:00 MaxDate = 01/05/1997 00:00:00 patBits: 0,1,1,1,1,1,0 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : 11/28/1996 00:00:00 12/25/1996 00:00:00 Series Data: ----------------------------- Date Value 11/01/1996 00:00:00 59 11/04/1996 00:00:00 60 11/05/1996 00:00:00 61 11/06/1996 00:00:00 62 11/07/1996 00:00:00 63 11/08/1996 00:00:00 64 11/11/1996 00:00:00 65 11/12/1996 00:00:00 66 11/13/1996 00:00:00 67 10/27/1996 00:00:00 68 10/26/1996 00:00:00 69 01/06/1997 00:00:00 70 01/07/1997 00:00:00 71 72 73 11/22/1996 00:00:00 74 11/22/1996 01:00:00 75 11/22/1996 15:00:00 76 11/22/1996 15:00:00 77 11/29/1996 00:00:00 78 11/29/1996 00:00:00 79 12/03/1996 00:00:00 80 12/04/1996 00:00:00 81 12/05/1996 00:00:00 82 12/06/1996 00:00:00 83 12/09/1996 00:00:00 84 12/10/1996 00:00:00 85 12/11/1996 00:00:00 86 12/12/1996 00:00:00 87 12/13/1996 00:00:00 88 12/16/1996 00:00:00 89 12/17/1996 00:00:00 90 12/18/1996 00:00:00 91 12/19/1996 00:00:00 92 12/20/1996 00:00:00 93 12/29/1996 00:00:00 94 12/28/1996 00:00:00 95 12/26/1996 00:00:00 96 12/27/1996 00:00:00 97 12/30/1996 00:00:00 98 01/04/1997 00:00:00 99 ----------------------------- Value returned by IsValid = 0 Value returned by ValidateTS = 0 DisplayValTS: Testing DisplayValTS: TS-WRN: the input time series has errors. See the message for details message output by validateTS: TS-ERR: the input time series is unsorted TS-ERR: the time series has null timestamps TS-ERR: the time series has timestamps < calendar minDate (refer LoDateTab) TS-ERR: the time series has timestamps > calendar maxDate (refer HiDateTab) TS-ERR: the time series has imprecise timestamps (refer impreciseDateTab) TS-ERR: the time series has duplicate timestamps (refer DuplicateDateTab) list of dates < calendar minDate - lowDateTab : 10/26/1996 00:00:00 10/27/1996 00:00:00 list of dates > calendar maxDate - hiDateTab : 01/06/1997 00:00:00 01/07/1997 00:00:00 list of imprecise dates - impreciseDateTab : 11/22/1996 01:00:00 11/22/1996 15:00:00 list of duplicate dates - duplicateDateTab : 11/22/1996 15:00:00 11/29/1996 00:00:00 ExtraDateTab : 12/28/1996 00:00:00 12/29/1996 00:00:00 01/04/1997 00:00:00 MissingDateTab : 10/28/1996 00:00:00 10/29/1996 00:00:00 10/30/1996 00:00:00 10/31/1996 00:00:00 11/14/1996 00:00:00 11/15/1996 00:00:00 11/18/1996 00:00:00 11/19/1996 00:00:00 11/20/1996 00:00:00 11/21/1996 00:00:00 11/25/1996 00:00:00 11/26/1996 00:00:00 11/27/1996 00:00:00 12/02/1996 00:00:00 12/23/1996 00:00:00 12/24/1996 00:00:00 12/31/1996 00:00:00 01/01/1997 00:00:00 01/02/1997 00:00:00 01/03/1997 00:00:00
ORDSYS.TimeSeries.Lag (
[tsname VARCHAR2,]
ts ORDSYS.ORDTNumSeriesIOTRef,
units INTEGER
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
or
ORDSYS.TimeSeries.Lag (
[tsname VARCHAR2,]
ts ORDSYS.ORDTNumSeriesIOTRef,
lead_date DATE
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given a time series, a positive or negative number (units) or a date (lead_date), and optionally a starting and ending timestamp within the time series, returns a time series that lags or (for negative numeric values) leads the input time series by the appropriate number of timestamps.
Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.
The input time series.
Integer specifying the number of timestamps by which the output time series is to be adjusted. If units is positive, each element in the output time series is the same as the element in the input time series for that relative position minus the units. If units is negative, each element in the output time series is the same as the element in the input time series for that relative position plus the units.
The date relative to the starting date reflecting the number of timestamps by which the output time series is to be adjusted. The function calculates the number of timestamps between lead_date and startDate, and then uses that number as if it were a units parameter value. (If lead_date is later than startDate, the effective units value is positive; if lead_date is before the starting date, the effective units value is negative.)
Starting date to be used in calculating the lead or lag value; also the starting date in the input time series for which the output time series is to be created. If startDate is specified, endDate must also be specified.
Ending date in the input time series for which the output time series is to be created. If endDate is specified, startDate must also be specified.
The function creates a time series whose elements reflect an input time series adjusted by a number of timestamps. For example, using the United States stock trading calendar for 1997, if the first timestamp in the input time series is 06-Jan-1997 (Monday) and the units value is 2, the first timestamp in the output time series is 02-Jan-1997 (Thursday) and its associated value (such as closing price) is the same as that for 06-Jan-1997 in the input time series. Subsequent elements of the output time series reflect the timestamp adjustment.
For example, assuming the United States stock trading calendar for 1997, Table 5-1 shows some time series data with a two-day lag period.
For convenience, both the Lead and Lag functions are provided.The functions operate identically, except that they interpret the sign of the units value in opposite ways. For example, Lead with -10 for units is equivalent to Lag with 10 for units. Moreover, because of the way the lead_date parameter is interpreted, Lead and Lag with a lead_date operate identically.
The Lead and Lag functions do not operate on irregular time series. For an explanation of irregular time series, see Section 2.1.1.
Return a time series starting with 03-Mar-1997 using closing prices from the time series from 01-Nov-1996 through 30-Nov-1996 for stock ACME. The returned time series has the same number of timestamps as are in the specified date range (startDate through endDate).
SELECT to_char(tstamp) tstamp, value FROM stockdemo_ts ts, TABLE (CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.Lag(ts.close, to_date('03-MAR-97','DD-MON-YY'), to_date('01-NOV-96','DD-MON-YY'), to_date('30-NOV-96','DD-MON-YY')) ) AS ORDSYS.ORDTNumTab)) t WHERE ts.ticker='ACME';
This example might produce the following output:
TSTAMP VALUE --------- ---------- 03-MAR-97 59 04-MAR-97 60 05-MAR-97 61 06-MAR-97 62 07-MAR-97 63 10-MAR-97 64 ... ... 27-MAR-97 77 28-MAR-97 78 20 rows selected.
ORDSYS.TimeSeries.Last(
Given a time series, returns the last element in it.
The input time series.
A null is returned if the time series (ts) is empty.
An exception is returned if the time series (ts) is null.
Return the last timestamp and opening price for stock ACME in the stockdemo_ts time series:
SET SERVEROUTPUT ON ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS'; DECLARE dummyval INTEGER; BEGIN SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.Last(ts.open), 'Last Results') INTO dummyval FROM TSDEV.stockdemo_ts ts WHERE ts.ticker='ACME'; END; /
This example might produce the following output:
Last Results : Timestamp : 12/31/1996 00:00:00 Value : 99
ORDSYS.TimeSeries.LastN(
[tsname VARCHAR2,]
ts ORDSYS.ORDTNumSeriesIOTRef,
NumValues NUMBER
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given a time series and a number of elements (NumValues) to return, returns the last NumValues elements in the time series.
Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.
The input time series.
Number of elements from the end of the time series to be returned.
Starting date within the time series for which NumValues elements are to be returned. If startDate is specified, endDate must also be specified.
Ending date within the time series for which NumValues elements are to be returned. If endDate is specified, startDate must also be specified.
The function returns a time series populated with the last NumValues cells from the input time series (ts). The calendar of the output time series is the same as that of the input time series.
An exception is returned if the time series (ts) is null, if NumValues is zero (0) or negative, or if endDate is earlier than startDate.
If startDate and endDate are specified, the time series is trimmed to the date range before the last NumValues cells are returned.
Return the last 10 timestamps and opening prices in the time series for stock ACME.:
SET SERVEROUTPUT ON ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS'; DECLARE dummyval INTEGER; BEGIN SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.LastN(ts.open, 10), 'LastN Results') INTO dummyval FROM TSDEV.stockdemo_ts ts WHERE ts.ticker='ACME'; END; /
This example might produce the following output:
LastN Results : Calendar Data: Calendar Name = BUSINESS-96 Frequency = 4 (day) MinDate = 11/01/1996 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0,1,1,1,1,1,0 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : 11/28/1996 00:00:00 12/25/1996 00:00:00 Series Data: ----------------------------- Date Value 12/17/1996 00:00:00 90 12/18/1996 00:00:00 91 12/19/1996 00:00:00 92 12/20/1996 00:00:00 93 12/23/1996 00:00:00 94 12/24/1996 00:00:00 95 12/26/1996 00:00:00 96 12/27/1996 00:00:00 97 12/30/1996 00:00:00 98 12/31/1996 00:00:00 99 -----------------------------
ORDSYS.TimeSeries.Lead (
[tsname VARCHAR2,]
ts ORDSYS.ORDTNumSeriesIOTRef,
units INTEGER
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
or
ORDSYS.TimeSeries.Lead (
[tsname VARCHAR2,]
ts ORDSYS.ORDTNumSeriesIOTRef,
lead_date DATE
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given a time series, a positive or negative number (units) or a date (lead_date), and optionally a starting and ending timestamp within the time series, returns a time series that leads or (for negative numeric values) lags the input time series by the appropriate number of timestamps.
Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.
The input time series.
Integer specifying the number of timestamps by which the output time series is to be adjusted. If units is positive, each element in the output time series is the same as the element in the input time series for that relative position plus the units. If units is negative, each element in the output time series is the same as the element in the input time series for that relative position minus the units.
The date relative to the starting date reflecting the number of timestamps by which the output time series is to be adjusted. The function calculates the number of timestamps between lead_date and startDate, and then uses that number as if it were a units parameter value. (If lead_date is later than startDate, the effective units value is positive; if lead_date is before startDate, the effective units value is negative.)
Starting date to be used in calculating the lead or lag value; also the starting date in the input time series for which the output time series is to be created. If startDate is specified, endDate must also be specified.
Ending date in the input time series for which the output time series is to be created. If endDate is specified, startDate must also be specified.
The function creates a time series whose elements reflect an input time series adjusted by a number of timestamps. For example, using the United States stock trading calendar for 1997, if the first timestamp in the input time series is 02-Jan-1997 (Thursday) and the units value is 2, the first timestamp in the output time series is 06-Jan-1997 (Monday) and its associated value (such as closing price) is the same as that for 02-Jan-1997 in the input time series. Subsequent elements of the output time series reflect the timestamp adjustment.
For example, assuming the United States stock trading calendar for 1997, Table 5-2 shows some time series data with a two-day lead period.
For convenience, both the Lead and Lag functions are provided. The functions operate identically, except that they interpret the sign of the units value in opposite ways. For example, Lead with -10 for units is equivalent to Lag with 10 for units. Moreover, because of the way the lead_date parameter is interpreted, Lead and Lag with a lead_date operate identically.
The Lead and Lag functions do not operate on irregular time series. For an explanation of irregular time series, see Section 2.1.1.
Return a time series starting with 03-Mar-1997 using closing prices from the time series from 01-Nov-1996 through 30-Nov-1996 for stock ACME. The returned time series has the same number of timestamps as are in the specified date range (startDate through endDate).
SELECT to_char(tstamp) tstamp, value FROM stockdemo_ts ts, TABLE (CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.Lead(ts.close, to_date('03-MAR-97','DD-MON-YY'), to_date('01-NOV-96','DD-MON-YY'), to_date('30-NOV-96','DD-MON-YY')) ) AS ORDSYS.ORDTNumTab)) t WHERE ts.ticker='ACME';
This example might produce the following output:
TSTAMP VALUE --------- ---------- 03-MAR-97 59 04-MAR-97 60 05-MAR-97 61 06-MAR-97 62 07-MAR-97 63 10-MAR-97 64 ... ... 27-MAR-97 77 28-MAR-97 78 20 rows selected.
ORDSYS.TimeSeries.Mavg(
[tsname VARCHAR2,]
ts ORDSYS.ORDTNumSeriesIOTRef,
k INTEGER
[,startDate DATE, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given an input ORDTNumSeries, returns a moving average for the time series, or for the date range if one is specified. Each value in the returned time series is the average of the value for the current timestamp plus the value for each of the previous specified number of timestamps minus one.
For example, a 30-day moving average of closing prices for a stock on any given date is the average of that day's closing price and the 29 preceding closing prices.
Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.
The input time series.
Positive integer specifying the lookback window (number of timestamps, including the current one, over which to compute the moving average).
Starting date within the time series for which to return moving averages. If startDate is specified, endDate must also be specified.
Ending date within the time series for which to return moving averages. If endDate is specified, startDate must also be specified.
The returned time series has nulls for any entry where there are not k-1 timestamps preceding it in the calendar. For example, if a stock trading calendar for 1997 starts on 02-Jan-1997, the series of 5-day moving averages of the closing price for a stock for the year has nulls for the closing price for the first four timestamps (02-Jan, 03-Jan, 06-Jan, and 07-Jan), because there are insufficient timestamps for computing the average.
Any nulls in the entries for the k timestamps are ignored, as explained in Section 2.4.1.
An exception is returned if one or more of the following conditions are true:
Return a table of 10-day moving average values of the closing price for stock ACME for the month of December 1996:
SELECT to_char(tstamp) tstamp, value FROM tsquick ts, TABLE (CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.Mavg(ts.close,to_date('01-DEC-96','DD-MON-YY'), to_date('31-DEC-96','DD-MON-YY'),10) ) AS ORDSYS.ORDTNumTab)) t WHERE ts.ticker='ACME';
This example might produce the following output:
TSTAMP VALUE --------- ---------- 02-DEC-96 74.5 03-DEC-96 75.5 04-DEC-96 76.5 05-DEC-96 77.5 06-DEC-96 78.5 09-DEC-96 79.5 10-DEC-96 80.5 11-DEC-96 81.5 12-DEC-96 82.5 13-DEC-96 83.5 16-DEC-96 84.5 17-DEC-96 85.5 18-DEC-96 86.5 19-DEC-96 87.5 20-DEC-96 88.5 23-DEC-96 89.5 24-DEC-96 90.5 26-DEC-96 91.5 27-DEC-96 92.5 30-DEC-96 93.5 31-DEC-96 94.5 21 rows selected.
ORDSYS.TimeSeries.Msum(
[tsname VARCHAR2,]
ts ORDSYS.ORDTNumSeriesIOTRef,
k INTEGER
[,startDate DATE, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given an input ORDTNumSeries, returns a moving sum for the time series, or for the date range if one is specified. Each value in the returned time series is the sum of the value for the current timestamp plus the value for each of the previous specified number of timestamps minus one.
For example, a 30-day moving sum for a stock's daily trading volume on any given date is the sum of that day's volume and the 29 preceding daily volumes.
Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.
The input time series.
Positive integer specifying the lookback window (number of timestamps, including the current one, over which to compute the moving sum).
Starting date within the time series for which to return moving sums. If startDate is specified, endDate must also be specified.
Ending date within the time series for which to return moving sums. If endDate is specified, startDate must also be specified.
The returned time series has nulls for any entry where there are not k-1 timestamps preceding it in the calendar. For example, if a stock trading calendar for 1997 starts on 02-Jan-1997, the series of 5-day moving sums of the trading volume for a stock for the year has nulls for the volume for the first four timestamps (02-Jan, 03-Jan, 06-Jan, and 07-Jan), because there are insufficient timestamps for computing the sum.
Any nulls in the entries for the k timestamps are ignored, as explained in Section 2.4.1.
An exception is returned if one or more of the following conditions are true:
Return a table of 30-day moving sum values of trading volume for stock ACME for December 1996:
SELECT to_char(tstamp) tstamp, value FROM tsquick ts, TABLE (CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.Msum(ts.volume,to_date('01-DEC-96','DD-MON-YY'), to_date('31-DEC-96','DD-MON-YY'),30) ) AS ORDSYS.ORDTNumTab)) t WHERE ts.ticker='ACME';
With the simplified data in the demo database (where all ACME daily volumes are 1000 and there are no ACME timestamps before November 1996), this example might produce the following output:
TSTAMP VALUE --------- ---------- 02-DEC-96 03-DEC-96 04-DEC-96 05-DEC-96 06-DEC-96 09-DEC-96 10-DEC-96 11-DEC-96 12-DEC-96 13-DEC-96 30000 16-DEC-96 30000 17-DEC-96 30000 18-DEC-96 30000 19-DEC-96 30000 20-DEC-96 30000 23-DEC-96 30000 24-DEC-96 30000 26-DEC-96 30000 27-DEC-96 30000 30-DEC-96 30000 31-DEC-96 30000 21 rows selected.
ORDSYS.TimeSeries.TrimSeries(
[tsname VARCHAR2,]
ts ORDSYS.ORDTNumSeriesIOTRef,
startDate DATE,
endDate DATE
) RETURN ORDSYS.ORDTNumSeries;
or
ORDSYS.TimeSeries.TrimSeries(
[tsname VARCHAR2,]
ts ORDSYS.ORDTVarchar2SeriesIOTRef,
startDate DATE,
endDate DATE
) RETURN ORDSYS.ORDTVarchar2Series;
Given an input ORDT series, returns an ORDT series of the same type with all data outside of the given date range removed. The calendar of the returned series will be the same as that of the original series.
Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.
The input time series.
Starting date within the time series. You must specify a value, either null or not null. If you specify a null value, the starting date (minDate) of the calendar (if any) associated with ts is used.
Ending date within the time series. You must specify a value, either null or not null. If you specify a null value, the ending date (maxDate) of the calendar (if any) associated with ts is used.
An exception is returned if one or more of the following conditions are true:
Return the opening prices for stock ACME for dates in the calendar from 01-Dec-1996 through 31-Dec-1996:
SET SERVEROUTPUT ON DECLARE tmp INTEGER; tstDate1 DATE; tstDate2 DATE; BEGIN -- Set tstDate values tstDate1 := TO_DATE('12/01/1996 00:00:00','MM/DD/YYYY HH24:MI:SS'); tstDate2 := TO_DATE('12/31/1996 00:00:00','MM/DD/YYYY HH24:MI:SS'); SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.TrimSeries(open, tstDate1, tstDate2)) INTO tmp FROM TSDEV.stockdemo_ts WHERE ticker = 'ACME'; END; /
This statement might produce the following output:
Calendar Data: Calendar Name = BUSINESS-96 Frequency = 4 (day) MinDate = 01-JAN-90 00:00:00 MaxDate = 01-JAN-01 00:00:00 patBits: 0,1,1,1,1,1,0 patAnchor = 07-JAN-96 00:00:00 onExceptions : offExceptions : 28-NOV-96 00:00:00 25-DEC-96 00:00:00 Series Data: ----------------------------- Date Value 02-DEC-96 00:00:00 79 03-DEC-96 00:00:00 80 04-DEC-96 00:00:00 81 05-DEC-96 00:00:00 82 06-DEC-96 00:00:00 83 09-DEC-96 00:00:00 84 10-DEC-96 00:00:00 85 11-DEC-96 00:00:00 86 12-DEC-96 00:00:00 87 13-DEC-96 00:00:00 88 16-DEC-96 00:00:00 89 17-DEC-96 00:00:00 90 18-DEC-96 00:00:00 91 19-DEC-96 00:00:00 92 20-DEC-96 00:00:00 93 23-DEC-96 00:00:00 94 24-DEC-96 00:00:00 95 26-DEC-96 00:00:00 96 27-DEC-96 00:00:00 97 30-DEC-96 00:00:00 98 31-DEC-96 00:00:00 99 -----------------------------
ORDSYS.TimeSeries.TSAdd (
[tsname VARCHAR2,]
ts1 ORDSYS.ORDTNumSeriesIOTRef,
ts2 ORDSYS.ORDTNumSeriesIOTRef
[,startDate DATE, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
or
ORDSYS.TimeSeries.TSAdd (
[tsname VARCHAR2,]
ts1 ORDSYS.ORDTNumSeriesIOTRef,
k NUMBER
[,startDate DATE, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given two input time series or a time series and a constant, and optionally starting and ending dates, returns a time series that reflects the addition of the first two parameters.
Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.
The time series (or first time series) whose elements are to be added either to corresponding elements in the second time series or to a constant.
The time series whose elements are to be added to corresponding elements in the first time series.
A constant to be added to corresponding elements in the first time series.
Starting date within the time series for which the addition is to be performed. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the addition is to be performed. If endDate is specified, startDate must also be specified.
The function performs a pairwise addition operation on each element of the time series. This operation determines the value of each element of the returned time series. For example:
If ts1 and ts2 are specified, the function returns a time series whose calendar is the result of using the CombineCals function on the calendars associated with these two time series.
An exception is returned if one or more of the following conditions are true:
Add the high price for stock ACME and the low price for stock FUNCO for each trading day from 14-Nov-1996 through 14-Dec-1996:
CONNECT TSUSER/TSUSER SET SERVEROUTPUT ON ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS'; DECLARE tstCal ORDSYS.ORDTCalendar; startDate date; endDate date; dummyval INTEGER; BEGIN startDate := TO_DATE('11/14/1996'); endDate := TO_DATE('12/14/1996'); SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.TSAdd(ts1.high, ts2.low, startDate, endDate), 'TSAdd Results') INTO dummyval FROM TSDEV.stockdemo_ts ts1, TSDEV.stockdemo_ts ts2 WHERE ts1.ticker='ACME' and ts2.ticker='FUNCO'; END; /
This example might produce the following output:
TSAdd Results : Calendar Data: Frequency = 4 (day) MinDate = 11/01/1996 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0,1,1,1,1,1,0 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : 11/28/1996 00:00:00 12/25/1996 00:00:00 Series Data: ----------------------------- Date Value 11/14/1996 00:00:00 92.87 11/15/1996 00:00:00 93.84 11/18/1996 00:00:00 94.87 11/19/1996 00:00:00 95.85 11/20/1996 00:00:00 96.82 11/21/1996 00:00:00 97.84 11/22/1996 00:00:00 98.85 11/25/1996 00:00:00 99.81 11/26/1996 00:00:00 100.78 11/27/1996 00:00:00 101.71 11/29/1996 00:00:00 102.75 12/02/1996 00:00:00 103.88 12/03/1996 00:00:00 105.03 12/04/1996 00:00:00 106.02 12/05/1996 00:00:00 107.13 12/06/1996 00:00:00 107.75 12/09/1996 00:00:00 108.77 12/10/1996 00:00:00 109.8 12/11/1996 00:00:00 110.5 12/12/1996 00:00:00 111.41 12/13/1996 00:00:00 112.4 -----------------------------
ORDSYS.TimeSeries.TSAvg (
Given an input ORDTNumSeries and optionally starting and ending dates, returns a number corresponding to the average of all non-null time series entries.
The input time series.
Starting date within the time series for which the average is to be calculated. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the average is to be calculated. If endDate is specified, startDate must also be specified.
An exception is returned if one or more of the following conditions are true:
Return the average, variance, and standard deviation of the closing price of stock ACME:
-- -- Compute various aggregate statistics. -- SELECT ORDSYS.TimeSeries.TSAvg(close), ORDSYS.TimeSeries.TSVariance(close), ORDSYS.TimeSeries.TSStdDev(close) FROM TSDEV.stockdemo_ts WHERE ticker='ACME';
This example might produce the following output:
ORDSYS.TIM ORDSYS.TIM ORDSYS.TIM ---------- ---------- ---------- 79 143.5 11.9791486 1 row selected.
ORDSYS.TimeSeries.TSCount (
Given an input ORDTNumSeries and optionally starting and ending dates, returns a number corresponding to the count of all non-null time series entries.
The input time series.
Starting date within the time series for which the count is to be calculated. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the count is to be calculated. If endDate is specified, startDate must also be specified.
Nulls are ignored in computing the count.
An exception is returned if one or more of the following conditions are true:
Return the total number of daily closing prices for stock ACME for the month of November 1996:
SELECT ORDSYS.TimeSeries.TSCount(close, to_date('11/01/1996 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('11/30/1996 23:59:59', 'MM/DD/YYYY HH24:MI:SS')) TSCount FROM TSDEV.stockdemo_ts WHERE ticker='ACME';
This example might produce the following output:
TSCOUNT ---------- 20 1 row selected.
ORDSYS.TimeSeries.TSDivide (
[tsname VARCHAR2,]
ts1 ORDSYS.ORDTNumSeriesIOTRef,
ts2 ORDSYS.ORDTNumSeriesIOTRef
[,startDate DATE, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
or
ORDSYS.TimeSeries.TSDivide (
[tsname VARCHAR2,]
ts1 ORDSYS.ORDTNumSeriesIOTRef,
k NUMBER
[,startDate DATE, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given two input time series or a time series and a constant, and optionally starting and ending dates, returns a time series that reflects the division of the first parameter by the second parameter.
Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.
The time series (or first time series) whose elements are to be divided by either the corresponding elements in the second time series or a constant.
The time series whose elements are to be divided into corresponding elements in the first time series.
A constant to be divided into corresponding elements in the first time series.
Starting date within the time series for which the division is to be performed. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the division is to be performed. If endDate is specified, startDate must also be specified.
The function performs a pairwise division operation on each element of the time series (or first time series) by the corresponding element in the second time series or by a constant. This operation determines the value of each element of the returned time series. For example:
If ts1 and ts2 are specified, the function returns a time series whose calendar is the result of using the CombineCals function on the calendars associated with these two time series.
An exception is returned if one or more of the following conditions are true:
Divide the high price for stock ACME by the low price for stock FUNCO for each trading day from 14-Nov-1996 through 14-Dec-1996:
CONNECT TSUSER/TSUSER SET SERVEROUTPUT ON ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS'; DECLARE tstCal ORDSYS.ORDTCalendar; startDate date; endDate date; dummyval INTEGER; BEGIN startDate := TO_DATE('11/14/1996'); endDate := TO_DATE('12/14/1996'); SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.TSDivide(ts1.high, ts2.low, startDate, endDate), 'TSDivide Results') INTO dummyval FROM TSDEV.stockdemo_ts ts1, TSDEV.stockdemo_ts ts2 WHERE ts1.ticker='ACME' and ts2.ticker='FUNCO'; END; /
This example might produce the following output:
TSDivide Results : Calendar Data: Frequency = 4 (day) MinDate = 11/01/1996 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0,1,1,1,1,1,0 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : 11/28/1996 00:00:00 12/25/1996 00:00:00 Series Data: ----------------------------- Date Value 11/14/1996 00:00:00 2.89065772936740678676162547130289065773 11/15/1996 00:00:00 2.93624161073825503355704697986577181208 11/18/1996 00:00:00 2.97444490992878089652283200670297444491 11/19/1996 00:00:00 3.01886792452830188679245283018867924528 11/20/1996 00:00:00 3.0646515533165407220822837951301427372 11/21/1996 00:00:00 3.10402684563758389261744966442953020134 11/22/1996 00:00:00 3.1446540880503144654088050314465408805 11/25/1996 00:00:00 3.19193616127677446451070978580428391432 11/26/1996 00:00:00 3.23801513877207737594617325483599663583 11/27/1996 00:00:00 3.28975115984816533108393083087304934627 11/29/1996 00:00:00 3.32631578947368421052631578947368421053 12/02/1996 00:00:00 3.35008375209380234505862646566164154104 12/03/1996 00:00:00 3.37078651685393258426966292134831460674 12/04/1996 00:00:00 3.41382181515403830141548709408825978351 12/05/1996 00:00:00 3.43970161624533775383340240364691255698 12/06/1996 00:00:00 3.53684210526315789473684210526315789474 12/09/1996 00:00:00 3.57593605384938998737904922170803533866 12/10/1996 00:00:00 3.61344537815126050420168067226890756303 12/11/1996 00:00:00 3.70212765957446808510638297872340425532 12/12/1996 00:00:00 3.75907731738573259290901324220418624519 12/13/1996 00:00:00 3.8034188034188034188034188034188034188 -----------------------------
ORDSYS.TimeSeries.TSMax (
Given an input ORDTNumSeries and optionally starting and ending dates, returns a number corresponding to the highest (maximum) of all non-null time series entries.
The input time series.
Starting date within the time series for which the maximum is to be calculated. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the maximum is to be calculated. If endDate is specified, startDate must also be specified.
An exception is returned if one or more of the following conditions are true:
Return the highest closing price for stock ACME for the month of November 1996:
SELECT ORDSYS.TimeSeries.TSMax(close, to_date('11/01/1996 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('11/30/1996 23:59:59', 'MM/DD/YYYY HH24:MI:SS')) TSMax FROM TSDEV.stockdemo_ts WHERE ticker='ACME';
This example might produce the following output:
TSMAX ---------- 78 1 row selected.
ORDSYS.TimeSeries.TSMaxN (
ts ORDSYS.ORDTNumSeriesIOTRef,
NumValues INTEGER,
[,startDate DATE, endDate DATE]
) RETURN ORDSYS.ORDTNumTab;
Given an input ORDTNumSeries, a number of values to return, and optionally starting and ending dates, returns an ORDTNumTab with the specified number (NumValues) of the top (highest) values.
The input time series.
Number of values to return.
Starting date within the time series for which the top values are to be calculated. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the top values are to be calculated. If endDate is specified, startDate must also be specified.
An exception is returned if one or more of the following conditions are true:
Return the 10 highest closing prices for stock ACME for the month of November 1996:
SELECT * FROM THE( SELECT CAST( ORDSYS.TimeSeries.TSMaxN(close, 10, to_date('11011996','MMDDYYYY'), to_date('11301996','MMDDYYYY')) as ORDSYS.ORDTNumTab) FROM TSDEV.stockdemo_ts WHERE ticker ='ACME');
This example might produce the following output:
TSTAMP VALUE --------- ---------- 29-NOV-96 78 27-NOV-96 77 26-NOV-96 76 25-NOV-96 75 22-NOV-96 74 21-NOV-96 73 20-NOV-96 72 19-NOV-96 71 18-NOV-96 70 15-NOV-96 69 10 rows selected.
ORDSYS.TimeSeries.TSMedian (
Given an input ORDTNumSeries and optionally starting and ending dates, returns a number corresponding to the median of all non-null time series entries.
The input time series.
Starting date within the time series for which the median is to be calculated. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the median is to be calculated. If endDate is specified, startDate must also be specified.
An exception is returned if one or more of the following conditions are true:
Return the median closing price for stock ACME for the month of November 1996:
SELECT ORDSYS.TimeSeries.TSMedian(close, to_date('11/01/1996 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('11/30/1996 23:59:59', 'MM/DD/YYYY HH24:MI:SS')) TSMedian FROM TSDEV.stockdemo_ts WHERE ticker='ACME';
This example might produce the following output:
TSMEDIAN ---------- 68.5 1 row selected.
ORDSYS.TimeSeries.TSMin (
Given an input ORDTNumSeries and optionally starting and ending dates, returns a number corresponding to the lowest (minimum) of all non-null time series entries.
The input time series.
Starting date within the time series for which the minimum is to be calculated. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the minimum is to be calculated. If endDate is specified, startDate must also be specified.
An exception is returned if one or more of the following conditions are true:
Return the lowest closing price for stock ACME for the month of November 1996:
SELECT ORDSYS.TimeSeries.TSMin(close, to_date('11/01/1996 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('11/30/1996 23:59:59', 'MM/DD/YYYY HH24:MI:SS')) TSMin FROM TSDEV.stockdemo_ts WHERE ticker='ACME';
This example might produce the following output:
TSMIN ---------- 59 1 row selected.
ORDSYS.TimeSeries.TSMinN (
ts ORDSYS.ORDTNumSeriesIOTRef,
NumValues INTEGER,
[,startDate DATE, endDate DATE]
) RETURN ORDSYS.ORDTNumTab;
Given an input ORDTNumSeries, a number of values to return, and optionally starting and ending dates, returns an ORDTNumTab with the specified number (NumValues) of the bottom (lowest) values.
The input time series.
Number of values to return.
Starting date within the time series for which the bottom values are to be calculated. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the bottom values are to be calculated. If endDate is specified, startDate must also be specified.
An exception is returned if one or more of the following conditions are true:
Return the 10 lowest closing prices for stock ACME for the month of November 1996:
SELECT * FROM THE( SELECT CAST( ORDSYS.TimeSeries.TSMinN(close, 10, to_date('11011996','MMDDYYYY'), to_date('11301996','MMDDYYYY')) as ORDSYS.ORDTNumTab) FROM TSDEV.stockdemo_ts WHERE ticker ='ACME');
This example might produce the following output:
TSTAMP VALUE --------- ---------- 01-NOV-96 59 04-NOV-96 60 05-NOV-96 61 06-NOV-96 62 07-NOV-96 63 08-NOV-96 64 11-NOV-96 65 12-NOV-96 66 13-NOV-96 67 14-NOV-96 68 10 rows selected.
ORDSYS.TimeSeries.TSMultiply (
[tsname VARCHAR2,]
ts1 ORDSYS.ORDTNumSeriesIOTRef,
ts2 ORDSYS.ORDTNumSeriesIOTRef
[,startDate DATE, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
or
ORDSYS.TimeSeries.TSMultiply (
[tsname VARCHAR2,]
ts1 ORDSYS.ORDTNumSeriesIOTRef,
k NUMBER
[,startDate DATE, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given two input time series or a time series and a constant, and optionally starting and ending dates, returns a time series that reflects the multiplication of the first parameter by the second parameter.
Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.
The time series (or first time series) whose elements are to be multiplied by either the corresponding elements in the second time series or a constant.
The time series whose elements are to be multiplied by corresponding elements in the first time series.
A constant to be multiplied by corresponding elements in the first time series.
Starting date within the time series for which the multiplication is to be performed. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the multiplication is to be performed. If endDate is specified, startDate must also be specified.
The function performs a pairwise multiplication operation on each element of the time series (or first time series) by the corresponding element in the second time series or by a constant. This operation determines the value of each element of the returned time series. For example:
If ts1 and ts2 are specified, the function returns a time series whose calendar is the result of using the CombineCals function on the calendars associated with these two time series.
An exception is returned if one or more of the following conditions are true:
Multiply the high price for stock ACME by the low price for stock FUNCO for each trading day from 14-Nov-1996 through 14-Dec-1996:
CONNECT TSUSER/TSUSER SET SERVEROUTPUT ON ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS'; DECLARE tstCal ORDSYS.ORDTCalendar; startDate date; endDate date; dummyval INTEGER; BEGIN startDate := TO_DATE('11/14/1996'); endDate := TO_DATE('12/14/1996'); SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.TSMultiply(ts1.high, ts2.low, startDate, endDate), 'TSMultiply Results') INTO dummyval FROM TSDEV.stockdemo_ts ts1, TSDEV.stockdemo_ts ts2 WHERE ts1.ticker='ACME' and ts2.ticker='FUNCO'; END; /
This example might produce the following output:
TSMultiply Results : Calendar Data: Frequency = 4 (day) MinDate = 01/01/1990 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0,1,1,1,1,1,0 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : 11/28/1996 00:00:00 12/25/1996 00:00:00 Series Data: ----------------------------- Date Value 11/14/1996 00:00:00 1647.03 11/15/1996 00:00:00 1668.8 11/18/1996 00:00:00 1694.77 11/19/1996 00:00:00 1717.2 11/20/1996 00:00:00 1738.86 11/21/1996 00:00:00 1764.16 11/22/1996 00:00:00 1788.75 11/25/1996 00:00:00 1809.56 11/26/1996 00:00:00 1831.06 11/27/1996 00:00:00 1849.38 11/29/1996 00:00:00 1876.25 12/02/1996 00:00:00 1910.4 12/03/1996 00:00:00 1946.43 12/04/1996 00:00:00 1969.64 12/05/1996 00:00:00 2002.79 12/06/1996 00:00:00 1995 12/09/1996 00:00:00 2020.45 12/10/1996 00:00:00 2046.8 12/11/1996 00:00:00 2044.5 12/12/1996 00:00:00 2060.08 12/13/1996 00:00:00 2082.6 -----------------------------
ORDSYS.TimeSeries.TSProd (
Given an input ORDTNumSeries and optionally starting and ending dates, returns a number corresponding to the product (result of multiplication) of all non-null time series entries.
The input time series.
Starting date within the time series for which the product is to be calculated. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the product is to be calculated. If endDate is specified, startDate must also be specified.
An exception is returned if one or more of the following conditions are true:
Return the product resulting from multiplying the daily closing prices for stock ACME for the month of November 1996. (This example is not very plausible, but is presented merely to illustrate the syntax.)
SELECT ORDSYS.TimeSeries.TSProd(close, to_date('11/01/1996 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('11/30/1996 23:59:59', 'MM/DD/YYYY HH24:MI:SS')) TSProd FROM TSDEV.stockdemo_ts WHERE ticker='ACME';
This example might produce the following output:
TSPROD ---------- 4.8177E+36 1 row selected.
ORDSYS.TimeSeries.TSStdDev (
Given an input ORDTNumSeries and optionally starting and ending dates, returns a number corresponding to the standard deviation of all non-null time series entries. (This function returns a value that is the square root of the value returned by the TSVar function.)
The input time series.
Starting date within the time series for which the standard deviation is to be calculated. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the standard deviation is to be calculated. If endDate is specified, startDate must also be specified.
If the date range refers to a time series with fewer than two timestamps, a null is returned.
An exception is returned if one or more of the following conditions are true:
Return the average, variance, and standard deviation of the closing price of stock ACME:
-- -- Compute various aggregate statistics. -- SELECT ORDSYS.TimeSeries.TSAvg(close), ORDSYS.TimeSeries.TSVariance(close), ORDSYS.TimeSeries.TSStdDev(close) FROM TSDEV.stockdemo_ts WHERE ticker='ACME';
This example might produce the following output:
ORDSYS.TIM ORDSYS.TIM ORDSYS.TIM ---------- ---------- ---------- 79 143.5 11.9791486 1 row selected.
ORDSYS.TimeSeries.TSSubtract (
[tsname VARCHAR2,]
ts1 ORDSYS.ORDTNumSeriesIOTRef,
ts2 ORDSYS.ORDTNumSeriesIOTRef
[,startDate DATE, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
or
ORDSYS.TimeSeries.TSSubtract (
[tsname VARCHAR2,]
ts1 ORDSYS.ORDTNumSeriesIOTRef,
k NUMBER
[,startDate DATE, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given two input time series or a time series and a constant, and optionally starting and ending dates, returns a time series that reflects the subtraction of the second parameter from the first parameter.
Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.
The time series (or first time series) whose elements are to be decreased either by corresponding elements in the second time series or by a constant.
The time series whose elements are to be subtracted from corresponding elements in the first time series.
A constant to be subtracted from corresponding elements in the first time series.
Starting date within the time series for which the subtraction is to be performed. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the subtraction is to be performed. If endDate is specified, startDate must also be specified.
The function performs a pairwise subtraction operation on each element of ts1, decreasing it by either the corresponding element in ts2 or by k. This operation determines the value of each element of the returned time series. For example:
If ts1 and ts2 are specified, the function returns a time series whose calendar is the result of using the CombineCals function on the calendars associated with these two time series.
An exception is returned if one or more of the following conditions are true:
Subtract the low price for stock FUNCO from the high price for stock ACME for each trading day from 14-Nov-1996 through 14-Dec-1996:
CONNECT TSUSER/TSUSER SET SERVEROUTPUT ON ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS'; DECLARE tstCal ORDSYS.ORDTCalendar; startDate date; endDate date; dummyval INTEGER; BEGIN startDate := TO_DATE('11/14/1996'); endDate := TO_DATE('12/14/1996'); SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.TSSubtract(ts1.high, ts2.low, startDate, endDate), 'TSSubtract Results') INTO dummyval FROM TSDEV.stockdemo_ts ts1, TSDEV.stockdemo_ts ts2 WHERE ts1.ticker='ACME' and ts2.ticker='FUNCO'; END; /
This example might produce the following output:
TSSubtract Results : Calendar Data: Frequency = 4 (day) MinDate = 01/01/1990 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0,1,1,1,1,1,0 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : 11/28/1996 00:00:00 12/25/1996 00:00:00 Series Data: ----------------------------- Date Value 11/14/1996 00:00:00 45.13 11/15/1996 00:00:00 46.16 11/18/1996 00:00:00 47.13 11/19/1996 00:00:00 48.15 11/20/1996 00:00:00 49.18 11/21/1996 00:00:00 50.16 11/22/1996 00:00:00 51.15 11/25/1996 00:00:00 52.19 11/26/1996 00:00:00 53.22 11/27/1996 00:00:00 54.29 11/29/1996 00:00:00 55.25 12/02/1996 00:00:00 56.12 12/03/1996 00:00:00 56.97 12/04/1996 00:00:00 57.98 12/05/1996 00:00:00 58.87 12/06/1996 00:00:00 60.25 12/09/1996 00:00:00 61.23 12/10/1996 00:00:00 62.2 12/11/1996 00:00:00 63.5 12/12/1996 00:00:00 64.59 12/13/1996 00:00:00 65.6 -----------------------------
ORDSYS.TimeSeries.TSSum (
Given an input ORDTNumSeries and optionally starting and ending dates, returns a number corresponding to the sum of all non-null time series entries.
The input time series.
Starting date within the time series for which the sum is to be calculated. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the sum is to be calculated. If endDate is specified, startDate must also be specified.
An exception is returned if one or more of the following conditions are true:
Return the sum of the daily trading volumes for stock ACME for the month of November 1996 (that is, the total ACME volume for the month):
SELECT ORDSYS.TimeSeries.TSSum(volume, to_date('11/01/1996 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('11/30/1996 23:59:59', 'MM/DD/YYYY HH24:MI:SS')) TSSum FROM TSDEV.stockdemo_ts WHERE ticker='ACME';
This example might produce the following output:
TSSUM ---------- 20000 1 row selected.
ORDSYS.TimeSeries.TSVariance (
Given an input ORDTNumSeries and optionally starting and ending dates, returns a number corresponding to the variance of all non-null time series entries. (This function is analogous to the SQL group function VAR.)
The input time series.
Starting date within the time series for which the variance is to be calculated. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the variance is to be calculated. If endDate is specified, startDate must also be specified.
If the date range refers to a time series with fewer than two timestamps, a null is returned.
An exception is returned if one or more of the following conditions are true:
Return the average, variance, and standard deviation of the closing price of stock ACME:
-- -- Compute various aggregate statistics. -- SELECT ORDSYS.TimeSeries.TSAvg(close), ORDSYS.TimeSeries.TSVariance(close), ORDSYS.TimeSeries.TSStdDev(close) FROM TSDEV.stockdemo_ts WHERE ticker='ACME';
This example might produce the following output:
ORDSYS.TIM ORDSYS.TIM ORDSYS.TIM ---------- ---------- ---------- 79 143.5 11.9791486 1 row selected.
ORDSYS.TimeSeries.ValidateTS(
ts IN ORDSYS.ORDTNumSeriesIOTRef,
outMesg OUT VARCHAR2,
loDateTab OUT ORDSYS.ORDTDateTab,
hiDateTab OUT ORDSYS.ORDTDateTab,
impreciseDateTab OUT ORDSYS.ORDTDateTab,
duplicateDateTab OUT ORDSYS.ORDTDateTab,
extraDateTab OUT ORDSYS.ORDTDateTab,
missingDateTab OUT ORDSYS.ORDTDateTab
) RETURN INTEGER;
or
ORDSYS.TimeSeries.ValidateTS(
ts IN ORDSYS.ORDTVarchar2SeriesIOTRef,
outMesg OUT VARCHAR2,
loDateTab OUT ORDSYS.ORDTDateTab,
hiDateTab OUT ORDSYS.ORDTDateTab,
impreciseDateTab OUT ORDSYS.ORDTDateTab,
duplicateDateTab OUT ORDSYS.ORDTDateTab,
extraDateTab OUT ORDSYS.ORDTDateTab,
missingDateTab OUT ORDSYS.ORDTDateTab
) RETURN INTEGER;
Checks whether a time series is valid, and if the time series is not valid, generates a diagnostic message and tables with timestamps that are causing the time series to be invalid.
The time series to be checked for validity.
If the time series is invalid (if the return value = 0), contains a diagnostic message describing any problems.
A table of dates before the starting date of the calendar associated with the time series.
A table of dates after the ending date of the calendar associated with the calendar.
A table of the imprecise timestamps found in the time series.
A table of the duplicate timestamps found in the time series.
A table of dates that are included in the time series but that should be excluded based on the calendar definition (for example, a Saturday timestamp that is in a Monday-Friday calendar and that is not an on-exception).
A table of dates that are excluded from the time series but that should be included based on the calendar definition (for example, a Wednesday date that is not a holiday in a Monday-Friday calendar and for which there is no data). Such dates can be considered as "holes" in the time series.
The function returns one of the following values:
| Value | Meaning |
|---|---|
|
1 |
The time series is valid. No errors were found. |
|
0 |
The time series in invalid. |
A time series is invalid if one or more of the following conditions are true:
Contrast this function with IsValidTS, which simply checks to determine if a time series is valid.
You can use the DisplayValTS procedure (documented in this chapter) to display the information returned by the ValidateTS function.
The ValidateTS function cannot be called from SQL. It must be called from PL/SQL because of the OUT parameters.
Use the IsValidTS and ValidateTS functions and the DisplayValTS procedure with an invalid time series:
SET SERVEROUTPUT ON ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS'; DECLARE numTS ORDSYS.ORDTNumSeries; tempVal integer; retIsValid integer; retValTS integer; loDateTab ORDSYS.ORDTDateTab := NULL; hiDateTab ORDSYS.ORDTDateTab := NULL; impDateTab ORDSYS.ORDTDateTab := NULL; dupDateTab ORDSYS.ORDTDateTab := NULL; extraDateTab ORDSYS.ORDTDateTab := NULL; missingDateTab ORDSYS.ORDTDateTab := NULL; outMesg varchar2(2000); BEGIN -- Set the buffer size. DBMS_OUTPUT.ENABLE(100000); -- -- NOTE: Here, an instance of the time series is materialized -- so that it could be modified to generate an invalid time series. -- SELECT ORDSYS.TIMESERIES.GetSeries(ts.open) INTO numTS FROM tsdev.stockdemo_ts ts WHERE ts.ticker = 'ACME'; -- Example of validating a valid time series. SELECT ordsys.timeseries.display(numTS, 'A VALID TIME SERIES') INTO tempVal FROM dual; retIsValid := ORDSYS.TIMESERIES.IsValidTS(numTS); retValTS := ORDSYS.TIMESERIES.ValidateTS(numTS, outMesg, loDateTab, hiDateTab, impDateTab, dupDateTab, extraDateTab, missingDateTab); DBMS_OUTPUT.PUT_LINE('Value returned by IsValid = ' || retIsValid); DBMS_OUTPUT.PUT_LINE('Value returned by ValidateTS = ' || retValTS); ORDSYS.TIMESERIES.DisplayValTS(retValTS, outMesg, loDateTab, hiDateTab, impDateTab, dupDateTab, extraDateTab, missingDateTab, 'Testing DisplayValTS'); DBMS_OUTPUT.NEW_LINE; -- For illustration let us first create an invalid timeseries. -- -- Here we are adjusting the calendar's minDate and maxDate to avoid -- getting a huge list of missing dates. -- numTS.cal.minDate := TO_DATE('10/28/1996'); numTS.cal.maxDate := TO_DATE('01/05/1997'); -- Add Dates Before numTS.cal.minDate numTS.series(10).tstamp := numTS.cal.minDate - 1; numTS.series(11).tstamp := numTS.cal.minDate - 2; -- Add Dates Beyond numTS.cal.maxDate numTS.series(12).tstamp := numTS.cal.maxDate + 1; numTS.series(13).tstamp := numTS.cal.maxDate + 2; -- Add some null timestamps numTS.series(14).tstamp := NULL; numTS.series(15).tstamp := NULL; -- Add some imprecise dates (some are duplicated) numTS.series(17).tstamp := numTS.series(16).tstamp + 1/24; numTS.series(18).tstamp := numTS.series(16).tstamp + 15/24; -- Add some duplicate timestamps numTS.series(19).tstamp := numTS.series(18).tstamp; numTS.series(21).tstamp := numTS.series(20).tstamp; -- Add some extra dates in the middle numTS.series(37).tstamp := TO_DATE('12/28/1996'); numTS.series(36).tstamp := TO_DATE('12/29/1996'); -- Add some holes at the end numTS.series(numTS.series.count).tstamp := TO_DATE('01/04/1997'); -- Example of validating an invalid time series. SELECT ordsys.timeseries.display(numTS, 'AN INVALID TIME SERIES') INTO tempVal FROM dual; retIsValid := ORDSYS.TIMESERIES.IsValidTS(numTS); retValTS := ORDSYS.TIMESERIES.ValidateTS(numTS, outMesg, loDateTab, hiDateTab, impDateTab, dupDateTab, extraDateTab, missingDateTab); DBMS_OUTPUT.PUT_LINE('Value returned by IsValid = ' || retIsValid); DBMS_OUTPUT.PUT_LINE('Value returned by ValidateTS = ' || retValTS); ORDSYS.TIMESERIES.DisplayValTS(retValTS, outMesg, loDateTab, hiDateTab, impDateTab, dupDateTab, extraDateTab, missingDateTab, 'Testing DisplayValTS'); END; /
This example might produce the following output:
A VALID TIME SERIES : Name = OPEN ACME Calendar Data: Calendar Name = BUSINESS-96 Frequency = 4 (day) MinDate = 01/01/1990 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0,1,1,1,1,1,0 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : 11/28/1996 00:00:00 12/25/1996 00:00:00 Series Data: ----------------------------- Date Value 11/01/1996 00:00:00 59 11/04/1996 00:00:00 60 11/05/1996 00:00:00 61 11/06/1996 00:00:00 62 11/07/1996 00:00:00 63 11/08/1996 00:00:00 64 11/11/1996 00:00:00 65 11/12/1996 00:00:00 66 11/13/1996 00:00:00 67 11/14/1996 00:00:00 68 11/15/1996 00:00:00 69 11/18/1996 00:00:00 70 11/19/1996 00:00:00 71 11/20/1996 00:00:00 72 11/21/1996 00:00:00 73 11/22/1996 00:00:00 74 11/25/1996 00:00:00 75 11/26/1996 00:00:00 76 11/27/1996 00:00:00 77 11/29/1996 00:00:00 78 12/02/1996 00:00:00 79 12/03/1996 00:00:00 80 12/04/1996 00:00:00 81 12/05/1996 00:00:00 82 12/06/1996 00:00:00 83 12/09/1996 00:00:00 84 12/10/1996 00:00:00 85 12/11/1996 00:00:00 86 12/12/1996 00:00:00 87 12/13/1996 00:00:00 88 12/16/1996 00:00:00 89 12/17/1996 00:00:00 90 12/18/1996 00:00:00 91 12/19/1996 00:00:00 92 12/20/1996 00:00:00 93 12/23/1996 00:00:00 94 12/24/1996 00:00:00 95 12/26/1996 00:00:00 96 12/27/1996 00:00:00 97 12/30/1996 00:00:00 98 12/31/1996 00:00:00 99 ----------------------------- Value returned by IsValid = 1 Value returned by ValidateTS = 1 DisplayValTS: Testing DisplayValTS: TS-SUC: the input time series is a valid time series AN INVALID TIME SERIES : Name = OPEN ACME Calendar Data: Calendar Name = BUSINESS-96 Frequency = 4 (day) MinDate = 10/28/1996 00:00:00 MaxDate = 01/05/1997 00:00:00 patBits: 0,1,1,1,1,1,0 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : 11/28/1996 00:00:00 12/25/1996 00:00:00 Series Data: ----------------------------- Date Value 11/01/1996 00:00:00 59 11/04/1996 00:00:00 60 11/05/1996 00:00:00 61 11/06/1996 00:00:00 62 11/07/1996 00:00:00 63 11/08/1996 00:00:00 64 11/11/1996 00:00:00 65 11/12/1996 00:00:00 66 11/13/1996 00:00:00 67 10/27/1996 00:00:00 68 10/26/1996 00:00:00 69 01/06/1997 00:00:00 70 01/07/1997 00:00:00 71 72 73 11/22/1996 00:00:00 74 11/22/1996 01:00:00 75 11/22/1996 15:00:00 76 11/22/1996 15:00:00 77 11/29/1996 00:00:00 78 11/29/1996 00:00:00 79 12/03/1996 00:00:00 80 12/04/1996 00:00:00 81 12/05/1996 00:00:00 82 12/06/1996 00:00:00 83 12/09/1996 00:00:00 84 12/10/1996 00:00:00 85 12/11/1996 00:00:00 86 12/12/1996 00:00:00 87 12/13/1996 00:00:00 88 12/16/1996 00:00:00 89 12/17/1996 00:00:00 90 12/18/1996 00:00:00 91 12/19/1996 00:00:00 92 12/20/1996 00:00:00 93 12/29/1996 00:00:00 94 12/28/1996 00:00:00 95 12/26/1996 00:00:00 96 12/27/1996 00:00:00 97 12/30/1996 00:00:00 98 01/04/1997 00:00:00 99 ----------------------------- Value returned by IsValid = 0 Value returned by ValidateTS = 0 DisplayValTS: Testing DisplayValTS: TS-WRN: the input time series has errors. See the message for details message output by validateTS: TS-ERR: the input time series is unsorted TS-ERR: the time series has null timestamps TS-ERR: the time series has timestamps < calendar minDate (refer LoDateTab) TS-ERR: the time series has timestamps > calendar maxDate (refer HiDateTab) TS-ERR: the time series has imprecise timestamps (refer impreciseDateTab) TS-ERR: the time series has duplicate timestamps (refer DuplicateDateTab) list of dates < calendar minDate - lowDateTab : 10/26/1996 00:00:00 10/27/1996 00:00:00 list of dates > calendar maxDate - hiDateTab : 01/06/1997 00:00:00 01/07/1997 00:00:00 list of imprecise dates - impreciseDateTab : 11/22/1996 01:00:00 11/22/1996 15:00:00 list of duplicate dates - duplicateDateTab : 11/22/1996 15:00:00 11/29/1996 00:00:00 ExtraDateTab : 12/28/1996 00:00:00 12/29/1996 00:00:00 01/04/1997 00:00:00 MissingDateTab : 10/28/1996 00:00:00 10/29/1996 00:00:00 10/30/1996 00:00:00 10/31/1996 00:00:00 11/14/1996 00:00:00 11/15/1996 00:00:00 11/18/1996 00:00:00 11/19/1996 00:00:00 11/20/1996 00:00:00 11/21/1996 00:00:00 11/25/1996 00:00:00 11/26/1996 00:00:00 11/27/1996 00:00:00 12/02/1996 00:00:00 12/23/1996 00:00:00 12/24/1996 00:00:00 12/31/1996 00:00:00 01/01/1997 00:00:00 01/02/1997 00:00:00 01/03/1997 00:00:00
