최근 현장에서 IoT 센서데이터를 빠르게 저장하고 조회하기 위해 마크베이스 Tag 테이블을 사용하는 케이스가 증가하고 있다.
하지만 Tag 테이블에서 특정 센서(tagID)별로 값을 집계하여 조회할 때 결과가 세로로 출력되어 다소 보기 불편한 점이 있었다.
이는 대부분의 데이터 입력이 PLC 형태로서 가로로 센서 데이터가 연결된 모습으로 입력이 되기 때문이다.
이러한 시각적인 불편함으로 해결하기 위해 마크베이스 5.6 버전에 PIVOT 구문이 새롭게 추가되었다.
이 PIVOT 구문을 활용하면 Tag 테이블에 입력된 다수의 태그 데이터를 시간을 기준으로 정렬하여, 가로로 깔끔하게 볼 수 있다.
이번 포스트에서는 PIVOT 구문의 정의와 사용방법에 대해 알아보고 센서데이터 수집에서 활용할 수 있는 예를 설명하고자 한다.
주의) 이 PIVOT 기능은 기존의 5.5. 버젼에서는 지원되지 않으며, 5.6 버젼은 7월 중으로 릴리즈될 예정이다.
PIVOT 구문
PIVOT 구문은 row로 출력되는 GROUP BY에 대한 집계 결과를 column으로 재배열하여 보여주는 기능이다.
PIVOT 구문은 반드시 Inline view와 함께 사용되어야하며 다음과 같이 동작한다.
– Inline view의 결과 컬럼 중 PIVOT 절에 사용되지 않은 컬럼으로 GROUP BY를 수행한 후 PIVOT IN 절에 나열된 값 별로 집계함수를 수행한다.
– 결과로 나온 grouping 컬럼과 집계 결과를 회전하여 컬럼으로 보여준다.
Pivot 절은 위 그림과 같은 syntax를 같는다.
aggr_func:
– COUNT, SUM, MIN, MAX 와 같은 집계함수가 사용된다
column_name:
– PIVOT 앞에 사용된 inline view의 결과 column 중 하나가 사용된다.
value_expr:
– value_expr 자리에는 constant value가 사용된다. 즉, column 또는 bind variable 은 사용될 수 없다.
예제를 통해 더 자세히 알아보자.
사원 정보를 담는 emp 테이블이 아래와 같이 존재한다.
CREATE TABLE emp (
empno INTEGER,
name VARCHAR(10),
job VARCHAR(10),
sal INTEGER,
deptno INTEGER
);
Mach> SELECT * FROM emp;
EMPNO NAME JOB SAL DEPTNO
------------------------------------------------------------------
7934 MILLER CLERK 1300 10
7902 FORD ANALYST 3000 20
7900 JAMES CLERK 950 30
7876 ADAMS CLERK 1100 20
7844 TURNER SALESMAN 1500 30
7839 KING PRESIDENT 5000 10
7788 SCOTT ANALYST 3000 20
7782 CLARK MANAGER 2450 10
7698 BLAKE MANAGER 2850 30
7654 MARTIN SALESMAN 1250 30
7566 JONES MANAGER 2975 20
7521 WARD SALESMAN 1250 30
7499 ALLEN SALESMAN 1600 30
7369 SMITH CLERK 800 20
[14] row(s) selected.
Example:
모든 직원들을 직책(job)별로 grouping 한 후 부서(deptno)별로 급여(sal)의 합을 구분하여 출력하라.
Mach> SELECT * FROM (SELECT job, sal, deptno FROM emp) PIVOT (SUM(sal) FOR deptno IN (10, 20, 30)); job 10 20 30 -------------------------------------------------------------------------------- MANAGER 2450 2975 2850 PRESIDENT 5000 NULL NULL CLERK 1300 1900 950 SALESMAN NULL NULL 5600 ANALYST NULL 6000 NULL [5] row(s) selected.
직책이 ‘CLERK’ 이면서 부서번호가 10인 사람은 MILLER 1명이고 급여 합은 1300이다.
직책이 ‘CLERK’ 이면서 부서번호가 20인 사람은 ADAMS와 SMITH 2명이고 급여 합은 1900이다.
직책이 ‘CLERK’ 이면서 부서번호가 30인 사람은 JAMES 1명이고 급여 합은 950이다.
IN 절에 있는 값을 column 형태로 보여주기 때문에 질의 결과를 더 쉽게 이해할 수 있다.
하지만 IN 값이 그대로 column으로 출력할 경우 다소 보기 불편하다. 이때 alias를 주면 사용자가 원하는 이름으로 column 이름을 지정할 수 있다.
Mach> SELECT * FROM (SELECT job, sal, deptno FROM emp) PIVOT (SUM(sal) FOR deptno IN (10 AS "HR", 20 AS "RND", 30 AS "SALES")); job HR RND SALES -------------------------------------------------------------------------------- MANAGER 2450 2975 2850 PRESIDENT 5000 NULL NULL CLERK 1300 1900 950 SALESMAN NULL NULL 5600 ANALYST NULL 6000 NULL [5] row(s) selected.
Alias를 통해서 column 이름이 바뀌었음을 알 수 있다.
Execution Plan을 확인하기 위해 EXPLAIN 질의를 수행해 보았다.
Mach> EXPLAIN SELECT * FROM (SELECT job, sal, deptno FROM emp) PIVOT (SUM(sal) FOR deptno IN (10, 20, 30));
PLAN
------------------------------------------------------------------------------------
PROJECT
INLINE VIEW
PIVOT GROUP BY
PROJECT
FULL SCAN
[5] row(s) selected.
새로운 plan node “PIVOT GROUP BY”가 생성되었다.
FROM 절 내부의 SELECT 결과를 PIVOT GROUP BY를 통해 grouping 및 집계함수를 수행하고 그 결과를 외부 SELECT 에서 하나의 inline view로 인식하게 된다.
센서 데이터에 대한 PIVOT 질의 활용
마크베이스는 센서 데이터 수집과 조회에 특화된 tag table을 제공한다.
아래 그림과 같이 fog server에서 tag table을 사용하여 sensor data를 저장하는 예를 살펴보자.
Sensor device에서 sensor 값을 측정하여 주기적으로 fog server 에 보내준다.
Sensor data는 tagID (device ID), 시간, 측정값 등의 정보를 담는다.
이 data 는 fog server의 tag table에 저장된다.
실제 현장에서 사용하는 sensor data를 임의로 생성하여 tag table을 구성해 보았다.
Mach> CREATE TAGDATA TABLE tag (tagid VARCHAR(50) PRIMARY KEY, time DATETIME BASETIME, value DOUBLE SUMMARIZED);
Executed successfully.
machloader -i -t tag -d data.csv -P 5656 -F "time YYYY-MM-DD HH24:MI:SS.mmm"
PORT : 5656 NLS : US7ASCII
EXECUTE MODE : IMPORT TARGET TABLE : tag
DATA FILE : data.csv IMPORT MODE : APPEND
FIELD TERM : , ROW TERM : \n
ENCLOSURE : " ESCAPE : \
ARRIVAL_TIME : FALSE ENCODING : NONE
HEADER : FALSE CREATE TABLE : FALSE
Progress bar Imported records Error records
7539766 0
Import time : 0 hour 0 min 22.182 sec
Load success count : 7539766
Load fail count : 0
Mach> SELECT * FROM _tag_meta;
ID TAGID
----------------------------------------------------------------------------
1 HOIST_VELOCITY
2 HOIST_AXIS
3 SLIDE_AXIS
4 FRONT_AXIS
5 REAR_AXIS
6 REGULATOR_VOLTAGE
7 SLIDE_VELOCITY
8 DRIVING_VELOCITY
9 VEHICLE_POSITION
10 ACCUM_SLIDE_DIST
11 REGULATOR_TEMPERATURE
[11] row(s) selected.
Tag 테이블을 생성하고 임의로 생성한 CSV 파일을 machloader를 이용하여 upload 하였다.
Upload를 마치면 _tag_meta 테이블에 11개의 tagID가 자동으로 생성되었음을 알 수 있다.
11개의 device에서 sensor 측정값을 보내주고 이를 fog server의 tag table에 저장하고 있다고 가정하자.
Example:
Sensor 측정값을 시간대별로 grouping 한 후, 각 device (전방축, 후방축, 승강축, 슬라이드축) 별로 값을 집계하여 출력하라.
Mach> -- without PIVOT
Mach> SELECT * FROM (
SELECT DATE_TRUNC('MINUTE', time, 1) AS ts,
SUM(CASE WHEN tagid = 'FRONT_AXIS' THEN value ELSE 0 END) AS FRONT_AXIS,
SUM(CASE WHEN tagid = 'REAR_AXIS' THEN value ELSE 0 END) AS REAR_AXIS,
SUM(CASE WHEN tagid = 'HOIST_AXIS' THEN value ELSE 0 END) AS HOIST_AXIS,
SUM(CASE WHEN tagid = 'SLIDE_AXIS' THEN value ELSE 0 END) AS SLIDE_AXIS
FROM tag
WHERE tagid IN ('FRONT_AXIS', 'REAR_AXIS', 'HOIST_AXIS', 'SLIDE_AXIS')
GROUP BY ts
) ORDER BY ts DESC LIMIT 20;
ts FRONT_AXIS REAR_AXIS HOIST_AXIS SLIDE_AXIS
------------------------------------------------------------------------------------------------------------------------------------------------------
2018-12-08 04:34:00 000:000:000 0 -40931 -10040 639
2018-12-08 04:33:00 000:000:000 0 -3248 -43215 -1715
2018-12-08 04:32:00 000:000:000 0 -1975 -41703 -1202
2018-12-08 04:31:00 000:000:000 0 5438 -37956 -1803
2018-12-08 04:30:00 000:000:000 0 5022 -38280 -7
2018-12-08 04:29:00 000:000:000 0 -19777 -54481 -1650
2018-12-08 04:28:00 000:000:000 0 -767 -38613 -3982
2018-12-08 04:27:00 000:000:000 0 -8166 -30472 5
2018-12-08 04:26:00 000:000:000 0 11177 -30467 -2465
2018-12-08 04:25:00 000:000:000 0 -53122 -33965 -1054
2018-12-08 04:24:00 000:000:000 0 -3033 -32181 -2294
2018-12-08 04:23:00 000:000:000 0 9514 -36295 -1714
2018-12-08 04:22:00 000:000:000 0 9564 -35140 193
2018-12-08 04:21:00 000:000:000 0 -21154 -46622 -4240
2018-12-08 04:20:00 000:000:000 0 -12793 -28937 -1049
2018-12-08 04:19:00 000:000:000 0 2834 -41371 1865
2018-12-08 04:18:00 000:000:000 0 7115 -17994 -1337
2018-12-08 04:17:00 000:000:000 0 -45497 -25074 172
2018-12-08 04:16:00 000:000:000 0 -13266 -41876 -1869
2018-12-08 04:15:00 000:000:000 0 0 -28977 898
[20] row(s) selected.
Elapsed time: 1.578
PIVOT 기능이 없는 경우 위 질의와 같이 CASE 구문을 사용하여 SQL로 표현할 수 있다.
같은 질의를 PIVOT을 사용하여 아래와 같이 표현할 수 있다.
Mach> -- with PIVOT
Mach> SELECT * FROM (
SELECT DATE_TRUNC('MINUTE', time, 1) AS ts, tagid, value FROM tag
WHERE tagid IN ('FRONT_AXIS', 'REAR_AXIS', 'HOIST_AXIS', 'SLIDE_AXIS')
) PIVOT (SUM(value) FOR tagid IN ('FRONT_AXIS', 'REAR_AXIS', 'HOIST_AXIS', 'SLIDE_AXIS'))
ORDER BY ts DESC LIMIT 20;
ts 'FRONT_AXIS' 'REAR_AXIS' 'HOIST_AXIS' 'SLIDE_AXIS'
------------------------------------------------------------------------------------------------------------------------------------------------------
2018-12-08 04:34:00 000:000:000 NULL -40931 -10040 639
2018-12-08 04:33:00 000:000:000 NULL -3248 -43215 -1715
2018-12-08 04:32:00 000:000:000 NULL -1975 -41703 -1202
2018-12-08 04:31:00 000:000:000 NULL 5438 -37956 -1803
2018-12-08 04:30:00 000:000:000 NULL 5022 -38280 -7
2018-12-08 04:29:00 000:000:000 NULL -19777 -54481 -1650
2018-12-08 04:28:00 000:000:000 NULL -767 -38613 -3982
2018-12-08 04:27:00 000:000:000 NULL -8166 -30472 5
2018-12-08 04:26:00 000:000:000 NULL 11177 -30467 -2465
2018-12-08 04:25:00 000:000:000 NULL -53122 -33965 -1054
2018-12-08 04:24:00 000:000:000 NULL -3033 -32181 -2294
2018-12-08 04:23:00 000:000:000 NULL 9514 -36295 -1714
2018-12-08 04:22:00 000:000:000 NULL 9564 -35140 193
2018-12-08 04:21:00 000:000:000 NULL -21154 -46622 -4240
2018-12-08 04:20:00 000:000:000 NULL -12793 -28937 -1049
2018-12-08 04:19:00 000:000:000 NULL 2834 -41371 1865
2018-12-08 04:18:00 000:000:000 NULL 7115 -17994 -1337
2018-12-08 04:17:00 000:000:000 NULL -45497 -25074 172
2018-12-08 04:16:00 000:000:000 NULL -13266 -41876 -1869
2018-12-08 04:15:00 000:000:000 NULL NULL -28977 898
[20] row(s) selected.
Elapsed time: 0.968
본 글에서는 PIVOT 기능을 통해 PLC 형태의 센서 데이터를 가로로 깔끔하게 출력할 수 있는 기능을 살펴보았다.
이 PIVOT 기능은 질의도 더 간단해질 뿐만 아니라 질의 성능 또한 더 나은 결과를 보여주기 때문에 Tag 데이터를 원래 입력된 모습으로 복원하는데 매우 효율적이며, 편리한 기능이다.
참고로 위에서 사용한 예제는 아래 링크에서 다운로드 받을 수 있으며, 테스트를 위한 좋은 자료가 될 것이다..
https://github.com/MACHBASE/pivot-example
모쪼록 이 기능을 활용하여 현장에서 많은 도움이 되기를 빌면서 이 글을 마친다.
