[2과목] 2-15. PIVOT과 UNPIVOT

■ 데이터의 구조

 

1) LONG DATA (Tidy Data)

• 하나의 속성이 하나의 컬럼으로 정의되어 값들이 여러 행으로 쌓이는 구조
• RDBMS의 테이블 설계 방식
• 다른 테이블과의 조인 연산이 가능한 구조

 

 

2) WIDE DATA (Cross table)

• 행과 컬럼에 유의미한 정보 전달을 목적으로 작성하는 교차표
• 하나의 속성값이 여러 컬럼으로 분리되어 표현
• RDBMS에서 WIDE 형식으로 테이블 설계시 값이 추가될 때마다 컬럼이 추가되어야 하므로 비효율적임
• 다른 테이블과의 조인 연산이 불가함
• 주로 데이터를 요약할 목적으로 사용

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

 

 

 

■ 데이터 구조 변경

 

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 컬럼 모두 명시!

 

 

 

 

ex1) emp 테이블에서 아래와 같이 job별, deptno별 도수(count) 출력

select *
from (select empno, job, deptno from emp)
pivot (count(empno) for deptno in (10,20,30));

 

 

 

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

select * 
from (select empno, deptno from emp)
pivot (count(empno) for deptno in(10,20,30));

 

 

 

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

select * 
from emp
pivot (count(empno) for deptno in(10,20,30));

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

 

 

 

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

 

unstack_test 테이블

select * 
from (select 년도, 성별, 구매량 from unstack_test)
pivot (sum(구매량) for 년도 in (2008,2009));

 

 

 

2) UNPIVOT : WIDE → LONG

• WIDE 데이터를 LONG 데이터로 변경하는 문법
• STACK 컬럼 : 이미 UNSTACK되어 있는 여러 컬럼을 하나의 컬럼으로 STACK시 새로 만들 컬럼명 (사용자 정의)
• VALUE 컬럼 : 교차표에서 셀 자리(VALUE)값을 하나의 컬럼으로 표현하고자 할 때 새로 만들 컬럼명 (사용자 정의)
• 값1, 값2 : 실제 UNSTACK돠어 있는 컬럼이름들

 

** 문법

select * 
from 테이블명 또는 서브쿼리
unpivot (value컬럼명 for stack컬럼명 in (값1, 값2, ...));

 

 

 

ex1) 위의 예제의 unstack_test의 pivot 결과가 stack_table에 저장되어 있을 때, 다시 stack_test 테이블의 값을 unstack_test 형태로 변경 (stack 처리)

stack_test

select * 
from stack_test
unpivot (cnt for 년도 in ("2008","2009"));

▷ IN 뒤에 값은 UNSTACK 데이터의 컬럼명이 숫자이지만 컬럼명은 문자로 저장되므로 쌍따옴표 전달 필요!

 

 

ex2) STACK 처리 (LONG DATA로 변환)

TT5

select *
from TT5
unpivot (구매건수 for 요일 in (월,화,수,목,금,토,일));

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