[오라클 레퍼런스 함수] ROW_NUMBER - 정렬된 순서에 따라 1부터 고유한 번호 할당

★

 

 

 

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 함수 문서