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

Code Block

language	sql

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).

Ui text box

Index

Table of Contents

maxLevel	3
indent	30px
exclude	목차
class	toc

Data Sample

Below is sample data for ROLLUP test.

Code Block

language	sql

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.)

Code Block

language	sql

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.

Code Block

language	text

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.

Code Block

language	text

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.

Code Block

language	text

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:

Code Block
{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.

Code Block

language	bash

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.

Code Block

language	bash

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.

Version	Old Version 1	New Version Current
Changes made by	Ryan Park (Unlicensed)	Grey Shim
Saved on	Oct 25, 2021	Nov 09, 2021

Versions Compared

Key

Manipulating Rollup Tables

Syntax

Index

Data Sample

ROLLUP force update

Getting average value using ROLLUP data.

Getting min/max values using ROLLUP data.

Getting Sum/Count values using ROLLUP data.

Getting ROLLUP data using RESTful API

Syntax

Average statistics for a minute

Average statistics per 2 minutes

Content Comparison

Versions Compared

Key

Manipulating Rollup Tables

Syntax

Index

Data Sample

ROLLUP force update

Getting average value using ROLLUP data.

Getting min/max values using ROLLUP data.

Getting Sum/Count values using ROLLUP data.

Getting ROLLUP data using RESTful API

Syntax

Average statistics for a minute

Average statistics per 2 minutes