[개인공부 / 오라클] TOP N Query, Pivot

TOP N QUERY

페이징 처리를 효과적으로 수행하기 위함, 전체 결과에서 특정 N개를 추출한다.

추출 방법 : ROWNUM, RANK, FETCH

 

1) ROWNUM

출력된 데이터 기준으로 행 번호 부여
절대적인 행이 아닌 가상의 번호 이르모 특정행을 지정할 수 없다 = 연산 불가
첫 번째 행이 증가한 이후로 할당되므로 비교 연산 사용 불가

 

ROWNUM 출력 형태

select ROWNUM, e.*
from employees e
where salary >= 1500;

select 
from 
START WITH 시작조건        -- 시작점을 지정하는 조건 전달
CONNECT BY PRIOR 연결조건  -- 행을 이어나갈 조건

 

잘못된 형태 : 비교 연산 불가

ROWNUM = 1 : 항상 불변하는 절대적 번호가 아니므로 단독 전달도 불가능

select *
from employees
where ROWNUM > 1;

 

올바른 형태

select employee_id, first_name, department_id, salary
from employees
where ROWNUM <= 5; --1부터 5

 

[Q] EMP 테이블에서 급여가 높은 순서대로 상위 5명의 직원 정보 출력

가장 많이 하는 실수

-- 가장 많이 하는 실수 ****
select employee_id, first_name, department_id, salary
from employees
where ROWNUM <= 5
order by salary desc;

올바른 추출

select *
from (select *
      from employees
      order by salary desc)
where ROWNUM <= 5
order by salary desc;

 

[Q]  EMP 테이블에서 급여가 높은 순서대로 4~6번째 해당하는 직원 정보 출력

잘못된 예

select *
from (select * from employees
      order by salary desc)
where ROWNUM BETWEEN 4 AND 6 -- 시작값인 1이 정의되지 않았기 때문에 
order by salary desc;

해결 : 인라인 뷰에서 각 행마다 순위를 직접 부여

select *
from (select ROWNUM as rn, tbl1.*
      from (select *
            from employees
            order by salary desc)tbl1)tbl2
where rn BETWEEN 4 AND 6
order by salary desc;

 

2) RANK

윈도우 함수의 RANK 사용

select *
from ( select e.*
            , RANK() OVER(ORDER BY salary desc) as rn
        from employees e) tbl
where rn BETWEEN 4 AND 6
order by salary desc;

 

3) FETCH 절

출력된 행의 수를 제한하는 절
ORDER BY 절 뒤에 사용 (내부 파싱 순서도)

select 
from
where
group by
having
order by
OFFSET N { ROW | ROWS }
FETCH { FIRST | NEXT } N { ROW | ROWS } ONLY

 

- OFFSET(건너뛸 행의 수) N(출력할 행의 수)
- FETCH(출력할 행의 수를 전달하는 구문)
- FIRST : OFFSET을 쓰지 않았을때 처음부터 N행까지
- NEXT : OFFSET을 사용했을 경우 제외한 행 다음부터 N행 출력 명령
- ROW | ROWS : 행의 수에 따라 하나일 경우 단수, 여러이면 복수형 (구분은 하지 않는다)

 

[Q] EMP에서 salary 순서대로 상위 5명 (12 이상만 가능)

select employee_id, first_name, department_id, salary
from employees
order by salary desc FETCH FIRST 5 ROWS ONLY;

 

[Q] EMP 테이블에서 급여가 높은 순서대로 4~6번째 해당하는 직원 정보 출력

select employee_id, first_name, job_id, salary
from employees
order by salary desc
OFFSET 3 ROW -- 3개를 건너뛰어라
FETCH FIRST 2 ROW ONLY;

 

PIVOT

데이터의 구조

1) LONG DATA(TIDY DATA) : 하나의 속성이 하나의 컬럼으로 정의되어 여러행으로 쌓이는 구조, 다른 테이블과 조인이 가능하다.
2) WIDE DATA : 행과 컬럼의 유의미한 정보전달을 목적으로 작성하는 교차표.(데이터 요약용)
테이블 설계시 값이 추가될때마다 컬럼이 추가되야 한다, 다른 테이블과의 조인이 불가능하다.

 

변경

1) PIVOT   : LONG -> WIDE
2) UNPIVOT : WIDE -> LONG

 

1) PIVOT (LONG -> WIDE)

교차표를 만드는 기능

- STACK 컬럼, UNSTACK 컬럼, VALUE 컬럼 정의가 중요하다.
- FROM 절에 STACK 컬럼, UNSTACK 컬럼, VALUE 컬럼의 컬럼명 정의
- PIVOT 절에  UNSTACK 컬럼, VALUE컬럼의  컬럼명 정의
- PIVOT 절 IN 연산자에 UNSTACK 컬럼값을 정렬

 

* 반드시 from절에 STACK 컬럼, UNSTACK 컬럼, VALUE 컬럼의 컬럼명을 정의!

select *
from 테이블명 / 서브쿼리
PIVOT (VALUE 컬럼명 FOR UNSTACK 컬럼명 IN (값1, 값2, 값3));

 

[Q] EMP 테이블에서 아래와 같이 JOB별 department_id 별 도수 출력

select *
from (select employee_id, job_id, department_id  
        -- 필요없는 데이터는 서브쿼리에 존재하면 안된다.
        from employees)
PIVOT (COUNT(employee_id) FOR department_id IN (10,20,30));

 

FROM 절 안에 job_id가 없다면 단지 부서별의 도수가 출력된다.

select *
from (select employee_id, department_id  
        -- 필요없는 데이터는 서브쿼리에 존재하면 안된다.
        from employees)
PIVOT (COUNT(employee_id) FOR department_id IN (10,20,30));

 

FROM절에 서브쿼리로 컬럼을 제한하지 않으면 STACK컬럼이 많아진다.

select *
from employees
PIVOT (COUNT(employee_id) FOR department_id IN (10,20,30));

 

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

select * 
from ( select location_id, city, country_id
        from locations)
PIVOT (COUNT(city) for country_id IN ('JP','US','CA'));

 

2) UNPIVOT  (WIDE -> LONG)

1) STACK컬럼 : 이미 UNSTACK인 여러 컬럼을 새로 만들 컬럼명(사용자정의)
2) VALUE컬럼 : 교차표에서 새로 만들 컬럼명(사용자 정의)
3) 값1, 값2.. : 실제 UNSTACK 되어 있는 컬럼 이름

select *
from 테이블명 / 서브쿼리 -- 제한할 필요는 없음
UNPIVOT (VALUE 컬럼명 FOR STACK 컬럼명 IN (값1, 값2, 값3));

 

select * 
from ( select location_id, city, country_id
        from locations)
PIVOT (COUNT(city) for location_id IN (1000,1100,1200));

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

 

 

계층형 질의

select 
from 
START WITH 시작조건        -- 시작점을 지정하는 조건 전달
CONNECT BY PRIOR 연결조건  -- 행을 이어나갈 조건