마크베이스는 고속의 태그 데이터 추출 성능을 제공하며, 특히 특정 태그의 시간 범위에 대한 탁월한 성능을 제공한다.
이후의 샘플은 아래와 같이 TAG 테이블이 생성되고, 두개의 태그를 생성하였다.
각 태그는 2018년 1월 1일 부터 각각 2월 10일까지 데이터를 입력하였다.
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 metadata values ('TAG_0002');
insert into tag values('TAG_0001', '2018-01-01 01:00:00 000:000:000', 1);
insert into tag values('TAG_0001', '2018-01-02 02:00:00 000:000:000', 2);
insert into tag values('TAG_0001', '2018-01-03 03:00:00 000:000:000', 3);
insert into tag values('TAG_0001', '2018-01-04 04:00:00 000:000:000', 4);
insert into tag values('TAG_0001', '2018-01-05 05:00:00 000:000:000', 5);
insert into tag values('TAG_0001', '2018-01-06 06:00:00 000:000:000', 6);
insert into tag values('TAG_0001', '2018-01-07 07:00:00 000:000:000', 7);
insert into tag values('TAG_0001', '2018-01-08 08:00:00 000:000:000', 8);
insert into tag values('TAG_0001', '2018-01-09 09:00:00 000:000:000', 9);
insert into tag values('TAG_0001', '2018-01-10 10:00:00 000:000:000', 10);
insert into tag values('TAG_0002', '2018-02-01 01:00:00 000:000:000', 11);
insert into tag values('TAG_0002', '2018-02-02 02:00:00 000:000:000', 12);
insert into tag values('TAG_0002', '2018-02-03 03:00:00 000:000:000', 13);
insert into tag values('TAG_0002', '2018-02-04 04:00:00 000:000:000', 14);
insert into tag values('TAG_0002', '2018-02-05 05:00:00 000:000:000', 15);
insert into tag values('TAG_0002', '2018-02-06 06:00:00 000:000:000', 16);
insert into tag values('TAG_0002', '2018-02-07 07:00:00 000:000:000', 17);
insert into tag values('TAG_0002', '2018-02-08 08:00:00 000:000:000', 18);
insert into tag values('TAG_0002', '2018-02-09 09:00:00 000:000:000', 19);
insert into tag values('TAG_0002', '2018-02-10 10:00:00 000:000:000', 20); |
Mach> select * from tag; NAME TIME VALUE -------------------------------------------------------------------------------------- TAG_0001 2018-01-01 01:00:00 000:000:000 1 TAG_0001 2018-01-02 02:00:00 000:000:000 2 TAG_0001 2018-01-03 03:00:00 000:000:000 3 TAG_0001 2018-01-04 04:00:00 000:000:000 4 TAG_0001 2018-01-05 05:00:00 000:000:000 5 TAG_0001 2018-01-06 06:00:00 000:000:000 6 TAG_0001 2018-01-07 07:00:00 000:000:000 7 TAG_0001 2018-01-08 08:00:00 000:000:000 8 TAG_0001 2018-01-09 09:00:00 000:000:000 9 TAG_0001 2018-01-10 10:00:00 000:000:000 10 TAG_0002 2018-02-01 01:00:00 000:000:000 11 TAG_0002 2018-02-02 02:00:00 000:000:000 12 TAG_0002 2018-02-03 03:00:00 000:000:000 13 TAG_0002 2018-02-04 04:00:00 000:000:000 14 TAG_0002 2018-02-05 05:00:00 000:000:000 15 TAG_0002 2018-02-06 06:00:00 000:000:000 16 TAG_0002 2018-02-07 07:00:00 000:000:000 17 TAG_0002 2018-02-08 08:00:00 000:000:000 18 TAG_0002 2018-02-09 09:00:00 000:000:000 19 TAG_0002 2018-02-10 10:00:00 000:000:000 20 [20] row(s) selected. Elapsed time: 0.002 |
위와 같이 특별한 조건이 없으면, 각 시간 순으로 소팅된 태그별로 데이터를 추출할 수 있다.
아래는 TAG 이름이 TAG_0002 인 데이터를 출력하는 예제이다. WHERE 절에 주어진 name에 대한 조건을 설정한다.
Mach> select * from tag where name='TAG_0002'; NAME TIME VALUE -------------------------------------------------------------------------------------- TAG_0002 2018-02-01 01:00:00 000:000:000 11 TAG_0002 2018-02-02 02:00:00 000:000:000 12 TAG_0002 2018-02-03 03:00:00 000:000:000 13 TAG_0002 2018-02-04 04:00:00 000:000:000 14 TAG_0002 2018-02-05 05:00:00 000:000:000 15 TAG_0002 2018-02-06 06:00:00 000:000:000 16 TAG_0002 2018-02-07 07:00:00 000:000:000 17 TAG_0002 2018-02-08 08:00:00 000:000:000 18 TAG_0002 2018-02-09 09:00:00 000:000:000 19 TAG_0002 2018-02-10 10:00:00 000:000:000 20 [10] row(s) selected. Elapsed time: 0.001 Mach> |
아래는 TAG_0002에 대한 시간 범위를 주고, 데이터를 받아오는 쿼리이다.
between 절을 활용해서 시간 범위를 주는 것이 일반적인 방법이다.
그냥 time을 < 혹은 > 기호로 범위를 줘도 유사한 결과를 얻을 수 있다.
Mach> select * from tag where name = 'TAG_0002' and time between to_date('2018-02-01') and to_date('2018-02-05');
NAME TIME VALUE
--------------------------------------------------------------------------------------
TAG_0002 2018-02-01 01:00:00 000:000:000 11
TAG_0002 2018-02-02 02:00:00 000:000:000 12
TAG_0002 2018-02-03 03:00:00 000:000:000 13
TAG_0002 2018-02-04 04:00:00 000:000:000 14
[4] row(s) selected.
Elapsed time: 0.001
Mach> select * from tag where name = 'TAG_0002' and time > to_date('2018-02-01') and time < to_date('2018-02-05');
NAME TIME VALUE
--------------------------------------------------------------------------------------
TAG_0002 2018-02-01 01:00:00 000:000:000 11
TAG_0002 2018-02-02 02:00:00 000:000:000 12
TAG_0002 2018-02-03 03:00:00 000:000:000 13
TAG_0002 2018-02-04 04:00:00 000:000:000 14
[4] row(s) selected.
Elapsed time: 0.001 |
아래는 2개 이상의 태그에 대해서 동일한 시간 범위 데이터를 검색하는 예제이다.
만일 한번의 질의 수행으로 다수의 태그에 대해 한꺼번에 빠른 결과를 받고 싶을 경우에는 아래와 같은 형태의 수행이 바람직하다.
Mach> select * from tag where name in ('TAG_0002', 'TAG_0001') and time between to_date('2018-01-05') and to_date('2018-02-05');
NAME TIME VALUE
--------------------------------------------------------------------------------------
TAG_0001 2018-01-05 05:00:00 000:000:000 5
TAG_0001 2018-01-06 06:00:00 000:000:000 6
TAG_0001 2018-01-07 07:00:00 000:000:000 7
TAG_0001 2018-01-08 08:00:00 000:000:000 8
TAG_0001 2018-01-09 09:00:00 000:000:000 9
TAG_0001 2018-01-10 10:00:00 000:000:000 10
TAG_0002 2018-02-01 01:00:00 000:000:000 11
TAG_0002 2018-02-02 02:00:00 000:000:000 12
TAG_0002 2018-02-03 03:00:00 000:000:000 13
TAG_0002 2018-02-04 04:00:00 000:000:000 14
[10] row(s) selected.
Elapsed time: 0.001 |