2-13) TOP N QUERY

TOP N QUERY

• 페이징 처리를 효과적으로 수행하기 위해 사용
• 전체 결과에서 특정 N개 추출
• 예) 성적 상위자 3명

 

TOP-N 행 추출 방법

1. ROWNUM
2. RANK
3. FETCH
4. TOP N (SQL Server)

 

ROWNUM

• 출력된 데이터 기준으로 행 번호 부여
• 절대적인 행 번호가 아닌 가상의 번호이므로 특정 행을 지정할 수 없음 (= 연산 불가)
• 첫번째 행이 증가한 이후 할당되무로 '>' 연산 사용 불가 (>0은 가능 → 전체 출력)

 

예1) ROWNUM을 사용한 출력 형태

 

 

예2) ROWNUM 잘못된 사용 1

• > 조건 전달 불가
• > 0은 모든 행에 해당하므로 가능

 

예3) ROWNUM 잘못된 사용 2

• 항상 불변하는 절대적인 번호가 아니므로 '=' 연산자 단독 전달 불가

 

예4) ROWNUM 올바른 사용

• EQAUL 비교 시 작다(<)와 함께 사용하면 1부터 순서대로 뽑을 수 있기 때문에 출력 가능
• 정렬 순서에 따라 출력되는 ROWNUM이 달라짐

 

예5) EMP 테이블에서 급여가 높은 순서대로 상위 5명의 직원 정보 출력

 

잘못된 예)

• WHERE 절이 ORDER BY 절보다 먼저 수행되므로 실제 상위 5명 출력 안됨
• WHERE 절에 의해 먼저 5개 추출한 뒤, 결과 집합에 대해 정렬 수행

 

해결) 서브쿼리(인라인뷰)를 사용하여 정렬 먼저 한 뒤 ROWNUM을 통해 추출

• ROWNUM이 결정되기 전에 먼저 데이터 정렬 순서를 바꿔놓는 방법

 

예6) EMP 테이블에서 급여가 높은 순서대로 4~6번째 해당하는 직원 정보 출력

 

잘못된 예)

• ROWNUM의 시작값 1이 정의되지 않았으므로 1을 건너 띄고 그 다음 행번호에 대한 추출 불가

 

해결) 인라인 뷰에서 각 행마다의 순위를 직접 부여 (SELECT ROWNUM)

• 서브쿼리를 통해 얻은 결과에 ROWNUM을 다시 부여하여 새로운 테이블인 것처럼 사용 (인라인뷰)

 

해결) 윈도우 함수의 RANK 사용

 

 

FETCH 절

• 출력된 행의 수를 제한하는 절
• ORACLE 12 이상부터 제공 (이전버전에서는 ROWNUM 주로 사용)
• SQL-Server 사용 가능
• ORDER BY 절 뒤에 사용 (내부 파싱 순서도 ORDER BY 뒤)

 

** 문법

• OFFSET : 건너뛸 행의 수
  • 예) 성적 높은 순 1등 제외, 나머지 3명 (1 건너뜀)
• N : 출력할 행의 수
• FETCH : 출력할 행의 수를 전달하는 구문
• FIRST : OFFSET을 쓰지 않았을 때 처음부터 N행 출력 명령
• NEXT : OFFSET을 사용했을 경우 제외한 다음부터 N행 출력 명령
• ROW | ROWS : 행의 수에 따라 하나일 경우 단수, 여러 값이면 복수형
  • 특별히 구분하지 않아도 됨

 

예1) EMP에서 SAL 순서대로 상위 5명 (ORACLE 19C에서 실행)

 

 

예2) EMP 테이블에서 급여가 높은 순서대로 4~5번째 해당하는 직원 정보 출력

• FIRST 대신 NEXT 가능

 

TOP N 쿼리

• SQL Server에서의 상위 n개 행 추출 문법
• 서브쿼리 사용 없이 하나의 쿼리로 정렬된 순서대로 상위 n개 추출 가능
• WITH TIES를 사용하여 동순위까지 함께 출력 가능

 

** 문법

 

 

예1) EMP 테이블의 상위 급여자 2명 출력 (SQL Server에서 수행)

 

예2) WITH TIES 사용

• 2등까지 뽑지만 동순위가 있을 경우 포함해서 출력

** TOP 2만 사용하면 2개만 출력 / WITH TIES를 붙이면 동순위까지 모두 출력