[오라클 데이터베이스] ROW_NUMBER()을 통해 ROWNUM을 추출해보자(Feat. 서브쿼리, 윈도우 함수, 순위 함수)

가끔씩 데이터베이스에서 ROWNUM이 필요할 때가 있다.

ROWNUM 추출해는 방법은 크게 서브쿼리 방식과 ROW_NUMBER() 함수를 사용하는 방식으로 나뉜다.

 

다음과 같은 테이블(NOTICE)과 컬럼들(ID, TITLE, HIT)이 있다.

위의 테이블에서 ROWNUM을 뽑을 때, 추출하고자 하는 데이터를 정렬하지 않고 기본적으로만 뽑아내는 방법은 간단하다. 

SELECT ROWNUM, ID, TITLE, HIT 
FROM NOTICE;

아무런 정렬없이 ROWNUM을 뽑아낸 결과

 

그런데 HIT(조회수)이 낮은 순으로(오름차순) 정렬을 한 이후에 ROWNUM을 뽑아내려면?

서브쿼리 방식과 ROW_NUMBER() 함수를 모르는 사람들은 다음과 같이 생각할 수 있다.

SELECT ROWNUM, ID, TITLE, HIT 
FROM NOTICE
ORDER BY HIT;

HIT을 정렬한 이후 ROWNUM을 뽑아낸 결과

응? ROWNUM이 1 ~ 8까지 순서대로 나오지 않고 7, 8, 1 ... 5, 4 순으로 뽑아졌다.

아니 HIT은 오름차순 정렬이 잘 되었는데 왜 그럴까?

 

ORDER BY 절은 SELECT문이 실행된 이후에 실행이 된다. 

즉, SELECT문에 의해 이미 ROWNUM이 1 ~ 8까지 순서대로 뽑아진 상태에서 ORDER BY 절에 의해 HIT 순으로 재정렬이 되었기 때문에 ROWNUM의 순서가 꼬일 수 밖에 없다! (ROWNUM은 SELECT 절에서 사용되기 때문에)

 

그렇다면 ROWNUM을 정렬 이후에 올바른 순서대로 뽑아내려면 어떻게 해야 할까?

 

1. 서브쿼리 방식

SELECT ROWNUM, ID, TITLE, HIT
FROM (SELECT * FROM NOTICE ORDER BY HIT);

서브쿼리에서 HIT을 기준으로 오름차순 정렬을 한 이후에 ROWNUM을 추출하므로 올바르게 ROWNUM을 뽑을 수 있다.

서브쿼리 방식을 활용한 올바른 순서의 ROWNUM 추출

 

2. ROW_NUMBER() 함수 방식

ROW_NUMBER() 함수는 윈도우(WINDOW) 함수의 순위 함수 중 하나이다.(나머지 함수는 RANK, DENSE_RANK)

ROW_NUMBER() 함수를 사용하는 방법은 다음과 같다. 

OVER 이후에 정렬하고자 하는 쿼리를 괄호로 감싸준다.

SELECT ROW_NUMBER() OVER (ORDER BY HIT) ID, TITLE, HIT
FROM NOTICE;

ROW_NUMBER() 함수를 활용한 올바른 순서의 ROWNUM 추출

 

 

서브쿼리보다는 ROW_NUMBER()함수가 조금 더 직관적이라고 생각하기 때문에 ROW_NUMBER()을 사용하는게 좋을 것 같다.