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. Howevernumeric type column. However, only the five six aggregate functions (SUM, COUNT, MIN, MAX, AVG, SUMSQ) 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.
Code Block | ||
---|---|---|
| ||
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.
Code Block | ||
---|---|---|
| ||
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.
Code Block | ||
---|---|---|
| ||
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.
Code Block | ||
---|---|---|
| ||
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.
Code Block | ||
---|---|---|
| ||
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.
Code Block | ||
---|---|---|
| ||
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.
Code Block |
---|
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 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 |
Using ROLLUP for JSON type
Starting from version 7.5, ROLLUP can be used for JSON types.
You can create by adding the JSON PATH and OPERATOR at create statement.
A ROLLUP can be created for each PATH in one JSON column.
Code Block | ||
---|---|---|
| ||
-- create tag table
CREATE TAG TABLE tag (name VARCHAR(20) PRIMARY KEY, time DATETIME BASETIME, jval JSON);
-- insert data
insert into tag values ('tag-01', '2022-09-01 01:01:01', "{ \"x\": 1, \"y\": 1.1}");
insert into tag values ('tag-01', '2022-09-01 01:01:02', "{ \"x\": 2, \"y\": 1.2}");
insert into tag values ('tag-01', '2022-09-01 01:01:03', "{ \"x\": 3, \"y\": 1.3}");
insert into tag values ('tag-01', '2022-09-01 01:01:04', "{ \"x\": 4, \"y\": 1.4}");
insert into tag values ('tag-01', '2022-09-01 01:01:05', "{ \"x\": 5, \"y\": 1.5}");
insert into tag values ('tag-01', '2022-09-01 01:02:00', "{ \"x\": 6, \"y\": 1.6}");
insert into tag values ('tag-01', '2022-09-01 01:03:00', "{ \"x\": 7, \"y\": 1.7}");
insert into tag values ('tag-01', '2022-09-01 01:04:00', "{ \"x\": 8, \"y\": 1.8}");
insert into tag values ('tag-01', '2022-09-01 01:05:00', "{ \"x\": 9, \"y\": 1.9}");
insert into tag values ('tag-01', '2022-09-01 01:06:00', "{ \"x\": 10, \"y\": 2.0}");
-- create rollup
CREATE ROLLUP _tag_rollup_jval_x_sec ON tag(jval->'$.x') INTERVAL 1 SEC;
CREATE ROLLUP _tag_rollup_jval_y_sec ON tag(jval->'$.y') INTERVAL 1 SEC; |
You can also use selecting ROLLUP in the same way.
Code Block | ||
---|---|---|
| ||
Mach> SELECT time ROLLUP 2 SEC mtime, MIN(jval->'$.x'), MAX(jval->'$.x'), SUM(jval->'$.x'), COUNT(jval->'$.x'), SUMSQ(jval->'$.x') FROM tag GROUP BY mtime ORDER BY mtime; mtime min(jval->'$.x') max(jval->'$.x') sum(jval->'$.x') count(jval->'$.x') sumsq(jval->'$.x') ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2022-09-01 01:01:00 000:000:000 1 1 1 1 1 2022-09-01 01:01:02 000:000:000 2 3 5 2 13 2022-09-01 01:01:04 000:000:000 4 5 9 2 41 2022-09-01 01:02:00 000:000:000 6 6 6 1 36 2022-09-01 01:03:00 000:000:000 7 7 7 1 49 2022-09-01 01:04:00 000:000:000 8 8 8 1 64 2022-09-01 01:05:00 000:000:000 9 9 9 1 81 2022-09-01 01:06:00 000:000:000 10 10 10 1 100 [8] row(s) selected. Mach> SELECT time ROLLUP 2 SEC mtime, MIN(jval->'$.y'), MAX(jval->'$.y'), SUM(jval->'$.y'), COUNT(jval->'$.y'), SUMSQ(jval->'$.y') FROM tag GROUP BY mtime ORDER BY mtime mtime min(jval->'$.y') max(jval->'$.y') sum(jval->'$.y') count(jval->'$.y') sumsq(jval->'$.y') ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2022-09-01 01:01:00 000:000:000 1.1 1.1 1.1 1 1.21 2022-09-01 01:01:02 000:000:000 1.2 1.3 2.5 2 3.13 2022-09-01 01:01:04 000:000:000 1.4 1.5 2.9 2 4.21 2022-09-01 01:02:00 000:000:000 1.6 1.6 1.6 1 2.56 2022-09-01 01:03:00 000:000:000 1.7 1.7 1.7 1 2.89 2022-09-01 01:04:00 000:000:000 1.8 1.8 1.8 1 2 2018-01-01 01:01:00 000:000:000 7 3.24 2 20182022-0109-01 01:0205:00 000:000:000 11 1.9 1.9 1.9 2 2018-01-01 02:00:00 000:000:000 3 1 2 2018-01-01 02:01:00 000:000:000 7 3.61 2 20182022-0109-01 0201:0206:00 000:000:000 2 11 2 2018-01-01 03:00:00 000:000:000 3 2 2 2018-01-01 03:01:00 000:000:000 7 1 2 2018-01-01 03:02:00 000:000:000 114 [8] 2row(s) selected. |