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

 

오라클 순번매기기 ROWNUM

SELECT 
ROWNUM,
A.PRODUCT_NAME,
A.STANDARD_COST
FROM
PRODUCTS A

ROWNUM이라는 시스템 함수를 사용하면 위와 같이 라인별 순번을 가져올 수 있습니다. 하지만 위와 같이 아무런 정렬이 되지 않은 데이터에 순번을 매긴다면 그 순번은 상당히 의미 없는 숫자가 되어 버릴 것입니다.

 

정렬된 데이터에 순번매기기

위의 예제와 같이 순번을 매기는 시스템 함수는 ROWNUM이고 정렬은 ORDER BY를 사용하시면 되는데 ROWNUM와 ORDER BY를 같이 사용할 경우 매겨놓은 순번이 섞여버리는 현상이 발생합니다.

SELECT 
ROWNUM,--순번매기기
A.PRODUCT_NAME,
A.STANDARD_COST
FROM
PRODUCTS A
ORDER BY A.STANDARD_COST

위와같이 ROWNUM함수는 ORDER BY함수를 같이 사용하면 순번이 뒤죽박죽이 됩니다. 그 이유는 먼저 순번을 매기고 난 뒤 ORDER BY 정렬을 하기 때문입니다. 이 문제를 해결하기 위해서는 현재 순번매기기 -> 정렬 순으로 진행하는 방식을 정렬 -> 순번매기기로 순으로 진행하면 해결이 가능합니다.

 

서브쿼리에서 먼저 정렬(ORDER BY) 후 순번 매기기(ROWNUM)

SELECT 
ROWNUM,--순번매기기
A.PRODUCT_NAME,
A.STANDARD_COST
FROM
(
    SELECT 
    * 
    FROM 
    PRODUCTS 
    ORDER BY STANDARD_COST --서브쿼리내에서 정렬
)A

위와 같이 서브쿼리에서 먼저 정렬을 하고 순번을 매기는 방법으로 정렬된 데이터에 순번을 매길 수 있습니다.

 

정렬된 데이터의 MAX값, MIN값 가져오기

SELECT 
ROWNUM,--순번매기기
A.PRODUCT_NAME,
A.STANDARD_COST
FROM
(
    SELECT 
    * 
    FROM 
    PRODUCTS 
    ORDER BY STANDARD_COST --서브쿼리내에서 정렬
)A
WHERE
ROWNUM = 1 --가장 작은 STANDARD_COST

WHERE절에 ROWNUM = 1 이라고 명시해준다면 가장 첫번째 ROW만을 가져올 수 있습니다. 정렬된 데이터에 순번을 잘 매겼다면 가장 작은 STANDARD_COST의 값을 가져오게 됩니다. 가장 높은 STANDARD_COST의 값을 가져오고싶다면 서브쿼리내에서 ORDER BY 방식을 역순으로 바꿔주면 됩니다.

 

그룹별 순번을 반환하는 ROW_NUMBER() 함수

사실 서브쿼리를 사용하여 먼저 정렬을 하고 순번을 매기는 방법은 상당히 비효율적입니다. 정렬된 데이터에 순번을 주고싶다면 ROW_NUMBER()이라는 함수를 사용하면 서브쿼리를 사용하지않고도 순번을 매길 수 있습니다. 

SSELECT 
ROW_NUMBER() OVER (ORDER BY A.STANDARD_COST) AS NUM, --그룹별 순번
A.PRODUCT_NAME,
A.STANDARD_COST
FROM
PRODUCTS A

ROW_NUMBER은 파티션으로 분할 된 그룹별로 각 로우에 대한 순번을 반환하는 함수입니다.

 

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