Oracle ROW_NUMBER() OVER()

ROWNUM과는 다르게 OVER에 파라미터를 전달하여 ROWNUMBUR를 적용할 기준을 정할 수 있다. 아래 두 사진의 차이점을 비교하여 ORDER BY / ROWNUM 혼용 사용과 ROW_NUMBER() OVER()의 차이를 알아보자.

 

1) ROW_NUMBER() OVER() 사용

SELECT
	ROW_NUMBER() OVER(ORDER BY EMPNO) AS RN,
	-- ROWNUM을 EMPNO가 ORDER BY된 결과로 적용해줘~ 로 보면 된다.
	B.*
FROM
	DEPT A
JOIN
	EMP B
ON
	A.DEPTNO = B.DEPTNO;

 

2) ORDER BY / ROWNUM 혼용 사용

SELECT
	ROWNUM,
	B.*
FROM
	DEPT A
JOIN
	EMP B
ON
	A.DEPTNO = B.DEPTNO
ORDER BY
	EMPNO;

 

두 쿼리의 결과를 보면 EMPNO Column으로 정렬이 된 것은 동일하나 ROWNUM이 다르게 적용 된 모습을 볼 수 있다.

일반적인 ORDER BY 사용법과 동일하게 특정 데이터들을 결과에서 우선시 할 수 있다.

SELECT
	ROW_NUMBER() OVER(
		ORDER BY
			CASE
				WHEN MGR = '7698' THEN
					2
				ELSE
					1
			END,
			-- 각 행의 MGR이 7698일 경우 해당 행을 2순위, 아닐 경우 1순위로 정렬해줘~ 가 된다.
			EMPNO
	) AS RN,
	B.*
FROM
	DEPT A
JOIN
	EMP B
ON
	A.DEPTNO = B.DEPTNO;

MGR이 7698인 행은 2순위, 그 외의 행은 1순위로 정렬 된 후 EMPNO로 정렬되고 있는 모습

 

 

 

 

 

OVER() 내에 ORDER BY와 함께 사용 할 수 있는 정렬 기준이 하나 더 있는데 PARTITION BY다. 정렬하고자 하는 컬럼 별로 ROWNUM을 따로 적용하고 싶을 경우 사용한다.

SELECT
	ROW_NUMBER() OVER(
		PARTITION BY 
			A.DEPTNO 
		ORDER BY
			EMPNO
	) AS RN,
	B.*
FROM
	DEPT A
JOIN
	EMP B
ON
	A.DEPTNO = B.DEPTNO;

DEPTNO가 같은 행들을 집합시킨 후 각 집합별로 ROWNUM이 적용되고 있는 모습

 

 

SELECT
	ROW_NUMBER() OVER(
		PARTITION BY
			A.DEPTNO
		ORDER BY
			CASE
				WHEN MGR = '7566' THEN
					1
				WHEN MGR = '7839' THEN
					2
				ELSE
					3
			END
	) AS RN,
	B.*
FROM
	DEPT A
JOIN
	EMP B
ON
	A.DEPTNO = B.DEPTNO
WHERE
	MGR IS NOT NULL;

DEPTNO로 파티션을 지정, 특정 데이터별로 정렬 순위를 지정하여 ROWNUM을 적용하고 있다.