Power of Expression Based Virtual Table Interface

Its been long time I haven't posted any blog. Have been busy with customer engagements. But then thought of sharing few good timeseries use cases that I handled using Expression Based Virtual Table Interface (EVTI).

All the examples below shall be explaining different queries based of the  following table structure:
create row type series
(   ts datetime year to fraction (5),   val1 float,   val2 float,  val3 float );

create row type float4
(  ts datetime year to fraction(5),  f_val1 float,f_val2 float,f_val3 float,f_val4 float );

create table ops_data
(
   meter_id integer,
   data timeseries(row1)
) ;



- As simple use case, need realtime aggregate of 1 minute data to 15min. Need avg, max,first and last value of val1

execute procedure TSCreateExpressionVirtualTab ('ops_data_agg15',
'ops_data','aggregateby("avg(val1),max(val1),last(val1),first(val1)","cal_15min",series,0,$ts_begin_time, $ts_end_time)::timeseries(float4))','float4');

- Val1 might have -ve values, hence need to run aggregates on absolute value of val1

execute procedure TSCreateExpressionVirtualTab ('ops_data_agg15',
'ops_data','aggregateby("avg(val1),max(val1),last(val1),first(val1)","cal_15min",abs(series),0,$ts_begin_time, $ts_end_time)::timeseries(float4))','float4');

- Need (current - previous) value of average of val1 and along with max, last and first value.

execute procedure TSCreateExpressionVirtualTab ('ops_data_agg15',
'ops_data','apply("$f_val1 - tsprevious($f_val1), f_val2,f_val3_f_val4", aggregateby("avg(val1),max(val1),last(val1),first(val1)","cal_15min",abs(series),0,$ts_begin_time, $ts_end_time)::timeseries(float4))','float4');

Few things to be noted in above query:
- Function "apply" is used and the timeseries created by aggregateby function is used as an argument to apply function
- tsprevious function is used to keep track of previous record and then subtract it from current value
- The output of function aggregateBy type cased to timeseries(float4), hence the column names used in apply as the named available in float4 row.

Disclaimer: The postings on this site are my own and don't necessarily represent IBM's positions,strategies or opinions