[Oracle] ROW_NUMBER() OVER(PARTITION BY)

1. ROW_NUMBER()

---

ROW_NUMBER() 함수는 각 행에 고유한 순번을 부여하는 윈도우 함수로, 데이터 정렬, 순위 매기기, 페이징 처리 등에 사용된다.

정렬 기준이 필요하므로 ORDER BY와 필수로 사용되며 그룹별로 순번을 따로 지정하고 싶은 경우 PARTITION BY를 선택적으로 사용할 수 있다.

sql

SELECT 
  EMPNO, ENAME, SAL,
  ROW_NUMBER() OVER (ORDER BY SAL DESC)
FROM
    EMP

 

 

 

 

2. OVER()

---

OVER()는 윈도우 함수를 사용할 때 필수 절로, 집합화된 row들의 정렬 순서를 지정하는 데 사용된다.

OVER()를 사용할 때는 ORDER BY 절을 사용하여 정렬 순서를 지정할 수 있다.

ORDER BY 절은 출력되는 로우의 순서와 일치하지 않아도 된다.

sql

SELECT
    DEPTNAME,
    EMPNO,
    SAL,
    RANK() OVER (PARTITION BY DEPTNAME ORDER BY SAL DESC)
FROM
    EMP

 

 

 

 

3. PARTITION BY

---

PARTITION BY는 데이터를 그룹으로 분할하여 계산하는 데 사용된다. 마치 GROUP BY처럼 동작하지만, 집계하지 않고 원본 데이터를 유지하면서 그룹 내에서만 순번이나 계산을 적용할 수 있다.

 

즉, GROUP BY는 그룹화된 데이터 묶음을 집계함수를 이용하여 필요한 값을 계산하고, 이를 하나의 행으로 집약시키지만, PARTITION BY는 계산된 값을 그룹화된 데이터의 각각 행에 모두 별도의 컬럼으로 추가한다.

 

 

예시 1. GROUP BY - 부서 별 평균 급여

sql

SELECT 
    DEPTNO,
    AVG(SAL) AS AVG_SAL
FROM
    EMP
GROUP BY
    DEPTNO

- DEPTNO가 10, 20, 30이면 3행만 출력됨 (요약된 값)

 

 

예시 2. PARTITION BY - 부서별 평균 급여를 모든 행에 표시

sql

SELECT 
  EMPNO, ENAME, DEPTNO, SAL,
  AVG(SAL) OVER(PARTITION BY DEPTNO) AS AVG_SAL
FROM
    EMP

 

 

 

 

4. Trouble Shoot

---

실무 중 입금 내역을 조회하는 화면에서 약정금이 중복으로 표시되고 집계되는 문제가 있었다.

 

 

• 하나의 약정금에 대해 입금이 여러 차례 나눠서 이루어지는 경우가 있다.
• 이 경우 입금이 2번, 3번 나뉘어져 있다면, 같은 약정금이 2번, 3번 반복해서 조회되는 상황이 발생한다.
• 이로 인해 소계, 총합 계산 시에도 약정금이 실제보다 더 크게 집계되는 오류가 발생한다.

 

즉, 회차별로 한 번만 보여야 할 약정금이 입금 횟수만큼 반복되어 나오는 문제였다.

 

동일 회차의 입금 행 중 첫 번째 행만 약정금을 보여주고, 나머지는 0으로 처리하기 위해 Sheet 렌더링 과정에서 셀 병합(Merge) 처리 하려 했지만, 소계 열 생성 내부 메서드와 같이 동작하지 않았다.

 

또한, 조회 후 Js에서 후처리하는 방법으로 동일 회차의 약정금을 Merge 처리하도록 작성하였다. 시간 복잡도를 계산했을 때 O(n)이 걸림에도 내부적으로 DOM을 조작과 렌더링 관련 오버헤드 때문에 브라우저 성능 저하가 심하여 정상적으로 동작하지 않았다.

 

앞서 해결 방법들이 잘못됐다 생각이 들었고, 조회 SQL 단계에서 해결하는 것이 단순히 시각적으로 보정하는 것보다 데이터 자체를 논리적으로 통제할 수 있을 것이라 생각했고 다음과 같이 처리했다.

 

sql

SELECT
  입금자, 품목, 구분, 회차, 입금액, 입금일, ...
  CASE
    WHEN ROW_NUMBER() OVER (
      PARTITION BY 입금자, 품목, 구분, 회차, ...
      ORDER BY 입금액 DESC, 입금일, ... ASC
    ) = 1 THEN 약정금
    ELSE 0
  END AS 약정금
FROM 입금내역
...