[SQLD] TOP N QUERY
개념
- 페이징 처리를 효과적으로 수행하기 위해 사용
- 전체 결과에서 특정 N개 추출
- 성적 상위자 3명
TOP-N 행 추출 방법
- ROWNUM
- RANK
- FETCH
- TOP N(SQL Server)
ROWNUM
- 출력된 데이터 기준으로 행 번호 부여
- 절대적인 행 번호가 아닌 가상의 번호이므로 특정 행 지정할 수 없음(=연산 불가)
- 첫번째 행이 증가한 이후 할당되므로 '>' 연산 사용 불가(0은 가능)
👉 ROWNUM 출력 형태
👉 ROWNUM 잘못된 사용1
👉 크다 조건 전달 불가
👉 ROWNUM 잘못된 사용2
👉 항상 불변하는 절대적 번호가 아니므로 '=' 연산자 단독 전달 불가
👉 ROWNUM 올바른 사용
👉 EQAUL 비교 시 작다(<)와 함께 사용하면 1부터 순서대로 뽑을 수 있기 때문에 출력 가능함
👉 정렬 순서에 따라 출력되는 ROWNUM이 달라짐
👉 EMP 테이블에서 급여가 높은 순서대로 상위 5명의 직원 정보 출력
잘못된 예)
👉 실제로 상위 5명이 출력 안됨(급여최대가 5000임)
👉 추출원리 : WHERE절에 의해 먼저 5개를 추출 뒤 이 결과 집합에 대해 정렬 수행
해결) 먼저 서브쿼리를 사용하여(인라인뷰) SAL에 대해 내림차순 정렬을 해놓고 상위 5개 가져옴
👉 즉 ROWNUM이 결정되기 전에 데이터 정렬순서를 바꿔놓는 방법
👉 EMP 테이블에서 급여가 높은 순서대로 4~6번째 해당하는 직원 정보 출력
잘못된 예)
👉 ROWNUM 시작값(1)이 정의되지 않았으므로 1을 건너띄고 그 다음 행번호에 대한 추출 불가
해결1)
👉 서브쿼리를 통해 얻은 결과에 ROWNUM을 다시 부여하여 새로운 테이블인것 처럼 사용(인라인뷰)
해결2) 윈도우 함수의 RANK 사용
FETCH 절
- 출력될 행의 수를 제한하는 절
- ORACLE 12C이상부터 제공(이전버전에는 ROWNUM 주로 사용)
- SQL-Server 사용 가능
- ORDER BY절 뒤에 사용(내부 파싱 순서도 ORDER BY 뒤)
문법
- OFFSET
- 건너뛸 행의 수
- 성적 높은 순 1등 제외, 나머니 3명
- N
- 출력할 행의 수
- FETCH
- 출력할 행의 수를 전달하는 구문
- FIRST
- OFFSET을 쓰지 않았을 때, 처음부터 N행 출력
- NEXT
- OFFSET을 사용했을 때, 제외한 행 다음부터 N행 출력
- ROW|ROWS
- 행의 수에 따라 하나일 경우 단수, 여러값이면 복수형(특별히 구분하지 않아도 됨)
👉 EMP에서 SAL 순서대로 상위 5명(19C에서 실행)
👉 EMP 테이블에서 급여가 높은 순서대로 4~5번째 해당하는 직원 정보 출력
TOP N 쿼리
- SQL SERVER에서의 상위 n개 행 추출 문법
- 서브쿼리 사용 없이 하나의 쿼리로 정렬된 순서대로 상위 n개 추출 가능
- WITH TIES를 사용하여 동순위까지 함께 출력 가능
문법
👉 EMP 테이블의 상위 급여자 2명 출력(SQL Server에서 수행)
👉 SAL은 큰 순서대로 5000, 3000, 3000이라 3000이 공동 2위이지만 TOP2는 2개만 출력, WITH TIES를 사용하면 동순위 행도 함께 출력 가능