[퀴즈] 구분자로 나누어 행,열 바꾸기
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 19DESCRIPTION)
<리스트 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 구문까지 사용하여 원하는 결과를 도출!