[Oracle] 파티션 별 순번 매기기 ROW_NUMBER

테이블 쿼리를 한 후 순번을 매기는 경우가 있다. 예를 들어 판매량별로 우선순위를 매기고 이를 순번으로 기록해 두면 추후에 특정 등수 이상 또는 이하의 레코드를 추출할 떄 해당 컬럼을 활용하여 쉽게 추출할 수 있다.

 

ROW_NUMBER 문법 스펙은 다음과 같다.

ROW_NUMBER( )
   OVER ([ query_partition_clause ] order_by_clause)

 

예제1) 아래 쿼리는 employees 테이블에서 department_id 별로 salary 액수기준 내림차순으로 정렬한 후 rn(row_number)가 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;

 

예제2) 아래는 1999년 기준으로 채널별 top 5 판매량 상품을 서브쿼리로 생성한 후 2000년 기준 테이블과 조인하여 2개 년도 간의 상품 판매량 비교를 쉽게 할 수 있는 쿼리이다.

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
/* 아래 첫번쨰 서브쿼리는 1999년에 채널 별로 Top 5 판매량이 많은 상품을 출력한다. */
  (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
       /* channel_desc, prod_name 으로 group by를 해서 SUM(amount_sold)로 합을 구한다. 
       그리고 ROW_NUMBER 함수를 사용해 channel_desc별로 합계를 Desc로 정렬하고 rn (row_number)
       를 생성한다. */                    
     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,
/* 다음 서브 쿼리는 2000년도에 channel_desc, prod_name 별 amount_sold를 구한다. */
  (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
/* 최종적으로 위 2개의 서브쿼리끼리 join을 수행한다. */
;
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.

참조링크 : https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/ROW_NUMBER.html#GUID-D5A157F8-0F53-45BD-BF8C-AE79B1DB8C41

 

-- The End --