Top-N분석

개념

• Subquery에서 확장된 문법.
• 컬럼에서 가장 큰 n개의 값 또는 가장 작은 n개의 값을 요청함.

• ROWNUM : 서브퀴리에서 반환되는 결과집합에 순차적인 번호를 할당.
  • 진짜 컬럼이 아닌데 컬럼처럼 행동하는 요소.
  • 행번호 의사컬럼(현재행의 순서를 반환 의사컬럼).
  • 오라클 전용.
  • ROWNUM은 쿼리의 결과를 하나의 ROW씩 가져오면서 숫자를 지정함.
  • rownum 조건을 첫번째 숫자가 아니라 중간부터 지정하면 결과를 얻을수 없다.
  • 이때 서브쿼리를 사용하면 숫자를 먼저 지정한 테이블을 사용하기 때문에 조건을 중간부터(BETWEEN문 사용가능) 사용할 수있음.

• 인라인뷰 : FROM절에 있는 서브퀴리.
• 예를 들어 가장 적게 팔린 제품 10가지는? 회사의 최상위 소득자 3명은?

예시

예시1 : 급여 가장 적게 받는 직원, 가장 오래 일한 직원

• 급여를 가장 적게 받는 직원 3명을 구하려면 부등호 방향이 바뀌는 것이 아니라 정렬이 바뀐다. => desc생략. where rownum <= 3; 그대로
• 근속일수가 가장 오래된 직원 5명을 구하려면 부등호 방향이 바뀌는 것이 아니라 정렬이 바뀐다. => desc생략. where rownum <= 5; 그대로

예시2 : employees테이블에서 최하위 소득자 5명의 last_name, salary를 출력하는 구문.

• 최상위 소득자를 작성할때, 부등호 방향이 바뀌는 것이 아니라 정렬이 바뀐다. => desc추가. where rownum <= 5; 그대로

select rownum as rank, last_name, salary
from (select last_name, salary from employees order by salary)
where rownum <= 5;

예시3 : employees테이블에서 가장 예전에 입사한 사원 10명의 employee_id, last_name, hire_date를 출력하는 구문

• 예시문제를 보고 처음에 작성한 코드는 아래와 같았다.
• 서브쿼리에서 employee_id 컬럼은 필요없으니까 빼고 작성했는데 오류가 발생했다.

select rownum as rank, employee_id, last_name, hire_date
from (select last_name, hire_date from employees order by hire_date)
where rownum <= 10;

• select에서 출력될 컬럼은 from절안의 서브쿼리 select 컬럼에 무조건 있어야한다. -> 서브쿼리 출력값을 하나의 인라인뷰를 만드는데 인라인뷰 컬럼에 없다면 메인 select는 해당 컬럼을 찾지못한다.

select rownum as rank, employee_id, last_name, hire_date
from (select employee_id, last_name, hire_date from employees order by hire_date)
where rownum <= 10;

퀴즈 : 최상위 소득자 순으로 10위에서 20위 구하기

• EMPLOYEES 테이블과 DEPARTMENTS 테이블을 사용하여 최상위 소득자 순으로 10위에서 20위에 해당하는 사원들의 정보를 출력하는 쿼리를 작성하시오.
• 퀴즈를 보고 처음 든 생각은 일단 상위 20위까지 구하는 쿼리를 작성하는 것이었다.

//1. 범위좁히기
select ROWNUM  as rank, employee_id, last_name, salary, department_id, department_name
from (select e.employee_id, e.last_name, e.salary, e.department_id, d.department_name 
	 from departments d join  employees e
	 on (d.department_id = e.department_id)
	 ORDER BY  e.salary desc)
where ROWNUM <= 20;

//출력값
오류없이 잘 출력됨

• 첫 시도(내코드) : between조건 이용하기.

//2. 첫시도 : between이용하기
select rownum as rank, employee_id, last_name, salary, department_id, department_name
from (select rownum as rank, employee_id, last_name, salary, department_id, department_name
     from departments d join  employees e
	 on (d.department_id = e.department_id)
     ORDER BY  e.salary desc)
 where rank between 10 and 20;
 
 //출력값
 오류 : 열의 정의가 애매합니다

• 하지만 rownum은 한 행이 조건에 맞는지 확인한 뒤 rownum 순차적으로 붙인다. 따라서 첫번째 rownum이 없다면 그 이후의 결과는 출력되지않는다.

• 두번째 시도(내코드) : 중첩서브쿼리를 이용하기.
  위의 rownum특성을 생각하여 먼저 rownum을 포함한 테이블을 만든 뒤 그 테이블의 rownum 10부터 20번까지 가져오고자하였다.

//3. 두번째 시도 : 중첩서브쿼리이용하기
select rank, employee_id, last_name, salary, department_id, department_name
from (select rownum as rank, employee_id, last_name, salary, department_id, department_name
    from (select e.employee_id, e.last_name, e.salary, e.department_id, d.department_name 
                from employees e join  departments d
                on (d.department_id = e.department_id)
                ORDER BY  e.salary desc))
 where rank between 10 and 20
 order by rank;
 
 //출력값
 원하는 값이 잘 출력됨.

• 세번째 시도(쿼리 리팩토링) : join을 서브퀴리 끝난뒤 마지막에 해보기.
  강사님께 내가 작성한 쿼리보다 나은 쿼리가 있는 지 물어보니 join을 서브쿼리끝난뒤에 하면 데이터처리속도가 향상된다고 하셨다.

//강사님 코드
select e.rank, e.employee_id, e.last_name, e.salary, e.department_id, d.department_name
from (select rownum as rank, employee_id, last_name, salary, department_id
          from (select employee_id, last_name, salary, department_id 
                    from employees 
                    ORDER BY salary desc)) e
join departments d
on (d.department_id = e.department_id)
where e.rank between 10 and 20
order by rank;