퀴즈로 배우는 SQL
목록
[퀴즈] 구분자로 나누어 행,열 바꾸기 3 4 99,999+

by 마농 UNPIVOT PIVOT INSTR REGEXP_SUBSTR 행을열로 열을행으로 [2015.12.21]

이번 퀴즈로 배워보는 SQL 시간에는 구분자로 데이터를 분할하고, 행과 열을 바꾸어 출력하는 SQL 문제를 풀어본다. 지면 특성상 문제와 정답 그리고 해설이 같이 있다.

진정으로 자신의 SQL 실력을 키우고 싶다면 스스로 문제를 해결한 다음 정답과 해설을 참조하길 바란다. 공부를 잘하는 학생의 문제집은 항상 문제지면의 밑바닥은 까맣지만 정답과 해설지면은 하얗다는 사실을 기억하자.

문제

<리스트 1> 의 원본테이블에서 <리스트 2>의 결과를 출력하는 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

문제설명

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

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

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

정답

문제를 스스로 해결해 보셨나요? 이제 정답을 알아보겠습니다.

  • [리스트 2] 정답 리스트
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))
;

어떤가요? 여러분이 만들어본 리스트와 같은가요? 틀렸다고 좌절할 필요는 없답니다. 첫 술에 배부를 순 없는 것이니까요. 해설을 꼼꼼히 보고 자신이 잘못한 점을 비교해 보는 것이 더 중요합니다.

해설

이번 문제는 다가 속성을 가진 비정규화 컬럼의 값을 분리해 내고, 분리한 결과 값을 행과 열을 전환해 출력하는 문제입니다. 차근차근 단계별로 접근해 보도록 하겠습니다.

첫 번째 단계는 구분자를 이용한 데이터 분할입니다. 구분자의 위치를 파악하는데는 INSTR 함수를 이용할 수 있습니다. 문자를 잘라내는 함수는 SUBSTR입니다. 두 가지 함수를 이용해 문자열을 분리하겠습니다.

  • [리스트 3] INSTR 을 이용한 문자열 분리 시도
  • SELECT no
     , SUBSTR(v, 1, INSTR(v, '|') - 1) v1
     , SUBSTR(v, INSTR(v, '|', 1, 1) + 1
               , INSTR(v, '|', 1, 2)
               - INSTR(v, '|', 1, 1) - 1
               ) v2
     , SUBSTR(v, INSTR(v, '|', 1, 2) + 1
               , INSTR(v, '|', 1, 3)
               - INSTR(v, '|', 1, 2) - 1
               ) v3
     , SUBSTR(v, INSTR(v, '|', 1, 3) + 1) v4
  FROM t
;

  • [표 3] INSTR 을 이용한 문자열 분리 시도
  •      NO V1         V2         V3         V4
------- ---------- ---------- ---------- -----------------
      1 1:10       2:11       3:12       4:15
      2 1:17       3:15                  1:17|3:15|4:25
      3 2:11                             2:11|4:15
      4 1:10       2:21                  1:10|2:21|4:19

<리스트 3>의 쿼리를 통해 <표 3>의 결과를 얻었습니다. 그런데 결과가 이상합니다. 네 개 구분이 모두 있는 1번 자료를 제외한 나머지 자료의 결과가 틀렸습니다.

쿼리도 상당히 복잡하고 이해하기도 어렵습니다. 이렇게 반복되는 문자 패턴을 잘라낼 때 유용한 것이 바로 정규식 이용입니다. 정규식을 이용해 보도록 하겠습니다.

  • [리스트 4] 정규식 이용
  • 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
;

  • [표 4] 정규식 이용
  •     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

<리스트 4>에서는 정규식 함수인 REGEXP_SUBSTR 을 이용해 데이터를 분리했습니다.

‘[^|]+’는 |가 아닌 문자열이 여러개 반복되는 형태를 표현합니다. 다르게 표현하면 구분자인 | 사이의 값을 의미합니다.

(1, 1)은 첫 번째 자리부터 검색해 첫 번째 값을 찾으라는 의미입니다. 마지막 숫자를 바꿔가면서 네 개의 값을 구한 것입니다.

  • [리스트 5] 정규식 이용2
  • SELECT no
     , REGEXP_SUBSTR(v, '1:[^|]+') v1
     , REGEXP_SUBSTR(v, '2:[^|]+') v2
     , REGEXP_SUBSTR(v, '3:[^|]+') v3
     , REGEXP_SUBSTR(v, '4:[^|]+') v4
  FROM t
;

  • [표 5] 정규식 이용2
  •     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

<표 5>에서는 구분별로 컬럼이 나뉘었습니다. 구분별로 컬럼이 나뉘었으므로 콜론(:) 앞의 구분은 없애도 의미가 손상되지 않습니다.

  • [리스트 6] 정규식 이용3
  • 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
;

  • [표 6] 정규식 이용3
  •    NO V1         V2         V3         V4
----- ---------- ---------- ---------- ------
    1 10         11         12         15
    2 17                    15         25
    3            11                    15
    4 10         21                    19

<리스트 6>의 쿼리를 통해 데이터 분할이 완성됐습니다. 이번에는 행과 열이 서로 뒤바뀌어야 합니다. 열을 행으로 바꾸고, 다시 또 행을 열로 바꾸어야 합니다.

  • [리스트 7] 열을 행으로
  • 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
;

  • [표 7] 열을 행으로
  •     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

<리스트 7> 에서는 CONNECT BY LEVEL < = 4를 이용해 네 개의 GB를 만들어내고

  (SELECT LEVEL gb FROM dual CONNECT BY LEVEL < = 4)

이를 조건 없이 조인해 데이터를 네 배로 늘리는 효과가 나타나게 됩니다. 이때 늘어난 데이터는 DECODE 구문을 이용해 구분값에 따라 다르게 표현합니다.

    , DECODE(gb, 1, v1, 2, v2, 3, v3, 4, v4) v

이렇게 Cross Join 과 Decode 를 총해 열을 행으로 변환하는 과정을 UNPIVOT이라고 하는데요, 실제로 11G 에서는 UNPIVOT 전용 구문이 존재합니다.

  • [리스트 8] 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))
;

  • [표 8] UNPIVOT
  •       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

<리스트 8> 에서 UNPIVOT 구문이 사용됐습니다.

    UNPIVOT (v FOR gb IN (v1, v2, v3, v4))

<리스트 7> 에서 복잡하게 구현했던 부분이 UNPIVOT 한줄 작성으로 구현됐습니다. 구문의 의미를 살펴보면 (v1, v2, v3, v4) 4개의 컬럼을 gb 로 구별해 하나의 v 로 표현하라는 의미입니다.

열을 행으로 바꾸었다면 이번에는 반대로 행을 열로 바꿀 차례입니다. UNPIVOT 구문이 있다면 PIVOT 구문도 존재하겠지요. PIVOT 구문을 살펴보기 전에 우선 예전 방식대로 구현하겠습니다.

  • [리스트 9] 행을 열로 
  • 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
;

  • [표 9] 행을 열로
  • GB 1    2    3    4
-- ---- ---- ---- ----
1  10   17        10
2  11        11   21
3  12   15
4  15   25   15   19

<리스트 9>에서는 <리스트 8>의 UNPIVOT 결과에 GROUP BY 와 MIN(DECODE()) 구문을 이용해서 <표 9>의 결과를 도출했습니다. 이것이 바로 행을 열로 전환하는 전통적인 방법이며 11G 부터는 PIVOT 구문 사용이 가능합니다.

여기서 눈여겨볼 것이 하나 있는데요. 그것은 UNPIVOT 결과를 인라인뷰로 사용해 결과를 얻어낸 것이 아니라 인라인뷰 없이 UNPIVOT 결과를 바로 사용했다는 점입니다.

UNPIVOT 구문은 FROM 절에서 동작해 FROM 절에서 동작이 끝나며, 이미 FROM 절 안에서 결과집합이 완성돼 나오는 것입니다.

마지막으로 행을 열로 전환하는 전통적인 방법 대신 PIVOT 방식을 사용해 보도록 하겠습니다.

  • [리스트 10] 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))
;

  • [표 10] [리스트 10] PIVOT 
  • GB 1    2    3    4
-- ---- ---- ---- ----
1  10   17        10
3  12   15
2  11        11   21
4  15   25   15   19

<리스트 10> 에서는 PIVOT 을 사용해 정답쿼리를 완성했습니다.

  PIVOT (MIN(v) FOR no IN (1, 2, 3, 4))

마찬가지로 UNPIVOT 결과를 인라인뷰로 사용하지 않고 바로 PIVOT했습니다. SELECT절에서도 일일이 컬럼을 나열하지 않고 간단하게 *를 사용했습니다.

간단하게 UNPIVOT 한줄과 PIVOT 한줄 작성으로 정답쿼리가 완성됐습니다. 이번 퀴즈로 배우는 SQL 시간에 다룬 내용을 정리해 볼까요?

  • - 정규표현식(regular expression) 함수를 이용한 데이터 분할
  • - 열을 행으로 바꾸기 : UNPIVOT
  • - 행을 열로 바꾸기 : PIVOT

- 강좌 URL : http://www.gurubee.net/lecture/2956

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

목록
by 백종현 [2017.09.04 14:09:15] 
WITH 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
    GB,
    MIN(DECODE(NO,1,V)) AS "1",
    MIN(DECODE(NO,2,V)) AS "2",
    MIN(DECODE(NO,3,V)) AS "3",
    MIN(DECODE(NO,4,V)) AS "4" 
FROM (
    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)
     )
GROUP BY GB;
by 박군two [2017.09.14 13:37:19] 
WITH T(NO, V) AS (
    SELECT 1, '1:10|2:11|3:12|4:15' 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   (SELECT D.NO,
               SUBSTR(D.EACH_DATA, 1, INSTR(D.EACH_DATA, ':')-1) DATA_NO,
               SUBSTR(D.EACH_DATA, INSTR(D.EACH_DATA, ':')+1)      DATA_CONTENT
        FROM   (SELECT T2.NO,
                       SUBSTR(T2.V2, INSTR(T2.V2, '|', 1, T1.LV)+1, INSTR(T2.V2, '|', 1, T1.LV+1) - INSTR(T2.V2, '|', 1, T1.LV)-1) EACH_DATA
                FROM   (SELECT LEVEL LV
                        FROM   DUAL
                        CONNECT BY LEVEL <= (SELECT MAX(LENGTH(T.V) - LENGTH(REPLACE(T.V, '|')) + 1) DATA_CNT FROM T )
                       ) T1,
                       (SELECT T.NO, T.V, '|' || T.V || '|' V2, LENGTH(T.V) - LENGTH(REPLACE(T.V, '|')) + 1 DATA_CNT               
                        FROM   T
                       ) T2
                WHERE  T1.LV <= T2.DATA_CNT
               ) D
        )
PIVOT ( MAX(DATA_CONTENT) FOR NO IN (1, 2, 3, 4))
ORDER BY DATA_NO

 

by 봄빛 [2018.10.05 11:42:54]

어렵네요 답안보고 풀었는데 40라인이상 나와서 답보고 겨우 수정했네요 

--postgreSQL

SELECT SUBSTRING( UNNEST(REGEXP_MATCHES(v, '1:[^|]+')) ,3) AS "1"
,SUBSTRING( UNNEST(REGEXP_MATCHES(v, '2:[^|]+')) ,3) AS "2"
,SUBSTRING( UNNEST(REGEXP_MATCHES(v, '3:[^|]+')) ,3) AS "3"
,SUBSTRING( UNNEST(REGEXP_MATCHES(v, '4:[^|]+')) ,3) AS "4"
FROM T;

 

by 동글이 [2023.07.06 10:58:31]

문제풀이 들어가려고 하는데

처음부터 너무 어렵네요 ㅎㅎ 

그래도 5년차인데 ㅠㅠ

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입