자바 웹 개발자가 될거야/DB

[Oracle] 오라클 Rownum 이용한 Top-N 분석

whitz 2021. 12. 17. 16:21

< Top-N 분석 >

 

- 컬럼에서 가장 큰 n개의 값 또는 가장 작은 n개의 값을 요처하는 것이다.

- ex) 가장 적게 팔린 제품 10가지는 ? 회사의 최상위 소득자 3명은 ?

 

① 구조

 

SELECT [컬럼명,..], ROWNUM
FROM ( SELECT [컬럼명,..]
		FROM table
		ORDER BY TOP-N_column)
WHERE ROWNUM <= N;

 

- ROWNUM : 서브쿼리에서 반환되는 각 행에 1부터 시작하는 순차 값을 할당함

 

 

② 예제

 

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

 

더보기 
SELECT ROWNUM as RANK, last_name, salary
FROM (select last_name, salary from employees order by salary DESC)
WHERE ROWNUM <= 3;

 

 

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

 

더보기 
SELECT ROWNUM as RANK, last_name, salary
FROM (select last_name, salary from employees order by salary)
WHERE ROWNUM <= 3;

 

 

- EMPLOYEES 테이블에서 가장 예전에 입사한 사원 5명의 employee_id, last_name, salary, hire_date를 출력하시오.

 

더보기 
SELECT employee_id, last_name, salary, hire_date
FROM (select employee_id, last_name, salary, hire_date from employees order by hire_date)
WHERE ROWNUM <= 5;

 

 

- EMPLOYEES 테이블에서 가장 최근에 입사한 사원 5명의 employee_id, last_name, salary, hire_date를 출력하시오.

 

더보기 
SELECT employee_id, last_name, salary, hire_date
FROM (select employee_id, last_name, salary, hire_date from employees order by hire_date desc)
WHERE ROWNUM <= 5;

 

 

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

 

 

 

더보기 
SELECT *
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 e.department_id=d.department_id
				ORDER BY e.salary DESC)
		WHERE rownum <= 20)
WHERE rank between 10 AND 20;

 

문제보자마자 금방 풀거라고 자만한 나 ... 공부를 좀 더 열심히 해야겠다 !

아래는 선생님 풀이

 

- 1위에서 20위 추출 > 10위에서 20위 추출 > 부서 테이블과 조인

- 부서 테이블과 조인 > 1위에서 20위 추출 > 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
				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;

 

 

- WHERE 조건절에서 RONUM은 작다 OR 작거나 같다 기호를 써야함

 

- 인라인뷰라서 ROWNUM이 1만 선언된 상태에서 시작한다. 3보다 작거나 같은 행을 출력하고 그 뒤인 ROWNUM 4는 조건을 만족하지 않기때문에 더이상 비교하지 않고 끝난다.

 

 

※ 만약에 WHERE절에 ROWNUM >= 3 이라면

 

 

- 1부터 False라서 저기서 더이상 진행되지 않아서 아무것도 출력을 하지않음