순위 함수와 Top N 쿼리

「급여 상위 3명」·「11번째부터 20번째 게시글」처럼 정렬한 뒤 앞에서 몇 줄만 자르는 질의를 Top N 쿼리라 합니다. 쉬워 보이지만 동순위를 어떻게 셀지, 정렬과 자르기 중 무엇이 먼저 일어나는지에 따라 결과가 갈려 SQLP가 즐겨 묻는 자리입니다. 이 노트는 순위를 매기는 함수, 오라클의 ROWNUM, 표준 문법인 FETCH FIRST 순서로 갑니다.

예시는 사원 다섯 명의 점수로 계속 씁니다 — 가 90, 나 85, 다 85, 라 80, 마 70.

순위 함수

세 함수의 번호

순위 함수는 OVER (ORDER BY …)에 적은 순서대로 줄마다 번호를 붙이는 함수입니다. 행을 줄이지 않고 번호 칸 하나를 더한다는 점에서 집계 함수와 다르고, 셋은 같은 값이 둘 이상일 때 번호를 붙이는 방식이 다릅니다.

SELECT 사원, 점수,
       RANK()       OVER (ORDER BY 점수 DESC) AS rk,
       DENSE_RANK() OVER (ORDER BY 점수 DESC) AS drk,
       ROW_NUMBER() OVER (ORDER BY 점수 DESC) AS rn
  FROM 평가;

동순위 다음의 번호

RANK는 동순위에 같은 번호를 주고 그 인원수만큼 다음 번호를 건너뜁니다. 2위가 둘이면 다음은 3이 아니라 4입니다. 올림픽 메달 순위와 같은 방식이라 「내 앞에 몇 명이 있는가 + 1」로 읽으면 됩니다.

DENSE_RANK는 동순위에 같은 번호를 주되 건너뛰지 않습니다. 2위가 둘이어도 다음은 3입니다. 그래서 DENSE_RANK의 가장 큰 번호는 곧 서로 다른 값의 가짓수입니다.

ROW_NUMBER는 동순위를 인정하지 않고 1부터 끊김 없이 번호를 줍니다. 나와 다 중 누가 2번이 될지는 정해지지 않습니다. 같은 질의를 두 번 돌려 다른 결과가 나올 수 있으므로, 정확히 한 줄씩 잘라야 하는 페이징에서는 ORDER BY 점수 DESC, 사번처럼 유일한 칼럼을 뒤에 붙여 순서를 못 박습니다.

PARTITION BY로 나눈 순위

OVER 안에 PARTITION BY 부서를 적으면 부서마다 번호가 1부터 다시 시작합니다. 「부서별 최고 연봉자」는 부서별 RANK가 1인 줄을 고르면 되고, 동점자를 다 보려면 RANK를, 부서마다 딱 한 명만 원하면 ROW_NUMBER를 씁니다.

ROWNUM

번호가 붙는 시점

ROWNUM은 오라클이 결과 줄에 붙이는 가상 칼럼입니다. 테이블에 저장된 값이 아니라, WHERE 절을 통과한 줄에 1부터 차례로 붙는 번호입니다. 핵심은 번호가 붙는 시점이 ORDER BY 정렬보다 앞이라는 것입니다.

-- 의도: 점수 상위 3명 / 실제: 아무 순서로 읽은 3명을 정렬
SELECT 사원, 점수
  FROM 평가
 WHERE ROWNUM <= 3
 ORDER BY 점수 DESC;

이 질의는 테이블에서 먼저 읽힌 세 줄에 번호 1~3을 붙여 남기고, 그 세 줄만 정렬합니다. 저장 순서가 마·라·가였다면 결과는 가·라·마이고 나·다는 빠집니다. 상위 3명이 아니라 임의의 3명을 정렬한 결과입니다.

조건절에서의 함정

ROWNUM은 통과한 줄에만 번호가 붙으므로 조건 모양에 따라 한 줄도 안 나올 수 있습니다.

번호 2를 받으려면 번호 1인 줄이 먼저 통과해야 하는데, = 2나 > 1은 1을 탈락시키므로 영원히 2가 생기지 않습니다. 「11번째부터」 같은 중간 구간을 ROWNUM만으로 자를 수 없는 이유가 이것입니다.

인라인 뷰 페이징

정렬을 안쪽으로

정렬보다 번호가 먼저 붙는 문제는 정렬을 인라인 뷰 안쪽에 넣어 풉니다. 안쪽에서 정렬이 끝난 결과에 바깥의 ROWNUM이 번호를 붙이므로 번호가 곧 정렬 순서가 됩니다.

SELECT 사원, 점수
  FROM (SELECT 사원, 점수 FROM 평가 ORDER BY 점수 DESC)
 WHERE ROWNUM <= 3;

옵티마이저는 이 모양을 알아보고 전체를 다 정렬하지 않고 상위 3개만 유지하며 정렬하는 방식으로 처리합니다. 실행 계획에 COUNT STOPKEY가 보이면 그 처리가 걸린 것이고, 정렬할 양이 줄어 대량 테이블에서도 빠릅니다.

중간 구간 자르기

「11번째부터 20번째」는 인라인 뷰를 한 겹 더 씌웁니다. 가운데 겹에서 ROWNUM을 별칭이 붙은 보통 칼럼으로 바꿔 두면 바깥에서는 >=를 마음대로 쓸 수 있습니다.

SELECT 사원, 점수
  FROM (SELECT ROWNUM AS rn, a.*
          FROM (SELECT 사원, 점수 FROM 평가 ORDER BY 점수 DESC, 사번) a
         WHERE ROWNUM <= 20)
 WHERE rn >= 11;

가운데 겹의 ROWNUM <= 20을 바깥으로 빼서 rn BETWEEN 11 AND 20으로만 적어도 결과는 같습니다. 하지만 그러면 가운데 겹이 전체에 번호를 다 붙인 뒤에야 거르므로 COUNT STOPKEY가 사라집니다. 위 상한은 가운데에, 아래 하한은 바깥에 두는 것이 이 모양의 요점입니다.

FETCH FIRST와 OFFSET

표준 문법

오라클 12c부터는 SQL 표준의 행 제한 절을 씁니다. 이 절은 ORDER BY 뒤에 적용되므로 인라인 뷰 없이도 정렬 결과의 앞부분을 정확히 자릅니다.

SELECT 사원, 점수 FROM 평가
 ORDER BY 점수 DESC
 FETCH FIRST 3 ROWS ONLY;

SELECT 사원, 점수 FROM 평가
 ORDER BY 점수 DESC, 사번
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;   -- 11~20번째

OFFSET n ROWS는 앞의 n줄을 건너뛰고, FETCH NEXT는 거기서부터 몇 줄을 가져옵니다. SQL Server에도 같은 OFFSET … FETCH 문법이 있고, 예전부터 쓰던 SELECT TOP (3)도 정렬 뒤에 자릅니다.

WITH TIES

FETCH FIRST 2 ROWS ONLY는 가·나 두 줄에서 멈추고, 나와 같은 85점인 다는 잘립니다. 동점자를 함께 가져오려면 ONLY 대신 WITH TIES를 씁니다.

WITH TIES는 마지막 줄과 정렬 키가 같은 줄을 모두 더하므로 ORDER BY가 반드시 있어야 합니다. SQL Server의 TOP (2) WITH TIES도 같습니다. 동점자를 포함한 상위 N은 RANK() <= N과 결과가 같아서, 시험은 두 방식 중 어느 쪽이 몇 줄을 내는지를 나란히 놓고 묻습니다.

구간 나누기

NTILE

NTILE(n)은 정렬한 줄을 n개의 무리로 최대한 고르게 나누고 무리 번호를 붙입니다. 나누어떨어지지 않으면 남는 줄을 앞 무리부터 하나씩 더 줍니다. 예시 다섯 명에 NTILE(2)를 쓰면 앞 무리 3명(가·나·다), 뒤 무리 2명(라·마)입니다. 열 줄을 NTILE(4)로 나누면 $10 = 4 \times 2 + 2$ 라서 앞의 두 무리가 3줄, 뒤의 두 무리가 2줄입니다. 「상위 25%」처럼 비율로 자르는 요구에 씁니다.

RATIO_TO_REPORT와 비율 함수

RATIO_TO_REPORT(칼럼) OVER ()는 그 줄의 값을 파티션 합계로 나눈 비율을 돌려줍니다. 매출이 400·300·200·100인 네 지점이면 합계 1000에 대해 0.4·0.3·0.2·0.1입니다. 이 비율을 누적해 「상위 몇 개 지점이 매출의 70%를 내는가」를 찾는 데 씁니다 — 0.4와 0.3을 더한 두 지점입니다.

같은 무리의 함수가 둘 더 있습니다. PERCENT_RANK는 $(\text{RANK} - 1) / (\text{전체 줄 수} - 1)$ 로 0부터 1 사이의 백분위 순위를 내고, CUME_DIST는 정렬 순서에서 자기 자리까지 오는 줄(동순위 포함)의 비율을 냅니다. 점수 내림차순으로 매기면 라(80점)는 RANK가 4이므로 PERCENT_RANK가 $(4-1)/(5-1) = 0.75$ 이고, 라까지 오는 줄이 가·나·다·라 넷이라 CUME_DIST는 $4/5 = 0.8$ 입니다.

연습 문제

1. 점수가 90, 85, 85, 80, 70인 다섯 명에게 DENSE_RANK() OVER (ORDER BY 점수 DESC)를 매겼을 때 70점의 순위와, RANK로 매겼을 때 80점의 순위는?
   ① 4, 4
   ② 5, 3
   ③ 4, 3
   ④ 5, 4

   ①. DENSE_RANK는 건너뛰지 않으므로 90→1, 85→2, 80→3, 70→4입니다. RANK는 2위가 둘이라 다음 번호를 건너뛰어 80점이 4위입니다.
2. 오라클에서 다음 중 한 줄 이상을 돌려줄 수 있는 조건은? (테이블에는 10줄이 있다)
   ① WHERE ROWNUM = 2
   ② WHERE ROWNUM > 5
   ③ WHERE ROWNUM <= 5
   ④ WHERE ROWNUM BETWEEN 3 AND 5

   ③. ROWNUM은 조건을 통과한 줄에만 1부터 차례로 붙으므로, 1을 탈락시키는 ①②④는 어떤 줄도 2 이상의 번호를 받지 못해 0줄입니다.
3. 점수 상위 3명을 뽑으려던 질의 SELECT * FROM 평가 WHERE ROWNUM <= 3 ORDER BY 점수 DESC가 틀린 까닭은?
   ① ROWNUM은 ORDER BY와 함께 쓸 수 없다
   ② 번호가 정렬 전에 붙어 임의의 세 줄을 정렬한다
   ③ ROWNUM <= 3은 0줄을 돌려준다
   ④ 동점자가 있으면 오류가 난다

   ②. ROWNUM은 WHERE를 통과할 때 붙고 정렬은 그 뒤입니다. 정렬을 인라인 뷰 안에 넣거나 FETCH FIRST 3 ROWS ONLY를 써야 합니다.
4. 점수가 90, 85, 85, 80, 70일 때 결과 줄 수가 나머지와 다른 것은?
   ① FETCH FIRST 2 ROWS WITH TIES (점수 내림차순)
   ② RANK() OVER (ORDER BY 점수 DESC) <= 2인 줄
   ③ DENSE_RANK() OVER (ORDER BY 점수 DESC) <= 2인 줄
   ④ DENSE_RANK() OVER (ORDER BY 점수 DESC) <= 3인 줄

   ④. ①②③은 모두 90·85·85의 세 줄입니다. ④는 DENSE_RANK가 80점에 3을 주므로 80점까지 넷입니다.
5. 23줄을 NTILE(5)로 나누었을 때 각 무리의 줄 수를 앞에서부터 차례로 적은 것은?
   ① 5, 5, 5, 4, 4
   ② 4, 4, 5, 5, 5
   ③ 5, 5, 5, 5, 3
   ④ 4, 4, 4, 4, 7

   ①. $23 = 5 \times 4 + 3$ 이라 기본이 4줄이고 남은 3줄을 앞 무리부터 하나씩 더합니다. 그래서 앞의 셋이 5줄, 뒤의 둘이 4줄입니다.
6. 게시글을 등록일 내림차순으로 정렬해 21번째부터 30번째를 가져오는 오라클 질의를 ROWNUM만으로 작성하시오. 12c 이전 버전이라 행 제한 절은 쓸 수 없다. 조건을 어느 겹에 두는지와 그 이유를 함께 적으시오.

   SELECT * FROM (SELECT ROWNUM AS rn, a.* FROM (SELECT * FROM 게시글 ORDER BY 등록일 DESC, 글번호 DESC) a WHERE ROWNUM <= 30) WHERE rn >= 21입니다. 정렬은 가장 안쪽에 둬야 번호가 정렬 순서대로 붙습니다. 상한 ROWNUM <= 30은 가운데 겹에 둬야 30줄에서 읽기를 멈추는 처리(COUNT STOPKEY)가 걸리고, 하한은 가운데 겹에서 rn이라는 보통 칼럼으로 바꾼 뒤 바깥에서 걸어야 합니다 — ROWNUM >= 21을 직접 쓰면 번호 1이 탈락해 0줄이 됩니다. 등록일이 같은 글의 순서를 못 박으려 글번호를 정렬 키에 더했습니다. 채점은 정렬을 가장 안쪽에 둔 것에 2점, 하한을 별칭 칼럼으로 바깥에서 건 것에 2점, 상한을 가운데 겹에 둔 이유에 1점입니다.