TOP-N 쿼리

TOP-N 쿼리란?

TOP-N 쿼리는 SQL에서 상위 N개 행을 추출하는 방법을 말한다.
특정 기준으로 가장 높은 값이나 가장 낮은 값을 가진 데이터를 일정 개수만 조회할 때 사용하는 방식이다.

예시)점수가 가장 높은 학생 상위 3명, 매출이 가장 높은 상품 5개

ROWNUM

ROWNUM은 Oracle 데이터베이스에서 조회 결과에 대해 임시로 부여되는 순차 번호다.
조회된 행의 순서대로 1부터 번호가 매겨지며, 이 값은 테이블에 실제로 저장된 컬럼이 아니라 조회 시점에만 만들어지는 가상 컬럼이다.

대표적인 가상 컬럼 예시로 ROWNUM, ROWID, LEVEL 등이 있다

예시)

SELECT ROWNUM, STUDENT_ID, NAME, SCORE
FROM STUDENT_SCORE;

결과에는 조회된 순서대로 1, 2, 3... 같은 번호가 붙는다.
여기서 중요한 점은 테이블 저장 순서가 아니라 조회 결과 순서에 따라 ROWNUM이 부여된다는 점이다.

WHERE절에서 ROWNUM 사용하기

ROWNUM은 WHERE절에서 사용할 수 있다.
특정 개수 이하의 행만 가져오고 싶을 때 유용하다.

예시) 상위 3개만 결과 보기

SELECT ROWNUM, STUDENT_ID, NAME, SCORE
FROM STUDENT_SCORE
WHERE ROWNUM <= 3;

조회 결과에 부여된 ROWNUM 기준으로 1, 2, 3까지만 출력한다.
따라서 앞에서부터 3개만 가져오기 라고 이해하면 된다.

ROWNUM 사용 시 주의사항

ROWNUM은 1부터 시작해서 순차적으로 증가해야 한다. 그래서 1이 먼저 선택되지 않으면 2나 3도 선택될 수 없다.

잘못된 예시 1)

SELECT *
FROM STUDENT_SCORE
WHERE ROWNUM = 2;

이 쿼리는 아무 결과도 나오지 않는다.
왜냐하면 ROWNUM은 1부터 부여되는데, 1이 먼저 선택되지 않은 상태에서는 2라는 값을 만들 수 없기 때문이다.

가능한 예시)

SELECT *
FROM STUDENT_SCORE
WHERE ROWNUM = 1;

이건 정상적으로 첫 번째 행이 출력된다.
ROWNUM의 시작값이 1이기 때문이다.

잘못된 예시 2)

SELECT *
FROM STUDENT_SCORE
WHERE ROWNUM >= 2;

역시 첫 번째 기준값인 1을 건너뛰었기 때문에 2 이상도 만들 수 없기 때문이다.

가능한 예시)

SELECT *
FROM STUDENT_SCORE
WHERE ROWNUM <= 3;

이건 가능하다.
ROWNUM 1부터 시작해서 순서대로 2, 3까지 평가할 수 있기 때문이다.

정리 : ROWNUM은 1부터 시작한다

ROWNUM만으로는 정확한 TOP-N이 안 되는 이유

단순히 ROWNUM <= N만 쓴다고 해서 정렬된 기준의 상위 N개 가 되는 것은 아니다. Oracle에서는 ROWNUM 필터링이 ORDER BY보다 먼저 적용되기 때문이다.

예시) 점수가 가장 높은 3명을 구할때

SELECT STUDENT_ID, NAME, SCORE
FROM STUDENT_SCORE
WHERE ROWNUM <= 3
ORDER BY SCORE DESC;

내가 기대한것 : 점수 상위 3명

실제로는 먼저 ROWNUM <= 3으로 앞의 3개 행이 잘리고, 그 잘린 결과 안에서만 ORDER BY가 적용된다.
따라서 정렬된 전체 결과에서 상위 3개를 뽑는 것이 아니라, 앞에서 3개를 먼저 뽑고 그 3개를 정렬하는 셈이 된다.

그래서 정확한 TOP-N이 보장되지 않는다.

정확한 TOP-N을 구하는 방법: 인라인 뷰 + ROWNUM

정확한 TOP-N을 구하려면 먼저 정렬을 끝낸 뒤, 그 결과에 ROWNUM 조건을 걸어야 한다.
이때 사용하는 방법이 인라인 뷰다.

예시)

SELECT STUDENT_ID, NAME, SCORE
FROM (
    SELECT STUDENT_ID, NAME, SCORE
    FROM STUDENT_SCORE
    ORDER BY SCORE DESC
)
WHERE ROWNUM <= 3;

이 쿼리는 먼저 인라인 뷰 안에서 SCORE 기준 내림차순 정렬을 완료한 다음 바깥쪽 WHERE절에서 ROWNUM <= 3을 적용한다.
따라서 정렬된 결과에서 진짜 상위 3명만 남기게 된다.

정리 : Oracle에서 TOP-N을 정확하게 구현하려면 정렬 먼저, ROWNUM 나중 순서로 진행하면 된다

SQL Server의 TOP-N 구현 방식

SQL Server에서는 Oracle처럼 인라인 뷰와 ROWNUM을 조합할 필요 없이, TOP(N) 키워드를 사용해서 훨씬 간단하게 상위 N개를 조회할 수 있다.

Oracle 방식과 비교하면 SQL Server가 더 직관적이다.

기본구조

SELECT TOP (N) 컬럼목록
FROM 테이블명
ORDER BY 정렬기준;

TOP 구문의 주요 옵션

• TOP(N) : 상위 N개 행 반환
• PERCENT : 상위 N개가 아니라 N% 반환
• WITH TIES : 마지막 값과 동일한 데이터를 함께 반환

예시) 점수가 가장 높은 상위 3명 구하기

SELECT TOP (3) STUDENT_ID, NAME, SCORE
FROM STUDENT_SCORE
ORDER BY SCORE DESC;

쿼리문을 이렇게 작성하면 상위 3개를 바로 가져올 수 있다

WITH TIES 옵션

정렬 기준의 마지막 값과 동일한 데이터가 있을 경우 함께 출력하는 옵션이다.
딱 N개만 자르는 것이 아니라, 마지막 순위에서 동점이 있다면 그 데이터도 추가로 포함시킨다.

주의할 점은 반드시 ORDER BY와 함께 사용해야 한다

예시)

SELECT TOP (3) WITH TIES STUDENT_ID, NAME, SCORE
FROM STUDENT_SCORE
ORDER BY SCORE DESC;

3등 점수가 90점인데, 다른 학생도 같은 90점이라면 그 학생도 함께 결과에 포함된다.
TOP-N에서 동점 처리까지 같이 하고 싶을 때 유용한 옵션이다

Oracle의 FETCH 문

Oracle 12c부터는 복잡한 인라인 뷰 없이도 FETCH 문으로 TOP-N을 간단하게 처리할 수 있다

예전에는 ROWNUM + 인라인 뷰 조합이 필요했지만, 최신 Oracle에서는 FETCH 구문으로 더 쉽게 쓸 수 있다.

기본구조

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

주요 키워드

• OFFSET : 앞에서 몇 개 행을 건너뛸지 지정
• FETCH : 가져올 행 수 또는 비율 지정
• FIRST, NEXT : 앞에서부터 지정 개수 가져오기
• ONLY : 정확히 지정한 수만 반환
• WITH TIES : 마지막 값과 동률인 행도 함께 반환

FETCH 문 예시

예시 1) 상위 3명 조회

SELECT STUDENT_ID, NAME, SCORE
FROM STUDENT_SCORE
ORDER BY SCORE DESC
FETCH FIRST 3 ROWS ONLY;

이 쿼리는 SCORE 기준으로 내림차순 정렬한 뒤, 앞에서 3개 행만 가져온다.
따라서 Oracle에서도 SQL Server의 TOP처럼 간단하게 상위 N개를 조회할 수 있게 된 것이다.

예시 2) 상위 3명을 건너뛰고 다음 2명 조회

SELECT STUDENT_ID, NAME, SCORE
FROM STUDENT_SCORE
ORDER BY SCORE DESC
OFFSET 3 ROWS FETCH NEXT 2 ROWS ONLY;

이 쿼리는 먼저 SCORE 기준 내림차순 정렬을 하고, 앞의 3행을 건너뛴 다음, 그다음 2행만 반환한다.
OFFSET은 건너뛰기, FETCH NEXT는 가져오기 역할을 한다.