TSCreateExpressionVirtualTab procedure

The TSCreateExpressionVirtualTab procedure creates a virtual table that is based on the results of an expression that was performed on a table that contains a TimeSeries column. The resulting virtual table is read-only.

Syntax

TSCreateExpressionVirtualTab
                  (VirtualTableName  lvarchar,
                   BaseTableName     lvarchar,
                   expression        lvarchar,
                   subtype           lvarchar,
                   TSVTMode          integer default 0,
                   TSColName         lvarchar default NULL);

VirtualTableName: The name of the new virtual table.
BaseTableName: The name of the base table.
expression: The expression to be evaluated on time series data. The expression must result in a time series value that has a TimeSeries subtype that is specified by the subtype parameter.
subtype: The name of the TimeSeries subtype for the values that are the results of the expression.
TSVTMode (optional): Sets the virtual table mode, as described in The TSVTMode parameter.
TSColName (optional): For base tables that have more than one TimeSeries column, specifies the name of the TimeSeries column to be used to create the virtual table. The default value for the TSColName parameter is NULL, in which case the base table must have only one TimeSeries column.

Usage

Use the TSCreateExpressionVirtualTab procedure to create a virtual table based on a time series that results from an expression that is performed on time series data each time a query, such as a SELECT statement, is performed. You specify the name of the TimeSeries subtype in the virtual table with the subtype parameter.

The total length of a row in the virtual table (non-time-series and TimeSeries columns combined) must not exceed 32 KB.

If you specify either of the optional parameters, you must include them in the order that is shown in the syntax, but you can use either one without the other. For example, you can specify the TSColName parameter without including the TSVTMode parameter.

The virtual table is read-only. You cannot run INSERT, UPDATE, or DELETE statements on a virtual table that is based on an expression. When you query the virtual table, the WHERE clause in the SELECT statement cannot have any predicates that are based on the columns in the virtual table that are derived from the resulting TimeSeries subtype.

In the expression, you can use time series SQL routines and other SQL statements to manipulate the data, for example, the AggregateBy function and the Apply function.

You can use the following variables in the expression:

$ts_column_name: If the base table has multiple TimeSeries columns, instead of specifying the name of the TimeSeries column in the expression, you can use the $ts_column_name variable to substitute the value of the TScolName parameter in the TSCreateExpressionVirtualTab procedure. Because the column name is a variable, you can use the same expression for each of the TimeSeries columns in the table.
$ts_begin_time: Instead of specifying a DATETIME value, you can use this variable and specify the beginning time point of the time series in the WHERE clause of the SELECT statement when you query the virtual table. If the WHERE clause does not contain the beginning timepoint, the first timepoint in the time series is used.
$ts_end_time: Instead of specifying a DATETIME value, you can use this variable and specify the ending time point of the time series in the WHERE clause of the SELECT statement when you query the virtual table. If the WHERE clause does not contain the ending timepoint, the last timepoint in the time series is used.

You can test whether the subtype that you create for the results of the expression is valid by running the following statement against your base table with the expression and subtype parameters that you plan to use in the TSCreateExpressionVirtualTab procedure:

SELECT expression::timeseries(subtype) FROM BaseTableName;

If the statement fails, you cannot create the virtual table.

Examples

The following examples use a table named smartmeters that contains a column named meter_id and a TimeSeries column named readings. The TimeSeries subtype has the columns t and energy.

Example 1: Find the daily maximum and minimum values

The following statement creates a virtual table that is named smartmeters_vti_agg_max_min based on a time series that contains the maximum and minimum energy readings per day:

EXECUTE PROCEDURE TSCreateExpressionVirtualTab(
       'smartmeters_vti_agg_max_min', 'smartmeters',
       'AggregateBy(''max($energy),min($energy)'',
                    ''smartmeter_daily'', readings, 0)',
       'tworeal_row');

The following query shows the daily maximum and minimum of the energy reading between 2011-0-01 and 2011-01-02:

SELECT * FROM smartmeters_vti_agg_max_min
 WHERE t >= '2011-01-01 00:00:00.00000'::datetime year to fraction(5)
   AND t <= '2011-01-02 23:59:59.99999'::datetime year to fraction(5);

meter_id   t                                 value1         value2

met00000   2011-01-01 00:00:00.00000 37.00000000000 9.000000000000
met00000   2011-01-02 00:00:00.00000 34.00000000000 8.000000000000
met00001   2011-01-01 00:00:00.00000 36.00000000000 9.000000000000
met00001   2011-01-02 00:00:00.00000 36.00000000000 10.00000000000
met00002   2011-01-01 00:00:00.00000 34.00000000000 9.000000000000
met00002   2011-01-02 00:00:00.00000 36.00000000000 10.00000000000

6 row(s) retrieved.

Example 2: Find the daily maximum of a running average

The following statement creates a virtual table that is named smartmeters_vti_daily_max that contains the daily maximum of the running average of the energy readings:

EXECUTE PROCEDURE TSCreateExpressionVirtualTab(
      'smartmeters_vti_daily_max', 'smartmeters',
      'AggregateBy(''max($value)'',''smartmeter_daily'',
                   Apply(''TSRunningAvg($energy, 4)'', 
                         $ts_begin_time, $ts_end_time, 
                         $ts_col_name)
                   ::TimeSeries(onereal_row), 0)',
      'onereal_row', 0, 'readings');

The $ts_col_name parameter is replaced by the column name that is specified by the TSCreateExpressionVirtualTab procedure, in this case, readings. The $ts_begin_time and $ts_end_time parameters are replaced when the virtual table is queried.

The following query shows the maximum daily average energy readings for two days:

SELECT * FROM smartmeters_vti_daily_max
 WHERE t >= '2011-01-01 00:00:00.00000'::datetime year to fraction(5)
   AND t <= '2011-01-02 23:59:59.99999'::datetime year to fraction(5);

meter_id   t                                  value

met00000   2011-01-01 00:00:00.00000 30.25000000000
met00000   2011-01-02 00:00:00.00000 29.50000000000
met00001   2011-01-01 00:00:00.00000 29.75000000000
met00001   2011-01-02 00:00:00.00000 31.00000000000
met00002   2011-01-01 00:00:00.00000 31.25000000000
met00002   2011-01-02 00:00:00.00000 28.75000000000

6 row(s) retrieved.