AggregateRange function

The AggregateRange function produces an aggregate over each element for a time range that is specified by start and end DATETIME parameters.

Syntax

AggregateRange(agg_express lvarchar, 
           ts              TimeSeries
           flags           integer default 0
           start           datetime year to fraction(5) default NULL,
           end             datetime year to fraction(5) default NULL
)
returns row;

agg_express: A comma-separated list of these SQL aggregate operators: MIN, MAX, MEDIAN, SUM, AVG, FIRST, LAST, or Nth. The MIN, MAX, MEDIAN, SUM, and AVG expressions can operate only on numeric columns.
ts: The time series to be aggregated.
flags (optional): See The flags argument values.
You cannot use a flags argument value of 1 with this function.
start (optional): The date and time at which to start aggregation.
end (optional): The date and time at which to end aggregation.

Description

The AggegateRange function converts the input section of a time series to a row of aggregate values.

The agg_express expressions operate on a column of the input time series, which is specified by one of the following column identifiers:

$colname

The colname is the name of the column to aggregate in the TimeSeries data type. For example, if the column name is high, the column identifier is $high.

$colnumber

The colnumber is the position of the column to aggregate in the TimeSeries data type. For example, if the column number is 1, the column identifier is $1.

$bson_field_name

The bson_field_name is the name of a field in at least one BSON document in the BSON column in the TimeSeries data type. For example, if the field name is v1, the column identifier is $v1. If the BSON field name is the same as another column in the TimeSeries data type, you must qualify the field name in one of the following ways:

$colname.bson_field_name
For example, if the BSON column name is b_data and the field name is v1, the column identifier is $b_data.v1.
$colnumber.bson_field_name
For example, if the BSON column number is 1 and the field name is v1, the column identifier is $1.v1.

You must cast the results of the AggregateRange function on a BSON field to a TimeSeries data type the appropriate type of columns for the result of the expression, for example a timestamp column and an INTEGER column.

The Nth expression returns the value of a column for the specified aggregation period, using the following syntax:

Nth($col, n)

$col: The column identifer.
n: A positive or negative number indicating the position of the TimeSeries row within the aggregation period. Positive values of n begin at the first row in the aggregation period; therefore, Nth($col, 1) is equivalent to FIRST($col). Negative values of n begin with the last row in the aggregation period; therefore, Nth($col, -1) is equivalent to LAST($col).
If an aggregation period does not have a value for the nth row, then the Nth function returns a null value for that period. The Nth function is more efficient for positive values of the n argument than for negative values.

An aggregation time period is denoted by the start date and time of the period.

The flags argument values

The flags argument determines how data points in the off periods of calendars are handled during aggregation and how hidden elements are managed. It can have the following values.

0 (default): Data in off periods is aggregated with the next output period.
2: Indicates that the scan runs with the TS_SCAN_HIDDEN flag set (hidden elements are returned).
4: Indicates that the scan runs with the TS_SCAN_SKIP_HIDDEN flag set (hidden elements are not returned).

Returns

A single element (row).

Example: Stock data

The following example produces an average of the values in the column high of the time series called stock_data. First, the example creates the row type, elemval, as a cast for the result.

create row type elemval (tstamp datetime year to fraction(5),
                         high double precision);


select 
  AggregateRange('avg($high)', stock_data)::elemval
from daily_stocks;

Examples: BSON data

This example is based on the following row type and time series definition. The TimeSeries row type contains an INTEGER column that is named v1 and the BSON column contains a field that is also named v1.

CREATE ROW TYPE rb(timestamp datetime year to fraction(5), data bson, v1 int); 

INSERT INTO tj VALUES(1,'origin(2011-01-01 00:00:00.00000), calendar(ts_15min), 
container(kontainer),threshold(0), regular,[({"v1":99},20)]');

The following statement creates a TimeSeries data type for the results:

CREATE ROW TYPE outrow(timestamp datetime year to fraction(5), x int);

If a column and a BSON field have the same name, the column takes precedence. The following statement returns the maximum value from the v1 INTEGER column:

SELECT AggregateRange('max($v1)','ts_1year',tsdata,0
           "2011-01-01 00:00:00.00000"::datetime year to fraction(5),
           "2012-01-01 00:00:00.00000"::datetime year to fraction(5)) 
FROM tj;

The following two equivalent statements return the maximum value from the v1 field in the data BSON column, which is column 1 in the TimeSeries row type:

SELECT AggregateRange('max($data.v1)','ts_1year',tsdata,0
           "2011-01-01 00:00:00.00000"::datetime year to fraction(5),
           "2012-01-01 00:00:00.00000"::datetime year to fraction(5))
           ::outrow
FROM tj; 

SELECT AggregateRange('max($1.v1)','ts_1year',tsdata,0
           "2011-01-01 00:00:00.00000":datetime year to fraction(5),
           "2012-01-01 00:00:00.00000":datetime year to fraction(5))
           ::outrow
FROM tj;

The aggregated time series that is returned has the TimeSeries data type outrow. If you do not cast the result to a row type that has the appropriate columns for the results, the statement fails.