[Oracle] 오라클 순번 매기기 (행, 그룹별 순번) / ROWNUM, ROW_NUMBER()

오라클에서 행에 순번을 부여하는 방법 중 가장 기본적인 2가지를 알려 드리겠습니다.

 

ROWNUM 사용 방법 (정렬 미적용)

SELECT ROWNUM
     , A.*
    FROM EMP A
    WHERE JOB IN ('MANAGER','CLERK','ANALYST')

ROWNUM 사용 예제

• ROWNUM을 선언하면 해당 테이블의 행 적제 순서대로 행 번호가 순번으로 부여
• ORDER BY로 정렬하기 전에 순번이 부여

ROWNUM 사용 방법 (정렬 적용)

SELECT ROWNUM
     , A.*
    FROM (
    SELECT *
        FROM EMP
        WHERE JOB IN ('MANAGER','CLERK','ANALYST')
        ORDER BY SAL
    ) A

ROWNUM 정렬 사용 예제

• 사용자가 임의로 정렬한 순서대로 순번 부여해야 할 때는 서브쿼리(인라인 뷰)에서 정렬 후 ROWNUM 사용
• 서브 쿼리에서 정렬 = 적제 순서 변경 ▷ 순번 부여 

ROW_NUMBER() 사용 방법 (그룹 미지정)

SELECT ROW_NUMBER() OVER(ORDER BY A.SAL) AS NUM
     , A.*
    FROM EMP A
    WHERE JOB IN ('MANAGER','CLERK','ANALYST')
    ORDER BY SAL

ROW_NUMBER 사용 예제

• ROW_NUMBER() 사용 시 OVER() 안에 선언한 ORDER BY 순서대로 순번 부여

ROW_NUMBER() 사용 시 주의사항

ROW_NUMBER 정렬 우선순위

• 쿼리 마지막에 선언한 ORDER BY 정렬 값과는 무관
• GROUP BY로 그룹화하지 않은 상태에서 사용

ROW_NUMBER 필수 옵션

• ROW_NUMBER() 뒤 OVER()에 ORDER BY 옵션 필수 사용
• COUNT() OVER()와 사용 방법 상이

ROW_NUMBER() 그룹별 순번 부여 

SELECT ROW_NUMBER() OVER(PARTITION BY A.JOB ORDER BY A.SAL) AS NUM
     , A.*
    FROM EMP A
    WHERE JOB IN ('MANAGER','CLERK','ANALYST')
    ORDER BY JOB, NUM

ROW_NUMBER 그룹별 순번 부여

• PARTITION BY 로 그룹을 지정하여 그룹별 순번 부여 가능
• ROW_NUMBER() 로 부여한 순번을 ORDER BY 에서 정렬 옵션으로 지정 가능

ROW_NUMBER() / COUNT() OVER() 병행 활용 예제

SELECT ROW_NUMBER() OVER(PARTITION BY A.JOB ORDER BY A.SAL) AS NUM
     , COUNT(*) OVER(PARTITION BY A.JOB) AS CNT
     , A.*
    FROM EMP A
    WHERE JOB IN ('MANAGER','CLERK','ANALYST')
    ORDER BY JOB, NUM

ROW_NUMBER / COUNT OVER 활용 예제

• 그룹별 개수를 구하고 순번을 부여해서 구분한 값을 정렬해야 하는 경우 사용
• Ex) 사용자별 대화 기록 시간순으로 정렬해서 표시
• COUNT(*) OVER를 사용하여 그룹별 행(값)의 개수 표시
• ROW_NUMBER() 를 사용하여 그룹별 순번 부여 (사용자 임의 정렬)