6
Time Scaling Functions: Reference

The Oracle8i Time Series library consists of:

• Data types (described in Section 2.3)

• Calendar functions (described in Chapter 4)

• Time series functions (described in Chapter 5)

• Time scaling functions (described in this chapter)

• Administrative tools procedures for creating time series schema objects (described in Chapter 7)

Calendar functions are mainly used by product developers, such as ISVs, to develop new time series functions and to administer and modify calendars.

Time series and time scaling functions and the administrative tools procedures are used mainly by application developers.

Syntax notes:

• The ORDSYS schema name and the package name must be used with the function name, although public synonyms can be created to eliminate the need for specifying the schema name (see Section 1.5). Each function is included in a PL/SQL package, such as Calendar, TimeSeries, or TimeScale. The ORDSYS schema name and the package name are included in the Format and in any examples.

• Function calls are not case sensitive, except for any quoted literal values. For example, the following code line excerpts are valid and semantically identical:

  select CAST(TimeSeries.ExtractTable(close) AS ORDTNumTab)
  select cast(TIMESERIES.extracttable(close) as ordtnumtab)
  select cast(TiMeSeRiEs.eXtRaCtTaBlE(ClosE) As ordtNUMtab)
  
  

• The syntax and examples show the reference-based interface (types ORDTNumSeriesIOTRef and ORDTVarchar2SeriesIOTRef).

All time series and time scaling functions accept both references and instances as parameters. (For example, an ORDTNumSeriesIOTRef parameter could also be ORDTNumSeries.) All time series functions return instances. Thus, if you nest functions, such as Cmax(Cmax(...), ...), the innermost nesting accepts a reference and returns an instance, and any other functions in the nesting accept an instance and return an instance.

For an explanation of the reference-based interface, see Section 2.7.2.

---

ScaledownInterpolate

Format

ORDSYS.TimeScale.ScaledownInterpolate(

[tsname VARCHAR2,]

inputTS ORDSYS.ORDTNumSeriesIOTRef,

targetCal ORDSYS.ORDTCalendar

[, startDate DATE

, endDate DATE]

) RETURN ORDSYS.ORDTNumSeries;

Description

Given a time series, a calendar to be used for scaling, and optionally starting and ending dates, returns a time series in which data values are interpolated between values in the input time series. For example, in a semi-annual (January and July) to month scaledown, if the data value for a January input timestamp is 100 and the data value for the next (July) input timestamp is 160, the data values for the monthly timestamps for January through June will be 100, 110, 120, 130, 140, and 150.

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

inputTS

The input time series.

targetCal

The calendar to be used for the scaling.

startDate

The starting date to be used. If startDate is specified, endDate must also be specified.

endDate

The ending date to be used. If endDate is specified, startDate must also be specified.

Usage

inputTS cannot be an irregular time series (a time series with no associated calendar).

An exception is returned for any of the following conditions:

• The input time series (inputTS) or the specified calendar (targetCal) is null.

• The frequency of the calendar on which the time series is based is shorter than the frequency of the specified calendar (for example, the time series calendar's frequency is day and the specified calendar's frequency is month).

• The frequency of the calendar on which the time series is based is incompatible for scaling to the frequency of the specified calendar because the smaller interval does not divide evenly into the larger interval. (For example, the time series calendar's frequency is month and the frequency of targetCal is week.)

• Any data in the input time series has no corresponding interval in the target time series.

• An interval of the input time series straddles two or more intervals of the target scaling calendar.

For an explanation of concepts related to time scaling, see Section 2.11.

Example

Scale quarterly unemployment rate values down to monthly values, using interpolation:

SELECT to_char(tstamp) tstamp, value
  FROM myts ts, tsdev.stockdemo_calendars cal,
     TABLE (CAST(ORDSYS.TimeSeries.ExtractTable(
         ORDSYS.TimeScale.ScaledownInterpolate(ts.unemployment_rate,
                                               VALUE(cal))
       ) AS ORDSYS.ORDTNumTab)) t
   WHERE ts.region='1' AND cal.name  ='Monthly';

Assume the following timestamps and values for unemployment_rate:

Timestamp	Value
01-Jan-1998	4.0
01-Apr-1998	3.2
01-Jul-1998	5.1
01-Oct-1998	3.9

This example might produce the following output:

TSTAMP    VALUE
--------- ----------
01-JAN-98          4 
01-FEB-98 3.72444444 
01-MAR-98 3.47555556 
01-APR-98        3.2 
01-MAY-98 4.15604396 
01-JUN-98 5.14395604 
01-JUL-98        6.1 
01-AUG-98 5.35869565 
01-SEP-98  4.6173913 
01-OCT-98        3.9 
10 rows selected. 

Note that only 10 rows are returned here, as opposed to 12 rows in the ScaledownRepeat example. Interpolation cannot be performed for the months of November and December in this example because the input time series does not contain a timestamp for the following January.

See also the Month function in Chapter 4 for an example of using a calendar-creation function (in this case, Month) to perform scaling, as opposed to specifying a stored calendar that has the desired frequency.

---

ScaledownRepeat

Format

ORDSYS.TimeScale.ScaledownRepeat(

[tsname VARCHAR2,]

inputTS ORDSYS.ORDTNumSeriesIOTRef,

targetCal ORDSYS.ORDTCalendar

[, startDate DATE

, endDate DATE]

) RETURN ORDSYS.ORDTNumSeries;

Description

Given a time series, a calendar to be used for scaling, and optionally starting and ending dates, returns a time series in which data values in the input time series are repeated. For example, in a semi-annual (January and July) to month scaledown, if the data value for a January input timestamp is 100 and the data value for the next (July) input timestamp is 160 (or any other value), the data values for the monthly timestamps for January through June will all be 100.

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

inputTS

The input time series.

targetCal

The calendar to be used for the scaling.

startDate

The starting date to be used. If startDate is specified, endDate must also be specified.

endDate

The ending date to be used. If endDate is specified, startDate must also be specified.

Usage

inputTS cannot be an irregular time series (a time series with no associated calendar).

An exception is returned for any of the following conditions:

• The input time series (inputTS) or the specified calendar (targetCal) is null.

• The input time series (inputTS) does not have an associated calendar.

• The frequency of the calendar on which the time series is based is shorter than the frequency of the specified calendar (for example, the time series calendar's frequency is day and the specified calendar's frequency is month).

• The frequency of the calendar on which the time series is based is incompatible for scaling to the frequency of the specified calendar because the smaller interval does not divide evenly into the larger interval. (For example, the time series calendar's frequency is month and the frequency of targetCal is week.)

• Any data in the input time series has no corresponding interval in the target time series.

• An interval of the input time series straddles two or more intervals of the target scaling calendar.

For an explanation of concepts related to time scaling, see Section 2.11.

Example

Scale quarterly unemployment rate values down to monthly values, using repetition:

SELECT to_char(tstamp) tstamp, value
  FROM myts ts, tsdev.stockdemo_calendars cal,
     TABLE (CAST(ORDSYS.TimeSeries.ExtractTable(
         ORDSYS.TimeScale.ScaledownRepeat(ts.unemployment_rate,
                                               VALUE(cal))
       ) AS ORDSYS.ORDTNumTab)) t
   WHERE ts.region='1' AND cal.name  ='Monthly';

Assume the following timestamps and values for unemployment_rate:

Timestamp	Value
01-Jan-1998	4.0
01-Apr-1998	3.2
01-Jul-1998	5.1
01-Oct-1998	3.9

This example might produce the following output:

TSTAMP    VALUE
--------- ----------
01-JAN-98          4
01-FEB-98          4
01-MAR-98          4
01-APR-98        3.2
01-MAY-98        3.2
01-JUN-98        3.2
01-JUL-98        6.1
01-AUG-98        6.1
01-SEP-98        6.1
01-OCT-98        3.9
01-NOV-98        3.9
01-DEC-98        3.9
12 rows selected.

Note that 12 rows are returned here, as opposed to only 10 rows in the ScaledownInterpolate example. Repetition is performed for the months of November and December based on the October value, and is not dependent on the value for the following January.

See also the Month function in Chapter 4 for an example of using a calendar-creation function (in this case, Month) to perform scaling, as opposed to specifying a stored calendar that has the desired frequency.

---

ScaledownSplit

Format

ORDSYS.TimeScale.ScaledownSplit(

[tsname VARCHAR2,]

inputTS ORDSYS.ORDTNumSeriesIOTRef,

targetCal ORDSYS.ORDTCalendar

[, startDate DATE

, endDate DATE]

) RETURN ORDSYS.ORDTNumSeries;

Description

Given a time series, a calendar to be used for scaling, and optionally starting and ending dates, returns a time series in which data values reflect the division of the data value in the input time series by the number of associated timestamps in the resulting time series. For example, in a semi-annual (January and July) to month scaledown, if the data value for a January input timestamp is 100 and the data value for the next (July) input timestamp is 160 (or any other value), the data values for the monthly timestamps for January through June will all be 16.667 (1/6 of 100).

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

inputTS

The input time series.

targetCal

The calendar to be used for the scaling.

startDate

The starting date to be used. If startDate is specified, endDate must also be specified.

endDate

The ending date to be used. If endDate is specified, startDate must also be specified.

Usage

inputTS cannot be an irregular time series (a time series with no associated calendar).

An exception is returned for any of the following conditions:

• The input time series (inputTS) or the specified calendar (targetCal) is null.

• The input time series (inputTS) does not have an associated calendar.

• The frequency of the calendar on which the time series is based is shorter than the frequency of the specified calendar (for example, the time series calendar's frequency is day and the specified calendar's frequency is month).

• The frequency of the calendar on which the time series is based is incompatible for scaling to the frequency of the specified calendar because the smaller interval does not divide evenly into the larger interval. (For example, the time series calendar's frequency is month and the frequency of targetCal is week.)

• Any data in the input time series has no corresponding interval in the target time series.

• An interval of the input time series straddles two or more intervals of the target scaling calendar.

For an explanation of concepts related to time scaling, see Section 2.11.

Example

Scale quarterly widget production values down to monthly values, dividing each quarter's value evenly among the three months in that quarter:

SELECT to_char(tstamp) tstamp, value
  FROM myts ts, tsdev.stockdemo_calendars cal,
     TABLE (CAST(ORDSYS.TimeSeries.ExtractTable(
         ORDSYS.TimeScale.ScaledownSplit(ts.widget_production,
                                               VALUE(cal))
       ) AS ORDSYS.ORDTNumTab)) t
   WHERE ts.region='1' AND cal.name  ='Monthly';

With quarterly widget_production values of 1000, 1500, 900, and 1200, this example might produce the following output:

TSTAMP    VALUE
--------- ----------
01-JAN-98 333.333333
01-FEB-98 333.333333
01-MAR-98 333.333333
01-APR-98        500
01-MAY-98        500
01-JUN-98        500
01-JUL-98        300
01-AUG-98        300
01-SEP-98        300
01-OCT-98        400
01-NOV-98        400
01-DEC-98        400
12 rows selected.

For example, one-third (333.33...) of the quarterly value of 1000 for 01-Jan is returned as the monthly value for 01-Jan, 01-Feb, and 01-Mar.

See also the Month function in Chapter 4 for an example of using a calendar-creation function (in this case, Month) to perform scaling, as opposed to specifying a stored calendar that has the desired frequency.

---

ScaleupAvg

Format

ORDSYS.TimeScale.ScaleupAvg(

[tsname VARCHAR2,]

inputTS ORDSYS.ORDTNumSeriesIOTRef,

targetCal ORDSYS.ORDTCalendar

[, startDate DATE

, endDate DATE]

[,options]

) RETURN ORDSYS.ORDTNumSeries;

Description

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 values.

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

inputTS

The input time series.

targetCal

The calendar to be used for the scaling.

startDate

The starting date to be used. If startDate is specified, endDate must also be specified.

endDate

The ending date to be used. If endDate is specified, startDate must also be specified.

options

Either or both of the following options:

• ORDSYS.TimeScale.IgnoreNulls (default) | IgnoreNullsOFF. The default is to ignore null input values in performing the scaleup.

• ORDSYS.TimeScale.DiscardError (default) | DiscardErrorOFF. The default is to raise an exception if any data in the input time series has no corresponding interval in the target time series.

See Section 2.11.2 for detailed information about these options and examples of their use.

Usage

An exception is returned for any of the following conditions:

• The input time series (ts) or the specified calendar (targetCal) is null.

• The frequency of the calendar on which the time series is based is greater than the frequency of the specified calendar (for example, the time series calendar's frequency is month and the specified calendar's frequency is day).

• The frequency of the calendar on which the time series is based is incompatible for scaling to the frequency of the specified calendar because the smaller interval does not divide evenly into the larger interval. (For example, the time series calendar's frequency is week and the frequency of targetCal is month.)

• Any data in the input time series has no corresponding interval in the target time series and DiscardErrorOFF is not specified.

• An interval of the input time series straddles two or more intervals of the target scaling calendar.

For an explanation of concepts related to time scaling, see Section 2.11.

Example

Return the average closing prices for stock SAMCO for each month for the entire time series:

SELECT to_char(tstamp) tstamp, value
  FROM tsdev.stockdemo_ts ts, tsdev.stockdemo_calendars cal,
     TABLE (CAST(ORDSYS.TimeSeries.ExtractTable(
         ORDSYS.TimeScale.ScaleupAvg(ts.close,
                             VALUE(cal))
       ) AS ORDSYS.ORDTNumTab)) t
   WHERE ts.ticker='SAMCO' and cal.name='Monthly';

This example might produce the following output:

TSTAMP    VALUE
--------- ----------
01-NOV-96   39.83125
01-DEC-96 38.2738095
2 rows selected.

See also the Month function in Chapter 4 for an example of using a calendar-creation function (in this case, Month) to perform scaling, as opposed to specifying a stored calendar that has the desired frequency.

---

ScaleupAvgX

Format

ORDSYS.TimeScale.ScaleupAvgX(

[tsname VARCHAR2,]

ts ORDSYS.ORDTNumSeriesIOTRef,

calendar ORDSYS.ORDTCalendar

[, startDate DATE

, endDate DATE]

[,options]

) RETURN ORDSYS.ORDTNumSeries;

Description

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 values plus the immediately preceding source period.

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

ts

The input time series.

calendar

The calendar to be used for the scaling.

startDate

The starting date to be used. If startDate is specified, endDate must also be specified.

endDate

The ending date to be used. If endDate is specified, startDate must also be specified.

options

Either or both of the following options:

• ORDSYS.TimeScale.IgnoreNulls (default) | IgnoreNullsOFF. The default is to ignore null input values in performing the scaleup.

• ORDSYS.TimeScale.DiscardError (default) | DiscardErrorOFF. The default is to raise an exception if any data in the input time series has no corresponding interval in the target time series.

See Section 2.11.2 for detailed information about these options and examples of their use.

Usage

ScaleupAvgX is like ScaleupAvg, except that ScaleupAvgX also considers the last timestamp before the current scaling period. For example:

• The monthly average closing price for January for a stock is the average of the closing price on trading days in January and the last trading day in December.

• The quarterly average unemployment rate for the first calendar quarter (scaling up from monthly data) is the average of the rates for December, January, February, and March.

An exception is returned for any of the following conditions:

• The input time series (ts) or the specified calendar (targetCal) is null.

• The frequency of the calendar on which the time series is based is greater than the frequency of the specified calendar (for example, the time series calendar's frequency is month and the specified calendar's frequency is day).

• The frequency of the calendar on which the time series is based is incompatible for scaling to the frequency of the specified calendar because the smaller interval does not divide evenly into the larger interval. (For example, the time series calendar's frequency is week and the frequency of targetCal is month.)

• Any data in the input time series has no corresponding interval in the target time series and DiscardErrorOFF is not specified.

• An interval of the input time series straddles two or more intervals of the target scaling calendar.

For an explanation of concepts related to time scaling, see Section 2.11.

Example

Return the average closing prices for stock SAMCO for each month plus the last trading day of the preceding month for the entire time series:

SELECT to_char(tstamp) tstamp, value
  FROM tsdev.stockdemo_ts ts, tsdev.stockdemo_calendars cal,
     TABLE (CAST(ORDSYS.TimeSeries.ExtractTable(
         ORDSYS.TimeScale.ScaleupAvgX(ts.close,
                             VALUE(cal))
       ) AS ORDSYS.ORDTNumTab)) t
   WHERE ts.ticker='SAMCO' and cal.name='Monthly';

This example might produce the following output:

TSTAMP    VALUE
--------- ----------
01-NOV-96   39.83125
01-DEC-96 38.2727273
2 rows selected.

Note that the value for 01-Dec-1996 in this example is different from the value in the ScaleupAvg example, because this ScaleupAvgX value for 01-Dec considers the closing price for the last timestamp in November. (There is no October data in the stockdemo_ts table, and thus the 01-Nov values are the same in the ScaleupAvg and ScaleupAvgX examples.)

See also the Month function in Chapter 4 for an example of using a calendar-creation function (in this case, Month) to perform scaling, as opposed to specifying a stored calendar that has the desired frequency.

---

ScaleupCount

Format

ORDSYS.TimeScale.ScaleupCount(

[tsname VARCHAR2,]

ts ORDSYS.ORDTNumSeriesIOTRef,

calendar ORDSYS.ORDTCalendar

[, startDate DATE

, endDate DATE]

[,options]

) RETURN ORDSYS.ORDTNumSeries;

Description

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 of values.

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

ts

The input time series.

calendar

The calendar to be used for the scaling.

startDate

The starting date to be used. If startDate is specified, endDate must also be specified.

endDate

The ending date to be used. If endDate is specified, startDate must also be specified.

options

Either or both of the following options:

• ORDSYS.TimeScale.IgnoreNulls (default) | IgnoreNullsOFF. The default is to ignore null input values in performing the scaleup.

• ORDSYS.TimeScale.DiscardError (default) | DiscardErrorOFF. The default is to raise an exception if any data in the input time series has no corresponding interval in the target time series.

See Section 2.11.2 for detailed information about these options and examples of their use.

Usage

An exception is returned for any of the following conditions:

• The input time series (ts) or the specified calendar (targetCal) is null.

• The frequency of the calendar on which the time series is based is greater than the frequency of the specified calendar (for example, the time series calendar's frequency is month and the specified calendar's frequency is day).

• The frequency of the calendar on which the time series is based is incompatible for scaling to the frequency of the specified calendar because the smaller interval does not divide evenly into the larger interval. (For example, the time series calendar's frequency is week and the frequency of targetCal is month.)

• Any data in the input time series has no corresponding interval in the target time series and DiscardErrorOFF is not specified.

• An interval of the input time series straddles two or more intervals of the target scaling calendar.

For an explanation of concepts related to time scaling, see Section 2.11.

Example

Return the monthly count of daily closing prices for stock SAMCO for the period 01-Nov-1996 through 31-December 1996:

SELECT to_char(tstamp) tstamp, value
  FROM tsdev.stockdemo_ts ts, tsdev.stockdemo_calendars cal,
     TABLE (CAST(ORDSYS.TimeSeries.ExtractTable(
         ORDSYS.TimeScale.ScaleupCount(ts.close,
                   VALUE(cal),
                   to_date('01-NOV-1996','DD-MON-YYYY'),
                   to_date('31-DEC-1996','DD-MON-YYYY'))
       ) AS ORDSYS.ORDTNumTab)) t
   WHERE ts.ticker='SAMCO' and cal.name='Monthly';

This example might produce the following output:

TSTAMP    VALUE
--------- ----------
01-NOV-96         20
01-DEC-96         21
2 rows selected.

See also the Month function in Chapter 4 for an example of using a calendar-creation function (in this case, Month) to perform scaling, as opposed to specifying a stored calendar that has the desired frequency.

---

ScaleupFirst

Format

ORDSYS.TimeScale.ScaleupFirst(

[tsname VARCHAR2,]

ts ORDSYS.ORDTNumSeriesIOTRef,

calendar ORDSYS.ORDTCalendar

[, startDate DATE

, endDate DATE]

[,options]

) RETURN ORDSYS.ORDTNumSeries;

Description

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.

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

ts

The input time series.

calendar

The calendar to be used for the scaling.

startDate

The starting date to be used. If startDate is specified, endDate must also be specified.

endDate

The ending date to be used. If endDate is specified, startDate must also be specified.

options

Either or both of the following options:

• ORDSYS.TimeScale.IgnoreNulls (default) | IgnoreNullsOFF. The default is to ignore null input values in performing the scaleup.

  If IgnoreNulls (the default) is enabled, the first non-null value of the group is returned (unless all values of the group are null, in which case a null is returned). If IgnoreNullsOFF is enabled, the first value of the group is returned.

• ORDSYS.TimeScale.DiscardError (default) | DiscardErrorOFF. The default is to raise an exception if any data in the input time series has no corresponding interval in the target time series.

See Section 2.11.2 for detailed information about these options and examples of their use.

Usage

An exception is returned for any of the following conditions:

• The input time series (ts) or the specified calendar (targetCal) is null.

• The frequency of the calendar on which the time series is based is greater than the frequency of the specified calendar (for example, the time series calendar's frequency is month and the specified calendar's frequency is day).

• The frequency of the calendar on which the time series is based is incompatible for scaling to the frequency of the specified calendar because the smaller interval does not divide evenly into the larger interval. (For example, the time series calendar's frequency is week and the frequency of targetCal is month.)

• Any data in the input time series has no corresponding interval in the target time series and DiscardErrorOFF is not specified.

• An interval of the input time series straddles two or more intervals of the target scaling calendar.

For an explanation of concepts related to time scaling, see Section 2.11.

Example

Return the first closing prices for stock SAMCO for the months of November and December of 1996:

SELECT to_char(tstamp) tstamp, value
  FROM tsdev.stockdemo_ts ts, tsdev.stockdemo_calendars cal,
     TABLE (CAST(ORDSYS.TimeSeries.ExtractTable(
         ORDSYS.TimeScale.ScaleupFirst(ts.close,
                   VALUE(cal),
                   to_date('01-NOV-1996','DD-MON-YYYY'),
                   to_date('31-DEC-1996','DD-MON-YYYY'))
       ) AS ORDSYS.ORDTNumTab)) t
   WHERE ts.ticker='SAMCO' and cal.name='Monthly';

This example might produce the following output:

TSTAMP    VALUE
--------- ----------
01-NOV-96     41.875
01-DEC-96     38.125
2 rows selected.

See also the Month function in Chapter 4 for an example of using a calendar-creation function (in this case, Month) to perform scaling, as opposed to specifying a stored calendar that has the desired frequency.

---

ScaleupGMean

Format

ORDSYS.TimeScale.ScaleupGMean(

[tsname VARCHAR2,]

ts ORDSYS.ORDTNumSeriesIOTRef,

calendar ORDSYS.ORDTCalendar

[, startDate DATE

, endDate DATE]

[,options]

) RETURN ORDSYS.ORDTNumSeries;

Description

Given a time series, a calendar to be used for scaling, and optionally starting and ending dates, returns a time series reflecting the geometric mean of each scaled group of values.

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

ts

The input time series.

calendar

The calendar to be used for the scaling.

startDate

The starting date to be used. If startDate is specified, endDate must also be specified.

endDate

The ending date to be used. If endDate is specified, startDate must also be specified.

options

Either or both of the following options:

• ORDSYS.TimeScale.IgnoreNulls (default) | IgnoreNullsOFF. The default is to ignore null input values in performing the scaleup.

• ORDSYS.TimeScale.DiscardError (default) | DiscardErrorOFF. The default is to raise an exception if any data in the input time series has no corresponding interval in the target time series.

See Section 2.11.2 for detailed information about these options and examples of their use.

Usage

The geometric mean of each scaled group is computed by taking the sum of the logarithms (base 10) of the values for the corresponding source period, and then raising 10 to the power of the logarithm sum divided by the number of elements in the corresponding source period. That is: POWER(10, log_sum/number_elements).

An exception is returned for any of the following conditions:

• The input time series (ts) or the specified calendar (targetCal) is null.

• The frequency of the calendar on which the time series is based is greater than the frequency of the specified calendar (for example, the time series calendar's frequency is month and the specified calendar's frequency is day).

• The frequency of the calendar on which the time series is based is incompatible for scaling to the frequency of the specified calendar because the smaller interval does not divide evenly into the larger interval. (For example, the time series calendar's frequency is week and the frequency of targetCal is month.)

• Any data in the input time series has no corresponding interval in the target time series and DiscardErrorOFF is not specified.

• An interval of the input time series straddles two or more intervals of the target scaling calendar.

For an explanation of concepts related to time scaling, see Section 2.11.

Example

Return the geometric mean of closing prices for stock SAMCO for each month for the entire time series:

SELECT to_char(tstamp) tstamp, value
  FROM tsdev.stockdemo_ts ts, tsdev.stockdemo_calendars cal,
     TABLE (CAST(ORDSYS.TimeSeries.ExtractTable(
         ORDSYS.TimeScale.ScaleupGMean(ts.close,
                                      VALUE(cal))
       ) AS ORDSYS.ORDTNumTab)) t
   WHERE ts.ticker='SAMCO' and cal.name='Monthly';

This example might produce the following output:

TSTAMP    VALUE
--------- ----------
01-NOV-96 39.7833842
01-DEC-96 38.2719057
2 rows selected.

See also the Month function in Chapter 4 for an example of using a calendar-creation function (in this case, Month) to perform scaling, as opposed to specifying a stored calendar that has the desired frequency.

---

ScaleupLast

Format

ORDSYS.TimeScale.ScaleupLast(

[tsname VARCHAR2,]

ts ORDSYS.ORDTNumSeriesIOTRef,

calendar ORDSYS.ORDTCalendar

[, startDate DATE

, endDate DATE]

[,options]

) RETURN ORDSYS.ORDTNumSeries;

Description

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.

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

ts

The input time series.

calendar

The calendar to be used for the scaling.

startDate

The starting date to be used. If startDate is specified, endDate must also be specified.

endDate

The ending date to be used. If endDate is specified, startDate must also be specified.

options

Either or both of the following options:

• ORDSYS.TimeScale.IgnoreNulls (default) | IgnoreNullsOFF. The default is to ignore null input values in performing the scaleup.

  If IgnoreNulls (the default) is enabled, the last non-null value of the group is returned (unless all values of the group are null, in which case a null is returned). If IgnoreNullsOFF is enabled, the last value of the group is returned.

• ORDSYS.TimeScale.DiscardError (default) | DiscardErrorOFF. The default is to raise an exception if any data in the input time series has no corresponding interval in the target time series.

See Section 2.11.2 for detailed information about these options and examples of their use.

Usage

An exception is returned for any of the following conditions:

• The input time series (ts) or the specified calendar (targetCal) is null.

• The frequency of the calendar on which the time series is based is greater than the frequency of the specified calendar (for example, the time series calendar's frequency is month and the specified calendar's frequency is day).

• The frequency of the calendar on which the time series is based is incompatible for scaling to the frequency of the specified calendar because the smaller interval does not divide evenly into the larger interval. (For example, the time series calendar's frequency is week and the frequency of targetCal is month.)

• Any data in the input time series has no corresponding interval in the target time series and DiscardErrorOFF is not specified.

• An interval of the input time series straddles two or more intervals of the target scaling calendar.

For an explanation of concepts related to time scaling, see Section 2.11.

Example

Return the last closing prices for stock SAMCO for the months of November and December of 1996:

SELECT to_char(tstamp) tstamp, value
  FROM tsdev.stockdemo_ts ts, tsdev.stockdemo_calendars cal,
     TABLE (CAST(ORDSYS.TimeSeries.ExtractTable(
         ORDSYS.TimeScale.ScaleupLast(ts.close,
                   VALUE(cal),
                   to_date('01-NOV-1996','DD-MON-YYYY'),
                   to_date('31-DEC-1996','DD-MON-YYYY'))
       ) AS ORDSYS.ORDTNumTab)) t
   WHERE ts.ticker='SAMCO' and cal.name='Monthly';

This example might produce the following output:

TSTAMP    VALUE
--------- ----------
01-NOV-96      38.25
01-DEC-96      39.75
2 rows selected.

Note that each timestamp reflects the first date of the month in the calendar (following the convention illustrated in Table 2-3 in Section 2.2.2), and each value in this case reflects the closing price on the last date for that month in the calendar.

See also the Month function in Chapter 4 for an example of using a calendar-creation function (in this case, Month) to perform scaling, as opposed to specifying a stored calendar that has the desired frequency.

---

ScaleupMax

Format

ORDSYS.TimeScale.ScaleupMax(

[tsname VARCHAR2,]

ts ORDSYS.ORDTNumSeriesIOTRef,

calendar ORDSYS.ORDTCalendar

[, startDate DATE

, endDate DATE]

[,options]

) RETURN ORDSYS.ORDTNumSeries;

Description

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.

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

ts

The input time series.

calendar

The calendar to be used for the scaling.

startDate

The starting date to be used. If startDate is specified, endDate must also be specified.

endDate

The ending date to be used. If endDate is specified, startDate must also be specified.

options

Either or both of the following options:

• ORDSYS.TimeScale.IgnoreNulls (default) | IgnoreNullsOFF. The default is to ignore null input values in performing the scaleup.

• ORDSYS.TimeScale.DiscardError (default) | DiscardErrorOFF. The default is to raise an exception if any data in the input time series has no corresponding interval in the target time series.

See Section 2.11.2 for detailed information about these options and examples of their use.

Usage

An exception is returned for any of the following conditions:

• The input time series (ts) or the specified calendar (targetCal) is null.

• The frequency of the calendar on which the time series is based is greater than the frequency of the specified calendar (for example, the time series calendar's frequency is month and the specified calendar's frequency is day).

• The frequency of the calendar on which the time series is based is incompatible for scaling to the frequency of the specified calendar because the smaller interval does not divide evenly into the larger interval. (For example, the time series calendar's frequency is week and the frequency of targetCal is month.)

• Any data in the input time series has no corresponding interval in the target time series and DiscardErrorOFF is not specified.

• An interval of the input time series straddles two or more intervals of the target scaling calendar.

For an explanation of concepts related to time scaling, see Section 2.11.

Example

Return the highest (maximum) closing prices for stock SAMCO for each month in the entire time series:

SELECT to_char(tstamp) tstamp, value
  FROM tsdev.stockdemo_ts ts, tsdev.stockdemo_calendars cal,
     TABLE (CAST(ORDSYS.TimeSeries.ExtractTable(
         ORDSYS.TimeScale.ScaleupMax(ts.close,
                                      VALUE(cal))
       ) AS ORDSYS.ORDTNumTab)) t
   WHERE ts.ticker='SAMCO' and cal.name='Monthly';

This example might produce the following output:

TSTAMP    VALUE
--------- ----------
01-NOV-96      43.75
01-DEC-96      39.75
2 rows selected.

See also the Month function in Chapter 4 for an example of using a calendar-creation function (in this case, Month) to perform scaling, as opposed to specifying a stored calendar that has the desired frequency.

---

ScaleupMin

Format

ORDSYS.TimeScale.ScaleupMin(

[tsname VARCHAR2,]

ts ORDSYS.ORDTNumSeriesIOTRef,

calendar ORDSYS.ORDTCalendar

[, startDate DATE

, endDate DATE]

[,options]

) RETURN ORDSYS.ORDTNumSeries;

Description

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.

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

ts

The input time series.

calendar

The calendar to be used for the scaling.

startDate

The starting date to be used. If startDate is specified, endDate must also be specified.

endDate

The ending date to be used. If endDate is specified, startDate must also be specified.

options

Either or both of the following options:

• ORDSYS.TimeScale.IgnoreNulls (default) | IgnoreNullsOFF. The default is to ignore null input values in performing the scaleup.

• ORDSYS.TimeScale.DiscardError (default) | DiscardErrorOFF. The default is to raise an exception if any data in the input time series has no corresponding interval in the target time series.

See Section 2.11.2 for detailed information about these options and examples of their use.

Usage

An exception is returned for any of the following conditions:

• The input time series (ts) or the specified calendar (targetCal) is null.

• The frequency of the calendar on which the time series is based is greater than the frequency of the specified calendar (for example, the time series calendar's frequency is month and the specified calendar's frequency is day).

• The frequency of the calendar on which the time series is based is incompatible for scaling to the frequency of the specified calendar because the smaller interval does not divide evenly into the larger interval. (For example, the time series calendar's frequency is week and the frequency of targetCal is month.)

• Any data in the input time series has no corresponding interval in the target time series and DiscardErrorOFF is not specified.

• An interval of the input time series straddles two or more intervals of the target scaling calendar.

For an explanation of concepts related to time scaling, see Section 2.11.

Example

Return the lowest (minimum) closing prices for stock SAMCO for each month in the entire time series:

SELECT to_char(tstamp) tstamp, value
  FROM tsdev.stockdemo_ts ts, tsdev.stockdemo_calendars cal,
     TABLE (CAST(ORDSYS.TimeSeries.ExtractTable(
         ORDSYS.TimeScale.ScaleupMin(ts.close,
                                      VALUE(cal))
       ) AS ORDSYS.ORDTNumTab)) t
   WHERE ts.ticker='SAMCO' and cal.name='Monthly';

This example might produce the following output:

TSTAMP    VALUE
--------- ----------
01-NOV-96     37.375
01-DEC-96     37.875
2 rows selected.

See also the Month function in Chapter 4 for an example of using a calendar-creation function (in this case, Month) to perform scaling, as opposed to specifying a stored calendar that has the desired frequency.

---

ScaleupSum

Format

ORDSYS.TimeScale.ScaleupSum(

[tsname VARCHAR2,]

ts ORDSYS.ORDTNumSeriesIOTRef,

calendar ORDSYS.ORDTCalendar

[, startDate DATE

, endDate DATE]

[,options]

) RETURN ORDSYS.ORDTNumSeries;

Description

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.

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

ts

The input time series.

calendar

The calendar to be used for the scaling.

startDate

The starting date to be used. If startDate is specified, endDate must also be specified.

endDate

The ending date to be used. If endDate is specified, startDate must also be specified.

options

Either or both of the following options:

• ORDSYS.TimeScale.IgnoreNulls (default) | IgnoreNullsOFF. The default is to ignore null input values in performing the scaleup.

• ORDSYS.TimeScale.DiscardError (default) | DiscardErrorOFF. The default is to raise an exception if any data in the input time series has no corresponding interval in the target time series.

See Section 2.11.2 for detailed information about these options and examples of their use.

Usage

An exception is returned for any of the following conditions:

• The input time series (ts) or the specified calendar (targetCal) is null.

• The frequency of the calendar on which the time series is based is greater than the frequency of the specified calendar (for example, the time series calendar's frequency is month and the specified calendar's frequency is day).

• The frequency of the calendar on which the time series is based is incompatible for scaling to the frequency of the specified calendar because the smaller interval does not divide evenly into the larger interval. (For example, the time series calendar's frequency is week and the frequency of targetCal is month.)

• Any data in the input time series has no corresponding interval in the target time series and DiscardErrorOFF is not specified.

• An interval of the input time series straddles two or more intervals of the target scaling calendar.

For an explanation of concepts related to time scaling, see Section 2.11.

Example

Return the sum of the daily trade volume for stock SAMCO for each month in the time series:

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='SAMCO' and cal.name='Monthly';

This example might produce the following output:

TSTAMP    VALUE
--------- ----------
01-NOV-96   10207000
01-DEC-96    3719450
2 rows selected.

Note that the following example uses the Month function to produce the same output. Using the Month function eliminates the need to have and specify a stored calendar with a month frequency.

SELECT to_char(tstamp) tstamp, value
  FROM tsdev.stockdemo_ts ts, 
     TABLE (CAST(ORDSYS.TimeSeries.ExtractTable(
         ORDSYS.TimeScale.ScaleupSum(ts.volume,
                   ORDSYS.Calendar.Month())
       ) AS ORDSYS.ORDTNumTab)) t
   WHERE ts.ticker='SAMCO';

---

ScaleupSumAnnual

Format

ORDSYS.TimeScale.ScaleupSumAnnual(

[tsname VARCHAR2,]

ts ORDSYS.ORDTNumSeriesIOTRef,

calendar ORDSYS.ORDTCalendar,

annualfactor

[, startDate DATE

, endDate DATE]

[,options]

) RETURN ORDSYS.ORDTNumSeries;

Description

Given a time series, a calendar to be used for scaling, and optionally starting and ending dates, returns a time series reflecting the sum, expressed as an annual rate, of each scaled group of values.

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

ts

The input time series.

calendar

The calendar to be used for the scaling.

annualfactor

The factor by which to multiply the sum of each scaled group in order to obtain the desired annualized value. You must specify a value, either null or not null. If you specify a null value, a default value is used depending on the frequency of calendar, as shown in Table 6-1.

Table 6-1 annualfactor Default Values for ScaleupSumAnnual

Frequency	annualfactor Default Value
second	31536000
minute	525600
hour	8760
day	365
week	52
month	12
quarter	4
year	1
10-day	36
semi-monthly	24
semi-annual	2

startDate

The starting date to be used. If startDate is specified, endDate must also be specified.

endDate

The ending date to be used. If endDate is specified, startDate must also be specified.

options

Either or both of the following options:

• ORDSYS.TimeScale.IgnoreNulls (default) | IgnoreNullsOFF. The default is to ignore null input values in performing the scaleup.

• ORDSYS.TimeScale.DiscardError (default) | DiscardErrorOFF. The default is to raise an exception if any data in the input time series has no corresponding interval in the target time series.

See Section 2.11.2 for detailed information about these options and examples of their use.

Usage

ScaleupSumAnnual is like ScaleupSum, except that ScaleupSumAnnual converts each scaled group to an annual rate by multiplying the scaled group's value by the annualfactor value.

The pattern and exceptions lists of calendar are not considered.

An exception is returned for any of the following conditions:

• The input time series (ts) or the specified calendar (targetCal) is null.

• The frequency of the calendar on which the time series is based is greater than the frequency of the specified calendar (for example, the time series calendar's frequency is month and the specified calendar's frequency is day).

• The frequency of the calendar on which the time series is based is incompatible for scaling to the frequency of the specified calendar because the smaller interval does not divide evenly into the larger interval. (For example, the time series calendar's frequency is week and the frequency of targetCal is month.)

• Any data in the input time series has no corresponding interval in the target time series and DiscardErrorOFF is not specified.

• An interval of the input time series straddles two or more intervals of the target scaling calendar.

For an explanation of concepts related to time scaling, see Section 2.11.

Example

Return the sum of the daily trade volume for stock SAMCO for each month in the entire time series, with each month's value expressed as if it were an annual value. In this case, each monthly value is computed and then multiplied by 12, the default annualfactor for monthly data.

SELECT to_char(tstamp) tstamp, value
  FROM tsdev.stockdemo_ts ts, tsdev.stockdemo_calendars cal,
     TABLE (CAST(ORDSYS.TimeSeries.ExtractTable(
         ORDSYS.TimeScale.ScaleupSumAnnual(ts.volume,
                   VALUE(cal),
                   NULL)
       ) AS ORDSYS.ORDTNumTab)) t
   WHERE ts.ticker='SAMCO' and cal.name='Monthly';

This example might produce the following output. (Note that each value is 12 times the corresponding value in the ScaleupAvg example.)

TSTAMP    VALUE
--------- ----------
01-NOV-96  122484000
01-DEC-96   44633400
2 rows selected.

See also the ScaleupSum function in this chapter and the Month function in Chapter 4 for examples of using a calendar-creation function (in this case, Month) to perform scaling, as opposed to specifying a stored calendar that has the desired frequency.

---