Oracle ROW_NUMBER 설명 : 오라클 함수

1. 함수의 목적

    Oracle ROW_NUMBER는 반환되는 집합에 order_by_clause에 지정된 순서로 1부터 시작하는 고유한 번호를 지정하여 리턴한다.

2. 샘플을 통한 개념 퀵뷰

EMPLOYEES테이블의 EMPLOYEE_ID열 순서를 기준으로 1부터 시작하는 일련번호를 부여한다.

SELECT ROW_NUMBER() OVER (ORDER BY EMPLOYEE_ID) RN,
       EMPLOYEE_ID
FROM EMPLOYEES;

...

3. 사용방법 

4. 함수 PARAMETER 설명

[query_partition_clause]
파티션을 지정한다.

[order_by_clasue]
정렬기준을 지정한다.

5. 다양한 샘플표현

example1) 각 부서마다 급여가 높은 순으로 3명이내로 리스팅한다.

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;

example2) 다음 예는 sh.sales 테이블에 대한 쿼리로, 1999 년 5 개의 최고 판매 제품 중 2000 년 판매량을 찾아 2000 년과 1999년 사이의 차이를 비교한다.

SELECT sales_2000.channel_desc, sales_2000.prod_name,
sales_2000.amt amt_2000, top_5_prods_1999_year.amt amt_1999,
sales_2000.amt - top_5_prods_1999_year.amt amt_diff
FROM
(SELECT channel_desc, prod_name, amt
FROM
(
SELECT channel_desc, prod_name, sum(amount_sold) amt,
ROW_NUMBER () OVER (PARTITION BY channel_desc
ORDER BY SUM(amount_sold) DESC) rn
FROM sales, times, channels, products
WHERE sales.time_id = times.time_id
AND times.calendar_year = 1999
AND channels.channel_id = sales.channel_id
AND products.prod_id = sales.prod_id
GROUP BY channel_desc, prod_name
)
WHERE rn <= 5
) top_5_prods_1999_year,
(SELECT channel_desc, prod_name, sum(amount_sold) amt
FROM sales, times, channels, products
WHERE sales.time_id = times.time_id
AND times.calendar_year = 2000
AND channels.channel_id = sales.channel_id
AND products.prod_id = sales.prod_id
GROUP BY channel_desc, prod_name
) sales_2000
WHERE sales_2000.channel_desc = top_5_prods_1999_year.channel_desc
AND sales_2000.prod_name = top_5_prods_1999_year.prod_name
ORDER BY sales_2000.channel_desc, sales_2000.prod_name
;