ORACLE(오라클) ROW_NUMBER() 함수

Oracle 데이터베이스에서 ROW_NUMBER() 함수는 순차적인 숫자를 생성하여 결과 집합 내의 각 행에 고유한 번호를 부여하는 데 사용됩니다. 이는 주로 데이터 순서를 지정하거나 특정 조건에 따라 데이터를 필터링할 때 유용합니다.

 

함수 구조

ROW_NUMBER() OVER (PARTITION BY <column> ORDER BY <column>)

• PARTITION BY: 데이터를 그룹화하는 기준을 지정합니다. 각 그룹별로 ROW_NUMBER()가 독립적으로 계산됩니다.
• ORDER BY: 각 그룹 내에서 행 번호를 매기는 기준이 되는 정렬 순서를 지정합니다.

사용 사례

1. 중복 데이터 제거
2. 페이징 처리
3. 그룹 내 특정 순위의 데이터 추출

---

예제 1: 중복 데이터 제거

중복된 데이터를 가진 테이블에서 최신 데이터만 남기고 나머지를 삭제한다고 가정합니다.

테이블 구조

CREATE TABLE employees (
    employee_id NUMBER,
    department_id NUMBER,
    hire_date DATE,
    salary NUMBER
);

데이터

EMPLOYEE_ID	DEPARTMENT_ID	HIRE_DATE	SALARY
1	10	2022-01-01	5000
2	10	2022-02-01	5500
3	20	2022-03-01	6000
4	20	2022-04-01	6500
5	10	2022-03-01	6000

중복 제거 쿼리

WITH RankedEmployees AS (
    SELECT
        employee_id,
        department_id,
        hire_date,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY hire_date DESC) AS row_num
    FROM employees
)
DELETE FROM employees
WHERE employee_id IN (
    SELECT employee_id
    FROM RankedEmployees
    WHERE row_num > 1
);

• PARTITION BY department_id: 부서별로 데이터를 그룹화합니다.
• ORDER BY hire_date DESC: 최신 데이터를 기준으로 순위를 매깁니다.
• row_num > 1: 각 그룹에서 1등을 제외한 나머지 데이터를 삭제합니다.

---

예제 2: 페이징 처리

사용자가 페이지당 2개의 결과를 요청한다고 가정합니다.

WITH PaginatedResults AS (
    SELECT
        employee_id,
        department_id,
        hire_date,
        salary,
        ROW_NUMBER() OVER (ORDER BY hire_date) AS row_num
    FROM employees
)
SELECT *
FROM PaginatedResults
WHERE row_num BETWEEN 1 AND 2;

• ROW_NUMBER() OVER (ORDER BY hire_date): 모든 데이터를 고용 날짜 순으로 정렬한 뒤 순번을 매깁니다.
• WHERE row_num BETWEEN 1 AND 2: 첫 번째 페이지 데이터를 가져옵니다.

결과

EMPLOYEE_ID	DEPARTMENT_ID	HIRE_DATE	SALARY
1	10	2022-01-01	5000
2	10	2022-02-01	5500

---

예제 3: 그룹 내 특정 순위 데이터 추출

부서별로 가장 높은 급여를 받는 직원을 조회한다고 가정합니다.

WITH TopSalaries AS (
    SELECT
        employee_id,
        department_id,
        hire_date,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num
    FROM employees
)
SELECT *
FROM TopSalaries
WHERE row_num = 1;

• PARTITION BY department_id: 부서별로 데이터를 그룹화합니다.
• ORDER BY salary DESC: 급여를 기준으로 내림차순 정렬하여 가장 높은 급여가 1번 순위를 가집니다.
• row_num = 1: 각 부서에서 가장 높은 급여를 받는 직원만 가져옵니다.

결과

EMPLOYEE_ID	DEPARTMENT_ID	HIRE_DATE	SALARY
5	10	2022-03-01	6000
4	20	2022-04-01	6500

---