Posting

Machbase의 최신 소식을 지금 만나보세요

[MACHBASE 기초] PIVOT

최근 현장에서 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
모쪼록 이 기능을 활용하여 현장에서 많은 도움이 되기를 빌면서 이 글을 마친다.

연관 포스트

C언어로 Binary data를 Machbase에 넣기

1.개요 Data를 다루다 보면 numeric, varchar 형 데이터뿐만 아니라 JPG, PNG, MP3와 같은 Binary data도 database에 저장해야 할 때가 존재한다. 그러나 일반 data들과는 달리 Binary

[MACHBASE 연동] Android studio에서 JDBC 연결하기

마크베이스 기술지원본부 이현민 1. 개요 수많은 데이터들이 많은 환경에서 생성되고 있는 오늘날, 우리 현대인들의 동반자인 스마트폰 또한 데이터생성의 주체로써 또는 전달자로서 알게 모르게 그 구실을