[ORACLE] ROW_NUMBER() OVER(PARTITION BY - ORDER BY ) 사용방법

쿼리 짤 때 정말 자주 사용하고 있는 ROW_NUMBER() 정리

 

ROW_NUMBER()는 SQL의 내장 함수로,

결과 집합의 각 행에 고유한 숫자를 할당하는 역할을 한다. 

그래서 순서 번호를 필요로 하는 쿼리에서 사용한다.

 

문법

ROW_NUMBER() OVER ([PARTITION BY 그룹핑할 컬럼] ORDER BY 정렬할 컬럼 [ASC|DESC])

 

PARTITION BY는 선택값이고,

ORDER BY는 필수 값이다.

 

예시

SELECT 
    EMPLOYEE_ID
    , JOB_ID
    , END_DATE
    , ROW_NUMBER() OVER(PARTITION BY EMPLOYEE_ID ORDER BY END_DATE DESC) AS RN
FROM HR.JOB_HISTORY;

RN컬럼으로 EMPLOYEE_ID가 같은 값끼리 순번이 매겨지는 걸 확인할 수 있다.

 

사용방법

각 직원의 가장 최신 JOB_ID를 가져오기

관계도

HR.EMPLOYEES

HR.JOB_HISTORY

 

SELECT 
	E.EMPLOYEE_ID
	, E.FIRST_NAME
	, HR.JOB_ID
	, HR.END_DATE
FROM HR.EMPLOYEES E
LEFT JOIN (
	SELECT 
		EMPLOYEE_ID
		, JOB_ID
		, END_DATE
		, ROW_NUMBER() OVER(PARTITION BY EMPLOYEE_ID ORDER BY END_DATE DESC) AS RN
        -- 각 직원(EMPLOYEE_ID)의 업무 기록을 'END_DATE' 기준으로 정렬하고, 순서 번호를 부여
        -- 가장 최신인 업무 기록에는 '1'이라는 숫자가 부여됨
	FROM HR.JOB_HISTORY
	) HR ON E.EMPLOYEE_ID = HR.EMPLOYEE_ID
WHERE RN = 1;
-- 'END_DATE' 기준 가장 최신인 값을 가져옴

 

 

결과화면

 

 'HR.EMPLOYEES' 테이블에서 직원 정보를 선택하고, 이에 'HR.JOB_HISTORY' 테이블의 정보를 조인한다.

조인할 때 사용하는 'HR.JOB_HISTORY' 테이블은 서브쿼리로, 각 직원의 업무 기록을 최신 날짜 순으로 정렬하고, 순서를 매긴다.
마지막에 'WHERE RN = 1' 조건을 사용하여 각 직원의 가장 최신의 JOB_ID 와 END_DATE 날짜를 선택할 수 있다.