SQL 데이터 변환 - Pivot , Unpivot

ROW 를 COLUMN 로 출력하기 (2) PIVOT

pivot = 회전하다.

SUMDECODE 는 너무 길어서 PIVOT 으로도 행을 컬럼으로 출력 할 수 있다. 

• 행을 컬럼으로 출력하는 함수 : PIVOT
• 컬럼을 행으로 출력하는 함수 : UNPIVOT
• 지금까지 SUM+DECODE문으로 출력했는데 PIVOT 문으로도 가로로 출력 할 수 있다.
• 쿼리 실행 순서 : from - pivot - select
• from 절에서 : 필요한 컬럼만 선정하는것 (select 절로)
• pivot 문에는 from 절에서 선정한 컬럼명만 쓸 수 있다.

예제 : 부서번호와 부서번호별 토탈월급을 출력하는데 가로로 출력하세요

(데이터를 컬럼으로 빼기)

select * 
from ( select deptno, sal from emp);

→ from 자리에 () 괄호를 열고 select 절을 입력함 (테이블 명 자리에 셀렉트 문장을 하나 쓴것,)

emp 테이블에서 내가 필요한 컬럼만 선정해서 가지고 와서 from 절에 쓴것이라 할 수 있다. 

→ 이대로 출력하면, 부서번호와, 월급이 세로로 출력이 된다.

 

→ PIVOT 문 사용

select *
from ( select deptno, sal from emp)
pivot ( sum(sal) for deptno in (10, 20, 30)) ;

PIVOT 직역 :

1. 토탈월급을 출력하는데 뭐에대한 (for) 토탈월급이냐?
2. 부서번호에 대한 토탈월급
3. 그럼 어떤 부서번호에 대한 것이냐 (in) ? 10, 20, 30 번에 대한 것입니다.
4. select 절의 * 의 의미는 ? 10,20,30 번 모두를 출력하겠다.select "10","20" 로 입력
5. 만약 10,20 번만 나오고 싶다면

→ from 절을 실행하면서 emp 테이블의 모든 컬럼을 다 가져오는것이 아니라, 부서번호와 월급데이터만 가져온다.

그다음에 pivot 문을 수행하는데 ,

가로로 회전된 출력 결과를 보고싶다는 의미이다.

PIVOT 문의 단점 : PIVOT 문을 입력할때 → IN ( 일일히 데이터값) 을 입력해야한다.

EX) IN( 10,20,30)

데이터가 40 번이 추가 되면 IN 에 40 번을 추가 해 주어야한다.

데이터 (엄청 귀찮음)

문제 203 : 우리반 테이블로 쿼리 작성, 클래스 타입클래스 타입별 토탈 나이를 출력하시오) 세로출력

select class_type, sum(age) 
from emp14
group by class_type;

문제 : 위 문제를 다시 가로로출력 (PIVOT 사용)

select *

from ( 위의 결과를 보기 위한 컬럼)

pivot ( 위의 결과를 보기위한 문법 ) ;

select * 
from (select class_type, age from emp14)
pivot (sum(age) for class_type in ('A','B'));

→ 컬럼에 'A' 이라고 나오는 싱글쿼테이션 결과값이 보기 싫으면 as 하고 " 더블 퀘테이션 마크로 둘러주어 다시작성

select *
from (select class_type, age from emp14)
pivot (sum(age) for class_type in ('A'as "A",'B'as "B"));

문제 205 : 직업, 직업별 토탈월급 출력 (세로출력)

select job, sum(sal) from emp group by job;

문제 206 : 위의 내용 가로출력

문제 207 : emp14_address를 조회해서 아래와 같이 지역을 뽑고, 지역과 지역별 평균나이를 출력하시오

select round(강원도)as 강원도,
       round(경기도) as 경기도, 
       round(서울시) as 서울시,
       round(울산) as 울산,
       round(충청북도) as 충청북도 
from (select address2, age from emp14_address)
pivot (avg(age) for address2 in( '강원도'as "강원도", 
                                '경기도' as "경기도", 
                                '서울시' as "서울시", 
                                '울산' as "울산", 
                                '충청북도' as "충청북도"));

설명

1. emp14_address 를 조회하면, 컬럼이 ename, age, address2 가 있다.
2. 필요한것은 emp14_address 의 지역과, 평균나이 이기때문에
3. 일단 emp14_address 에 있는 address2 와 age 를 출력한다.
4. select * from (select address2, age from emp14_address)
5. 후에 pivot 문을 작성하는데, 구할것이 지역의 평균나이이니까 문제에 맞춰 pivot 문법을 작성한다.
6. 출력하고나면 소숫점이 많이 나오는데
7. select 절에서 round 로 둘러주어야한다.
8. 딱 한 컬럼만 둘러줄 수는 없기 때문에 전체 컬럼 모두 round 해준다.
9. 끝.

---

 

---

컬럼을 ROW 로 출력하기 (2) UNPIVOT

→ 컬럼을 ROW 로 변경해 주기

unpivot 생성할 컬럼명 for 생성할 컬럼명 in (데이터로 변경할 컬럼명을 기술)

unpivot 문의 주의사항

in 다음에 싱글쿼테이션 마크 없이 기술 해야합니다.

• pivot 문 : 데이터 → 컬럼
• unpivot 문 : 컬럼 → 데이터

예제 : 컬럼을 로우로 출력해 보자.

스크립트 생성 :

1. order2 생성

create table order2 
       (ename varchar2(10), bicycle number(10), camera number(10), notebook number(10));

insert into order2 values('SMITH',2, 3, 1);
insert into order2 values('ALLEN', 1, 2, 3); 
insert into order2 values('KING', 3, 2, 2);

smith 가 bicycle 이 몇대냐 라는 데이터를 쉽게 빼려면 데이터들을 가로로 뽑는게 쉽다.

1. unpivot 문 사용하여 컬럼을 row 로 생성하기

select *
from order2
unpivot (건수 for 아이템 in (bicycle, camera, notebook));

→ 아이템들을 소문자로 작성해도 데이터 에는 대문자로 나와진다.

→ 건수, 아이템 컬럼명은 SQL 작성자가 기술하는대로 만들어집니다.

 

 

문제 : SMITH 는 BICYCLE 을 몇대 가지고있는가?

select 건수
from order2
unpivot (건수 for 아이템 in( bicycle, camera, notebook))
where ename = 'SMITH' 
      and 아이템 = 'BICYCLE';

설명 : 테이블명에 unpivot 문을 사용하여 건수와, 아이템을 출력할 수 있게 가로로 데이터를 생성한다. 그다음에 문제를 살펴보면

몇대 가지고있냐? 고했으니, select 절에 건수만 출력하고

검색조건인 스미스와 바이시클 을 뽑기위해 where 절을 입력한다

두개의 컬럼에서 각각 다른 내용을 뽑아야하기때문에 and 절로 이여서 where 절로 쿼리문을 작성해준다.

문제 209 : 위와 같이 복잡하게 SQL 작성하지 않도록 UNPIVOT 문을 VIEW 로 생성.

create view order2_view 
as 
select *
from order2
unpivot (건수 for 아이템 in( bicycle, camera, notebook));

 

VIEW 생성법

create view 뷰이름

as

내가 보고싶은 쿼리문 ;

• table 은 데이터를 저장하지만, view 는 데이터를 저장하지 않는다.
• 왜 view 생성? 복잡한 쿼리문을 편안하게 보기위해서

---

문제 210 : allen 은 카메라 몇대?

select 건수 
from order2_view
where ename ='ALLEN' 
       and 아이템 = 'CAMERA';

 

문제 211 : 아이템, 아이템별 건수의 토탈값을 출력

select 아이템, sum(건수) 
from order2_view 
group by 아이템;

---

sum 의 값과, 아이템의 값을 Group by 절로 묶지 않으면 오류가 난다.

-> sum 의값은 1개가 나오려하고 아이템은 전체 컬럼의 모든 아이템이 나오기때문이다.

따라서 아이템별로 건수가 나올 수 있도록 식을 써주는 것이다.

---

데이터 분석을 위한 unpivot 문

내가 알고 싶은 답을 얻는 과정을 데이터에서 얻어내는 연습이 필요하다.

1. 질문을 정의한다.
2. 데이터를 구해야 한다.
3. 데이터를 테이블로 생성해야한다.
4. SQL 을 작성할 수 있으면 된다.

질문 : 2018 년도에 가장 근로시간이 많은 나라 1위부터 5위까지 출력?

1번은 정의가 됨.

2번은 구글링 해서 데이터를 구했다.

근로자당_연평균_실제_근로시간_OECD__20191021155649

→ 그다음에 접속에 가서 엑셀 파일을 임포트 해준다

3번 데이터 테이블 생성

데이터를 살펴보면, 년도가 컬럼에 있는데 년도를 데이터로 빼 주어야 질문에 대한 답을 구하기 쉬워진다. → 따라서 UNPIVOT 문을 사용해야한다.

create table working_time ( country varchar2(30), 
                            y_2014 number(10), 
                            y_2015 number(10),
                            y_2016 number(10),
                            y_2017 number(10), 
                            y_2018 number(10) );

→ unpivot 문으로 년도를 데이터 화 해준다.

select *
from working_time
unpivot (시간 for 년도 in(y_2014, y_2015, y_2016, y_2017, y_2018));