[SQL] 데이터의 구조를 변경하는 기능 (PIVOT, UNPIVOT)

데이터의 구조

1)LONG DATA(tidy data)

-하나의  속성이 하나의 컬럼으로 정의되어 값들이 여러 행으로 쌓이는 구조

- PDBMS의 테이블 설계 방식

- 다른 테이블과의 조인 연산이 가능한 구조

 

1) LONG DATA(Tidy data)

우리가 알고있는 DBMS의 테이블형태

길게 생겼다(LONG DATA) 또는 깔끔하게 생겼다(Tidy data)라고 함

 

LONG DATA(Tidy data)

EMPNO	ENAME	JOB	MGR	HIREDATE
7369	SMITH	CLERK	7902	1980/12/17 00:00:00
7499	ALLEN	SALESMAN	7698	1981/02/20 00:00:00
7521	WARD	SALESMAN	7698	1981/02/22 00:00:00
7566	JONES	MANAGER	7839	1981/04/02 00:00:00
7654	MARTIN	SALESMAN	7698	1981/09/28 00:00:00
7698	BLAKE	MANAGER	7839	1981/05/01 00:00:00
7782	CLARK	MANAGER	7839	1981/06/09 00:00:00
7788	SCOTT	ANALYST	7566	1987/04/19 00:00:00
7839	KING	PRESIDENT		1981/11/17 00:00:00

 

2) WIDE DATA(Cross talbe)

- 행과 컬럼에 유의미한 정보 전달을 목적으로 작성하는 교차표

- 하나의 속성값이 여러 컬럼으로 분리되어 표현

- RDBMS 에서 WIDE 형식으로 테이블 성계시 값이 추가될 때 마다 컬럼이 추가돼야 하므로 비효율적!

- 다른 테이블과의 조인 연산이 불가함

- 주로 데이터를 요약할 목적으로 사용

 

WIDE DATA(Cross talbe)

JOB	10	20	30
CLERK	1	2	1
SALESMAN	0	0	4
PRESIDENT	1	0	0
MANAGER	1	1	1
ANALYST	0	2	0

컬럼의 정보는 부서번호로, 하나의 관찰대상(속성)을 한 컬럼으로 정의하지 않고 값의 종류별로 컬럼을 분리하였음

 

 

데이터 구조 변경

1)PIVOT : LONG -> WIDE

 

-교차표를 만드는 기능

-STACK 컬럼, UNSTACK 컬럼, VALUE 컬럼의 정의가 중요!

-FROM 절에  STACK, UNSTACK, VALUE 컬럼명만 정의 필요(필요 시 서브쿼리 사용하여 필요 컬럼 제한)

-PIVOT 절에 UNSTACK, VALUE 컬럼명 정의

-PIVOT 절 IN 연산자에 UNSTACK 컬럼 값을 정의

-FROM 절에 선언된 컬럼중 PIVOT 절에서 선언한 VALUE 컬럼, UNSTACK 컬럼을 제외한 모든 컬럼은 STACK컬럼이 됨

#문법
SELECT *
FROM 테이블명 또는 서브쿼리
PIVOT (VALUE컬럼명 FOR UNSTACK컬럼명 IN (값1,값2,값3));

* 반드시 FROM절에 STACK컬럼, UNSTACK컬럼, VALUE 컬럼 모두 명시!

 

 

예제1.) EMP 테이블에서 아래와 같이 JOB별 DEPTNO별 도수(COUNT) 출력

JOB	10	20	30
CLERK	1	2	1
SALESMAN	0	0	4
PRESIDENT	1	0	0
MANAGER	1	1	1
ANALYST	0	2	0

 

 

<정답>

SELECT *
FROM (SELECT EMPNO, JOB, DEPTNO FROM EMP)
PIVOT (COUNT(EMPNO) FOR DEPTNO IN (10,30,30));

JOB	10	20	30
CLERK	1	2	1
SALESMAN	0	0	4
PRESIDENT	1	0	0
MANAGER	1	1	1
ANALYST	0	2	0

 

 

<주의> 이 때 FROM절 서브쿼리 안에 JOB이 없으면 아래와 같이 그냥 부서별로의 도수가 출력됨

SELECT *
FROM (SELECT EMPNO, DEPTNO FROM EMP)
POVOT (COUNT(EMPNO) FOR DEPTNO IN (10,20,30));

10	20	30
3	5	6

 

 

<주의> FROM 절에 서브쿼리로 컬럼을 제한하지 않으면 STACK컬럼이 많아짐

SELECT *
FROM EMP
POVOT (COUNT(EMPNO) FOR DEPTNO IN (10,20,30));

ENAME	JOB	MGR	HIREDATE	SAL	COM	10	20	30
JAMES	CLERK	7689	1981/12/03 00:00:00	950		0	0	1
MILLER	CLERK	7782	1982/01/23 00:00:00	1300		1	0	0
SCOTT	ANALYST	7566	1987/04/19 00:00:00	3000		0	1	0
ADAMS	CLERK	7788	1987/05/23 00:00:00	1100		0	1	0
SMITH	CLERK	7902	1980/12/17 00:00:00	800		0	1	0
CLARK	MANAGER	7839	1981/06/09 00:00:00	2450		1	0	0

<설명>

FROM 절에 서브쿼리로 필요한 컬럼만 정의하지 않으면 EMP 테이블의 모든 컬럼 중 PIVOT 절에 선언된 EMPNO, DEPTNO컬럼을 제외한 모든 컬럼이 STACK처리 됨

 

 

 

예제2.) 다음의 테이블에서 성별, 연도별 구매량 총 합을 표현하는 교차표 작성

SELECT * FROM UNSTACK_TEST;

 

년도	성별	지역	구매량
2008	남자	서울	1
2008	남자	경기	2
2008	여자	서울	3
2008	여자	경기	4
2009	남자	서울	5
2009	남자	경기	6
2009	여자	서울	7
2009	여자	경기	8

    ----------------------->
    STACK컬럼 : 성별     

성별	2008	2009
남자	3	11
여자	7	15

 

 

<정답>

SELECT *
FROM (SELECT 년도, 성별, 구매량 FROM UNSTACK_TEST)
PIVOT (SUM(구매량) FOR 년도 IN (2008, 2009));

성별	2008	2009
남자	3	11
여자	7	15

 

 

2)UNPIVOT: WIDE -> LONG

-WIDE 데이터를 LONG 데이터로 변경하는 문법

-STACK 컬럼 : 이미 UNSTACK 되어 있는 여러 컬럼을 하나의 컬럼으로 STACK시 새로 만들 컬럼이름(사용자 정의)

-VALUE 컬럼 : 교차표에서 셀 자리(VALUE)값을 하나의 컬럼으로 표현하고자 할 때 새로 만들 컬럼명(사용자 정의)

-값1, 값2... : 실제 UNSTACK 되어 있는 컬럼이름

 

*만약 컬럼이 숫자형이면 쌍따옴표로 묶어서 전달해야함

#문법
SELECT *
FROM 테이블명 또는 서브쿼리
UNPIVOT (VALUE컬럼명 FOR STACK컬럼명 IN (값1,값2,.....));

 

 

예제) 위 UNSTACK_TEST PIVOT 결과가 STACK_TEST 테이블에 저장되어 있을 때, 다시 STACK_TEST 테이블의 값을 UNSTACK_TEST 형태로 변경(STACK 처리)

 

<정답>

SELECT *
FROM STACK_TEST
UNPIVOT (CNT FOR 년도 IN ("2008","2009"));

성별	년도	CNT
남자	2008	3
남자	2009	11
여자	2008	7
여자	2009	15

<설명> IN 뒤에 값은 INSTACK 데이터의 컬럼명이 숫자이지만 컬럼명은 문자로 저장되므로 쌍따움표 전달 필요

 

 

 

 

예제) 아래 테이블 생성 후 stack처리

SELECT *
FROM TT5;

 

MONTH	월	화	수	목	금	토	일
01	10	20	30	40	45	35	60
02	39	45	75	34	65	45	33
03	13	24	67	43	45	34	56
04	34	50	34	22	24	43	44

 

 

<정답>

SELECT *
FROM TT5
UNPIVOT (구매건수 FOR 요일 IN(월,화,수,목,금,토,일));

MONTH	요일	구매건수
01	월	10
01	화	20
01	수	30
01	목	40
01	금	45
01	토	35
01	일	60
02	월	39
02	화	45
02	수	75
02	목	34

월,화,수,목  값들은 컬럼명이므로 컬럼명과 테이블명처럼 대명사(객체이름)는 쌍따옴표를 붙이지 않음