Release 8.0.4
A57501-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8 Time Series Cartridge User's Guide Release 8.0.4 A57501-01 |
|
The Oracle8 Time Series Cartridge library consists of:
Two separate reference chapters are provided for the functions because the functions described in each are typically done at different times in the application development cycle and by people performing different job roles:
Syntax notes:
select CAST(TimeSeries.ExtractTable(close) AS ORDTNumTab) select cast(TIMESERIES.extracttable(close) as ordtnumtab) select cast(TiMeSeRiEs.eXtRaCtTaBlE(ClosE) As ordtNUMtab)
Usage note:
All time series 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 outputs an instance, and any other functions in the nesting accept an instance and output an instance.
For an explanation of the reference-based interface, see Section 2.5.2.
ORDSYS.TimeSeries.Cavg(
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.
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 any of the following conditions is 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 * FROM the (SELECT 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) FROM ORDTDEV.stockdemo_ts ts 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(
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.
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 any of the following conditions is 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 * FROM the (SELECT 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) FROM ORDTDEV.stockdemo_ts ts 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(
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.
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 any of the following conditions is 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 * FROM the (SELECT 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) FROM ORDTDEV.stockdemo_ts ts 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(
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.
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 any of the following conditions is 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 * FROM the (SELECT 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) FROM ORDTDEV.stockdemo_ts ts 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(
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.
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 any of the following conditions is 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 * FROM the (SELECT 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) FROM ORDTDEV.stockdemo_ts ts 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(
Approach 2:
ORDSYS.TimeSeries.DeriveExceptions(
or
ORDSYS.TimeSeries.DeriveExceptions(
Derives calendar exceptions from a calendar and a table of dates (Approach 1) or from two time series (Approach 2).
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).
See Section 2.2.4 for a detailed explanation of the two approaches to using this function.
See Sections 3.3.1 and 3.3.2 for examples of the two 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 datatypes:
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 AONE for the month of January 1996:
SET SERVEROUTPUT ON DECLARE tmp INTEGER; BEGIN SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.TSMaxN(close,10, to_date('01011996','MMDDYYYY'), to_date('01311996','MMDDYYYY'))) INTO tmp FROM ORDTDEV.stocks_ts WHERE ticker ='AONE'; END; /
This example might produce the following output:
Tab Data: ----------------------------- Date Value 01/24/1996 00:00:00 43.9138 01/25/1996 00:00:00 42.9925 01/31/1996 00:00:00 42.9925 01/26/1996 00:00:00 42.7413 01/30/1996 00:00:00 42.7413 01/29/1996 00:00:00 42.5738 01/23/1996 00:00:00 41.9875 01/22/1996 00:00:00 41.82 01/19/1996 00:00:00 41.485 01/18/1996 00:00:00 40.815 -----------------------------
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('01011996','MMDDYYYY'), to_date('01311996','MMDDYYYY'))) FROM ORDTDEV.stocks_ts WHERE ticker ='AONE';
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 ordtdev.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 = ACME open NumSeries Calendar Data: Calendar Name = BUSINESS-96 Frequency = 4 MinDate = 01/01/1990 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0111110 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 = ACME open NumSeries Calendar Data: Calendar Name = BUSINESS-96 Frequency = 4 MinDate = 10/28/1996 00:00:00 MaxDate = 01/05/1997 00:00:00 patBits: 0111110 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 ORDTDEV.stockdemo_ts ts WHERE ts.ticker='ACME'; END; /
This example might produce the following output:
ExtractCal Results : Calendar Name = BUSINESS-96 Frequency = 4 MinDate = 01/01/1990 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0111110 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 ORDTDEV.stocks_ts WHERE ticker = 'AONE';
This example might produce the following output:
TO_CHAR(ORDSYS.TIME ------------------- 01/15/1990 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 ORDTDEV.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 stocks_ts table:
SELECT ORDSYS.TimeSeries.ExtractValue( ORDSYS.TimeSeries.GetNthElement(open, 10)) FROM ORDTDEV.stocks_ts WHERE ticker = 'AONE';
This example might produce the following output:
ORDSYS.TIM ---------- 15.1875 1 row selected.
ORDSYS.TimeSeries.Fill(
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.
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 timeseries with missing dates. -- In the following example, the timeseries '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 timeseries by from XCORP's high (repeat forward). SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.Fill(ts, 1), 'Fill Forward') INTO dummyval FROM dual; -- Generate a timeseries by from XCORP's high (repeat backward). SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.Fill(ts, 2), 'Fill Backward') INTO dummyval FROM dual; -- Generate a timeseries 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 MinDate = 01/01/1996 00:00:00 MaxDate = 01/01/1997 00:00:00 patBits: 1111111 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 MinDate = 01/01/1996 00:00:00 MaxDate = 01/01/1997 00:00:00 patBits: 1111111 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 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 MinDate = 01/01/1996 00:00:00 MaxDate = 01/01/1997 00:00:00 patBits: 1111111 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 ORDTDEV.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(
Given a time series and a number of elements (NumValues) to return, returns the first NumValues elements in the time series.
The input time series.
Number of elements from the beginning of the time series to be returned.
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 or if NumValues is zero (0) or negative.
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 ORDTDEV.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 MinDate = 01/01/1990 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0111110 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 ORDTDEV.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 any of the following conditions is true:
Return the tenth opening price for stock AONE:
SELECT ORDSYS.TimeSeries.ExtractValue( ORDSYS.TimeSeries.GetNthElement(open, 10)) FROM ORDTDEV.stocks_ts WHERE ticker = 'AONE';
This example might produce the following output:
ORDSYS.TIM ---------- 15.1875 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).
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 ORDTDEV.stockdemo_ts ts WHERE ts.ticker='ACME'; END; /
This example might produce the following output:
GetSeries Results : Name = ACME open NumSeries Calendar Data: Calendar Name = BUSINESS-96 Frequency = 4 MinDate = 01/01/1990 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0111110 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, outputs 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 ordtdev.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 = ACME open NumSeries Calendar Data: Calendar Name = BUSINESS-96 Frequency = 4 MinDate = 01/01/1990 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0111110 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 = ACME open NumSeries Calendar Data: Calendar Name = BUSINESS-96 Frequency = 4 MinDate = 10/28/1996 00:00:00 MaxDate = 01/05/1997 00:00:00 patBits: 0111110 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 (
ts ORDSYS.ORDTNumSeriesIOTRef,
units INTEGER
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
or
ORDSYS.TimeSeries.Lag (
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.
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.
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 * FROM the (SELECT 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) FROM ORDTDEV.stockdemo_ts ts 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 ORDTDEV.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(
Given a time series and a number of elements (NumValues) to return, returns the last NumValues elements in the time series.
The input time series.
Number of elements from the end of the time series to be returned.
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 or if NumValues is zero (0) or negative.
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 ORDTDEV.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 MinDate = 01/01/1990 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0111110 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 (
ts ORDSYS.ORDTNumSeriesIOTRef,
units INTEGER
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
or
ORDSYS.TimeSeries.Lead (
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.
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.
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 * FROM the (SELECT 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) FROM ORDTDEV.stockdemo_ts ts 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(
ts ORDSYS.ORDTNumSeriesIOTRef,
[startDate DATE, endDate DATE,]
k INTEGER
) 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.
The input time series.
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.
Positive integer specifying the look-back window (number of timestamps, including the current one, over which to compute the moving average).
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.8.2.1.
An exception is returned if any of the following conditions is true:
Return a table of 10-day moving average values of the closing price for stock ACME for the month of December 1996:
SELECT * FROM the (SELECT CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.Mavg(ts.close,to_date('02-DEC-96','DD-MON-YY'), to_date('31-DEC-96','DD-MON-YY'),10) ) AS ORDSYS.ORDTNumTab) FROM ORDTDEV.stockdemo_ts ts 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. SVRMGR>
ORDSYS.TimeSeries.Msum(
ts ORDSYS.ORDTNumSeriesIOTRef,
[startDate DATE, endDate DATE,]
k INTEGER
) 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.
The input time series.
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.
Positive integer specifying the look-back window (number of timestamps, including the current one, over which to compute the moving sum).
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.8.2.1.
An exception is returned if any of the following conditions is true:
Return a table of 30-day moving sum values of trading volume for stock AONE for 1996:
SELECT * FROM THE( SELECT CAST(ORDTS.Extract(ORDTS.MSUM(volume, to_date(`01-01-96','MM-DD-YY'), to_date(`12-31-96','MM-DD-YY'), 30)) AS ORDTNumTab) FROM StockTabView WHERE ticker = `AONE');
ORDSYS.TimeSeries.Scaleup(
Given an input ORDTCalendar and a date, returns a scaled date.
The date to be used for scaling.
The calendar to be used for scaling the date.
For an explanation of concepts related to time scaling, see Section 2.9.
This function is used in a SQL GROUP BY clause for scaling of dates.
An exception is returned if inDate or calendar is null.
For all tickers accessible through the stockdemo_sv view (ACME, FUNCO, SAMCO, and XCORP), scale daily data to monthly summary data for the summed volume and average closing price.
-- -- Scaleup - Group By interface -- For all tickers in stockdemo, scale daily data to monthly -- summary data, reporting summed volumes and average closes. -- SELECT ticker, ORDSYS.TimeSeries.Scaleup(sv.tstamp, value(cal)),sum(volume),avg(close) FROM ORDTDEV.stockdemo_sv sv, ordtdev.stockdemo_calendars cal WHERE cal.name = 'MONTHLY' GROUP BY ticker,ORDSYS.TimeSeries.Scaleup(sv.tstamp, value(cal));
This example might produce the following output:
TICKE ORDSYS.OR SUM(VOLUME AVG(CLOSE) ----- --------- ---------- ---------- ACME 01-NOV-96 20000 68.5 ACME 01-DEC-96 21000 89 FUNCO 01-NOV-96 20000 23.823 FUNCO 01-DEC-96 21000 23.8257143 SAMCO 01-NOV-96 10207000 39.83125 SAMCO 01-DEC-96 3719450 38.2738095 XCORP 01-OCT-96 10270250 79.1458333 XCORP 01-NOV-96 100243350 84.6973684 XCORP 01-DEC-96 141838350 91.9572368 9 rows selected.
ORDSYS.TimeSeries.ScaleupAvg(
ts ORDSYS.ORDTNumSeriesIOTRef,
calendar ORDSYS.ORDTCalendar
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given a time series, a calendar to be used for scaling, and optionally starting and ending dates, returns a time series reflecting the average value of each scaled group of non-null values.
The input time series.
The calendar to be used for the scaling.
The starting date to be used. If startDate is specified, endDate must also be specified.
The ending date to be used. If endDate is specified, startDate must also be specified.
An exception is returned for any of the following conditions:
Nulls are ignored in computing the average for each group of values.
For an explanation of concepts related to time scaling, see Section 2.9.
Return the average closing prices for stock SAMCO for each month for the entire time series:
SELECT * FROM THE (SELECT CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.ScaleupAvg( ts.close, sc.calendar ) ) AS ORDSYS.ORDTNumTab) FROM ORDTDEV.stocks_ts ts, ordtdev.scale sc WHERE ts.ticker='SAMCO' and sc.name ='MONTHLY');
This example might produce the following output:
TSTAMP VALUE --------- ---------- 01-JAN-90 29.7074045 01-FEB-90 29.0477211 01-MAR-90 30.7003091 . . . 01-OCT-96 42.7717391 01-NOV-96 39.83125 01-DEC-96 38.2738095 84 rows selected.
ORDSYS.TimeSeries.ScaleupCount(
ts ORDSYS.ORDTNumSeriesIOTRef,
calendar ORDSYS.ORDTCalendar
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given a time series, a calendar to be used for scaling, and optionally starting and ending dates, returns a time series reflecting the count of non-null timestamps in each scaled group.
The input time series.
The calendar to be used for the scaling.
The starting date to be used. If startDate is specified, endDate must also be specified.
The ending date to be used. If endDate is specified, startDate must also be specified.
An exception is returned for any of the following conditions:
Nulls are ignored in computing the count for each group of values.
For an explanation of concepts related to time scaling, see Section 2.9.
Return the quarterly count of daily closing prices for stock SAMCO for the period 01-June-1996 through 31-December 1996:
SELECT * FROM THE (SELECT CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.ScaleupCount( ts.close, sc.calendar, to_date('01-JUL-1996','DD-MON-YYYY'), to_date('31-DEC-1996','DD-MON-YYYY') ) ) AS ORDSYS.ORDTNumTab) FROM ORDTDEV.stocks_ts ts, ordtdev.scale sc WHERE ts.ticker='SAMCO' and sc.name ='QUARTERLY');
This example might produce the following output:
TSTAMP VALUE --------- ---------- 01-JUL-96 64 01-OCT-96 64 2 rows selected.
ORDSYS.TimeSeries.ScaleupFirst(
ts ORDSYS.ORDTNumSeriesIOTRef,
calendar ORDSYS.ORDTCalendar
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given a time series, a calendar to be used for scaling, and optionally starting and ending dates, returns a time series reflecting the first non-null value of each scaled group of values.
The input time series.
The calendar to be used for the scaling.
The starting date to be used. If startDate is specified, endDate must also be specified.
The ending date to be used. If endDate is specified, startDate must also be specified.
An exception is returned for any of the following conditions:
For an explanation of concepts related to time scaling, see Section 2.9.
Return the first closing prices for stock SAMCO for the months of October, November, and December of 1996:
SELECT * FROM THE (SELECT CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.ScaleupFirst( ts.close, sc.calendar, to_date('01-OCT-1996','DD-MON-YYYY'), to_date('01-JAN-1997','DD-MON-YYYY') ) ) AS ORDSYS.ORDTNumTab) FROM ORDTDEV.stocks_ts ts, ordtdev.scale sc WHERE ts.ticker='SAMCO' and sc.name ='MONTHLY');
This example might produce the following output:
TSTAMP VALUE --------- ---------- 01-OCT-96 42.75 01-NOV-96 41.875 01-DEC-96 38.125 3 rows selected.
ORDSYS.TimeSeries.ScaleupLast(
ts ORDSYS.ORDTNumSeriesIOTRef,
calendar ORDSYS.ORDTCalendar
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given a time series, a calendar to be used for scaling, and optionally starting and ending dates, returns a time series reflecting the last non-null value of each scaled group of values.
The input time series.
The calendar to be used for the scaling.
The starting date to be used. If startDate is specified, endDate must also be specified.
The ending date to be used. If endDate is specified, startDate must also be specified.
An exception is returned for any of the following conditions:
For an explanation of concepts related to time scaling, see Section 2.9.
Return the last closing prices for stock SAMCO for the months of October, November, and December of 1996:
SELECT * FROM THE (SELECT CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.ScaleupLast( ts.close, sc.calendar, to_date('01-OCT-1996','DD-MON-YYYY'), to_date('01-JAN-1997','DD-MON-YYYY') ) ) AS ORDSYS.ORDTNumTab) FROM ORDTDEV.stocks_ts ts, ordtdev.scale sc WHERE ts.ticker='SAMCO' and sc.name ='MONTHLY');
This example might produce the following output:
TSTAMP VALUE --------- ---------- 01-OCT-96 42.375 01-NOV-96 38.25 01-DEC-96 39.75 3 rows selected.
Note that each timestamp reflects the first date of the month in the calendar (following the rules explained in Section 2.2.1), and each value in this case reflects the closing price on the last date for that month in the calendar.
ORDSYS.TimeSeries.ScaleupMax(
ts ORDSYS.ORDTNumSeriesIOTRef,
calendar ORDSYS.ORDTCalendar
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given a time series, a calendar to be used for scaling, and optionally starting and ending dates, returns a time series reflecting the maximum value of each scaled group of values.
The input time series.
The calendar to be used for the scaling.
The starting date to be used. If startDate is specified, endDate must also be specified.
The ending date to be used. If endDate is specified, startDate must also be specified.
An exception is returned for any of the following conditions:
For an explanation of concepts related to time scaling, see Section 2.9.
Return the highest (maximum) closing prices for stock SAMCO for each month in the entire time series:
SELECT * FROM THE (SELECT CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.ScaleupMax( ts.close, sc.calendar ) ) AS ORDSYS.ORDTNumTab) FROM ORDTDEV.stocks_ts ts, ordtdev.scale sc WHERE ts.ticker='SAMCO' and sc.name ='MONTHLY');
This example might produce the following output:
TSTAMP VALUE --------- ---------- 01-JAN-90 31.2813 01-FEB-90 29.7813 01-MAR-90 31.1875 01-APR-90 31.5938 01-MAY-90 32.875 01-JUN-90 33.7813 01-JUL-90 34.6875 01-AUG-90 31.875 ... ... 01-OCT-96 43.375 01-NOV-96 43.75 01-DEC-96 39.75 84 rows selected.
ORDSYS.TimeSeries.ScaleupMin(
ts ORDSYS.ORDTNumSeriesIOTRef,
calendar ORDSYS.ORDTCalendar
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given a time series, a calendar to be used for scaling, and optionally starting and ending dates, returns a time series reflecting the minimum value of each scaled group of values.
The input time series.
The calendar to be used for the scaling.
The starting date to be used. If startDate is specified, endDate must also be specified.
The ending date to be used. If endDate is specified, startDate must also be specified.
An exception is returned for any of the following conditions:
For an explanation of concepts related to time scaling, see Section 2.9.
Return the lowest (minimum) closing prices for stock SAMCO for each month in the entire time series:
SELECT * FROM THE (SELECT CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.ScaleupMin( ts.close, sc.calendar ) ) AS ORDSYS.ORDTNumTab) FROM ORDTDEV.stocks_ts ts, ordtdev.scale sc WHERE ts.ticker='SAMCO' and sc.name ='MONTHLY');
This example might produce the following output:
TSTAMP VALUE --------- ---------- 01-JAN-90 27.6875 01-FEB-90 28.2813 01-MAR-90 30.0938 01-APR-90 30.1875 01-MAY-90 30.7813 01-JUN-90 32.0938 01-JUL-90 32.2813 01-AUG-90 28.5938 ... ... 01-OCT-96 42 01-NOV-96 37.375 01-DEC-96 37.875 84 rows selected.
ORDSYS.TimeSeries.ScaleupSum(
ts ORDSYS.ORDTNumSeriesIOTRef,
calendar ORDSYS.ORDTCalendar
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given a time series, a calendar to be used for scaling, and optionally starting and ending dates, returns a time series reflecting the sum of each scaled group of values.
The input time series.
The calendar to be used for the scaling.
The starting date to be used. If startDate is specified, endDate must also be specified.
The ending date to be used. If endDate is specified, startDate must also be specified.
An exception is returned for any of the following conditions:
For an explanation of concepts related to time scaling, see Section 2.9.
Return the sum of the daily trade volume for stock SAMCO for each month in the entire time series:
SELECT * FROM THE (SELECT CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.ScaleupSum( ts.volume, sc.calendar ) ) AS ORDSYS.ORDTNumTab) FROM ORDTDEV.stocks_ts ts, ordtdev.scale sc WHERE ts.ticker='SAMCO' and sc.name ='MONTHLY');
This example might produce the following output:
TSTAMP VALUE --------- ---------- 01-JAN-90 3117750 01-FEB-90 2036500 01-MAR-90 1424375 01-APR-90 981500 01-MAY-90 1348875 01-JUN-90 1395875 01-JUL-90 1088125 01-AUG-90 1503000 ... ... 01-OCT-96 1615350 01-NOV-96 10207000 01-DEC-96 3719450 84 rows selected.
ORDSYS.TimeSeries.TrimSeries(ts ORDSYS.ORDTNumSeriesIOTRef
or
ORDSYS.TimeSeries.TrimSeries(ts ORDSYS.ORDTVarchar2SeriesIOTRef
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.
The input time series.
Starting date within the time series. If startDate is specified, endDate must also be specified.
Ending date within the time series. If endDate is specified, startDate must also be specified.
An exception is returned if any of the following conditions is true:
Return the opening prices for stock AONE 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 ORDTDEV.stocks_ts WHERE ticker = 'AONE'; END; /
This statement might produce the following output:
Calendar Data: Calendar Name = AONE Frequency = 4 MinDate = 01-JAN-80 MaxDate = 01-JAN-01 patBits: 0111110 patAnchor = 06-APR-97 onExceptions : offExceptions : 19-FEB-90 13-APR-90 28-MAY-90 04-JUL-90 03-SEP-90 22-NOV-90 25-DEC-90 01-JAN-91 18-FEB-91 29-MAR-91 27-MAY-91 04-JUL-91 02-SEP-91 28-NOV-91 25-DEC-91 01-JAN-92 17-FEB-92 17-APR-92 25-MAY-92 03-JUL-92 07-SEP-92 26-NOV-92 25-DEC-92 01-JAN-93 15-FEB-93 09-APR-93 31-MAY-93 05-JUL-93 06-SEP-93 25-NOV-93 24-DEC-93 21-FEB-94 01-APR-94 27-APR-94 30-MAY-94 04-JUL-94 05-SEP-94 24-NOV-94 26-DEC-94 02-JAN-95 20-FEB-95 14-APR-95 29-MAY-95 04-JUL-95 04-SEP-95 23-NOV-95 25-DEC-95 01-JAN-96 19-FEB-96 05-APR-96 27-MAY-96 04-JUL-96 02-SEP-96 17-OCT-96 28-NOV-96 25-DEC-96 27-DEC-96 Series Data: ----------------------------- Date Value 02-DEC-96 59.875 03-DEC-96 60.875 04-DEC-96 60.625 05-DEC-96 57.75 06-DEC-96 56.5 09-DEC-96 57 10-DEC-96 60.875 11-DEC-96 59.625 12-DEC-96 59.75 13-DEC-96 54.875 16-DEC-96 55.625 17-DEC-96 53.25 18-DEC-96 54.375 19-DEC-96 53.875 20-DEC-96 53.375 23-DEC-96 54.375 24-DEC-96 53.5 26-DEC-96 54.375 30-DEC-96 54.125 31-DEC-96 52.875 -----------------------------
ORDSYS.TimeSeries.TSAdd (
ts1 ORDSYS.ORDTNumSeriesIOTRef,
ts2 ORDSYS.ORDTNumSeriesIOTRef
[,startDate DATE, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
or
ORDSYS.TimeSeries.TSAdd (
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.
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 any of the following conditions is 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 ORDTUSER/ORDTUSER 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 ORDTDEV.stockdemo_ts ts1, ORDTDEV.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 MinDate = 01/01/1990 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0111110 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 any of the following conditions is 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 ORDTDEV.stockdemo_ts WHERE ticker='ACME';
This example might produce the following output:
ORDSYS.ORD ORDSYS.ORD ORDSYS.ORD ---------- ---------- ---------- 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 any of the following conditions is true:
Return the total number of daily closing prices for stock AONE for the month of January 1990:
SELECT ORDSYS.TimeSeries.TSCount(close, to_date('01/01/1990 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('01/31/1990 23:59:59', 'MM/DD/YYYY HH24:MI:SS')) TSCount FROM ORDTDEV.Stocks_TS WHERE ticker='AONE';
This example might produce the following output:
TSCOUNT ---------- 22 1 row selected.
ORDSYS.TimeSeries.TSDivide (
ts1 ORDSYS.ORDTNumSeriesIOTRef,
ts2 ORDSYS.ORDTNumSeriesIOTRef
[,startDate DATE, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
or
ORDSYS.TimeSeries.TSDivide (
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.
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 any of the following conditions is 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 ORDTUSER/ORDTUSER 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 ORDTDEV.stockdemo_ts ts1, ORDTDEV.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 MinDate = 01/01/1990 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0111110 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 any of the following conditions is true:
Return the highest closing price for stock AONE for the month of January 1990:
SELECT ORDSYS.TimeSeries.TSMax(close, to_date('01/01/1990 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('01/31/1990 23:59:59', 'MM/DD/YYYY HH24:MI:SS')) TSMax FROM ORDTDEV.Stocks_TS WHERE ticker='AONE';
This example might produce the following output:
TSMAX ---------- 16.3914 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 any of the following conditions is true:
Return the 10 highest closing prices for stock AONE for the month of January 1996:
SELECT * FROM THE( SELECT CAST( ORDSYS.TimeSeries.TSMaxN(close, 10, to_date('01011996','MMDDYYYY'), to_date('01311996','MMDDYYYY')) as ORDSYS.ORDTNumTab) FROM ORDTDEV.stocks_ts WHERE ticker ='AONE');
This example might produce the following output:
TSTAMP VALUE --------- ---------- 24-JAN-96 43.9138 25-JAN-96 42.9925 31-JAN-96 42.9925 26-JAN-96 42.7413 30-JAN-96 42.7413 29-JAN-96 42.5738 23-JAN-96 41.9875 22-JAN-96 41.82 19-JAN-96 41.485 18-JAN-96 40.815 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 any of the following conditions is true:
Return the median closing price for stock AONE for the month of January 1990:
SELECT ORDSYS.TimeSeries.TSMedian(close, to_date('01/01/1990 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('01/31/1990 23:59:59', 'MM/DD/YYYY HH24:MI:SS')) TSMedian FROM ORDTDEV.Stocks_TS WHERE ticker='AONE';
This example might produce the following output:
TSMEDIAN ---------- 15.4649 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 any of the following conditions is true:
Return the lowest closing price for stock AONE for the month of January 1990:
SELECT ORDSYS.TimeSeries.TSMin(close, to_date('01/01/1990 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('01/31/1990 23:59:59', 'MM/DD/YYYY HH24:MI:SS')) TSMin FROM ORDTDEV.Stocks_TS WHERE ticker='AONE';
This example might produce the following output:
TSMIN ---------- 15.1038 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 any of the following conditions is true:
Return the 10 lowest closing prices for stock AONE for the month of January 1996:
SELECT * FROM THE( SELECT CAST( ORDSYS.TimeSeries.TSMinN(close, 10, to_date('01011996','MMDDYYYY'), to_date('01311996','MMDDYYYY')) as ORDSYS.ORDTNumTab) FROM ORDTDEV.stocks_ts WHERE ticker ='AONE');
This example might produce the following output:
TSTAMP VALUE --------- ---------- 15-JAN-96 37.8 09-JAN-96 37.9675 04-JAN-96 38.3025 10-JAN-96 38.47 03-JAN-96 38.6375 16-JAN-96 38.9725 11-JAN-96 39.0563 08-JAN-96 39.3075 12-JAN-96 39.5588 17-JAN-96 39.6425 10 rows selected.
ORDSYS.TimeSeries.TSMultiply (
ts1 ORDSYS.ORDTNumSeriesIOTRef,
ts2 ORDSYS.ORDTNumSeriesIOTRef
[,startDate DATE, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
or
ORDSYS.TimeSeries.TSMultiply (
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.
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 any of the following conditions is 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 ORDTUSER/ORDTUSER 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 ORDTDEV.stockdemo_ts ts1, ORDTDEV.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 MinDate = 01/01/1990 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0111110 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 any of the following conditions is true:
Return the product resulting from multiplying the daily closing prices for stock AONE for the month of January 1990. (This example is not very plausible, but is presented merely to illustrate the syntax.)
SELECT ORDSYS.TimeSeries.TSProd(close, to_date('01/01/1990 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('01/31/1990 23:59:59', 'MM/DD/YYYY HH24:MI:SS')) TSProd FROM ORDTDEV.Stocks_TS WHERE ticker='AONE';
This example might produce the following output:
TSPROD ---------- 1.7126E+26 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 any of the following conditions is 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 ORDTDEV.stockdemo_ts WHERE ticker='ACME';
This example might produce the following output:
ORDSYS.ORD ORDSYS.ORD ORDSYS.ORD ---------- ---------- ---------- 79 143.5 11.9791486
1 row selected.
ORDSYS.TimeSeries.TSSubtract (
ts1 ORDSYS.ORDTNumSeriesIOTRef,
ts2 ORDSYS.ORDTNumSeriesIOTRef
[,startDate DATE, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
or
ORDSYS.TimeSeries.TSSubtract (
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.
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 any of the following conditions is 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 ORDTUSER/ORDTUSER 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 ORDTDEV.stockdemo_ts ts1, ORDTDEV.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 MinDate = 01/01/1990 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0111110 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 any of the following conditions is true:
Return the sum of the daily trading volumes for stock AONE for the month of January 1990 (that is, the total AONE volume for the month):
SELECT ORDSYS.TimeSeries.TSSum(volume, to_date('01/01/1990 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('01/31/1990 23:59:59', 'MM/DD/YYYY HH24:MI:SS')) TSSum FROM ORDTDEV.Stocks_TS WHERE ticker='AONE';
This example might produce the following output:
TSSUM ---------- 104434900 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 any of the following conditions is 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 ORDTDEV.stockdemo_ts WHERE ticker='ACME';
This example might produce the following output:
ORDSYS.ORD ORDSYS.ORD ORDSYS.ORD ---------- ---------- ---------- 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, outputs 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 whether 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 ordtdev.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 = ACME open NumSeries Calendar Data: Calendar Name = BUSINESS-96 Frequency = 4 MinDate = 01/01/1990 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0111110 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 = ACME open NumSeries Calendar Data: Calendar Name = BUSINESS-96 Frequency = 4 MinDate = 10/28/1996 00:00:00 MaxDate = 01/05/1997 00:00:00 patBits: 0111110 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
