ROW_NUMBER
구문
ROW_NUMBER( )
OVER ([ query_partition_clause ] order_by_clause)
참고:
구문, 의미, 그리고 제한 사항에 대한 정보는 "Analytic Functions"에 대한 내용을 참조하세요.
목적
ROW_NUMBER는 분석 함수입니다. 이 함수는 order_by_clause에 지정된 순서대로 각 행에 고유한 번호를 할당하며, 1부터 시작합니다.
ROW_NUMBER 값을 검색하는 쿼리 내에서 ROW_NUMBER을 사용하여 서브쿼리를 중첩함으로써, 내부 쿼리 결과에서 정확한 일부 행을 찾을 수 있습니다. 이 함수를 사용하면 상위-N(top-N), 하위-N(bottom-N) 및 내부-N(inner-N) 리포팅(reporting)을 구현할 수 있습니다. 일관된 결과를 얻기 위해 쿼리는 결정론적인 정렬 순서를 보장해야 합니다.
예제
다음 예제는 hr.employees 테이블에서 각 부서의 세 명의 최고 급여를 받는 직원을 찾습니다. 세 명 미만의 직원이 있는 부서의 경우 세 개 미만의 행이 반환됩니다.
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;
다음 예제는 sh.sales 테이블에서의 조인 쿼리입니다. 이 쿼리는 1999년에 가장 많이 판매된 다섯 개 제품의 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
/* The first subquery finds the 5 top-selling products per channel in year 1999. */
(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,
/* The next subquery finds sales per product and per channel in 2000. */
(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
;
CHANNEL_DESC PROD_NAME AMT_2000 AMT_1999 AMT_DIFF
--------------- --------------==-------------------------------- ---------- ---------- ----------
Direct Sales 17" LCD w/built-in HDTV Tuner 628855.7 1163645.78 -534790.08
Direct Sales Envoy 256MB - 40GB 502938.54 843377.88 -340439.34
Direct Sales Envoy Ambassador 2259566.96 1770349.25 489217.71
Direct Sales Home Theatre Package with DVD-Audio/Video Play 1235674.15 1260791.44 -25117.29
Direct Sales Mini DV Camcorder with 3.5" Swivel LCD 775851.87 1326302.51 -550450.64
Internet 17" LCD w/built-in HDTV Tuner 31707.48 160974.7 -129267.22
Internet 8.3 Minitower Speaker 404090.32 155235.25 248855.07
Internet Envoy 256MB - 40GB 28293.87 154072.02 -125778.15
Internet Home Theatre Package with DVD-Audio/Video Play 155405.54 153175.04 2230.5
Internet Mini DV Camcorder with 3.5" Swivel LCD 39726.23 189921.97 -150195.74
Partners 17" LCD w/built-in HDTV Tuner 269973.97 325504.75 -55530.78
Partners Envoy Ambassador 1213063.59 614857.93 598205.66
Partners Home Theatre Package with DVD-Audio/Video Play 700266.58 520166.26 180100.32
Partners Mini DV Camcorder with 3.5" Swivel LCD 404265.85 520544.11 -116278.26
Partners Unix/Windows 1-user pack 374002.51 340123.02 33879.49
15 rows selected.
출처: 오라클 레퍼런스
원문 링크: Oracle ROW_NUMBER 함수 문서
'코딩 > 오라클 함수' 카테고리의 다른 글
[오라클 레퍼런스 함수] ROWIDTONCHAR - ROWID 값을 NVARCHAR2 데이터 유형으로 변환 (0) | 2023.08.16 |
---|---|
[오라클 레퍼런스 함수] ROWIDTOCHAR - ROWID 값을 VARCHAR2 데이터 유형으로 변환 (0) | 2023.08.16 |
[오라클 레퍼런스 함수] ROUND_TIES_TO_EVEN (number) - 소수점 오른쪽 또는 왼쪽 n 위치에서의 반올림 (가장 가까운 짝수로) (0) | 2023.08.16 |
[오라클 레퍼런스 함수] ROUND (number) - 숫자 반올림 (0) | 2023.08.16 |
[오라클 레퍼런스 함수] ROUND (date) - 날짜 반올림 (0) | 2023.08.16 |
댓글