ROW_NUMBER() OVER 문법

Oracle의 ROW_NUMBER() OVER는 결과 집합의 각 행에 대해 정렬 기준을 기반으로 고유한 번호를 부여하는 윈도우 함수(Window Function) 입니다.

📌 기본 문법

ROW_NUMBER() OVER (PARTITION BY 컬럼명 ORDER BY 컬럼명)

• ROW_NUMBER(): 각 행에 순차적인 번호를 부여하는 함수
• PARTITION BY (선택 사항): 특정 그룹별로 행 번호를 부여 (예: 부서별 순위)
• ORDER BY: 행 번호를 부여할 때 사용할 정렬 기준

1. 기본 사용법 (전체 데이터에 대해 순번 매기기)

ROW_NUMBER()를 사용하면 전체 데이터에서 정렬된 순서대로 행 번호를 부여할 수 있습니다.

✅ 직원 테이블에서 급여순으로 순번 매기기

SELECT employee_id, first_name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

🔹 ORDER BY salary DESC로 급여가 높은 순서대로 번호가 부여됩니다.

employee_id	first_name	salary	row_num
105	Alice	9000	1
204	Bob	8500	2
301	Charlie	8000	3
150	David	7500	4
220	Eve	7000	5

2. PARTITION BY 사용 (그룹별 순번 매기기)

특정 그룹(예: 부서별)로 나누어 각 그룹 내에서 별도의 행 번호를 부여하려면 PARTITION BY를 사용할 수 있습니다.

✅ 부서별(Department)로 나누어 급여순으로 순번 매기기

SELECT department_id, employee_id, first_name, salary,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num
FROM employees;

🔹 같은 department_id를 가진 직원들끼리 그룹을 나눈 후, 급여 내림차순으로 순번을 부여합니다.

department_id	employee_id	first_name	salary	row_num
10	105	Alice	9000	1
10	204	Bob	8500	2
20	301	Charlie	8000	1
20	150	David	7500	2
20	220	Eve	7000	3

3. ROW_NUMBER()로 TOP N 쿼리 구현

ROW_NUMBER()를 활용하면 정렬된 데이터에서 특정 개수(N개)만 선택하는 TOP N 쿼리를 만들 수 있습니다.

✅ 급여가 가장 높은 상위 5명 조회

SELECT *
FROM (
    SELECT employee_id, first_name, salary,
           ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
    FROM employees
)
WHERE row_num <= 5;

🔹 서브쿼리를 사용하여 정렬된 순서로 번호를 부여한 후, row_num <= 5 조건을 사용하여 상위 5명만 조회합니다.

4. ROW_NUMBER()를 활용한 페이징 처리

ROW_NUMBER()는 페이징(Pagination) 쿼리를 만들 때 많이 사용됩니다.
예를 들어, 6번째부터 10번째까지의 데이터를 가져오려면 다음과 같이 구현할 수 있습니다.

✅ 페이징 처리 (6~10번째 행 조회)

SELECT *
FROM (
    SELECT employee_id, first_name, salary,
           ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
    FROM employees
)
WHERE row_num BETWEEN 6 AND 10;

🔹 row_num BETWEEN 6 AND 10을 사용하여 6번째부터 10번째까지의 행을 가져옵니다.

5. ROW_NUMBER() vs RANK() vs DENSE_RANK() 차이점

함수	역할	중복 값 처리	예제 결과
ROW_NUMBER()	순차적인 번호 부여	중복 값이 있어도 고유한 번호 부여	1, 2, 3, 4, 5
RANK()	순위를 매김 (동점자는 동일 순위)	동일 값이면 동일 순위를 부여하지만, 다음 순위는 건너뜀	1, 1, 3, 4, 5
DENSE_RANK()	RANK()와 비슷하지만 순위를 건너뛰지 않음	동일 값이면 동일 순위 부여, 다음 순위를 연속적으로 부여	1, 1, 2, 3, 4

✅ 세 가지 함수 비교 예제

SELECT employee_id, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
       RANK() OVER (ORDER BY salary DESC) AS rank_num,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_num
FROM employees;

employee_id	salary	ROW_NUMBER()	RANK()	DENSE_RANK()
105	9000	1	1	1
204	9000	2	1	1
301	8500	3	3	2
150	8000	4	4	3
220	8000	5	4	3

• ROW_NUMBER(): 고유한 번호 부여 (1, 2, 3, 4, 5)
• RANK(): 같은 값이면 같은 순위, 다음 순위 건너뜀 (1, 1, 3, 4, 4)
• DENSE_RANK(): 같은 값이면 같은 순위, 순위 건너뛰지 않음 (1, 1, 2, 3, 3)

✅ 정리

• ROW_NUMBER() → 고유한 번호를 부여할 때 사용
• PARTITION BY → 그룹별 행 번호 부여
• ROW_NUMBER()를 활용하면 TOP N 쿼리 및 페이징 처리 가능
• 동점 순위를 고려하려면 RANK() 또는 DENSE_RANK() 사용

ROW_NUMBER()는 OVER 없이 사용할 수 있을까?

ROW_NUMBER() 함수는 반드시 OVER 절과 함께 사용해야 합니다.
❌ OVER 없이 사용하면 오류(Error) 가 발생합니다.

📌 이유: ROW_NUMBER()는 윈도우 함수(Window Function)이기 때문

• ROW_NUMBER()는 윈도우 함수(Window Function) 이므로, 윈도우(창, 범위) 를 지정해야 합니다.
• OVER 절을 사용하여 정렬 기준(ORDER BY) 또는 그룹화(PARTITION BY) 를 지정해야 합니다.

❌ 잘못된 예제 (OVER 없이 사용)

SELECT employee_id, first_name, salary, ROW_NUMBER() AS row_num
FROM employees;

🚨 오류 발생:

ORA-30482: “WINDOW” 또는 “OVER” 절이 누락됨

🔹 ROW_NUMBER()는 개별 행에 단순히 번호를 부여하는 함수가 아니기 때문에 오류가 발생합니다.

✅ 올바른 사용법: OVER를 반드시 포함

1️⃣ 기본 사용법 (ORDER BY 필수)

SELECT employee_id, first_name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

🔹 ORDER BY salary DESC로 급여가 높은 직원부터 순차적인 번호(row_num)를 부여합니다.

2️⃣ 그룹별로 행 번호 매기기 (PARTITION BY)

SELECT department_id, employee_id, first_name, salary,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num
FROM employees;

🔹 부서별(department_id)로 나누어(PARTITION BY), 급여가 높은 순서대로 행 번호(row_num)를 부여합니다.

department_id	employee_id	first_name	salary	row_num
10	105	Alice	9000	1
10	204	Bob	8500	2
20	301	Charlie	8000	1
20	150	David	7500	2

🚀 결론

• ROW_NUMBER()는 반드시 OVER 절과 함께 사용해야 합니다.
• OVER 절에는 반드시 ORDER BY가 필요합니다.
• PARTITION BY를 사용하면 그룹별로 행 번호를 부여할 수 있습니다.

💡 즉, OVER 없이 ROW_NUMBER()를 사용하면 오류가 발생하므로, 항상 OVER를 함께 사용하세요! ✅