[퀴즈] 구분자로 나누어 행,열 바꾸기

Q) <리스트 1> 의 원본테이블에서 DESCRIPTION) 에서 설명하는 리스트의 결과를 출력하는 SQL을 작성하세요. (정답 제일 하단)

 

• [리스트 1] 원본리스트

CREATE TABLE t
AS
SELECT 1 no, '1:10|2:11|3:12|4:15' v FROM dual
UNION ALL SELECT 2, '1:17|3:15|4:25' FROM dual
UNION ALL SELECT 3, '2:11|4:15'      FROM dual
UNION ALL SELECT 4, '1:10|2:21|4:19' FROM dual;

SELECT * FROM t;

 

• [표 1] 원본테이블

  NO             V
------  --------------------
  1     1:10|2:11|3:12|4:15
  2     1:17|3:15|4:25
  3     2:11|4:15
  4     1:10|2:21|4:19

 

• [표 2] 결과테이블

GB  1    2    3    4
-- ---- ---- ---- ----
1   10   17        10
3   12   15
2   11        11   21
4   15   25   15   19

DESCRIPTION)

<리스트 1>은 행번호(NO)별 데이터(V) 값을 관리하는 테이블입니다. 이 테이블에 저장되는 값은 정규화가 돼 있지 않아 하나의 컬럼에 여러 개의 값을 한꺼번에 저장합니다.

저장형식은 ‘구분(GB):값’ 형태의 여러 개의 값이 구분자 ‘|’ 으로 구분돼 연결된 형식으로 저장됩니다. 이 때, 행번호(NO)와 구분번호(GB)의 값은 4가지 값(1, 2, 3, 4) 고정입니다.

행번호(NO)에 해당하는 구분 값은 4가지 값이 모두 있지 않을 수도 있습니다. <리스트2>는 구분번호(GB)가 앞으로 오고 행번호가 옆으로 가는 형태로 표현됩니다.

IDEA)

SELECT no AS GB, 
	   SUBSTR( v, 3, INSTR(v, '|',1)-3) as "1", 
       SUBSTR( v, 8, INSTR(v, '|',1)-3) as "2", 
       SUBSTR( v, 13, INSTR(v, '|',1)-3) as "3", 
       SUBSTR( v, 18, INSTR(v, '|',1)-3) as "4"  
FROM T;

첫번째단계로 행번호와 구분번호의 값은 고정이라고 했으니 ALIAS를 1,2,3,4로 주고 각 컬럼을 SUBSTR과 INSTR로 구분자로 나누기를 수행해서 출력을 하려했으나 첫번째 숫자로 구분할 수가 없었다.

GB  1   2   3   4
--  --------------
1   10  11  12  15
2   17	15  25
3   11	15
4   10	21  19

그래서 처음 생각한건 IF문이나 CASE문을 사용해서 조건절로 출력하면 되도록 쿼리를 짜면되지 않을까 생각해봤는데 그렇게 짜면 억지로 결과는 도출할 수 있어도 유연하지 못하고 비효율적인 쿼리가 된다.

 

 

S

O

L

U

T

I

O

N

 

 

SOLUTION)

REGEXP_SUBSTR : SUBSTR의 기능을 확장한 정규식 함수로 주어진 문자열을 대상으로 하여 정규 표현식 패턴을 수행 한 후, 일치하는 하위 문자열을 반환한다.

문법 : REGEXP_SUBSTR( [str], [pattern], [,position[,occurrence[,match_option]]])

 

● 정규식 이용 1 

SELECT no
     , REGEXP_SUBSTR(v, '[^|]+', 1, 1) v1
     , REGEXP_SUBSTR(v, '[^|]+', 1, 2) v2
     , REGEXP_SUBSTR(v, '[^|]+', 1, 3) v3
     , REGEXP_SUBSTR(v, '[^|]+', 1, 4) v4
  FROM t;

 NO 	V1         V2         V3         V4
------ ---------- ---------- ---------- -----------
     1 1:10       2:11       3:12       4:15
     2 1:17       3:15       4:25
     3 2:11       4:15
     4 1:10       2:21       4:19

 

● 정규식 이용 2

SELECT no
     , SUBSTR(REGEXP_SUBSTR(v, '1:[^|]+'), 3) v1
     , SUBSTR(REGEXP_SUBSTR(v, '2:[^|]+'), 3) v2
     , SUBSTR(REGEXP_SUBSTR(v, '3:[^|]+'), 3) v3
     , SUBSTR(REGEXP_SUBSTR(v, '4:[^|]+'), 3) v4
  FROM t;

  NO   V1         V2         V3         V4
----- ---------- ---------- --------- ------
    1 10         11         12         15
    2 17                    15         25
    3            11                    15
    4 10         21                    19

쿼리를 통해 데이터들이 분할되었고 열->행, 행->열 로 바꾸는 pivot작업이 필요하다.

 

SELECT no
     , gb
     , DECODE(gb, 1, v1, 2, v2, 3, v3, 4, v4) v 
  FROM (SELECT no
        , SUBSTR(REGEXP_SUBSTR(v, '1:[^|]+'), 3) v1
        , SUBSTR(REGEXP_SUBSTR(v, '2:[^|]+'), 3) v2
        , SUBSTR(REGEXP_SUBSTR(v, '3:[^|]+'), 3) v3
        , SUBSTR(REGEXP_SUBSTR(v, '4:[^|]+'), 3) v4
        FROM t
        )
     , (SELECT LEVEL gb
          FROM dual
         CONNECT BY LEVEL < = 4
        )
 ORDER BY no, gb;

CONNECT BY LEVEL 계층구조쿼리에 사용하는 함수

  -- ( SELECT LEVEL gb FROM dual CONNECT BY LEVEL < = 4 ) 를 이용해 4개의 gb를 만듬.

DECODE ( 컬럼, 조건1, 출력1, 조건2, 출력2 ...... ) 

  -- 조건없이 조인하여 데이터를 4배로 늘린 후 DECODE 구문을 이용해 구분값에 따라 다르게 표현.

    NO         GB V
------ ---------- ----
     1          1 10
     1          2 11
     1          3 12
     1          4 15
     2          1 17
     2          2
     2          3 15
     2          4 25
     3          1
     3          2 11
     3          3
     3          4 15
     4          1 10
     4          2 21
     4          3
     4          4 19

 

위 쿼리처럼 Cross Join과 DECODE를 통해 열-> 행으로 변환하는 과정을 UNPIVOT 이라고 하는데 실제 11G에서는 UNPIVOT 전용 구문이 있다.

 

● UNPIVOT 구문 사용

SELECT *
  FROM (SELECT no
        , SUBSTR(REGEXP_SUBSTR(v, '1:[^|]+'), 3) v1
        , SUBSTR(REGEXP_SUBSTR(v, '2:[^|]+'), 3) v2
        , SUBSTR(REGEXP_SUBSTR(v, '3:[^|]+'), 3) v3
        , SUBSTR(REGEXP_SUBSTR(v, '4:[^|]+'), 3) v4
        FROM t
        )
 UNPIVOT (v FOR gb IN (v1, v2, v3, v4));

 

      NO GB   V
-------- ---- ----
       1 V1   10
       1 V2   11
       1 V3   12
       1 V4   15
       2 V1   17
       2 V3   15
       2 V4   25
       3 V2   11
       3 V4   15
       4 V1   10
       4 V2   21
       4 V4   19

 

UNPIVOT ( v FOR gb IN (v1, v2, v3, v4)) 구문으로 복잡하게 구현되었던 쿼리가 한줄로 구현됨.

의미 : (v1, v2, v3, v4) 4개의 컬럼을 gb로 구별해 하나의 v로 표현

 

열을 행으로 바꾸었다면 이번에는 반대로 행을 열로 바꿀 차례이므로 UNPIVOT 대신 PIVOT 구문을 사용.

SELECT gb
     , MIN(DECODE(no, 1, v)) "1"
     , MIN(DECODE(no, 2, v)) "2"
     , MIN(DECODE(no, 3, v)) "3"
     , MIN(DECODE(no, 4, v)) "4"
  FROM (SELECT no
        , SUBSTR(REGEXP_SUBSTR(v, '1:[^|]+'), 3) "1"
        , SUBSTR(REGEXP_SUBSTR(v, '2:[^|]+'), 3) "2"
        , SUBSTR(REGEXP_SUBSTR(v, '3:[^|]+'), 3) "3"
        , SUBSTR(REGEXP_SUBSTR(v, '4:[^|]+'), 3) "4"
        FROM t
        )
 UNPIVOT (v FOR gb IN ("1", "2", "3", "4"))
 GROUP BY gb
 ORDER BY gb;

GB 1    2    3    4
-- ---- ---- ---- ----
1  10   17        10
2  11        11   21
3  12   15
4  15   25   15   19

 

위 쿼리에서는 UNPIVOT 결과에 GROUP BY와 MIN(DECODE())구문을 이용해 결과를 도출.

(위 쿼리는 11G 이전 버전의 전통적인 방식으로 11G 부터는 PIVOT 구문 사용이 가능)

 

● PIVOT 구문 사용

SELECT *
  FROM (SELECT no
        , SUBSTR(REGEXP_SUBSTR(v, '1:[^|]+'), 3) "1"
        , SUBSTR(REGEXP_SUBSTR(v, '2:[^|]+'), 3) "2"
        , SUBSTR(REGEXP_SUBSTR(v, '3:[^|]+'), 3) "3"
        , SUBSTR(REGEXP_SUBSTR(v, '4:[^|]+'), 3) "4"
        FROM t
        )
 UNPIVOT (v FOR gb IN ("1", "2", "3", "4"))
 PIVOT (MIN(v) FOR no IN (1, 2, 3, 4));

GB 1    2    3    4
-- ---- ---- ---- ----
1  10   17        10
3  12   15
2  11        11   21
4  15   25   15   19

 

UNPIVOT 쿼리를 사용했을 때와 마찬가지로 UNPIVOT 결과를 인라인뷰를 사용하지 않고 바로 PIVOT하였고 SELECT절에서도 일일이 컬럼을 나열하지 않고 간단히 * 를 사용하여 결과를 출력

 

마지막 PIVOT 구문까지 사용하여 원하는 결과를 도출!