SQL활용 - 부분범위 처리를 위한 TOP N 쿼리 작성방법(ROWNUM, ROW LIMITING 절)

SQL활용 - 부분범위 처리를 위한 TOP N 쿼리 작성방법(ROWNUM, ROW LIMITING 절)

 

 

ROWNUM PSEUDO 컬럼

 

ORACLE의 ROWNUM은 칼럼과 비슷한 성격의 PSEUDO COLUMN입니다. SQL 처리 결과 집합의 각 행에 대해 임시로 부여되는 일련번호입니다. 테이블이나 집합에서 원하는 만큼 행을 가져오고 싶을 때 WHERE 절에서 행의 개수를 제한하는 목적으로 사용합니다.

 

SELECT PLAYER_NAME FROM PLAYER WHERE ROWNUM <= N

 

ORACLE에서 순위가 높은 N개의 로우를 추출하기 위해 ORDER BY 절과 WHERE 절의 ROWNUM 조건을 같이 사용하는 경우가 있습니다. 하지만 이 두 조건으로는 원하는 결과를 얻을 수 없습니다. ORACLE의 경우, 정렬이 완료된 후 데이터의 일부가 출력되는 것이 아닌 데이터 일부를 먼저 추출한 후 정렬작업이 일어나기 때문입니다.

 

사원 테이블에서 급여가 높은 3명을 내림차순으로 출력하려고 합니다. 아래 쿼리는 급여 순서 상관없이 무작위로 추출된 3명에 한해 급여를 내림차순으로 정렬한 결과입니다.

 

SELECT EMPLOYEE_ID , SARARY
FROM HR.EMPLOYEES
WHERE ROWNUM < 4
ORDER BY SAL DESC;

 

정렬 후 원하는 데이터를 얻기 위해서는 인라인 뷰에서 먼저 데이터를 정렬한 후 메인 퀴리에서 ROWNUM 조건을 사용해야합니다.

 

SELECT EMPLOYEE_ID , SALARY
FROM (
    SELECT EMPLOYEE_ID , SALARY
    FROM HR.EMPLOYEES
    ORDER BY SALARY DESC
)
WHERE ROWNUM < 4

 

ROW LIMITING 절

 

ORACLE 12.1 버전 부터 ROW LIMITING 절이 추가돼 TOP N 쿼리를 작성할 수 있습니다.

 

• OFFSET N ROWS : 건너뛸 행 N개를 지정합니다.
• FETCH FIRST | NEXT ROWCOUNT/PERCENT : 반환할 행의 개수나 백분율을 지정합니다. FIRST와 NEXT는 동일기능을 합니다.
• ONLY : 지정된 행의 개수나 백분율만큼 행을 반환합니다.
• WITH TIES : 마지막 행과 값이 일치하는 다음 행들도 포함해서 반환합니다.

 

[OFFSET N {ROW|ROWS}]
[FETCH {FIRST|NEXT}[{rowcount | percent PERCENT}]{ROW|ROWS}{ONLY | WITH TIES}]

 

아래는 ROW LIMITING 절을 사용한 TOP 쿼리입니다.

 

-- 10개의 레코드를 뛰어넘고 5개만 출력합니다.

SELECT EMPLOYEE_ID, SALARY
FROM HR.EMPLOYEES
ORDER BY SALARY , EMPLOYEE_ID 
OFFER 10 ROWS FETCH FIRST 5 ROWS ONLY;

-- OFFSET 만 기술하면 건너뛴 행 이후의 전체 행이 반환됩니다.

SELECT EMPLOYEE_ID, SALARY
FROM HR.EMPLOYEES
ORDER BY SALARY , EMPLOYEE_ID 
OFFSET 5 ROWS;