/
Creating and Selecting in Rollup Table

Creating and Selecting in Rollup Table

Owned by jake shin (Unlicensed)
Last updated: Jul 05, 2022 by Kyle Song (Unlicensed)

Creating Rollup Table

When user create tag table, Rollup does not created default, user must create by themselves. Syntax is as follow.

CREATE ROLLUP [rollup_name] FROM [src_table_name] INTERVAL [number] [SEC|MIN|HOUR];
  • rollup name : rollup table's name (Can be freely created with string up to 40)
  • source table name : Name of source table which rollup will aggregate data.
  • *number sec/min/hour : time and time unit for aggregate
  •  ex) 1 sec aggregate : 1 sec
     ex) 30 seconds aggregate : 30 sec
     ex) 1 minute aggregate : 1 min
     ex) 1 hour aggregate : 1 hour
  • constraint
    • Source table for aggregation can specify only tag table or rollup table
    • Only one rollup table can be exist to source table for aggregation
    • If source table for aggregation is rollup table, time for rollup table must be bigger than time for source table. And it must be multiple.


Example for Creating rollup table

-- creating 1 second rollup for tag table
Mach> CREATE ROLLUP _tag_rollup_sec FROM tag INTERVAL 1 SEC;
 
-- creating 1 minute rollup for tag table
Mach> CREATE ROLLUP _tag_rollup_min FROM tag INTERVAL 1 MIN;
 
-- creating 1 hour rollup for tag table
Mach> CREATE ROLLUP _tag_rollup_hour FROM tag INTERVAL 1 HOUR;
 
-- creating 30 seconds rollup for tag table
Mach> CREATE ROLLUP _tag_rollup_30sec FROM tag INTERVAL 30 SEC;
 
-- creating 10 minutes rollup for roll up table above
Mach> CREATE ROLLUP _tag_rollup_10min FROM _tag_rollup_30sec INTERVAL 10 MIN;


Start/Stop Rollup Table

Even user create rollup, rollup thread does not start automatically so user must start rollup by themselves.

-- Start specific rollup
EXEC ROLLUP_START(rollup_name)

-- Stop specific rollup
EXEC ROLLUP_STOP(rollup_name)

Collect Rollup Instantly

rollup basically aggregate data in set time unit.

  • ex) If it is 1 hour rollup, it aggregate data once in hour and rest in rest time.

User can aggregate data in force.

-- Execute specific rollup flush immediately
EXEC ROLLUP_FORCE(rollup_name)

Drop Rollup

Drop Rollup.

DROP ROLLUP rollup_name
  • rollup_name : Name of rollup
  • constraint: if there is an rollup table that reference rollup table that will be deleted as source tree, it will cause error. User must delete it in reverse order.
mach> create tag table tag (name varchar(20) primary key, time datetime basetime, value double summarized);
mach> create rollup _tag_rollup_1 from tag interval 1 sec;
mach> create rollup _tag_rollup_2 from _tag_rollup_1 interval 1 min;
mach> create rollup _tag_rollup_3 from _tag_rollup_2 interval 1 hour;
 
When created as above, the reference order is as follows.
 
tag -> _tag_rollup_1 -> _tag_rollup_2 -> _tag_rollup_3
 
At this time, if you try to delete the tag table or rollup in the middle, an error occurs.
 
mach> drop rollup tag
> [ERR-02651: Dependent ROLLUP table exists.]
mach> drop rollup _tag_rollup_1
> [ERR-02651: Dependent ROLLUP table exists.]
 
User must delete them in the following order to delete them normally.
 
mach> drop rollup _tag_rollup_3;
mach> drop rollup _tag_rollup_2;
mach> drop rollup _tag_rollup_1;
mach> drop table tag;


Syntax

SELECT TIME ROLLUP 3 SECOND, AVG(VALUE) FROM TAG WHERE ...;
As above, if the ROLLUP clause is appended after the Datetime type column specified as the BASETIME attribute, the rollup table is selected.

[BASETIME_COLUMN] ROLLUP [PERIOD] [TIME_UNIT]
  • BASETIME_COLUMN : Datetime column of the TAG table specified by the BASETIME attribute
  • PERIOD : DATE_TRUNC() can specify a range for each unit of time available. (see below)
  • TIME_UNIT : Any time unit available in the DATE_TRUNC() function can be used. (see below)
Depending on the selection of TIME_UNIT, the searched rollup table is different.
unit of time(Abbreviation)
range of timerollup table
nanosecond (nsec)1000000000 (1 sec)SECOND
microsecond (usec)60000000 (60 sec)SECOND
milisecond (msec)60000 (60 sec)SECOND
second (sec)86400 (1 day)SECOND
minute (min)1440 (1 day)MINUTE
hour24 (1 day)HOUR
day1HOUR
month1HOUR
year1HOUR

목차

Since using the ROLLUP clause directly performs a rollup table lookup, to use an aggregate function, it has the following characteristics.

  • The aggregate function must be called on the column specified with the SUMMARIZED attribute. However, only the five aggregate functions (SUM, COUNT, MIN, MAX, AVG) supported by the rollup table are supported. (For columns that do not have the SUMMARIZED attribute, all aggregate function calls are possible only for PRIMARY KEY columns and METADATA columns.)
  • GROUP BY must be done directly with the BASETIME column to be ROLLUP.
    • You can use the ROLLUP clause with the same meaning as it is.
    • Alternatively, an alias may be attached to the ROLLUP clause, and the alias may be written in GROUP BY.
SELECT   time rollup 3 sec mtime, avg(value) 
FROM     TAG 
GROUP BY time rollup 3 sec mtime;

-- or
SELECT   time rollup 3 sec mtime, avg(value) 
FROM     TAG 
GROUP BY mtime;

Data Sample

Below is sample data for rollup test.

create tag 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);

For one tag, different values ​​in seconds were input for 3 hours.


Get ROLLUP AVG

Below is the case of getting average of seconds, minutes, hours of tag table.

Mach> SELECT time rollup 1 sec mtime, avg(value) FROM TAG WHERE name = 'TAG_0001' group by mtime order by mtime;
mtime                           avg(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 time rollup 1 min mtime, avg(value) FROM TAG WHERE name = 'TAG_0001' group by mtime order by mtime;
mtime                           avg(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 time rollup 1 hour mtime, avg(value) FROM TAG WHERE name = 'TAG_0001' group by mtime order by mtime;
mtime                           avg(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.


Get ROLLUP MIN/MAX Value

Below is the case of getting min/max value of seconds, minutes, hours of tag table. The difference between others, you can get minimum value and maximum value at the same time with just one query.

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

Mach> SELECT time rollup 1 min mtime, min(value), max(value) FROM TAG WHERE name = 'TAG_0001' group by mtime order by mtime;
mtime                           min(value)                  max(value)
--------------------------------------------------------------------------------------------
2018-01-01 01:00:00 000:000:000 1                           2
2018-01-01 01:01:00 000:000:000 3                           4
2018-01-01 01:02:00 000:000:000 5                           6
2018-01-01 02:00:00 000:000:000 1                           2
2018-01-01 02:01:00 000:000:000 3                           4
2018-01-01 02:02:00 000:000:000 5                           6
2018-01-01 03:00:00 000:000:000 1                           2
2018-01-01 03:01:00 000:000:000 3                           4
2018-01-01 03:02:00 000:000:000 5                           6
[9] row(s) selected.


Get ROLLUP SUM/COUNT

Below is the case of getting sum/count value. Also you can get  sum value and count value at the same time with just one query.

Mach> SELECT time rollup 1 min  mtime, sum(value), count(value) FROM TAG WHERE name = 'TAG_0001' group by mtime order by mtime;
mtime                           sum(value)                  count(value)
-------------------------------------------------------------------------------------
2018-01-01 01:00:00 000:000:000 3                           2
2018-01-01 01:01:00 000:000:000 7                           2
2018-01-01 01:02:00 000:000:000 11                          2
2018-01-01 02:00:00 000:000:000 3                           2
2018-01-01 02:01:00 000:000:000 7                           2
2018-01-01 02:02:00 000:000:000 11                          2
2018-01-01 03:00:00 000:000:000 3                           2
2018-01-01 03:01:00 000:000:000 7                           2
2018-01-01 03:02:00 000:000:000 11                          2
[9] row(s) selected.

Get ROLLUP Sum of Squares

Below is the case of getting sum of squares in rollup.

Mach> SELECT time ROLLUP 1 SEC mtime, SUMSQ(value) FROM tag GROUP BY mtime ORDER BY mtime;
mtime                           SUMSQ(value)                
---------------------------------------------------------------
2018-01-01 01:00:01 000:000:000 1                           
2018-01-01 01:00:02 000:000:000 4                           
2018-01-01 01:01:01 000:000:000 9                           
2018-01-01 01:01:02 000:000:000 16                          
2018-01-01 01:02:01 000:000:000 25                          
2018-01-01 01:02:02 000:000:000 36                          
2018-01-01 02:00:01 000:000:000 1                           
2018-01-01 02:00:02 000:000:000 4                           
2018-01-01 02:01:01 000:000:000 9                           
2018-01-01 02:01:02 000:000:000 16                          
2018-01-01 02:02:01 000:000:000 25                          
2018-01-01 02:02:02 000:000:000 36                          
2018-01-01 03:00:01 000:000:000 1                           
2018-01-01 03:00:02 000:000:000 4                           
2018-01-01 03:01:01 000:000:000 9                           
2018-01-01 03:01:02 000:000:000 16                          
2018-01-01 03:02:01 000:000:000 25                          
2018-01-01 03:02:02 000:000:000 36                          
[18] row(s) selected.

Mach> SELECT time ROLLUP 1 MIN mtime, SUMSQ(value) FROM tag GROUP BY mtime ORDER BY mtime;
mtime                           SUMSQ(value)                
---------------------------------------------------------------
2018-01-01 01:00:00 000:000:000 5                           
2018-01-01 01:01:00 000:000:000 25                          
2018-01-01 01:02:00 000:000:000 61                          
2018-01-01 02:00:00 000:000:000 5                           
2018-01-01 02:01:00 000:000:000 25                          
2018-01-01 02:02:00 000:000:000 61                          
2018-01-01 03:00:00 000:000:000 5                           
2018-01-01 03:01:00 000:000:000 25                          
2018-01-01 03:02:00 000:000:000 61                          
[9] row(s) selected.


Grouping at Various Time Intervals

The advantage of the ROLLUP clause is that it is not necessary to intentionally use DATE_TRUNC() to vary the time interval.

To get the sum of the 3-second interval and the number of data, you can do as follows.
Since the example time range is only 0 sec, 1 sec, and 2 sec, it can be seen that they all converge to 0 sec. As a result, it matches the 'rollup by minute' query result.
Mach> SELECT time rollup 3 sec  mtime, sum(value), count(value) FROM TAG WHERE name = 'TAG_0001' GROUP BY mtime ORDER BY mtime;
mtime                           sum(value)                  count(value)
-------------------------------------------------------------------------------------
2018-01-01 01:00:00 000:000:000 3                           2
2018-01-01 01:01:00 000:000:000 7                           2
2018-01-01 01:02:00 000:000:000 11                          2
2018-01-01 02:00:00 000:000:000 3                           2
2018-01-01 02:01:00 000:000:000 7                           2
2018-01-01 02:02:00 000:000:000 11                          2
2018-01-01 03:00:00 000:000:000 3                           2
2018-01-01 03:01:00 000:000:000 7                           2
2018-01-01 03:02:00 000:000:000 11                          2

Related content