Apply function

Syntax

Apply(sql_express  lvarchar, 
     ts           TimeSeries, ...) 
returns TimeSeries;

Apply(sql_express  lvarchar, 
     multiset_ts  multiset(TimeSeries)) 
returns TimeSeries;

Apply(sql_express  lvarchar, 
     filter       lvarchar, 
     ts           TimeSeries, ...) 
returns TimeSeries;

Apply(sql_express lvarchar, 
     filter      lvarchar, 
     multiset_ts multiset(TimeSeries)) 
returns TimeSeries;

Apply(sql_express lvarchar, 
     begin_stamp datetime year to fraction(5), 
     end_stamp   datetime year to fraction(5), 
     ts          TimeSeries, ...) 
returns TimeSeries with (handlesnulls);

Apply(sql_express lvarchar, 
     begin_stamp datetime year to fraction(5), 
     end_stamp    datetime year to fraction(5), 
     multiset_ts multiset(TimeSeries)) 
returns TimeSeries with (handlesnulls);

Apply(sql_express lvarchar, 
     filter      lvarchar, 
     begin_stamp datetime year to fraction(5), 
     end_stamp   datetime year to fraction(5), 
     ts          TimeSeries, ...) 
returns TimeSeries with (handlesnulls);

Apply(sql_express lvarchar, 
     filter      lvarchar, 
     begin_stamp datetime year to fraction(5), 
     end_stamp   datetime year to fraction(5), 
     multiset_ts multiset(TimeSeries)) 
returns TimeSeries with (handlesnulls);

sql_express

The SQL expression or function to evaluate.

filter

The filter expression used to select time series elements.

begin_stamp

The begin point of the range. See Clip function for more detail about range specifications.

end_stamp

The end point of the range. See Clip function for more detail about range specifications.

ts

The first ts argument is the first series, the second ts argument is the second series, and so on. This function can take up to eight ts arguments. The order of the arguments must correspond to the desired order in the SQL expression or function. There is no limit to the number of $ parameters in the expression.

multiset_ts

A multiset of time series.

Description

This function runs a user-specified SQL expression on the given time series and produces a new time series containing the result of the expression at each qualifying element of the input time series.

You can qualify the elements from the input time series by specifying a time period to clip and by using a filter expression.

The sql_express argument is a comma-separated list of expressions to run for each selected element. There is no limit to the number of expressions you can run. The results of the expressions must match the corresponding columns of the result time series minus the first time stamp column. Do not specify the first time stamp as the first expression; the first time stamp is generated for each expression result.

The parameters to the expression can be an input element or any column of an input time series. Use the following format:

$ts_position.col_number

The ts_position is the position of a given time series on the input time series list. The col_number is the number of the column in the TimeSeries data type. Both the position number and column number are zero-based. For example, $0 means the element of the first input time series, $0.0 represents its time stamp column, and $0.1 is the column following the time stamp column. Another way to refer to a column is to use the column name directly, instead of the column number. Suppose the second time series has a column called high then you can use $1.high to refer to it. If the high column is the second column in the element, $1.high is equivalent to $1.1.

If the column of the input time series is a BSON column, you can specify a field with the following format:

$ts_position.bson_column_name.path.field

The ts_position is the position of a given time series on the input time series list. You cannot specify the column number. If the BSON top-level field name is the same as another column in the TimeSeries data type, you must include the bson_column_name. Otherwise, omit the bson_column_name. The path is the path to the field within the BSON document, with each part of the path separated by a period. The field is the field name. For example, $1.employee.id represents the second input time series and if the TimeSeries data type does not contain an employee column, employee.id is processed as a path to the value in the BSON column in the TimeSeries data type. The BSON field is automatically cast to a FLOAT data type, however, you can explicitly cast a BSON field to an INTEGER, BIGINT, or LVARCHAR data type. For example, you can cast the employee.id field to an INTEGER data type: $1.employee.id::integer. For examples, see Example: Run Apply on a BSON column.

If Apply has only one time series argument, you can refer to the column name without the time series position part; hence, $0.high is the same as $high. Notice that $0 always means the whole element of the first time series. It does not mean the first column of the time series, even if there is only one time series argument.

If you use a function as your expression, then it must take the subtype of each input time series in that order as its arguments and return a row type that corresponds to the subtype of the result time series of Apply. In most cases, it is faster to evaluate a function than to evaluate a generic expression. If performance is critical, you should implement the calculation to be performed in a function and use the function syntax. See Example: Run Apply without a filter or a clipped range for how to achieve this.

"avghigh(arg1 daybar, arg2 daybar) returns (one_real)"

"$0.vol > $1.vol and $0.close > ($0.high - $0.low)/2"

Apply with the multiset_ts argument evaluates the expression once for every timepoint in the resulting union of time series values. When all the data in the clipped period has been exhausted, Apply returns the resulting series.

Apply uses the optional clip time range to restrict the data to a particular time period. If the beginning timepoint is NULL, then Apply uses the earliest valid timepoint of all the input time series. If the ending timepoint is NULL, then Apply uses the latest valid timepoint of all the input time series. When the optional clip time range is not used, it is equivalent to both the beginning and ending timepoints being NULL: Apply considers all elements.

If both the clip time range and filter expression are given, then clipping is done before filtering.

If you use a string literal or NULL for the clip time range, you should cast to DATETIME YEAR TO FRACTION(5) on at least the beginning timepoint to avoid ambiguity in function resolution.

When more than one input time series is specified, a union of all input time series is performed to produce the source of data to be filtered and evaluated by Apply. Hence, Apply acts as a union function, with extra filtering and manipulation of union results. For details on how the Union function works, see Union function.

Returns

A new time series with the results of evaluating the expression on every selected element from the source time series.

Example: Run Apply without a filter or a clipped range

select Apply('$high-$low',
      datetime(2011-01-01) year to day,
      datetime(2011-01-06) year to day,
      stock_data)::TimeSeries(one_real)
   from daily_stocks
   where stock_name = 'IBM';