DAY-26. Oracle 인라인 뷰, rownum

🔐 2022-04-06 -1

데이터베이스

❗ 수업을 듣고, 개인이 공부한 내용을 적은 것 이기에 오류가 많을 수도 있음

🔔 인라인 뷰 (inline - view)

서브쿼리가 FROM 절에서 안에서 사용되는 경우, 해당 서브쿼리를 ‘인라인뷰’ 기존에 알던 서브쿼리는 where 절에

• 인라인뷰를 사용할 때는 인라인뷰에서 조회해온 데이터에 대해서만 메인쿼리문에서 데이터를 가져올 수 있음
• from 절에서 먼저 실시하고 가상테이블(emp_name, salary만 있는)을 만든것을 select 한다고 생각

select
    emp_no  // 오류 발생
from (select emp_name, salary from employee);

select
*         // emp_name과 salary만 보여짐
from (select emp_name, salary from employee);

💡 실행순서 : from → where → group by → having → select → order by

---

🔔 Top N 분석 (Top N Query)

• 컬럼에서 가장 큰 n개의 값을 혹은 가장 작은 n개의 값을 요청할 때 사용하는 쿼리
• 상위/하위 n개의 데이터를 추출하는 쿼리
• 회사에서 가장 많이 팔린 제품 10개는 무엇인가? 회사에서 월급을 제일 많이 받는 사람 3명은 누구인가?
• 게시판 페이징 / 조회수가 높은 인기글

// emplyoee 테이블에서 가장 높은 급여를 받는 사람 5명

select
    emp_name, salary
from employee
    order by 2 desc;

1️⃣ rownum

• 출력되는 select 문의 행마다 자동적으로 순위를 매겨주는것
• default로는 원래 존재하는 데이터의 순서대로 순위를 매겨줌

❗ 오류

// 월급이 큰 사람부터 순위를 매겨보자 -> order by

select
    rownum,
    emp_name, salary
from employee
    order by 3 desc;

• ❗ rownum은 order by적용 이전에 매겨진다. 순서가 먼저 매겨지고 나서 ORDER BY로 정렬되므로, rownum과 order by의 동시사용은 결과 순서를 뒤죽박죽인 것처럼 만든다
• select 절이 order by 절보다 먼저 실행되면서 이미 rownum이 매겨진 후에 다시 재정렬되기 때문에 순서가 뒤죽박죽

💡 해결

// 우리가 원하는 기준에 맞춰 이미 정렬이 된 상태의 데이터에 대해 rownum -> 순서를 매겨야 함
// 인라인뷰 -> 서브쿼리 안에서 월급이 제일 큰 순서대로 일단 데이터를 정렬
// -> 실제 메인쿼리 rownum 사용하게 되면 이미 정렬된 데이터에 대해 순서가 매겨짐

select * from employee order by salary desc;

select
    rownum , emp_name, salary
from (select * from employee order by salary desc);
// rownum은 원래 가지고 있던 테이블을 1,2,3,4,5 식으로 순번을 정해준다
// 먼저 salary로 순서를 정해주고 그 테이블을 rownum을 해준다

// 상위 5명 연봉 순서
select rownum,
       emp_name,
       salary
from (select * from employee order by salary desc)
where rownum <= 5;

• select보다 from 절이 먼저 실행 된다.
• from절 로부터 먼저 가상의 테이블이 생성된후 rownum과 emp_name, salary를 뽑는다
• from 실행 → where 실행 → select 실행

2️⃣ row_number() over()

• row_number() over (order by 컬럼..); over() 안쪽의 컬럼 순서에 따라 일단 정렬은 한 후에 순위를 매겨줌
• row_number()를 사용하면 같은 값(중복 데이터)이여도 순위가 나누어진다 → 같은 200이여도 하나는 19위 하나는 20위

select
   row_number() over(order by salary desc)"순위", emp_name, salary
from employee;

❗ 오류

🔎
// ---- 월급을 가장 많이 받는 top5

select
    row_number() over(order by salary desc) "순위",
    emp_name,
    salary
from employee
   where rownum <= 5 // 이렇게 하면 employee 테이블안에서 그냥 5명을 자른거임

• where 절 안에서 rownum <=5 를 해버리면 employee 테이블안에서 그냥 5명을 자르고 → 그 다음 그 안에서 salary 순위를 정한거임 → 잘못된 쿼리문
• 5명 안에서 순서를 나누는게 아닌 → 전체 인원중 월급 top5를 찾는 것이기 때문에

💡 해결

🔎 // 1번
select
    순위, emp_name, salary  // 여기서 순위(rownum)는 row_number에서 가져온것, emp_name,salary는 employee *에서 온 데이터
from (select row_number() over(order by salary desc)"순위" , employee.* from employee)
	where "순위" <= 5;
// employee.* : 다른 데이터나 컬럼과 함께 *을 써주고 싶으면 테이블명. *의 형식
// salary를 desc로 하고 rownum을 매겨주고 , employee로 부터 모든걸 얻어 온 테이블로부터
// 순위, emp_name, salary를 출력 하는것

🔎 // 2번
select
    *
from (select row_number() over(order by salary desc)"순위" ,
           emp_name "사원명", salary "월급" from employee);

// 1번과 2번은 같은 코드

🔎
// -- 5위 부터 10위 출력
select
    *
from (select row_number() over(order by salary desc)"순위" ,
        emp_name "사원명", salary "월급" from employee)
    where "순위" between 5 and 10;

🔎
// 연봉 (보너스 포함 -> (salary + (salary * bonus)) * 12 )이 가장 높은 순서대로 순위 매김
//10 ~ 15위 사이인 직원들의 순위, 사원명, 연봉, 직급코드, 부서코드

select
    *
from (select row_number() over(order by (salary + (salary * nvl(bonus,0))) * 12 desc) "순위",
    emp_name "사원명",
    (salary + (salary * nvl(bonus,0))) * 12  "연봉",
    job_code "직급코드",
    nvl(dept_code,'인턴') "부서코드" from employee)
    where "순위" between 10 and 15;

3️⃣ rank() over()

• 순위를 매길 때 중복데이터가 있다면 같은 순위를 매기고, 같은 순위를 매긴 데이터만큼 건너뛰어 다음 순위를 매김

4️⃣ dense_rank() over()

• 중복 데이터에 대해 같은 순위를 매기고 그 다음 순위부터는 건너뜀 없이 순차적으로 순위를 매김 → 총 데이터의 개수와 끝순위 번호가 일치하지 않을 수 있음

🔎 // 1번
select
    순위, emp_name, salary  // 여기서 순위(rownum)는 row_number에서 가져온것, emp_name,salary는 employee *에서 온 데이터
from (select row_number() over(order by salary desc)"순위" , employee.* from employee)
	where "순위" <= 5;
// employee.* : 다른 데이터나 컬럼과 함께 *을 써주고 싶으면 테이블명. *의 형식
// salary를 desc로 하고 rownum을 매겨주고 , employee로 부터 모든걸 얻어 온 테이블로부터
// 순위, emp_name, salary를 출력 하는것

🔎 // 2번
select
    *
from (select row_number() over(order by salary desc)"순위" ,
           emp_name "사원명", salary "월급" from employee);

// 1번과 2번은 같은 코드

🚀 실습

문제)

1. 가장 보너스를 많이 받는 순으로 순위를 매김
2. 4~8위 사원들의 순위,이름, 급여, 보너스를 출력

1) row_number

select
    *
from(select
        row_number() over(order by nvl(bonus,0) desc)"순위",
        emp_name "이름",
        salary "급여",
        nvl(bonus,0)"보너스" from employee)
    where "순위" between 4 and 8;

• order by에서 null이 가장 큰 값으로 인식

2) rank() over()

select
    *
from(select
        rank() over(order by nvl(bonus,0) desc)"순위",
        emp_name "이름",
        salary "급여",
        nvl(bonus,0)"보너스" from employee)
    where "순위" between 4 and 8;

3) dense_rank() over()

select
    *
from(select
        dense_rank() over(order by nvl(bonus,0) desc)"순위",
        emp_name "이름",
        salary "급여",
        nvl(bonus,0)"보너스" from employee)
    where "순위" between 4 and 8;