【오라클(Oracle) SQL 함수】 ROW_NUMBER 함수

o ROW_NUMBER

 

---

문 법

 

row_number::=

 

---

목 적

ROW_NUMBER 함수는 분석 함수이다. ROW_NUMBER 함수는 이 함수가 적용되는 각각의 행(파티션 그룹별 개별 행 또는 쿼리에서 반환되는 개별 행)에 대하여 order_by_clause 절에서 지정된 행의 정렬 순위를 기준으로 1부터 시작하는 중복을 허용하지 않는 유일한(unique) 순서 값을 할당한다.

사용자가 필요로 하는 특정 범위의 ROW_NUMBER 값을 검색하기 위한 쿼리 안에 ROW_NUMBER 함수를 사용하여 서브 쿼리를 중첩해서 프로그램을 작성하면, 내부(inner) 쿼리의 결과에서 정확한 하위 행의 집합을 선택할 수 있다. 이 방법으로 함수를 사용하면, top-N, bottom-N, inner-N 형식의 데이터 추출 작업을 수행할 수 있다. ROW_NUMBER 함수를 사용하여 추출되는 데이터 결과가 항상 동일하게 나오도록 하려면, 쿼리에서 정렬 순서가 변동이 되지 않도록 결정론적인 정렬 순서를 프로그램으로 지정해줘야 한다.

 

expr 인수에 ROW_NUMBER 또는 다른 분석 함수를 사용할 수 없다. 분석 함수를 중첩하여 사용할 수 없으나, expr 인수에 대하여 다른 함수를 이용할 수 있다. ROW_NUMBER 함수는 각 PARTITION 내에서 ORDER BY절에 의해 정렬된 순서로 유일한 값을 돌려주는 함수로 ROWNUM과는 관계가 없다.

 

---

패키지 함수 비교(Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table)

ROW_NUMBER Oracle Function 프로그래밍 비교 - Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table

 

---

관련 함수

함수명	함수 내용
CUME_DIST	그룹에 존재하는 관측치의 누적 분포 값을 계산한다.
DENSE_RANK	Rank()와 유사한 함수로 각 그룹별로 ORDER BY절에 사용된 칼럼이나 표현식을 기준으로 정렬하고, 그룹 내의 각 열에 대한 순위를 부여한다.
LAG	이 함수는 Self 조인 작업 수행하지 않고, 하나의 테이블을 접근하면서 여러 개의 행에 동시에 접근(Access)하여 작업한다. LAG 함수는 현재 위치를 기준으로 이전에 지정한 행에 접근하여 작업을 수행한다.
LEAD	이 함수는 Self 조인 작업을 수행하지 않고, 하나의 테이블을 접근하면서 여러 개의 행에 동시에 접근(Access)하여 작업한다. 쿼리에서 수행되는 행의 Series나 커서의 위치를 지정하면, LEAD 함수는 해당 위치 이후에 존재하는 물리적 Offset의 행에 ACCESS 한다.
NTILE	그룹 내에서 정렬된 데이터를 expr에 의해 지정된 bucket의 수로 분할하여, 각 행을 적절한 bucket의 번호를 부여하여 할당한다.
PERCENT_RANK	인수로 지정한 값의 그룹 내의 위치를 나타내는 백분위 순위(순위 퍼센트, Percent rank)를 반환한다.
RANK	값의 그룹에서 값의 순위를 계산한다. 반환되는 데이터형은 NUMBER이다.
ROW_NUMBER	이 함수는 파티션 그룹별 또는 전체 데이터에 대하여 order_by_clause에서 지정된 행의 정렬 순위대로 1로 시작하는 중복을 허용하지 않는 유일한(unique) 순서 값을 할당한다. (파티션에서 각 행 또는 쿼리에 의해서 반환되는 각 행에 대하여)

 

---

예 제

oe.employees 테이블에서 각 부분에 대하여, 다음 예제는 종업원의 고용일 각 순서에 대하여 number를 할당한다.

Oracle Programming

SELECT department_id, last_name, employee_id, 
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id
FROM   employees;

 

Results

DEPARTMENT_ID LAST_NAME                 EMPLOYEE_ID     EMP_ID
------------- ------------------------- ----------- ----------
           10 Whalen                            200          1
           20 Hartstein                         201          1
           20 Fay                               202          2
           30 Raphaely                          114          1
           30 Khoo                              115          2
           30 Baida                             116          3
           30 Tobias                            117          4
           30 Himuro                            118          5
           30 Colmenares                        119          6
           40 Mavris                            203          1
. . .
          100 Popp                              113          6
          110 Higgins                           205          1
          110 Gietz                             206          2

ROW_NUMBER는 비결정적 함수이다. 그러나 employee_id 는 중복이 없는 고유한 키이므로 이 함수를 적용한 결과는 결정론적이다.

참 조:FIRST_VALUE and LAST_VALUE for examples of nondeterministic behavior

 

다음 inner-N 쿼리는 employees 테이블의 모든 행에 대하여 ROW_NUMBER 값을 계산 후 51번째부터 100번째 행을 추출한다.

Oracle Programming

SELECT last_name
FROM   ( SELECT last_name, 
                ROW_NUMBER() over (order by last_name) R 
         FROM employees)
WHERE  R BETWEEN 51 and 100;

 

---

다음 예제는 hr.employees 테이블에서 각 부서의 가장 높은 급여를 받는 직원 3명을 찾습니다. 직원 수가 3명 미만인 부서의 경우 3개 미만의 행이 반환된다.

Oracle Programming

SELECT department_id, first_name, last_name, salary
FROM
(
  SELECT
    department_id, first_name, last_name, salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary desc) rn
  FROM employees
)
WHERE rn <= 3
ORDER BY department_id, salary DESC, last_name;

 

---

오라클 SQL 함수(Oracle SQL Function) 목록 리스트 링크

 

[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 처리 방법 리스트 링크

2024.01 수정