[Oracle] 상위 N개 데이터 추출하는 방법(서브쿼리 활용), 상위 0.1% 값 추출

오라클에서 상위 N개의 데이터를 추출하는 방법 여러가지 방법입니다.

서브 쿼리를 사용하여 데이터를 먼저 정렬한 후, 원하는 개수만큼 데이터를 추출하면 된다. 대부분 ROWNUM 키워드를 사용하며, 특수한 경우에 순위 함수(DENSE_RANK)를 사용하여 순위를 부여한 후 순위로 데이터를 추출하기도 한다.

## Ex. 연봉 상위 N개 행 추출

1. 상위 N개 행 추출 방법 - ORDER BY 사용 

#상위 n개의 행(ROW) 추출 방법
SELECT empno
     , ename
     , sal
  FROM (
         SELECT empno
              , ename
              , sal
           FROM emp
          ORDER BY sal DESC
       )
 WHERE ROWNUM <= 10

==> 연봉(sal)이 높은 순서로 정렬한 후 ROWNUM을 사용하여 10개의 데이터 추출.

(서브 쿼리에서 ORDER BY를 먼저 수행 후 ROWNUM을 사용해야 합니다)


2. 상위 순위 N개 추출 방법 - DENSE_RANK() 함수 사용

# 상위 순위 n개 추출 방법
SELECT empno
     , ename
     , sal
  FROM (
         SELECT empno
              , ename
              , sal
              , DENSE_RANK() OVER(ORDER BY sal DESC) AS rnk
           FROM emp
       )
 WHERE rnk <= 10

==> 연봉이 동일한 경우 같은 순위가 부여되므로 11개의 행이 조회된다.(순위 중 동일 값이 있는 경우 만큼 추가 추출됨)

 

3. 연봉 유니크값 기준 상위 순위 N개 추출 방법

#연봉 유니크값 기준 상위 순위 N개 추출 방법
SELECT empno
     , ename
     , sal
  FROM (
         SELECT DISTINCT empno
              , ename
              , sal
           FROM emp
          ORDER BY sal DESC
       )
 WHERE ROWNUM <= 10

==> 서브쿼리에 DISTINCT 추가

 

 

4. (심화) 연봉 유니크값 기준 상위 0.1% 연봉 값 추출하기

SELECT NTILE_100, MIN(SAL) MIN
FROM (
   SELECT DISTINCT EMPNO, TO_NUMBER(SAL) SAL, NTILE(100) OVER(ORDER BY TO_NUMBER(SAL)) AS NTILE_100
   # 그룹별로 상위 1% 집계하고 싶을 땐 NTILE(100) OVER(PARTITION BY 구분컬럼 ORDER BY ~) 로 작성하면 됨
   FROM EMP) A
WHERE NTILE_100 = 100
GROUP BY NTITLE_100;

 

 

--------------------------------------------------------------------------------------------------------------------