/
Manipulating Rollup Tables (1)

Manipulating Rollup Tables (1)

Owned by Ryan Park (Unlicensed)
Last updated: Nov 09, 2021 by Grey Shim

Manipulating Rollup Tables 

As mentioned previously, ROLLUP tables are read-only and designed to quickly get statistics for a particular tag within a desired time range.

ROLLUP table queries can be done by performing a SELECT on the TAG table with HINT to indicate that the query is for ROLLUP.


Syntax

SELECT /*+ ROLLUP(TAG, TIME_UNIT, FUNCTION_NAME) */ COLUMN_LIST FROM TAG WHERE USER_CONDITION;
  • TIME_UNIT : You can specify one of these item (HOUR, MIN, SEC). (Note: if you specify MIN as MINUTE, it will not work correctly.)
  • FUNCTION_NAME : You can specify one of these function names (AVG, MIN, MAX, COUNT, SUM).
  • COLUMN_LIST : You can specify column name of TAG table. but statistic values are from columns which is specified as SUMMERIZED. (normally value).

Index


Data Sample

Below is sample data for ROLLUP test.

create tagdata table TAG (name varchar(20) primary key, time datetime basetime, value double summarized);

insert into tag metadata values ('TAG_0001');

insert into tag values('TAG_0001', '2018-01-01 01:00:01 000:000:000', 1);
insert into tag values('TAG_0001', '2018-01-01 01:00:02 000:000:000', 2);
insert into tag values('TAG_0001', '2018-01-01 01:01:01 000:000:000', 3);
insert into tag values('TAG_0001', '2018-01-01 01:01:02 000:000:000', 4);
insert into tag values('TAG_0001', '2018-01-01 01:02:01 000:000:000', 5);
insert into tag values('TAG_0001', '2018-01-01 01:02:02 000:000:000', 6);

insert into tag values('TAG_0001', '2018-01-01 02:00:01 000:000:000', 1);
insert into tag values('TAG_0001', '2018-01-01 02:00:02 000:000:000', 2);
insert into tag values('TAG_0001', '2018-01-01 02:01:01 000:000:000', 3);
insert into tag values('TAG_0001', '2018-01-01 02:01:02 000:000:000', 4);
insert into tag values('TAG_0001', '2018-01-01 02:02:01 000:000:000', 5);
insert into tag values('TAG_0001', '2018-01-01 02:02:02 000:000:000', 6);

insert into tag values('TAG_0001', '2018-01-01 03:00:01 000:000:000', 1);
insert into tag values('TAG_0001', '2018-01-01 03:00:02 000:000:000', 2);
insert into tag values('TAG_0001', '2018-01-01 03:01:01 000:000:000', 3);
insert into tag values('TAG_0001', '2018-01-01 03:01:02 000:000:000', 4);
insert into tag values('TAG_0001', '2018-01-01 03:02:01 000:000:000', 5);
insert into tag values('TAG_0001', '2018-01-01 03:02:02 000:000:000', 6);



ROLLUP force update

Machbase creates ROLLUP data in real time, but in case of HOUR or MIN, it does not update if the time has not been exceeded.

However, since it is necessary to update ROLLUP data even before the time is up, Machbase provides the ability to force update through the EXEC ROLLUP_FORCE statement.

This procedure can take a very long time depending on the amount of data, so it should be executed only when absolutely necessary. (In most cases, you do not need to perform this function.)

Mach> EXEC ROLLUP_FORCE;
 Executed successfully.

Getting average value using ROLLUP data.

Below is an example of getting the average value in seconds, minutes, and hours for a specific tag.

Mach> SELECT /*+ ROLLUP(TAG, sec, AVG) */ time, value FROM TAG WHERE name = 'TAG_0001' order by time;
time                            value
---------------------------------------------------------------
2018-01-01 01:00:01 000:000:000 1
2018-01-01 01:00:02 000:000:000 2
2018-01-01 01:01:01 000:000:000 3
2018-01-01 01:01:02 000:000:000 4
2018-01-01 01:02:01 000:000:000 5
2018-01-01 01:02:02 000:000:000 6
2018-01-01 02:00:01 000:000:000 1
2018-01-01 02:00:02 000:000:000 2
2018-01-01 02:01:01 000:000:000 3
2018-01-01 02:01:02 000:000:000 4
2018-01-01 02:02:01 000:000:000 5
2018-01-01 02:02:02 000:000:000 6
2018-01-01 03:00:01 000:000:000 1
2018-01-01 03:00:02 000:000:000 2
2018-01-01 03:01:01 000:000:000 3
2018-01-01 03:01:02 000:000:000 4
2018-01-01 03:02:01 000:000:000 5
2018-01-01 03:02:02 000:000:000 6
[18] row(s) selected.

Mach> SELECT /*+ ROLLUP(TAG, min, AVG) */ time, value FROM TAG WHERE name = 'TAG_0001' order by time;
time                            value
---------------------------------------------------------------
2018-01-01 01:00:00 000:000:000 1.5
2018-01-01 01:01:00 000:000:000 3.5
2018-01-01 01:02:00 000:000:000 5.5
2018-01-01 02:00:00 000:000:000 1.5
2018-01-01 02:01:00 000:000:000 3.5
2018-01-01 02:02:00 000:000:000 5.5
2018-01-01 03:00:00 000:000:000 1.5
2018-01-01 03:01:00 000:000:000 3.5
2018-01-01 03:02:00 000:000:000 5.5
[9] row(s) selected.

Mach> SELECT /*+ ROLLUP(TAG, hour, AVG) */ time, value FROM TAG WHERE name = 'TAG_0001' order by time;
time                            value
---------------------------------------------------------------
2018-01-01 01:00:00 000:000:000 3.5
2018-01-01 02:00:00 000:000:000 3.5
2018-01-01 03:00:00 000:000:000 3.5
[3] row(s) selected.


Getting min/max values using ROLLUP data.

Below is an example of getting the minimum / maximum value according to the time range of the tag.

Mach> SELECT /*+ ROLLUP(TAG, hour, min) */ time, value FROM TAG WHERE name = 'TAG_0001' order by time;
time                            value
---------------------------------------------------------------
2018-01-01 01:00:00 000:000:000 1
2018-01-01 02:00:00 000:000:000 1
2018-01-01 03:00:00 000:000:000 1
[3] row(s) selected.

Mach> SELECT /*+ ROLLUP(TAG, hour, max) */ time, value FROM TAG WHERE name = 'TAG_0001' order by time;
time                            value
---------------------------------------------------------------
2018-01-01 01:00:00 000:000:000 6
2018-01-01 02:00:00 000:000:000 6
2018-01-01 03:00:00 000:000:000 6
[3] row(s) selected.

Mach> SELECT /*+ ROLLUP(TAG, min, min) */ time, value FROM TAG WHERE name = 'TAG_0001' order by time;
time                            value
---------------------------------------------------------------
2018-01-01 01:00:00 000:000:000 1
2018-01-01 01:01:00 000:000:000 3
2018-01-01 01:02:00 000:000:000 5
2018-01-01 02:00:00 000:000:000 1
2018-01-01 02:01:00 000:000:000 3
2018-01-01 02:02:00 000:000:000 5
2018-01-01 03:00:00 000:000:000 1
2018-01-01 03:01:00 000:000:000 3
2018-01-01 03:02:00 000:000:000 5
[9] row(s) selected.

Mach> SELECT /*+ ROLLUP(TAG, min, max) */ time, value FROM TAG WHERE name = 'TAG_0001' order by time;
time                            value
---------------------------------------------------------------
2018-01-01 01:00:00 000:000:000 2
2018-01-01 01:01:00 000:000:000 4
2018-01-01 01:02:00 000:000:000 6
2018-01-01 02:00:00 000:000:000 2
2018-01-01 02:01:00 000:000:000 4
2018-01-01 02:02:00 000:000:000 6
2018-01-01 03:00:00 000:000:000 2
2018-01-01 03:01:00 000:000:000 4
2018-01-01 03:02:00 000:000:000 6
[9] row(s) selected.


Getting Sum/Count values using ROLLUP data.

Below is an example of getting the sum / count value according to the time range of the tag.

Mach> SELECT /*+ ROLLUP(TAG, min, sum) */ time, value FROM TAG WHERE name = 'TAG_0001' order by time;
time                            value
---------------------------------------------------------------
2018-01-01 01:00:00 000:000:000 3
2018-01-01 01:01:00 000:000:000 7
2018-01-01 01:02:00 000:000:000 11
2018-01-01 02:00:00 000:000:000 3
2018-01-01 02:01:00 000:000:000 7
2018-01-01 02:02:00 000:000:000 11
2018-01-01 03:00:00 000:000:000 3
2018-01-01 03:01:00 000:000:000 7
2018-01-01 03:02:00 000:000:000 11
[9] row(s) selected.

Mach> SELECT /*+ ROLLUP(TAG, min, count) */ time, value FROM TAG WHERE name = 'TAG_0001' order by time;
time                            value
--------------------------------------------------------
2018-01-01 01:00:00 000:000:000 2
2018-01-01 01:01:00 000:000:000 2
2018-01-01 01:02:00 000:000:000 2
2018-01-01 02:00:00 000:000:000 2
2018-01-01 02:01:00 000:000:000 2
2018-01-01 02:02:00 000:000:000 2
2018-01-01 03:00:00 000:000:000 2
2018-01-01 03:01:00 000:000:000 2
2018-01-01 03:02:00 000:000:000 2
[9] row(s) selected.


Getting ROLLUP data using RESTful API

Syntax

You can get ROLLUP data from the RESTful API using the following syntax:

{MWA URL}/machiot-rest-api/datapoints/calculated/{TagName}/{Start}/{End}/{CalculationMode}/{Count}/{IntervalType}/{IntervalValue}/

Parameter

Description

TagName
  • Specify name of tag
  • In case of multiple tags, they should separated by comma(',').
Start, End
  • Specify time range of data
  • Supported date format is YYYY-MM-DD HH24:MI:SS or YYYY-MM-DD
CalcurationMode
  • Specify type of statistics data
  • Supported type are (min/max/sum/cnt/avg)
Count
  • Specify maximal number of rows
  • In case of 0, all data will be printed.
IntervalType
  • Specify the groping duration
  • You can specify (sec, min, hour)
IntervalValue
  • Specify value for IntervalType

  • If the IntervalType is min and the IntervalValue is 5, the statistical value of 5 minutes

Average statistics for a minute

Below is an example of outputting the statistical value per minute from the value of TAG_0001.

Host:~/work/nfx$ curl -G "http://192.168.0.148:5001/machiot-rest-api/v1/datapoints/calculated/TAG_0001/2018-01-01T00:00:00/2018-01-31T00:00:00/avg/0/min"
{"ErrorCode": 0, 
 "ErrorMessage": "", 
 "Data": [{"DataType": "DOUBLE", 
           "ErrorCode": 0, 
           "TagName": "TAG_0001", 
           "CalculationMode": "avg", 
           "Samples": [{"TimeStamp": "2018-01-01 01:00:00 000:000:000", "Value": 1.5, "Quality": 0}, 
                       {"TimeStamp": "2018-01-01 01:01:00 000:000:000", "Value": 3.5, "Quality": 0}, 
                       {"TimeStamp": "2018-01-01 01:02:00 000:000:000", "Value": 5.5, "Quality": 0}, 
                       {"TimeStamp": "2018-01-01 02:00:00 000:000:000", "Value": 1.5, "Quality": 0}, 
                       {"TimeStamp": "2018-01-01 02:01:00 000:000:000", "Value": 3.5, "Quality": 0}, 
                       {"TimeStamp": "2018-01-01 02:02:00 000:000:000", "Value": 5.5, "Quality": 0}, 
                       {"TimeStamp": "2018-01-01 03:00:00 000:000:000", "Value": 1.5, "Quality": 0}, 
                       {"TimeStamp": "2018-01-01 03:01:00 000:000:000", "Value": 3.5, "Quality": 0}, 
                       {"TimeStamp": "2018-01-01 03:02:00 000:000:000", "Value": 5.5, "Quality": 0}]}]}

Average statistics per 2 minutes

Below is a similar case as above, but with 2 minutes instead of 1 minute.

Host:~/work/nfx$ curl -G "http://192.168.0.148:5001/machiot-rest-api/v1/datapoints/calculated/TAG_0001/2018-01-01T00:00:00/2018-01-31T00:00:00/avg/0/min/2"
{"ErrorCode": 0, 
 "ErrorMessage": "", 
 "Data": [{"DataType": "DOUBLE", 
           "ErrorCode": 0, 
           "TagName": "TAG_0001", 
           "CalculationMode": "avg", 
           "Samples": [{"TimeStamp": "2018-01-01 01:00:00 000:000:000", "Value": 2.5, "Quality": 0}, 
                       {"TimeStamp": "2018-01-01 01:02:00 000:000:000", "Value": 5.5, "Quality": 0}, 
                       {"TimeStamp": "2018-01-01 02:00:00 000:000:000", "Value": 2.5, "Quality": 0}, 
                       {"TimeStamp": "2018-01-01 02:02:00 000:000:000", "Value": 5.5, "Quality": 0}, 
                       {"TimeStamp": "2018-01-01 03:00:00 000:000:000", "Value": 2.5, "Quality": 0}, 
                       {"TimeStamp": "2018-01-01 03:02:00 000:000:000", "Value": 5.5, "Quality": 0}]}]}

As described above, the user can designate arbitrary statistical functions for arbitrary tags in arbitrary time units and output them in real time.

Related content