23/02/14 [Database]

📌 Top-N 분석

1. Top_N 분석이란?

: 컬럼에서 가장 큰 n개의 값 또는 가장 작은 n개의 값을 찾는 것.
(예를 들어 ‘가장 적게 팔린 제품 10가지는?’, 또는 ‘회사의 최상위 소득자 3명은?’ 등...)

2. Top-N 분석 질의의 구조

3. Top-N 분석 예제

: EMP 테이블에서 최상위 소득자 3명의 이름과 급여를 표시하시오.

SQL> select ROWNUM as RANK, last_name, salary
 	 from (select last_name, salary 
     	   from employees 
           order by salary DESC) 
     where ROWNUM <= 3;

: EMP 테이블에서 최하위 소득자 3명의 이름과 급여를 표시하시오.

SQL> select ROWNUM as RANK, last_name, salary
     from (select last_name, salary 
           from employees 
           order by salary)
     where ROWNUM <= 3

• 더 좋은 성능의 문법 = 1번 문법

1.
SQL> select ROWNUM as RANK, last_name, salary
     from (select last_name, salary 
           from employees 
           order by salary)
     where ROWNUM <= 3

(==)
2.
SQL> select ROWNUM as RANK, last_name, salary
     from (select * 
           from employees 
           order by salary)
     where ROWNUM <= 3
// (11개의 컬럼을 임시저장했다가 3개를 다시 걸러냄)

---

✏️ Quiz – Top N 분석

• EMP테이블에서 가장 최근에 입사한 사원 10명의 rownum(순위), employee_id, last_name, hire_date를 출력하시오.

select ROWNUM as RANK, employee_id, last_name, hire_date
from (select  employee_id, last_name, hire_date
      from employees
      order by hire_date desc)
where ROWNUM <= 10;

• EMP테이블에서 가장 예전에 입사한 사원 10명의 rownum(순위), employee_id, last_name, hire_date를 출력하시오.

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;

• EMPLOYEES 테이블과 DEPARTMENTS 테이블을 사용하여 최상위 소득자 순으로 10위에서 20위에 해당하는 사원들의 정보를 출력하시오.

📍 Tip !!
⑴ emp->1~20위
⑵ 10~20위
⑶ dept 조인

⑴ emp->1~20위

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)
where ROWNUM <= 20;

⑵ 10~20위

select RANK, employee_id, last_name, salary, department_id
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)
      where ROWNUM <= 20)
where RANK between 10 and 20;    

⑶ dept 조인

select a.RANK, a.employee_id, a.last_name, a.salary, a.department_id, b.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)
      where ROWNUM <= 20) a join departments b
on (a.department_id = b.department_id)
where a.RANK between 10 and 20
order by a.rank ;