Example
Set up
Consider
the SQL syntax for invoking a moving average.
The following query issues a 10-day moving average for the close price of
'ORCL' for the month of December 2000.
SELECT *
FROM
stockdemo_ts ts,
TABLE (CAST(ordsys.timeseries.ExtractTable(
ordsys.timeseries.Mavg(ts.close,
to_date('01-DEC-00','DD-MON-YY'),
to_date('31-DEC-00','DD-MON-YY'),
10)
) AS ordsys.ordtNumTab))
WHERE
ts.ticker='ORCL';
Here
we see the Mavg function invoked with the qualifier "close", the first
and last date in December 2000, and a window of size 10. The ExtractTable
function produces a nested table type (ordtNumTab), which is a table of DATE and
NUMBER columns.
Note
that the Reference View is included in the FROM-list of time series queries.
Also note that the 'ORCL' time series may contain many years of data, and the
query may only require a few of these data points.
For
a more complete context, we next consider the tables involved in one approach
for using the reference view.
First,
consider an example of an underlying table of time series detail data. OTS (Oracle Time Series) employs three basic tables to store
time series information:
1.
Time Series Detail Table
The
Detail Table stores the "raw" time series data. The detail table has
three types of columns:
Identifier
Column(s) - The detail table also has one or more identifier columns
(such as "ticker") used to identify the time series.
Timestamp
Column - A timestamp indicates the time when the observation(s) were
made.
Observation
Column(s) - The detail table has data for five observations columns,
corresponding to open, high, low, volume, and close.
These columns correspond to observed numerical values in the time series.
(Note that in general, a detail table may have an arbitrary number of
observation columns, and the names of these columns are also arbitrary).
An example time
series detail table is show below:
CREATE TABLE
stockdemo
(ticker
VARCHAR2(5),
tstamp
DATE,
open
NUMBER,
high
NUMBER,
low
NUMBER,
close
NUMBER,
volume
INTEGER,
CONSTRAINT
pk_stockdemo PRIMARY KEY (ticker, tstamp));
2.
Calendar Table
The
calendar table stores instances of calendars.
Calendars essentially describe ALL VALID TIMESTAMPS in a time series, and
are used as a navigational aid in time series functions to enable functions such
as lead/lag, annualized sum, and moving average.
The
calendar is implemented as an object type
CREATE TABLE
stockdemo_calendars of ordsys.ordtCalendar (
name
CONSTRAINT pk_stockdemo_calendars PRIMARY KEY);
The
fields of the type include the calendar name (for retrieval indexed by name),
the frequency of the time series, boundary dates, and other information relevant
to describing which dates are valid dates in a time series.
The
map table associates a time series identifier with a specific calendar:
CREATE TABLE
stockdemo_map (
qualname
VARCHAR2(20),
calendarname
VARCHAR2(20),
CONSTRAINT
pk_stockdemo_map PRIMARY KEY (qualname));
The
reference view is defined over the qualifier and observation columns.
CREATE OR REPLACE
VIEW stockdemo_ts(ticker,
open,
high,
low,
close,
volume)
AS
SELECT
map.qualname,
ordsys.ordtNumSeriesIOTRef(
map.qualname, Ref(cal), 'stockdemo_sv',
'tstamp', 'open', 'ticker', map.qualname),
ordsys.ordtNumSeriesIOTRef(
map.qualname, Ref(cal), 'stockdemo_sv',
'tstamp', 'high', 'ticker', map.qualname),
ordsys.ordtNumSeriesIOTRef(
map.qualname, Ref(cal), 'stockdemo_sv',
'tstamp', 'low', 'ticker', map.qualname),
ordsys.ordtNumSeriesIOTRef(
map.qualname, Ref(cal), 'stockdemo_sv',
'tstamp', 'close', 'ticker', map.qualname),
ordsys.ordtNumSeriesIOTRef(
map.qualname, Ref(cal), 'stockdemo_sv',
'tstamp', 'volume', 'ticker', map.qualname)
FROM
stockdemo_map map,
stockdemo_calendars cal
WHERE
map.calendarname = cal.name;
The
type ordsys.ordtNumSeriesIOTRef includes the following fields:
i)
name of returned time series
ii)
reference to the calendar
iii)
name of time series detail table
iv)
name of timestamp column
v)
name of observation column
vi)
name of qualifier column
vii)
qualifier name
As mentioned earlier, these fields are used by the
internal runtime to issue queries to the appropriate tables, and the appropriate
columns of these tables.
|