Top N 쿼리

Top N 쿼리는 정렬한 결과에서 위의 N행만 꺼내는 쿼리입니다. Oracle은 ROWNUM이나 FETCH FIRST로, SQL Server는 TOP으로 씁니다.

"월급 상위 3명", "최근 주문 10건"처럼 순서를 매겨 앞의 몇 행만 보고 싶을 때가 많습니다. Oracle의 ROWNUM은 행에 1, 2, 3 하고 번호를 붙여 줍니다. 그런데 이 번호는 정렬하기 전에 붙습니다. 그래서 ROWNUM과 ORDER BY를 한 쿼리에 같이 쓰면 상위 N행이 나온다는 보장이 없습니다. 아래 그림의 목표는 월급 상위 3명, 곧 김민준 520 · 강도윤 470 · 최유나 450입니다.

SELECT ROWNUM,  EMPNO,  ENAME,  SALFROM EMP;

SELECT ROWNUM,  ENAME,  SALFROM EMPORDER BY SAL DESC;

SELECT ENAME,  SALFROM EMPWHERE ROWNUM <= 3ORDER BY SAL DESC;

SELECT ENAME,  SALFROM (  …  ORDER BY SAL DESC)WHERE ROWNUM <= 3;

SELECT ENAME,  SALFROM EMPWHERE ROWNUM > 1;

SELECT ENAME,  SALFROM EMPORDER BY SAL DESCFETCH FIRST 3 ROWS ONLY;

SELECT E.ENAME,  E.SAL,  S.GRADE…ORDER BY S.GRADE DESCFETCH FIRST 2 ROWS WITH TIES;

SELECT ROWNUM,  EMPNO,  ENAME,  SALFROM EMP;

1ROWNUM은 ORDER BY보다 먼저 붙는다

ROWNUM은 테이블에 없지만 열처럼 쓰는 Oracle의 가상 열(pseudocolumn)입니다. 번호는 WHERE 조건을 통과한 행에 차례로 붙고, 같은 쿼리의 ORDER BY는 그 뒤에 처리됩니다. 그래서 WHERE ROWNUM <= 3은 먼저 나온 김민준·이서연·박지호만 남기고, 강도윤(470)과 최유나(450)는 정렬에 끼지 못합니다. 이 교재의 Oracle은 EMP를 EMPNO 순서로 꺼냈지만, 테이블을 읽는 방법이 달라지면 꺼내는 순서도 달라질 수 있습니다.

2인라인 뷰로 먼저 정렬하기

인라인 뷰에서 먼저 SAL 내림차순으로 정렬하면, 메인 쿼리는 정렬된 행을 차례로 꺼내 번호를 붙입니다. 그래서 ROWNUM <= 3이 김민준·강도윤·최유나를 남깁니다.

ROWNUM > 1이면 첫 행이 1을 받아 떨어지고, 번호 1은 다음 행에 다시 붙어 그 행도 떨어집니다. ROWNUM = 2·ROWNUM BETWEEN 2 AND 3⁠처럼 1을 건너뛴 번호를 바라는 조건은 같은 까닭으로 0행이고, 뜻이 있는 조건은 1부터 세는 ROWNUM <= N·ROWNUM < N·ROWNUM = 1입니다. 2위부터 3위처럼 중간 구간은 ROWNUM에 별칭을 붙여 인라인 뷰 안에서 값으로 굳힌 뒤, 메인 쿼리에서 그 별칭으로 거릅니다.

3FETCH FIRST·WITH TIES와 TOP

Oracle 12c부터는 ORDER BY 뒤에 남길 행 수를 적어, 인라인 뷰 없이 정렬한 결과를 자릅니다. OFFSET n ROWS는 앞의 n행을 건너뛰고, FETCH FIRST와 FETCH NEXT는 같은 뜻입니다. ONLY는 정확히 n행이고, 50 PERCENT처럼 비율로도 적습니다. WITH TIES는 마지막 행과 정렬 값이 같은 행까지 붙여, 급여 등급(GRADE)이 높은 2행을 남기면 최유나와 같은 4등급인 강도윤까지 3행이 됩니다. ONLY면 2행인데, 동점 가운데 누가 남을지도 동점끼리의 순서도 보장되지 않습니다. SQL Server는 SELECT 바로 뒤에 TOP (n)을 쓰고, ORDER BY가 없으면 순서가 정해지지 않은 n행을 냅니다. TOP (50) PERCENT처럼 비율로도 씁니다. WITH TIES는 ORDER BY가 있어야 뜻이 있어, Oracle은 ORDER BY가 없으면 행을 더 붙이지 않고 SQL Server는 쓸 수 없습니다. SQL Server도 2012부터 ORDER BY 뒤에 OFFSET … FETCH를 쓰지만, OFFSET을 꼭 적어야 하고 WITH TIES는 TOP으로만 냅니다.

순위를 매기는 RANK를 인라인 뷰에 두고 걸러도 Top N이 됩니다(윈도우 함수).

예제

틀린 상위 3명과, 인라인 뷰로 고친 상위 3명입니다.

SELECT ENAME,
  SAL
FROM EMP
WHERE ROWNUM <= 3
ORDER BY SAL DESC;

SELECT ENAME,
  SAL
FROM (
  SELECT ENAME,
    SAL
  FROM EMP
  ORDER BY SAL DESC
)
WHERE ROWNUM <= 3;

2위부터 3위까지입니다.

SELECT *
FROM (
  SELECT ROWNUM AS RN,
    ENAME,
    SAL
  FROM (
    SELECT ENAME,
      SAL
    FROM EMP
    ORDER BY SAL DESC
  )
)
WHERE RN BETWEEN 2 AND 3;

OFFSET으로 같은 구간을 꺼냅니다.

SELECT ENAME,
  SAL
FROM EMP
ORDER BY SAL DESC
OFFSET 1 ROWS
FETCH NEXT 2 ROWS ONLY;

GRADE는 SALGRADE와 조인해 붙인 급여 등급입니다(조인 더 보기).

SELECT E.ENAME,
  E.SAL,
  S.GRADE
FROM EMP E
  JOIN SALGRADE S
  ON E.SAL BETWEEN
    S.LOSAL AND S.HISAL
ORDER BY S.GRADE DESC
FETCH FIRST 2 ROWS WITH TIES;

SQL Server의 TOP으로는 다음처럼 씁니다.

SELECT TOP (3) ENAME,
  SAL
FROM EMP
ORDER BY SAL DESC;

SELECT TOP (2) WITH TIES
  E.ENAME,
  E.SAL,
  S.GRADE
FROM EMP E
  JOIN SALGRADE S
  ON E.SAL BETWEEN
    S.LOSAL AND S.HISAL
ORDER BY S.GRADE DESC;

시험 포인트

• 같은 쿼리의 ORDER BY는 ROWNUM을 붙인 뒤에 처리됩니다. 그래서 WHERE ROWNUM <= N … ORDER BY …⁠는 먼저 꺼낸 N행을 정렬할 뿐이라, 상위 N행이 나온다는 보장이 없습니다. 테이블을 읽는 방법(인덱스 등)에 따라 우연히 상위 N행이 나올 때도 있습니다.
• 상위 N행은 인라인 뷰 안에서 정렬하고, 메인 쿼리에서 ROWNUM으로 앞의 N행만 남깁니다.
• ROWNUM > 1·ROWNUM = 2는 늘 0행입니다. 중간 구간은 ROWNUM에 별칭을 붙여 한 번 더 감쌉니다.
• FETCH FIRST(Oracle 12c부터)와 SQL Server TOP은 정렬한 뒤에 자르고, WITH TIES는 동점인 행까지 냅니다. TOP … WITH TIES는 ORDER BY 없이 쓸 수 없습니다.

확인 문제

1. 다음 쿼리는 월급이 가장 적은 2명을 찾으려 했습니다. 결과는 무엇이고, 왜 가장 적은 2명이 아닐까요?

SELECT ENAME,
  SAL
FROM EMP
WHERE ROWNUM <= 2
ORDER BY SAL;

SELECT ENAME,
  SAL
FROM (
  SELECT ENAME,
    SAL
  FROM EMP
  ORDER BY SAL
)
WHERE ROWNUM <= 2;

2. 다음 두 조건으로 EMP를 거르면 각각 몇 행이 나올까요? 번호가 붙는 차례로 따져 보세요.

• ① WHERE ROWNUM >= 1
• ② WHERE ROWNUM BETWEEN 1 AND 2

3. 월급 순위 3위만 꺼내려고 합니다. 빈칸에 들어갈 수는 무엇일까요?

SELECT ENAME,
  SAL
FROM EMP
ORDER BY SAL DESC
OFFSET ____ ROWS
FETCH NEXT 1 ROWS ONLY;

SELECT ENAME,
  SAL
FROM EMP
ORDER BY SAL DESC
OFFSET 2 ROWS
FETCH NEXT 1 ROWS ONLY;