TSRollup function

The TSRollup function aggregates time series values by time for multiple rows in the table.

Syntax

TSRollup(
        ts TimeSeries, 
        'agg_express' lvarchar)
RETURNS TimeSeries;

agg_express

A comma-separated list of the following elements, in any order:

SQL aggregate operator = A comma-separated list of SQL aggregate operators = AVG, COUNT, MIN, MAX, SUM, or the FIRST and LAST operators. The FIRST operator returns a time series that contains the first element that was entered into the database for each timestamp. The LAST operator returns the last element that is entered for each timestamp. You can include multiple operators. Each operator requires an argument that is the 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 TSRollup function on a BSON field to a TimeSeries data type that has the appropriate type of columns for the result of the expression.

start(start_time) = Optional. Specifies the start of the time range on which to operate. The value of start_time must be in the form of a DATETIME value. No value indicates that the time range starts at the origin of the time series. If you include the start argument more than once in the aggregate expression, only the last start argument is used.

end(end_time) = Optional. Specifies the end of the time range on which to operate. The value of end_time must be in the form of a DATETIME value. No value indicates that the time range ends at the last element in the time series. If you include the end argument more than once in the aggregate expression, only the last end argument is used.

ts

The name of the TimeSeries data type or a function that returns a TimeSeries data type, such as AggregateBy.

Description

Use the TSRollup function to run one or more aggregate operators on multiple rows of time series data in a table.

If you want to aggregate a specific time range of data instead of all the data in the time series rows, you can limit the data by running a Clip function or an AggregateBy function and passing the results into the TSRollup function.

You can specify a time range on which to run the aggregate expression by including the start and end arguments in the aggregate expression. Specifying the time range in the TSRollup function is faster than first clipping the data by running the Clip or AggregateBy function.

Returns

A TimeSeries data type that is the result of the expression or expressions.

Example: Sum of all electricity usage in a postal code

The following statement adds all the electricity usage values for each time stamp in the ts_data table in the stores_demo database for the customers that have a postal code of 94063:

SELECT TSRollup(raw_reads, "sum($value)")
    FROM ts_data, customer, customer_ts_data
    WHERE customer.zipcode = "94063"
       AND customer_ts_data.customer_num = customer.customer_num
       AND customer_ts_data.loc_esi_id = ts_data.loc_esi_id;

Example: Sum of daily electricity usage by postal code

Suppose that you have a table named ts_table that contains a user ID, the postal code of the user, and the electricity usage data for each customer, which is collected every 15 minutes and stored in a column named value in a time series named ts_col. The following query returns the total amounts of electricity used daily for each postal code for one month:

SELECT zipcode, 
  TSRollup(
	AggregateBy('SUM($value)', 'cal1day', ts_col, 0, 
	    '2011-01-01 00:00:00.00000', '2011-01-31 23:45:00:00.00000'),
      'SUM($value)'
  )
FROM ts_table
GROUP BY zipcode;

The first argument to the TSRollup function is an AggregateBy function, which sums the electricity usage for each customer for each day of January 2011. The second argument is a SUM operator that sums the daily electricity usage by postal code.

The resulting table contains a row for each postal code. Each row has a time series that contains the sum of the electricity that is used by customers who live in that postal code for each day in January 2011.

Example: Sum of electricity usage for one hour

The following example adds all the electricity usage values for the specified time range of one hour in the ts_data table in the stores_demo database for the customers that have a postal code of 94063:

SELECT TSRollup(raw_reads, "SUM($value), start(2010-11-10 11:45:00), 
                end(2010-11-10 12:45:00)")
    FROM ts_data, customer, customer_ts_data
    WHERE customer.zipcode = "94063"
      AND customer_ts_data.customer_num = customer.customer_num
      AND customer_ts_data.loc_esi_id = ts_data.loc_esi_id;

Example: Maximum value of a field in a BSON column

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 to hold the results of the aggregation on the BSON field in an INTEGER column:

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 TSRollup(tsdata, 'max($v1)') FROM tj; 

TSRollup  origin(2011-01-01 00:00:00.00000), calendar(ts_15min), container(), 
threshold(0), regular, [(20         )] 

1 row(s) retrieved.

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 TSRollup(tsdata, 'max($data.v1)')::timeseries(outrow) FROM tj; 
SELECT TSRollup(tsdata, 'max($1.v1)')::timeseries(outrow) FROM tj;   

TSRollup  origin(2011-01-01 00:00:00.00000), calendar(ts_15min), container(), 
threshold(0), regular, [(99.00000000000)] 

1 row(s) retrieved.

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 appropriate columns for the results, the statement fails.