240402 DB - Top-N 분석

Top-N 분석

Top-N 질의는 컬럼에서 가장 큰 n개의 값 또는 가장 작은 n개의 값을 요청

max, min을 이용하면 최고, 최저값은 찾을 수 있음.

하지만 1등에서 5등, 1등에서 3등 등 범위는 알 수 없음. 그 때 사용하는 것이 Top-N 분석임.

 

질의 구조

메인 쿼리 from (서브쿼리(정렬 구문이 반드시 들어가야함))-> inlineview

select [컬럼1, 컬럼2,...,], ROWNUM -- ROWNUM : 의사 열. 논리적인 컬럼. 서브쿼리에서 반환되는 각 행에 1부터 시작해서 순차 값을 할당
from (select [컬럼1, 컬럼2,...] from table order by top-n_column) -- from의 서브쿼리를 인라인뷰
where ROWNUM <= N; -- 고정. <= 나 < 만 사용

 

-- employees 테이블에서 급여를 가장 많이 받는 사원의 사번, 이름, 급여를 출력
select employee_id, last_name, salary
from employees
where salary = (select max(salary) from employees);

-- employees 테이블에서 최상위 소득자 3명의 이름과 급여를 표시
select rownum as rank, last_name, salary
from (select last_name, salary from employees order by salary desc)
where rownum <= 3;

-- employees 테이블에서 최하위 소득자 3명의 이름과 급여를 표시
select rownum as rank, last_name, salary
from (select last_name, salary from employees order by salary)
where rownum <= 3;

 

rownum은 논리적인 허위의 컬럼임. 1하나만 반환되어 있음. 이후 where 조건문에 만족하면 반환함

부등호 방향이 바뀌면 안됨 <= 나 < 만 가능하며, =, >, >=, <> 사용안됨,

 

-- employees 테이블에서 가장 최근에 입사한 사원 10명의 employee_id, last_name, hire_date, salary를 출력
select rownum as rank, employee_id, last_name, hire_date,salary
from (select employee_id, last_name, hire_date,salary from employees order by hire_date desc)
where rownum <= 10;

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

-- employees 테이블에서 가장 예전에 입사한 사원 10명의 employee_id, last_name, hire_date, salary를 출력
select rownum as rank, employee_id, last_name, hire_date,salary
from (select employee_id, last_name, hire_date,salary from employees order by hire_date)
where rownum <= 10;

 

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

-- 일단 내가 처음 한거
select a.rank, a.employee_id, a.last_name, a.salary, a.department_id, b.department_name
from (select rownum rank, employee_id, last_name, salary, department_id
	  from(select employee_id, last_name, salary, department_id
      	   from employees
           where rownum <=20
           order by salary desc)) a join departments b
on (a.department_id = b.department_id)
where a. rank between 10 and 20
order by rank;

-- 1. employees에서 salary 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;
-- 2. 1번 SQL 구문에 추가 -> 10위 ~ 20위 출력
select rank, employee_id, last_name, salary, department_id
from 1번 SQL 구문

=>
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;
-- 3. 2번 SQL 구문에 추가 -> departments join
select a.rank, a.employee_id, a.last_name, a.salary, a.department_id, b.department_name
from (select rownum 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 rank;

-- 정답
select a.rank, a.employee_id, a.last_name, a.salary, a.department_id, b.department_name
from (select rownum 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 rank;