|Oracle8i Time Series User's Guide
Part Number A67294-01
This chapter explains important procedures related to using Oracle8i Time Series. It contains the following major sections:
For detailed explanations of Oracle8i Time Series concepts and terminology, see Chapter 2.
You can use the administrative tools procedures to create a time series group (all the necessary time series schema objects), accepting default values for most object names. These procedures provide a convenient, simple way to create time series schema objects, and they are recommended for most users. These procedures are used in the quick-start demo (see Section 1.6.1) and the usage demo (see Section 1.6.2).
The following example shows the use of administrative tools procedures to create all the necessary schema objects:
DECLARE BEGIN -- -- Establish 'tsquick' as the time series group name for purposes of the -- administrative tools procedures. Columns will automatically be created -- for the time series name (which will be set to 'ticker') and a -- timestamp. The columns for the opening, high, low, and closing prices -- and the trading volume will be explicitly defined. -- ORDSYS.TSTools.Begin_Create_TS_Group('tsquick','flat'); -- Set 'ticker' as the name of the time series for functions. -- Sample values for specific tickers include 'ACME', 'AONE', and 'XCORP'. ORDSYS.TSTools.Set_Flat_Attributes(tsname_colname => 'ticker'); ORDSYS.TSTools.Set_Flat_Attributes(tsname_length => 10); -- Define numeric columns for prices. ORDSYS.TSTools.Add_Number_Column('open'); ORDSYS.TSTools.Add_Number_Column('high'); ORDSYS.TSTools.Add_Number_Column('low'); ORDSYS.TSTools.Add_Number_Column('close'); -- Define an integer column for trading volume (number of shares -- traded on a given day). ORDSYS.TSTools.Add_Integer_Column('volume'); -- End the specification of schema objects and create the objects. -- ORDSYS.TSTools.End_Create_TS_Group; exception when others then begin ORDSYS.TSTools.Cancel_Create_TS_Group; raise; end; END; /
The preceding call to End_Create_TS_Group causes many schema objects to be created. Among them are:
Example: SELECT TimeSeries.<function>(ts.OPEN) FROM TSQUICK ts;
Calendars are needed if one or more of the following conditions apply:
You have several options for creating a calendar, including:
Calendars for a regular time series are stored in the calendar table associated with that time series group. The calendar table typically has a name in the format groupname_CAL (for example, tsquick_cal for the quick-start demo). Calendars to be used for scaling can be stored in the group calendar table or in a calendar table that is separately created and managed.
Calendars are based on the system-defined data type ORDTCalendar, which is supplied with Oracle8i Time Series. ORDTCalendar has the following definition:
/* System-Defined Calendar Data Type */ CREATE TYPE ORDSYS.ORDTCalendar AS OBJECT ( caltype INTEGER, name VARCHAR2(256), frequency INTEGER, pattern ORDSYS.ORDTPattern, minDate DATE, maxDate DATE, offExceptions ORDSYS.ORDTExceptions, onExceptions ORDSYS.ORDTExceptions);
Example 3-1 creates a table named my_calendars and defines a calendar named BusinessDays-97. The BusinessDays-97 calendar includes Mondays through Fridays in 1997, but excludes 04-Jul-1997 and 25-Dec-1997. Explanatory notes follow the example.
CREATE TABLE my_calendars of ORDSYS.ORDTCalendar; INSERT INTO my_calendars VALUES( ORDSYS.ORDTCalendar( 0, `BusinessDays-97', 4, ORDSYS.ORDTPattern(ORDSYS.ORDTPatternBits(0,1,1,1,1,1,0), (to_date(`01-05-97','MM-DD-YY'))), to_date(`01-01-97','MM-DD-YY'), to_date(`01-01-98','MM-DD-YY'), ORDSYS. ORDTExceptions(to_date(`07-04-97','MM-DD-YY'), to_date(`12-25-97','MM-DD-YY')), NULL));
Notes on Example 3-1:
After you create the calendar, you should validate it to ensure that you have not made any mistakes in the calendar definition. The following example validates the BusinessDays-97 calendar created by Example 3-1.
DECLARE tstCal ordsys.ordtcalendar; dummyVal integer; validFlag integer; BEGIN -- Select a calendar into tstCal from my_calendars. select value(cal) into tstCal from my_calendars cal where cal.name = 'BusinessDays-97'; -- Display the calendar select ordsys.timeseries.display(tstCal) into dummyVal from dual; dbms_output.new_line; validFlag := ORDSYS.CALENDAR.IsValidCal(tstCal); if (validFlag = 1) then dbms_output.put_line('BusinessDays-97 calendar is valid.'); else dbms_output.put_line('BusinessDays-97 calendar is NOT valid.'); dbms_output.put_line('Use ValidateCal to determine inconsistency.'); end if; END; / Statement processed. Calendar Name = BusinessDays-97 Frequency = 4 (day) MinDate = 01-JAN-97 MaxDate = 01-JAN-98 patBits: 0,1,1,1,1,1,0 patAnchor = 05-JAN-97 onExceptions : Atomic NULL offExceptions : 04-JUL-97 25-DEC-97 BusinessDays-97 calendar is valid.
A map table maintains the mapping, or coupling, between a time series (such as a specific stock ticker) and a calendar. When you create a time series group, the map table by default has a name in the form groupname_MAP. (In the quick-start demo, the map table is named tsquick_map; in the usage demo, the map table is named stockdemo_metadata.) The map table has two VARCHAR2 columns:
The following example creates a map table named my_calendars_map:
CREATE TABLE my_calendars_map ( ticker VARCHAR2(5), calname VARCHAR2(256), CONSTRAINT pk_my_calendars_map PRIMARY KEY (ticker));
For each row in the map table, the calendar name can be null or can contain the name of a calendar:
For rows where a calendar name is specified, you can adopt one of the following strategies, depending on which calendars apply to which time series:
To populate the underlying data storage table or tables, perform a bulk load of the time series data, preferably using the SQL*Loader utility if you have a large amount of data. For example, the tsquick.sql procedure uses SQL*Loader with hypothetical stock market data, as follows:
To update the time series data, perform incremental loads as needed.
To ensure the consistency of time series data during loading, you must choose one of the approaches described in Section 2.8.3:
This strategy is normally appropriate when there is a unique calendar per time series.
This approach is particularly useful if the same calendar is used for all time series data being loaded.
This section describes how to perform bulk loading using these two approaches, and it also describes how to perform incremental loading.
The loading of time series data is usually performed under controlled circumstances, so it is safe to perform these loads directly to an underlying table instead of to a relational view.
After you create an index-organized table (IOT) to hold time series data (such as for the stockdemo demo database), you must populate the table with data. For a database of stock information, you may need to load millions of rows of daily summary information into the IOT.
SQL*Loader is recommended for loading large amounts of time series data. The following example shows a SQL*Loader script, with an excerpt from the sample data (stockdat.dat) and the SQL*Loader control file (stockdat.ctl). For complete information about SQL*Loader, see the Oracle8i Utilities manual.
The SQL*Loader script contains the following:
The stockdat.dat sample data file includes the following:
ACME 01-NOV-96 59.00 60.00 58.00 59.00 1000 ACME 04-NOV-96 60.00 61.00 59.00 60.00 1000 ACME 05-NOV-96 61.00 62.00 60.00 61.00 1000 ...
The stockdat.ctl file contains the following
options (direct=true) unrecoverable load data infile 'stockdat.dat' replace into table stockdemo sorted indexes (StockTabx) fields terminated by whitespace (ticker, tstamp DATE(13) "DD-MON-YY", open, high, low, close, volume)
SQL*Loader can handle many file formats and delimiters, as documented in the Oracle8i Utilities manual.
After the load has completed, you may want to choose one of the following approaches for ensuring calendar consistency:
In either case, you may need to update the exception lists of your calendars.
Often you will want to create calendars that conform to the time series data that you are receiving. In this case, you usually know the frequency and the pattern of a calendar, but not the specific on- or off-exceptions. You can extract these exceptions from the data by using the DeriveExceptions function.
Often you will want to ensure that the time series data extracted from the incoming data conforms to a predefined calendar. To do this, insert the exceptions either when you create the calendar or afterward with the InsertExceptions functions (or do both, creating the calendar with some exceptions and then adding others); then use the IsValidTimeSeries function to check that the time series is consistent with the calendar.
You can insert exceptions when you define the calendar. For example, the following statement specifies 28-Nov-1996 and 25-Dec-1996 as off-exceptions in the calendar named BUSINESS-96:
INSERT INTO stockdemo_calendars VALUES( ORDSYS.ORDTCalendar( 0, 'BUSINESS-96', 4, ORDSYS.ORDTPattern( ORDSYS.ORDTPatternBits(0,1,1,1,1,1,0), TO_DATE('01-JAN-1995','DD-MON-YYYY')), TO_DATE('01-JAN-1990','DD-MON-YYYY'), TO_DATE('01-JAN-2001','DD-MON-YYYY'), ORDSYS.ORDTExceptions( TO_DATE('28-NOV-1996','DD-MON-YYYY'), TO_DATE('25-DEC-1996','DD-MON-YYYY')), ORDSYS.ORDTExceptions() ));
You can also add exceptions after the calendar is defined by using the InsertExceptions function. For example, the following statement adds 01-Jan-1997, 17-Feb-1997, and 26-May-1997 as off-exceptions:
UPDATE stockdemo_calendars cal SET cal = (SELECT ORDSYS.Calendar.InsertExceptions( VALUE(cal), ORDSYS.ORDTDateTab( to_date('01-JAN-97','DD-MON-YY'), to_date('17-FEB-97','DD-MON-YY'), to_date('26-MAY-97','DD-MON-YY'))) FROM dual) WHERE cal.name = 'BUSINESS-96';
After you have defined the calendar and populated the exception lists, you can use the IsValidTimeSeries function to check that the time series is consistent with the calendar.
After you have performed the bulk load of time series data and have started using Oracle8i Time Series, you will probably want to add data periodically. For example, every trading day after the stock exchange closes, that day's data for each ticker becomes available.
As with bulk loading, incremental loading is typically done in a controlled environment. You know which timestamps will become off-exceptions, and you can explicitly update the exception lists of the appropriate calendars. The following example demonstrates such an update:
UPDATE stockdemo_calendars cal SET cal = (SELECT ORDSYS.Calendar.InsertExceptions( VALUE(cal), to_date('01-JAN-97','DD-MON-YY')) FROM dual) WHERE cal.name = 'XCORP';
The SQL*Loader utility is recommended for performing an incremental load of such additional data. The following example shows a SQL*Loader script, with an excerpt from the sample daily data (stockinc.dat) and the SQL*Loader control file (stockinc.ctl).
The SQL*Loader script contains the following:
The stockinc.dat sample data file includes the following:
ACME 02-JAN-97 100.00 101.00 99.00 100.00 1000 FUNCO 02-JAN-97 25.00 25.00 25.00 25.00 2000 SAMCO 02-JAN-97 39.00 40.00 38.00 39.50 30000 ...
The stockinc.ctl file contains the following:
load data infile 'stockinc.dat' append into table stockdemo fields terminated by whitespace (ticker, tstamp DATE(13) "DD-MON-YY", open, high, low, close, volume)
Note the following differences in the control file for incremental loading as opposed to bulk loading:
The conventional path is better for incremental loading because the amount of new data (daily stock information) is small relative to the total amount of data. For an explanation of conventional and direct paths, including situations in which the conventional path is necessary or preferable, see the SQL*Loader documentation in the Oracle8i Utilities manual.
You can use the administrative tools procedures to "retrofit" existing tables (that is, generate schema objects using existing detail, calendar, and map tables). The retrofit demo uses this approach, and the statements and comments in the retrofit.sql file reflect the approach described in this section. (The existing tables that are retrofitted are created in the tables.sql procedure, which the usage demo invokes before the retrofit.sql procedure.)
To use the administrative tools procedures to retrofit existing tables:
DECLARE BEGIN -- Establish 'stockdemo_ts' as the time series group name for purposes -- of the administrative tools procedures. ORDSYS.TSTools.Begin_Create_TS_Group('stockdemo_ts','flat'); -- Assert that the detail, map, and calendar tables exist, -- and define the names for these tables. -- Explicitly set the name of the relational view. -- Explicitly set the names of the timestamp and time series name -- columns. ordsys.tstools.set_flat_attributes( detail_table_name => 'stockdemo', detail_table_exists => 1, map_table_name => 'stockdemo_metadata', map_table_exists => 1, cal_table_name => 'stockdemo_calendars', cal_table_exists => 1, tstamp_colname => 'tstamp', tsname_colname => 'ticker', rel_view_name => 'stockdemo_sv'); -- Tell TSTools the names of existing time series columns -- (as defined for the table stockdemo) ORDSYS.TSTools.Add_Existing_Column('open'); ORDSYS.TSTools.Add_Existing_Column('high'); ORDSYS.TSTools.Add_Existing_Column('low'); ORDSYS.TSTools.Add_Existing_Column('close'); ORDSYS.TSTools.Add_Existing_Column('volume'); -- End the specification of schema objects and create the objects. ORDSYS.TSTools.End_Create_TS_Group; exception when others then begin ORDSYS.TSTools.Cancel_Create_TS_Group; raise; end; END; /
Choose one of the following approaches to ensuring the consistency of time series data, using the guidelines in Section 2.8.3:
Use the DeriveExceptions function in adjusting a calendar to be consistent with the time series. See Section 2.2.5 for more information about this approach.
Use the IsValidTS function to check that the time series is consistent with the calendar. See the IsValidTS function reference information in Chapter 5.
Formulating time series queries involves invoking time series or time scaling functions, or both. Example 3-2 uses the Mavg time series function to obtain 10-day moving average of the closing price for stock ACME for December 1996, and it uses the ScaleupSum time scaling function to obtain monthly trading volumes for stock ACME. (The results shown in the example reflect sample data for the Oracle8i Time Series usage demo.)
SELECT to_char(tstamp) tstamp, value FROM stockdemo_ts ts, TABLE (CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.Mavg(ts.close,to_date('01-DEC-96','DD-MON-YY'), to_date('31-DEC-96','DD-MON-YY'),10) ) AS ORDSYS.ORDTNumTab)) t WHERE ts.ticker='ACME'; 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. SELECT to_char(tstamp) tstamp, value FROM tsdev.stockdemo_ts ts, tsdev.stockdemo_calendars cal, TABLE (CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeScale.ScaleupSum(ts.volume, VALUE(cal)) ) AS ORDSYS.ORDTNumTab)) t WHERE ts.ticker='ACME' and cal.name='Monthly'; TSTAMP VALUE --------- ---------- 01-NOV-96 20000 01-DEC-96 21000 2 rows selected.
This section explains in greater detail the approaches to deriving calendar exceptions from time series data. These approaches were introduced in Section 2.2.5; see that section for information on concepts related to exceptions and the reasons for choosing a particular approach.
This approach to deriving exceptions takes a time series and optionally a date range as input parameters, using the following form of the function:
The input time series (inputTS) has an associated calendar and data for all the desired timestamps (for example, daily closing prices for stock XYZ for all trading days during the period for the time series or the date range bounded by startDate and endDate). A calendar is returned that is defined on the same pattern and frequency as the input calendar, and the exception lists of the returned calendar are populated to be consistent with the time series data. The exception lists are updated based on finding timestamps that are in the calendar pattern or in the time series, but not in both. (A timestamp is in the calendar pattern if it is within the date range of the calendar and maps to an on (1) bit in the pattern.)
The returned calendar's on- and off- exceptions are populated based on the calendar pattern and the time series, as follows:
For example, 04-Jul-1997 (Friday) is in the pattern of a stock trading calendar, but it is not a date on which U.S. stocks were traded.
The following example populates a calendar named Quarterly with exceptions based on the actual data in the unemployment_rate time series (in which data for some quarters is missing):
UPDATE myts_cal cal SET cal = (SELECT ORDSYS.TimeSeries.DeriveExceptions(ts.unemployment_rate) FROM myts ts WHERE ts.region = '1') WHERE cal.name = 'Quarterly';
This approach to deriving exceptions takes a calendar and an ORDTDateTab (that is, a table of dates) as input parameters, using the following form of the function:
DeriveExceptions(cal ORDTCalendar, DateTab ORDTDateTab
The table of dates (DateTab parameter) includes all dates in the time series, for example, all dates on which stock XYZ traded. A calendar is returned that is defined on the same pattern and frequency as the input calendar, and the exception lists of the returned calendar are populated to be consistent with the time series data in DateTab. The exception lists are updated based on finding timestamps that are in the calendar pattern or in the table of dates, but not in both. (A timestamp is in the calendar pattern if it is within the date range of the calendar and maps to an on (1) bit in the pattern.)
The returned calendar's on- and off- exceptions are populated based on the calendar pattern and the table of dates, as follows:
For example, 04-Jul-1997 (Friday) is in the pattern of a stock trading calendar, but it is not a date on which U.S. stocks were traded.
The following example derives the exceptions for all time series in the stockdemo table and updates the corresponding calendars in the stockdemo_calendars table:
UPDATE stockdemo_calendars cal SET cal = (SELECT ORDSYS.Calendar.DeriveExceptions( VALUE(cal), CAST(multiset( SELECT s.tstamp FROM stockdemo s WHERE cal.name = s.ticker) AS ORDSYS.ORDTDateTab)) FROM dual);
This approach (Approach 1A) to deriving calendar exceptions has the following requirements:
This approach to deriving exceptions takes two time series references as input parameters, using the following form of the function:
This overloading of the DeriveExceptions function allows the input parameters to be time series REFs (either two ORDTNumSeriesIOTRef parameters or two ORDTVarchar2SeriesIOTRef parameters).
Before calling DeriveExceptions, you must construct a time series based on a reference calendar. This time series will contain all the timestamps within the date range (minDate through maxDate) of the calendar.
The following example builds a reference time series based on a calendar named PATTERN-ONLY. An INSERT statement populates the time series named PATTERN-ONLY with the valid timestamps between the starting and ending dates of the calendar.
INSERT INTO stocks(ticker,tstamp) SELECT 'PATTERN-ONLY', t1.c1 FROM (SELECT column_value c1 FROM the (SELECT CAST(ORDSYS.Calendar.TimeStampsBetween(VALUE(cal), cal.mindate, cal.maxdate) AS ORDSYS.ORDTDateTab) FROM stock_calendars cal WHERE cal.name = 'PATTERN-ONLY')) t1;
The insertion is made directly into the underlying table, not into the relational view. Using the underlying table is safe here because the time series is presumed to be correct, so the mechanisms for ensuring consistency between the time series and the calendar provided by the relational view are not needed in this case.
The PATTERN-ONLY calendar should have no exceptions. If this calendar has any exceptions, the resulting time series will have exception lists that are not null, which will cause the DeriveExceptions function to report an error.
After you create the reference time series, call the DeriveExceptions function with the reference time series as the first parameter (series1). DeriveExceptions compares the dates in series1 with the dates in series2, and it returns the calendar of series2 with the exceptions created as follows:
For example, if series2 contains dates on which stock XYZ traded and 04-Jul-1997 (Friday) is not in that time series, then 04-Jul-1997 is added to the calendar as an off-exception.
The following example uses the reference time series created in the preceding statement to update the exception lists of every other calendar in the stockdemo_calendars table, with the exceptions for each calendar derived from the timestamps in the associated time series. (This example assumes that each calendar maps to a time series with the same name.)
UPDATE stockdemo_calendars cal SET cal = (SELECT ORDSYS.TimeSeries.DeriveExceptions(ts1.open,ts2.open) FROM stockdemo_ts ts1, stockdemo_ts ts2 WHERE ts1.ticker = 'PATTERN-ONLY' and ts2.ticker = cal.name) WHERE cal.name <> 'PATTERN-ONLY';
This approach (Approach 2) to deriving calendar exceptions has the following requirements:
Product-developer functions, described in Section 2.9.2, let you modify and expand the Oracle8i Time Series capabilities. For example, an ISV could develop additional time series analysis functions by calling product-developer functions.
The following example shows the use of the IsValidDate, NumTstampsBetween, and OffsetDate product-developer functions in a PL/SQL implementation of the Lead function. The Lead function accepts an input time series and a lead_date, and returns a time series where the starting timestamp is the lead_date. (Note that to simplify the presentation, some error checking has been omitted.)
create function Lead (ts ORDSYS.ORDTNumSeries, lead_date date) return ORDSYS.ORDTNumSeries is i integer; outts ORDSYS.ORDTNumSeries; /* Temporary Storage for Result */ new_tstamp date; /* Changeable version of lead_date */ last_lead_date date; /* Last timestamp of the output time series*/ first_tstamp date; /* First timestamp of the input time series */ last_index integer; /* Last index of the input time series */ last_tstamp date; /* Last timestamp of the input time series */ units integer; /* Number of timestamps between input and output time series */ ERR_LEAD_TSTAMP_BOUNDS constant integer := 20540; ERR_LEAD_TSTAMP_BOUNDS_MSG constant varchar2(100) := 'Projected lead timestamp beyond calendar bounds'; begin first_tstamp :=ts.series(1).tstamp; last_index :=ts.series.last; last_tstamp :=ts.series(last_index).tstamp; if ORDSYS.Calendar.IsValidDate(ts.cal, lead_date) = 0 then Raise_Application_Error(ERR_LEAD_TSTAMP_BOUNDS, ERR_LEAD_TSTAMP_BOUNDS_MSG); end if; /* units is the number of timestamps between the first timestamp of the input time series and lead_date. */ units := ORDSYS.Calendar.NumTimeStampsBetween(ts.cal, first_tstamp, lead_date); last_lead_date := ORDSYS.Calendar.OffsetDate(ts.cal, last_tstamp, units); if last_lead_date is null then Raise_Application_Error(ERR_LEAD_TSTAMP_BOUNDS, ERR_LEAD_TSTAMP_BOUNDS_MSG); end if; /* Instantiate output time series. */ outts := ORDSYS.ORDTNumSeries('Lead Result', ts.cal, ORDSYS.ORDTNumTab()); outts.series.extend(last_index); /* Assign the first timestamp of the output time series to first_lead_date. Copy value from input time series to output time series. */ new_tstamp := lead_date; outts.series(1) := ORDSYS.ORDTNumCell(new_tstamp, ts.series(1).value); /* Assign subsequent timestamps by calling OffsetDate with the previous date and an offset of 1. */ for i in 2..outts.series.last loop new_tstamp := ORDSYS.Calendar.OffsetDate(ts.cal, outts.series(i-1).tstamp, 1); outts.series(i) := ORDSYS.ORDTNumCell(new_tstamp, ts.series(i).value); end loop; return(outts); end;