Top N 쿼리
IT 위키
더 많은 작업
- 상위 문서: SQL
- Top-N Query
- 정렬된 결과에서 상위 N개 행만, 또는 특정 범위의 행만 골라 내는 쿼리
Top N 쿼리는 급여 상위 3명, 최근 게시글 10개처럼 정렬 기준에 따라 앞쪽 일부 행만 조회하는 쿼리다. 화면에 일정 개수씩 나누어 보여 주는 페이징도 같은 원리를 쓴다. 방법은 DBMS마다 다르다. Oracle은 전통적으로 ROWNUM 의사 칼럼과 인라인 뷰를 썼고, 12c부터 표준 문법인 FETCH FIRST를 지원한다. SQL Server는 TOP, MySQL과 PostgreSQL은 LIMIT를 쓴다. 순위가 같은 행을 어떻게 다룰지는 SQL 윈도우 함수의 ROW_NUMBER, RANK, DENSE_RANK로 정할 수 있다.
사원
| 사번 | 이름 | 급여 |
|---|---|---|
| 1 | 김 | 300 |
| 2 | 이 | 500 |
| 3 | 박 | 400 |
| 4 | 최 | 600 |
| 5 | 정 | 500 |
| 6 | 강 | 200 |
ROWNUM은 쿼리가 행을 하나씩 결과로 내보낼 때마다 1, 2, 3, … 순서로 붙이는 의사 칼럼(pseudocolumn)이다. 번호는 FROM 절에서 읽은 행이 WHERE 조건을 통과하는 순간 붙고, ORDER BY는 그 뒤에 실행된다.
SELECT 이름 FROM 사원 WHERE ROWNUM = 1; -- 1행
SELECT 이름 FROM 사원 WHERE ROWNUM = 2; -- 0행
SELECT 이름 FROM 사원 WHERE ROWNUM > 1; -- 0행
SELECT 이름 FROM 사원 WHERE ROWNUM <= 3; -- 3행
- 첫 행은 ROWNUM 1을 받은 상태로 조건
ROWNUM = 2를 검사한다. 거짓이라 버려지고, 번호 1은 다음 행에 다시 붙는다. 모든 행이 1을 받은 채 버려지므로 결과는 0행이다. - 그래서 ROWNUM 조건은
ROWNUM = 1,ROWNUM < n,ROWNUM <= n형태만 의미가 있다.ROWNUM BETWEEN 11 AND 20도 0행이다.
급여 상위 3명을 구하려고 다음처럼 쓰면 틀린다.
-- 잘못된 방법: ROWNUM이 먼저 붙고 나서 정렬된다
SELECT ROWNUM, 이름, 급여
FROM 사원
WHERE ROWNUM <= 3
ORDER BY 급여 DESC;
| ROWNUM | 이름 | 급여 |
|---|---|---|
| 2 | 이 | 500 |
| 3 | 박 | 400 |
| 1 | 김 | 300 |
WHERE 단계에서 먼저 읽힌 아무 3행(여기서는 김, 이, 박)에 번호가 붙고, 그 3행만 정렬된다. 실제 최고 급여자인 최(600)가 빠졌다. 어떤 3행이 먼저 읽힐지는 저장 상태와 실행 계획에 따라 달라지므로 정해져 있지 않다.
-- 올바른 방법: 인라인 뷰에서 먼저 정렬한 뒤 ROWNUM으로 자른다
SELECT ROWNUM, 이름, 급여
FROM (SELECT 이름, 급여
FROM 사원
ORDER BY 급여 DESC)
WHERE ROWNUM <= 3;
| ROWNUM | 이름 | 급여 |
|---|---|---|
| 1 | 최 | 600 |
| 2 | 이 | 500 |
| 3 | 정 | 500 |
- 3위 경계에 급여 500인 이와 정이 동점이지만 ROWNUM은 동점을 고려하지 않는다. 위 결과는 둘이 모두 나왔지만 4위와 동점인 행이 있었다면 잘려 나갔을 것이다. 동점 처리가 필요하면 아래의 RANK나 WITH TIES를 쓴다.
인라인 뷰에서 순위 함수로 번호를 매긴 뒤 바깥 WHERE에서 거른다. 윈도우 함수는 WHERE 절에 직접 쓸 수 없으므로 인라인 뷰가 필요하다.
SELECT 이름, 급여
FROM (SELECT 이름, 급여,
RANK() OVER (ORDER BY 급여 DESC) AS 순위
FROM 사원)
WHERE 순위 <= 3;
같은 자리에 ROW_NUMBER, RANK, DENSE_RANK를 넣으면 결과가 다르다.
| 이름 | 급여 | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|---|
| 최 | 600 | 1 | 1 | 1 |
| 이 | 500 | 2 | 2 | 2 |
| 정 | 500 | 3 | 2 | 2 |
| 박 | 400 | 4 | 4 | 3 |
| 김 | 300 | 5 | 5 | 4 |
| 강 | 200 | 6 | 6 | 5 |
| 조건 | 결과 | 행 수 |
|---|---|---|
| ROW_NUMBER(ORDER BY 급여 DESC, 사번) <= 2 | 최, 이 | 2 |
| RANK <= 2 | 최, 이, 정 | 3 |
| RANK <= 3 | 최, 이, 정 | 3 |
| DENSE_RANK <= 3 | 최, 이, 정, 박 | 4 |
- ROW_NUMBER는 동점이어도 번호가 겹치지 않아 항상 정확히 N행이 나온다. 동점끼리의 순서를 고정하려면 사번 같은 칼럼을 ORDER BY에 더 적는다.
- RANK는 동점에 같은 순위를 주고 다음 순위를 건너뛴다(1, 2, 2, 4). 그래서 RANK <= 3에는 3위가 없어 3행만 나온다.
- DENSE_RANK는 순위를 건너뛰지 않아(1, 2, 2, 3) 3위인 박까지 포함한다.
SQL 표준(SQL:2008)의 행 제한 절이다. Oracle은 12c부터 지원하며, ORDER BY 뒤에 쓴다.
SELECT 이름, 급여 FROM 사원
ORDER BY 급여 DESC, 사번
FETCH FIRST 2 ROWS ONLY; -- 최, 이
SELECT 이름, 급여 FROM 사원
ORDER BY 급여 DESC
FETCH FIRST 2 ROWS WITH TIES; -- 최, 이, 정 (2번째 행과 동점인 정 포함)
SELECT 이름, 급여 FROM 사원
ORDER BY 급여 DESC, 사번
OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY; -- 정, 박 (2행 건너뛰고 2행)
SELECT 이름, 급여 FROM 사원
ORDER BY 급여 DESC, 사번
FETCH FIRST 50 PERCENT ROWS ONLY; -- 최, 이, 정 (6행의 50%)
- FIRST와 NEXT, ROW와 ROWS는 같은 뜻이다.
- WITH TIES는 마지막 행과 ORDER BY 값이 같은 행을 모두 포함한다. ORDER BY가 없으면 동점을 판단할 기준이 없어 ONLY와 같이 동작한다.
- OFFSET만 쓰면 앞의 m행을 건너뛰고 나머지를 모두 돌려준다.
SELECT TOP (3) 이름, 급여 FROM 사원 ORDER BY 급여 DESC;
SELECT TOP (50) PERCENT 이름, 급여 FROM 사원 ORDER BY 급여 DESC;
SELECT TOP (2) WITH TIES 이름, 급여 FROM 사원 ORDER BY 급여 DESC;
- TOP은 SELECT 바로 뒤에 쓰며, ORDER BY가 적용된 뒤의 상위 행을 돌려준다. ROWNUM처럼 정렬 전에 잘리는 문제가 없다.
- WITH TIES는 ORDER BY가 있어야 쓸 수 있다.
- SQL Server 2012부터는 ORDER BY 뒤에
OFFSET m ROWS FETCH NEXT n ROWS ONLY도 쓸 수 있다.
SELECT 이름, 급여 FROM 사원 ORDER BY 급여 DESC LIMIT 3;
SELECT 이름, 급여 FROM 사원 ORDER BY 급여 DESC LIMIT 2 OFFSET 2; -- 3~4번째
SELECT 이름, 급여 FROM 사원 ORDER BY 급여 DESC LIMIT 2, 2; -- MySQL 전용: LIMIT 건너뛸수, 개수
| 구분 | Oracle (전통) | Oracle 12c+ / 표준 | SQL Server | MySQL / PostgreSQL |
|---|---|---|---|---|
| 상위 N행 | 인라인 뷰 정렬 후 WHERE ROWNUM <= N | FETCH FIRST N ROWS ONLY | TOP (N) | LIMIT N |
| 동점 포함 | RANK() 인라인 뷰 | FETCH FIRST N ROWS WITH TIES | TOP (N) WITH TIES | MySQL은 윈도우 함수, PostgreSQL 13+는 FETCH FIRST … WITH TIES |
| 비율 | 직접 계산 | FETCH FIRST N PERCENT ROWS ONLY | TOP (N) PERCENT | 직접 계산 |
| 건너뛰기 | ROWNUM 별칭을 바깥에서 비교 | OFFSET m ROWS | OFFSET m ROWS (2012+) | OFFSET m |
| 위치 | WHERE 절 | ORDER BY 뒤 | SELECT 뒤 | ORDER BY 뒤 |
게시글 테이블(글번호 1~25)을 최신 글부터 10개씩 보여 줄 때, 2페이지(11~20번째 행)를 가져오는 세 가지 방법이다.
-- (1) ROWNUM 3단 인라인 뷰: 정렬 → 번호 부여(상한) → 하한 필터
SELECT RN, 글번호, 제목
FROM (SELECT ROWNUM AS RN, 글번호, 제목
FROM (SELECT 글번호, 제목 FROM 게시글 ORDER BY 글번호 DESC)
WHERE ROWNUM <= 20)
WHERE RN >= 11;
-- (2) ROW_NUMBER
SELECT RN, 글번호, 제목
FROM (SELECT 글번호, 제목,
ROW_NUMBER() OVER (ORDER BY 글번호 DESC) AS RN
FROM 게시글)
WHERE RN BETWEEN 11 AND 20;
-- (3) Oracle 12c+ / 표준
SELECT 글번호, 제목 FROM 게시글
ORDER BY 글번호 DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
| RN | 글번호 | 제목 |
|---|---|---|
| 11 | 15 | 글15 |
| 12 | 14 | 글14 |
| 13 | 13 | 글13 |
| 14 | 12 | 글12 |
| 15 | 11 | 글11 |
| 16 | 10 | 글10 |
| 17 | 9 | 글9 |
| 18 | 8 | 글8 |
| 19 | 7 | 글7 |
| 20 | 6 | 글6 |
- 세 방법 모두 같은 10행(글번호 15~6)을 돌려준다. (3)에는 RN 칼럼이 없다.
- (1)에서 하한 조건을 ROWNUM에 직접 걸 수 없으므로, 안쪽에서 ROWNUM에 RN이라는 별칭을 붙여 일반 칼럼으로 만든 뒤 바깥에서 비교한다.
WHERE ROWNUM BETWEEN 11 AND 20처럼 한 번에 쓰면 0행이다.
- ROWNUM은 WHERE 단계에서 붙고 ORDER BY는 그 뒤에 실행된다. ORDER BY와 같은 SELECT에서 ROWNUM으로 자르면 정렬 전의 임의 행이 잘린다.
ROWNUM = 2,ROWNUM > 1은 항상 0행이다.ROWNUM = 1,ROWNUM <= N만 의미가 있다.- SQL Server의 TOP (N) WITH TIES, Oracle의 FETCH FIRST N ROWS WITH TIES는 동점 행을 포함해 N행보다 많이 나올 수 있다.
- 동점이 있을 때 ROW_NUMBER, RANK, DENSE_RANK 조건에 따라 결과 행 수가 달라진다.
- 페이징에서 ROWNUM은 별칭을 붙인 인라인 뷰를 한 겹 더 써야 하한 조건을 걸 수 있다.