[Oracle] 조회 순번 매기기 / ROWNUM, ROW_NUMBER()

++ 페이징을 구현할 때, ORACLE을 사용하면 ROWNUM을 사용한다.

 

 

조회 순번을 매기는 방법에는 두 가지가 있다.

1. ROWNUM

2. ROW_NUMBER()

 

 

 

 

 1. ROWNUM 

- 조회된 순서대로 순번을 매긴다.

- 또한, 쿼리로 반환되는 ROW의 갯수를 제한할 때도 사용 가능하다. (WHERE 절의 사용으로)

 

 

형식)
SELECT  ROWNUM, 컬럼명
FROM  테이블명

 

 

*주의할 점

1) 만약, ROWNUM을 사용할때 동시에 ORDER BY를 사용하려고 한다면, 주의해야한다.

 

ORDER BY를 ROWNUM과 같은 위치에 사용한다면 ROWNUM이 적용된 다음에 정렬을 하기 때문에 순번이 뒤섞여버린다. 따라서 ORDER BY를 사용하고자 한다면 꼭 서브쿼리를 사용해서 정렬된 서브쿼리 결과에 ROWNUM을 사용해야한다.

 

## ORDER BY를 ROWNUM과 같은 위치에 사용할때 << 잘못된 쿼리. 순서가 뒤죽박죽이 된다.

SELECT * FROM employees ORDER BY employee_id WHERE ROWNUM < 11; // 잘못된 쿼리!

 

## 서브쿼리에 ORDER BY사용하고 탑레벨에 ROWNUM 사용

SELECT * 
FROM
   (SELECT * FROM employees ORDER BY employee_id)
WHERE ROWNUM < 11;

 

 

2) 양의 정수 1보다 큰 ROWNUM 테스트 조건은 항상 거짓이다.

이유는 ROWNUM의 검색 결과의 첫번째로는 항상 ROWNUM 1이 되기 때문이다.

 

따라서, 아래의 쿼리문은 어떠한 ROW로도 리턴하지 않고 항상 false를 반환한다.

SELECT * FROM employees
WHERE ROWNUM > 1;

 

 

따라서, oracle에서는 ROWNUM을 사용하는 대신 ROW_NUMBER() 함수를 사용하여 행 순서를 부여하는 것이 더 안전하고 예측가능한 방법이라고 한다.

 

 

 

 2. ROW_NUMBER() 

- ROW_NUMBER() 은 분석함수로, 리턴된 각각의 ROW에 고유한 숫자를 할당한다. (1로 시작한다.)

 

- 구체적 범위에서 ROW_NUMBER를 검색하는 쿼리 내에서 ROW_NUMBER를 사용해 서브쿼리를 중첩하면, 내부 쿼리의 결과에서 ROW의 정확한 하위 부분을 조회할 수 있다.

 

- ROW_NUMBER() 사용 시에,  PARTITION BY 는 선택이고, ORDER BY 는 필수이다.

 

- PARTITION BY 는 같은 값들을 묶어서 표현할 때 사용한다. 일반적으로 파티션을 친다고 생각하면 쉽다.
각 파티션은 1로 시작하고 같은 파티션에 속하는 ROW가 많을수록 높은 숫자를 가진다. (숫자 순위가 높다)

- ORDER BY를 사용하여 정렬할 때는 ROWNUM 보다 ROW_NUMBER()을 사용하는 것이 더 효과적이다.

 

 

형식 1)  나누고 싶은 곳에서 구역을 나누고 정렬해서 순서를 정할때
ROW_NUMBER() OVER (PARTITION BY [그룹으로 묶을 컬럼] ORDER BY [정렬할 컬럼])  AS 별칭

SELECT department_id, last_name, employee_id, ROW_NUMBER()
   OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id
   FROM employees;


DEPARTMENT_ID LAST_NAME                 EMPLOYEE_ID     EMP_ID
------------- ------------------------- ----------- ----------
           10 Whalen                            200          1
           20 Hartstein                         201          1
           20 Fay                               202          2
           30 Raphaely                          114          1
           30 Khoo                              115          2
           30 Baida                             116          3
           30 Tobias                            117          4
           30 Himuro                            118          5
           30 Colmenares                        119          6
           40 Mavris                            203          1

 

 

형식 2)  원하는 컬럼에 대해 정렬만해서 순서를 정할때
ROW_NUMBER() OVER (ORDER BY [정렬할 컬럼])  AS 별칭

SELECT ROW_NUMBER() OVER(ORDER BY a.job) row_num, a* 
FROM emp a 
ORDER BY a.job;

 

SELECT last_name 
FROM 
   (SELECT last_name, ROW_NUMBER() OVER (ORDER BY last_name) R FROM employees)
WHERE R BETWEEN 51 and 100;

51~100번째 사이의 last_name만 조회하는 쿼리 >> MYSQL 의 limit 함수와 비슷하다.

 

 

 

+++

ROW_NUMBER() 사용 예시 >> [Lv.3 Oracle] 131123. 즐겨찾기가 가장 많은 식당 정보 출력하기

 

 

---

참고

• https://tychejin.tistory.com/201
• https://escapefromcoding.tistory.com/371
• https://gent.tistory.com/170