Versions Compared

Version Old Version 1 New Version Current
Changes made by

jake shin (Unlicensed)

jake shin (Unlicensed)

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

호출 문법Syntax

Code Block
languagesql
SELECT TIME ROLLUP 3 SECOND, AVG(VALUE) FROM TAG WHERE ...;
위와 같이 BASETIME 속성으로 지정된 Datetime 형 컬럼 뒤에 ROLLUP 절을 붙여 지정하면 롤업 테이블 조회가 된다
As above, if the ROLLUP clause is appended after the Datetime type column specified as the BASETIME attribute, the rollup table is selected.

Code Block
[BASETIME_COLUMN] ROLLUP [PERIOD] [TIME_UNIT]
  • BASETIME_COLUMN :  BASETIME 속성으로 지정된 TAG 테이블의 Datetime 형 컬럼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() 함수에서 사용 가능한 모든 시간 단위를 사용할 수 있다. (아래 참고)

TIME_UNIT 의 선택에 따라, 조회되는 롤업 테이블이 달라진다.

시간 단위 (축약어)시간 범위조회 대상 롤업 테이블
  • 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초1 sec)SECOND
microsecond (usec)60000000 (60초60 sec)SECOND
milisecond (msec)60000 (60초60 sec)SECOND
second (sec)86400 (1일1 day)SECOND
minute (min)1440 (1일1 day)MINUTE
hour24 (1일1 day)HOUR
day1HOUR
month1HOUR
year1HOUR


목차

Table of Contents
maxLevel3
indent30px
exclude목차
classtoc

ROLLUP 절을 사용하는 것은 롤업 테이블 조회를 직접 하는 것이기 때문에, 집계 함수를 사용하려면 다음의 특징이 있다.

SUMMARIZED 속성으로 지정된 컬럼에 집계 함수를 호출해야 한다. 단, 롤업 테이블에서 지원하는 다섯가지 집계 함수

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) 만 지원한다.
    (SUMMARIZED 속성이 아닌 컬럼의 경우, PRIMARY KEY 컬럼과 METADATA 컬럼에 한해서 모든 집계 함수 호출이 가능하다.)ROLLUP 하는 BASETIME 컬럼으로 GROUP BY 를 직접 해야 한다.
  • 같은 의미의 ROLLUP 절을 그대로 사용해도 된다.
    • 또는, ROLLUP 절에 별명 (alias) 를 붙이고, 별명으로 GROUP BY 에 작성해도 된다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
languagesql
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
languagesql
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);

태그 하나에 대해서 3시간 동안 초단위의 각기 다른 값을 입력해 놓았다For one tag, different values ​​in seconds were input for 3 hours.


ROLLUP

강제 갱신

마크베이스는 실시간으로 ROLLUP 데이터를 만들지만, HOUR 혹은 MIN의 경우 해당 시간이 넘어가지 않았을 경우에는 업데이트하지 않는다.

그러나 해당 시간이 되기 이전에라도 ROLLUP 을 확인할 필요가 있으므로, 마크베이스는 EXEC ROLLUP_FORCE 구문을 통해서 강제 갱신을 할 수 있는 기능을 제공한다.

이 함수는 데이터 양에 따라 매우 오래 걸릴 수 있기 때문에 꼭 필요한 경우에만 실행해야 한다. (대부분의 경우 이 함수를 수행할 필요가 없다

Force Update

Machbase creates ROLLUP data in real time. But, if the time has not passed, it is not updated in case of HOUR or MIN .
However, since it is necessary to check ROLLUP even before the relevant time, Machbase provides a function to force update through the EXEC ROLLUP_FORCE statement.
This function can take a very long time depending on the amount of data, so you should only run it when absolutely necessary. (In most cases, you do not need to perform this function.)

Code Block
Mach> EXEC ROLLUP_FORCE;
 Executed successfully.

ROLLUP 평균값 얻기

아래는 해당 태그에 대해 초, 분, 시 단위의 평균값을 얻는 예제이다


Get ROLLUP AVG

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

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


Grouping at Various Time Intervals

The advantage of the ROLLUP clause is that it is not necessary to intentionally use DATE_TRUNC()
2 2018-01-01 03:02:00 000:000:000 11 2 [9] row(s) selected.

다양한 시간 간격으로 그룹화

ROLLUP 절의 장점은, DATE_TRUNC() 를 의도적으로 사용해서 시간 간격을 다변화할 필요가 없다는 것이다.

3초 간격의 합계와 데이터 개수를 얻으려면 아래와 같이 하면 된다.
예제 시간 범위가 0초, 1초, 2초 뿐이라 전부 0초로 수렴된 것을 확인할 수 있다. 결과적으로는 '분 단위 롤업' 조회 결과와 일치한다
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                           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