소스: sql-src/adv_01_rank/01_rank_basic.sql, 02_partition_topn.sql. 직원(emp) 8명(경영 1·개발 4·영업 3)을 준비했습니다. 개발에 700 두 명, 영업에 600 두 명이 있어 동점이 생깁니다.
SELECT
name
, sal
, ROW_NUMBER() OVER (ORDER BY sal DESC) AS rn
, RANK() OVER (ORDER BY sal DESC) AS rnk
, DENSE_RANK() OVER (ORDER BY sal DESC) AS d_rnk
, NTILE(3) OVER (ORDER BY sal DESC) AS tile
FROM emp
ORDER BY sal DESC, id;NAME | SAL | RN | RNK | D_RNK | TILE
-------+------+----+-----+-------+-----
김대표 | 1000 | 1 | 1 | 1 | 1
이부장 | 800 | 2 | 2 | 2 | 1
박과장 | 700 | 3 | 3 | 3 | 1
최대리 | 700 | 4 | 3 | 3 | 2
정사원 | 600 | 5 | 5 | 4 | 2
조사원 | 600 | 6 | 5 | 4 | 2
강사원 | 500 | 7 | 7 | 5 | 3
윤사원 | 400 | 8 | 8 | 6 | 3
(8행)700 두 명은 RANK·DENSE_RANK 모두 3 이지만 ROW_NUMBER 는 3, 4 입니다. 다음 행(600)은 RANK 가 5 로 건너뛰고 DENSE_RANK 는 4 로 이어집니다. TILE 은 8행이 3, 3, 2 행으로 나뉩니다.
ROW_NUMBER 는 동점이면 어느 행이 먼저 번호를 받을지 정해지지 않습니다. 실행할 때마다, 또는 DB 마다 순서가 달라질 수 있습니다. ORDER BY 에 고유한 컬럼을 덧붙여 결과를 고정합니다.
SELECT
name
, sal
, ROW_NUMBER() OVER (ORDER BY sal DESC, id) AS rn
FROM emp
ORDER BY rn;NAME | SAL | RN
-------+------+---
김대표 | 1000 | 1
이부장 | 800 | 2
박과장 | 700 | 3
최대리 | 700 | 4
정사원 | 600 | 5
조사원 | 600 | 6
강사원 | 500 | 7
윤사원 | 400 | 8
(8행)sal DESC, id 로 정렬하면 동점 안에서 id 가 작은 사람이 먼저 번호를 받아 항상 같은 결과가 나옵니다. "최신 1건" 같은 조회에서 이 습관이 특히 중요합니다.
주의
ORDER BY sal DESC만 쓴ROW_NUMBER는 동점 행의 번호가 비결정적입니다. 오늘 맞던 결과가 데이터가 늘거나 실행 계획이 바뀌면 달라질 수 있으므로, 동점이 가능하면 고유 컬럼을 반드시 덧붙입니다.
SELECT
dept
, COUNT(*) AS cnt
FROM emp
GROUP BY dept
ORDER BY dept;DEPT | CNT
-----+----
개발 | 4
경영 | 1
영업 | 3
(3행)SELECT
dept
, name
, RANK() OVER (ORDER BY sal DESC) AS rnk
FROM emp
ORDER BY dept, rnk, id;DEPT | NAME | RNK
-----+--------+----
개발 | 이부장 | 2
개발 | 박과장 | 3
개발 | 최대리 | 3
개발 | 강사원 | 7
경영 | 김대표 | 1
영업 | 정사원 | 5
영업 | 조사원 | 5
영업 | 윤사원 | 8
(8행)GROUP BY 는 8행이 부서 3행으로 합쳐졌습니다. 분석 함수 쿼리는 8행이 그대로이고 각 행에 순위가 붙었습니다. 이 차이가 분석 함수를 쓰는 이유입니다.
SELECT
dept
, name
, sal
, RANK() OVER (PARTITION BY dept ORDER BY sal DESC) AS dept_rnk
FROM emp
ORDER BY dept, dept_rnk, id;DEPT | NAME | SAL | DEPT_RNK
-----+--------+------+---------
개발 | 이부장 | 800 | 1
개발 | 박과장 | 700 | 2
개발 | 최대리 | 700 | 2
개발 | 강사원 | 500 | 4
경영 | 김대표 | 1000 | 1
영업 | 정사원 | 600 | 1
영업 | 조사원 | 600 | 1
영업 | 윤사원 | 400 | 3
(8행)PARTITION BY dept 로 부서마다 순위가 1 부터 다시 시작합니다. 개발은 700 동점 뒤가 4, 영업은 600 동점 뒤가 3 입니다.
부서별 상위 2명을 뽑으려고 순위 조건을 WHERE 에 바로 쓰면 오류가 납니다.
-- @error
SELECT dept, name, sal FROM emp WHERE ROW_NUMBER() OVER (PARTITION BY dept ORDER BY sal DESC) <= 2;예상 오류: Feature not supported: "Window function"2.3절의 실행 순서 때문입니다. 순위를 계산하는 쿼리를 인라인 뷰로 감싸고 바깥에서 거릅니다.
SELECT
t.dept
, t.name
, t.sal
, t.rn
FROM (
SELECT
dept
, name
, sal
, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY sal DESC, id) AS rn
FROM emp
) t
WHERE t.rn <= 2
ORDER BY t.dept, t.rn;DEPT | NAME | SAL | RN
-----+--------+------+---
개발 | 이부장 | 800 | 1
개발 | 박과장 | 700 | 2
경영 | 김대표 | 1000 | 1
영업 | 정사원 | 600 | 1
영업 | 조사원 | 600 | 2
(5행)안쪽 쿼리가 먼저 모든 행에 순번을 붙이고, 바깥 WHERE t.rn <= 2 가 그 결과를 거릅니다. 바깥에서는 rn 이 평범한 컬럼이라 WHERE 에 쓸 수 있습니다.