31. PIVOT, UNPIVOT , Top n , Fetch

[ pivot / unpivot ]
1. pivot 
- long data -> wide data

** 문법
select
  from 테이블명 또는 서브쿼리
pivot(value값 for unstack컬럼명 in (값1, 값2, 값3, ...);

 

** 특징
- value값에는 집계함수 형태가 들어가야 함(sum, count, avg, ...)
- from절에는 unstack컬럼, stack컬럼, value컬럼 외 정의되면 안됨
  -> from절에 존재하는 컬럼 중 unstack컬럼과 value컬럼을 제외한 모든 컬럼이 stack 처리 되므로!!!!

 

 

예제) 각 부서내 job별로 인원수 파악
select *
  from emp                                       -- 잘못된 결과 리턴
 pivot (count(empno) for deptno in (10,20,30));  -- stack, unstack, value만 컬럼만
                                                 -- from절에 정의되어야 함(너무 많은 컬럼이 존재함)

 

 

 

 

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

 

 

 

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

 

 

 

 

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

 

 

 

select *
  from (select deptno, job from emp)
 pivot (count(job) for deptno in (10,20,30));      -- 오답(stack 컬럼은 value 컬럼 자리에 들어갈수 없음)

 

예제) 아래 테이블 생성 후 dcast_ex2.csv 파일 로딩한 뒤
연도별 메뉴별 판매수량에 대한 총 합 교차표 형식으로 출력

create table pivot1(
year    number,
name    varchar2(10),
qty     number,
price   number);

 

select * 
  from (select year, name, qty from pivot1)
 pivot (sum(qty) for year in (2000, 2001));

 

 

 

 

다음은 unpivot 을 설명하기 위해 만들기 위해 아래 테이블을 만들자. 

 

create table unpivot1
as
select year, 
       "'latte'" as latte, 
       "'mocha'" as mocha, 
       "'americano'" as americano
  from (select qty, name, year from pivot1)
 pivot (sum(qty) for name in ('latte','mocha','americano'));

 

select * from unpivot1;

 

2. unpivot
- wide data -> long data

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


예제) unpivot1 테이블을 long table로 변환
select *
  from unpivot1
unpivot(qty for menu in (LATTE, MOCHA, AMERICANO));

예제) 연령별실업율_40-49세.csv 데이터를 테이블로 생성한 뒤 long data로 변환
create table unpivot2(
월       number,
"2014년"   number,
"2015년"   number,
"2016년"   number,
"2017년"   number,
"2018년"   number);

select 연도, 월, 실업율 
  from unpivot2
unpivot (실업율 for 연도 in ("2014년", "2015년", "2016년", "2017년", "2018년"))
 order by 1,2;

[ 연습문제 ]
1. cal 테이블 데이터를 사용하여 캘린더 형태로 출력한 뒤 테이블(cal2)로 저장
create table cal2
as
select * 
  from cal
 pivot (sum(NUM_DAY) for DAY in ('일' as 일,'월' as 월,'화' as 화,'수' as 수,'목' as 목,'금' as 금,'토' as 토))
 order by week;

 

 
2. cal2 테이블 데이터를 다시 long data 형태로 변환 
select * 
  from cal2
unpivot (일자 for 요일 in (일,월,화,수,목,금,토));

 

 

 

 

[ top n 쿼리 ]
- 상위 n개 행 추출(페이징 처리)

1. rownum
- 출력되는 행의 순서에 따라 임시의 행번호를 부여
- 첫번째 행이 출력이 된 이후에 순서대로 행번호를 부여하므로 1 초과의 값으로 행 선택 불가

예) rownum 확인
select rownum, rowid, e.*
  from emp e;              -- insert된 순서대로 출력(rownum도 그 순서대로 부여)

select rownum, rowid, e.*
  from emp e
 where deptno = 20;        -- 출력되는 순서에 맞게 rownum이 부여

예) rownum을 사용한 행의 수 제한
select *
  from emp
 where rownum <= 10;
 
예) rownum을 사용한 행의 선택 불가
select *
  from emp
 where rownum = 10;

select *
  from emp
 where rownum >= 10;  -- 조회 불가


예제) rownum을 사용한 top n 쿼리 작성(급여 높은 순 3명 출력)
select *
  from emp
 where rownum <= 3
 order by sal desc;   -- order by보다 rownum 정의가 먼저 수행되므로 급여순으로 rownum 출력 불가
 
 
select rownum, i.*
  from (select * 
          from emp
         order by sal desc) i
 where rownum <= 3;


예제) rownum을 사용한 급여순서대로 4~6등 출력
select rownum, i.*
  from (select * 
          from emp
         order by sal desc) i
 where rownum between 4 and 6;    -- 불가

select *
  from (select rownum as rn, i.*
          from (select * 
                  from emp
                 order by sal desc) i)
 where rn between 4 and 6;         -- 불가


2. rank / dense_rank / row_number


3. fetch
 - oracle의 경우 12c부터 지원
 - SQL-Server를 포함한 다른 dbms 사용 가능

** 문법
select
  from
 where
 group by
having
 order by
offset n {row | rows}
 fetch {first | next} n {row | rows} only;


** 특징
- first | next 구분하지 X
- row | rows 구분하지 X
- offset은 앞의 n개 행을 스킵

예제) emp에서 급여가 높은순 상위 5명 선택
select empno, ename, sal
  from emp
 order by sal desc
 fetch first 5 rows only;
 
예제) emp에서 급여가 높은순 4~6번째 선택
select empno, ename, sal
  from emp
 order by sal desc
offset 3 rows
 fetch next 3 rows only;


4. top n
 - SQL-Server 문법

** 문법

select top n [with ties] 컬럼1, 컬럼2, 컬럼3
  from
 where
 ...
 order by ...
 
** 특징 
- with ties를 사용하면 n개 이상의 행이 추출될 수 있음(같은 값일 경우 함께 추출)