[Oracle] 오라클 그룹별 순번, 순위 매기기(ROW_NUMBER, RANK) 함수

 

그룹별 순번매기기 ROW_NUMBER()

카테고리(CATEGORY_ID)별 저렴한 금액(STANDARD_COST)순으로 순번 매기기

SELECT 
A.CATEGORY_ID,
B.CATEGORY_NAME,
A.STANDARD_COST,
ROW_NUMBER() OVER (PARTITION BY A.CATEGORY_ID
ORDER BY A.CATEGORY_ID, A.STANDARD_COST) AS NUM --그룹별 순번
FROM
PRODUCTS A
INNER JOIN PRODUCT_CATEGORIES B ON A.CATEGORY_ID = B.CATEGORY_ID

카테고리별 가격의 순번을 매기는 쿼리입니다. CPU카테고리에서는 70번까지의 순번이 값이 작은 순에서 큰 순으로 순번이 매겨졌고 그 뒤로 두번째 카테고리인 Video Card의 순번이 매겨지는것을 보실 수 있습니다. PARTITION BY절에 카테고리 (CATEGORY_ID)를 명시해 카테고리 별 파티션이 먼저 만들어졌고 ORDER BY절에서 카테고리별 가격을 정렬하여 순번을 부여하였습니다.

 

그룹별 순위매기기 RANK()

카테고리(CATEGORY_ID)별 저렴한 금액(STANDARD_COST)순으로 순위 매기기

SELECT 
A.CATEGORY_ID,
B.CATEGORY_NAME,
A.STANDARD_COST,
RANK() OVER (PARTITION BY A.CATEGORY_ID
ORDER BY A.CATEGORY_ID, A.STANDARD_COST) AS RANKING --그룹별 순위
FROM
PRODUCTS A
INNER JOIN PRODUCT_CATEGORIES B ON A.CATEGORY_ID = B.CATEGORY_ID

순위를 매기는 RANK()는 순번을 매기는 ROW_NUMBER()과 사용방식은 같고 리턴되는 값이 순번이냐 순위냐만 다릅니다. 위의 결과를 보시면 3000.41이라는 값을가진 데이터가 3개가 있습니다. 이 데이터는 서로 값이 같기때문에 동등한 순위를 가집니다. 위의 결과를 보시면 66이라는 순위로 3개가 출력되고 그 다음번 순위는 3번을 건너뛰어 69가 출력되는것을 보실 수 있습니다. 1등뒤에 공동 2등 있다면 3등은 없고 4등이 있는셈입니다.

 

같은 순위가 나왔을 경우 다음 순위가 건너띄게 하고 싶지 않을 경우

RANK함수는 같은 순위의 데이터가 나왔을 경우 그 다음 순위는 같은 순위의 데이터만큼 건너띄고 출력합니다. 하지만 건너띄고 싶지 않을 경우도 있을 수 있습니다. 이럴 경우에는 RANK() 함수대신 DENSE_RANK()를 사용하면 됩니다. 

SELECT 
A.CATEGORY_ID,
B.CATEGORY_NAME,
A.STANDARD_COST,
DENSE_RANK() OVER (PARTITION BY A.CATEGORY_ID
ORDER BY A.CATEGORY_ID, A.STANDARD_COST) AS RANKING --그룹별 순위
FROM
PRODUCTS A
INNER JOIN PRODUCT_CATEGORIES B ON A.CATEGORY_ID = B.CATEGORY_ID

DENSE_RANK()를 사용하시면 동일한 순위가 나왔을 경우 동일한 순위의 데이터만큼 다음번 순위를 건너띄지 않고 바로 다음번 순위를 출력합니다. 1등뒤에 공동 2등 있어도 그 다음번 순위는 3등인 셈입니다.

 

파티션 별 상위 3개만 조회

카테고리(CATEGORY_ID)별 저렴한 금액(STANDARD_COST)순으로 3개까지만 조회

SELECT
*
FROM
(
    SELECT 
    A.CATEGORY_ID,
    B.CATEGORY_NAME,
    A.STANDARD_COST,
    RANK() OVER (PARTITION BY A.CATEGORY_ID
    ORDER BY A.CATEGORY_ID, A.STANDARD_COST) AS RANKING --그룹별 순위
    FROM
    PRODUCTS A
    INNER JOIN PRODUCT_CATEGORIES B ON A.CATEGORY_ID = B.CATEGORY_ID
)
WHERE RANKING <=3

순위를 구하는 함수는 다양한 방식으로 응용이 가능합니다. 가장 대표적으로 많이 응용것이 그룹별로 상위 몇개의 데이터만 출력하는 경우가 있습니다. 먼저 그룹별로 등급을 매긴 데이터를 서브쿼리안에 넣고 WHERE절에 생성해준 순위 컬럼인 RANKING <= (NUMBER)을 명시하여 그룹별로 상위 데이터를 출력할 수 있습니다.

 

[Oracle] 오라클 순번 매기기 (ROWNUM, ROW_NUMBER) 함수