Manipulating Rollup Tables (2)

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

Inquiry of the Manipulating Rollup Tables (1) showed the lookup of the rollup table using a hint.

However, this method has the following disadvantages.

  • The average, maximum, and count in a rollup table cannot be obtained from a single query.

  • Only 1 second, 1 minute, 1 hour can be used. If it is a specific time unit (for example, 5 seconds), it is difficult to process in a single query.

  • You cannot use aggregate functions on other columns.

To solve this inconvenience, a rollup table lookup function using the ROLLUP clause has been added.

This functionality is supported since 5.7.

Syntax

SELECT TIME ROLLUP 3 SECOND, AVG(VALUE) FROM TAG WHERE ...;

If you specify a ROLLUP clause after the Datetime column specified in the BASETIME attribute as above, the rollup table lookup is performed.

[BASETIME_COLUMN] ROLLUP [PERIOD] [TIME_UNIT]
  • BASETIME_COLUMN : Datetime column of the TAG table specified by the BASETIME attribute.
  • PERIOD : You can specify the range of time units that can be used in the DATE_TRUNC () function. (See below)
  • TIME_UNIT : All the time units available in the DATE_TRUNC () function can be used. (See below)

Depending on the TIME_UNIT selection, the rollup table to be queried is different.

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

Because using the ROLLUP clause is a direct lookup of the rollup table, using the aggregate function has the following characteristics:

  • You must call an aggregate function on the column specified by the SUMMARIZED attribute. However, only five aggregate functions (SUM, COUNT, MIN, MAX, AVG) are supported in rollup tables.
    • (For non-SUMMARIZED attributes, all aggregate functions can be called only for PRIMARY KEY and METADATA columns.)
  • GROUP BY must be done directly with the BASETIME column to ROLLUP.
    • You can use the same ROLLUP clause as it is.

Alternatively, you can add an alias to the ROLLUP clause and create the alias in GROUP BY.

SELECT time rollup 3 sec mtime, avg(value)FROM TAGGROUP BY time rollup 3 sec mtime;
-- OR
SELECT time rollup 3 sec mtime, avg(value)FROM TAGGROUP BY mtime;

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

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


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

Group at various time intervals

The advantage of the ROLLUP clause is that you don't need to intentionally use DATE_TRUNC () to vary the time interval.

To get the sum of the 3 second intervals and the number of data:
Example The time range is only 0 seconds, 1 second, and 2 seconds, so you can see that all converged to 0 seconds.

As a result, it matches the results of the 'minute rollup' query.

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