[Oracle] 오라클 누적 합계 구하기 (SUM OVER)

 

오라클에서 누적 합계를 구하기 위해서는 SUM 함수와 OVER 절을 사용하면 된다. OVER 절을 사용하면 GROUP BY 절을 사용하지 않고도 SELECT 절에서 단독으로 합계를 구할 수 있다. OVER 절 내부의 ORDER BY 절의 칼럼 순서로 누적 합계가 표시되며, 조회된 결과도 해당 칼럼으로 정렬된다. ORDER BY 절에 선언된 칼럼의 값에 따라서 누적 합계 표시 형식이 달라질 수 있으므로 주의해야 한다.

 

오라클 누적 합계

누적 합계 구하기

SELECT empno
     , ename
     , job
     , sal
     , SUM(sal) OVER(ORDER BY empno) AS sal_sum
  FROM emp
 WHERE job IN ('MANAGER', 'SALESMAN')

 

 

SUM 함수와 OVER 절을 사용하여 순차적으로 누적 합계를 구할 수 있다.

조회된 결과는 OVER 절의 ORDER BY 절 칼럼 순서로 정렬된다.

 

OVER 절의 ORDER BY 절 칼럼 사용 시 주의 사항

 

조회된 결과의 정렬 순서를 바꾸기 위해서 OVER 절의 ORDER BY 절에 칼럼을 선언할 때 주의해야 한다.

ORDER BY 절에 선언된 칼럼 값을 기준으로 그룹화하여 값을 표시하기 때문에, 모든 행의 값이 순차적으로 표시되기 원한다면 ORDER BY 절의 칼럼을 모든 행의 유일한 값으로 선언해야 한다.

 

위의 예제는 sal 칼럼으로 정렬했을 때 1250 값이 중복되었기 때문에 동일한 값을 표시한다. 동일한 값이 표시되지 않게 하가 위해서는 ORDER BY 절에 칼럼을 추가해야 한다.

예) ORDER BY sal DESC → ORDER BY sal DESC, empno (모든 행을 순차적으로 누적 합계 표시)

 

그룹별 누적 합계 구하기

SELECT empno
     , ename
     , job
     , sal
     , SUM(sal) OVER(PARTITION BY job ORDER BY sal DESC, empno) AS sal_sum
  FROM emp
 WHERE job IN ('MANAGER', 'SALESMAN')

 

 

OVER 절 내부에 PARTITION BY 절을 사용하면 그룹으로 누적 합계를 표시할 수 있다.

 

SELECT 절의 ORDER BY 절  사용 시 주의 사항

 

SELECT 절의 ORDER BY 절을 사용할 경우 OVER 절의 누적 합계된 값이 다시 정렬되므로 주의해야 한다.

순서가 바뀌지 않게 하기 위해서는 OVER 절의 ORDER BY절과 동일하게 선언하거나 선언 자체를 하지 말아야 한다.

 

활용 예제

총합계 표시

SELECT empno
     , ename
     , job
     , sal
     , SUM(sal) OVER(ORDER BY empno) AS sal_sum
     , SUM(sal) OVER() AS sal_total_sum
  FROM emp
 WHERE job IN ('MANAGER', 'SALESMAN')

 

 

OVER절 내부에 ORDER BY 절을 선언하지 않으면 전체 합계가 표시된다.

 

이전 누적 합계 표시

SELECT empno
     , ename
     , job
     , sal
     , SUM(sal) OVER(ORDER BY empno) AS sal_sum
     , SUM(sal) OVER(ORDER BY empno 
                      ROWS BETWEEN UNBOUNDED PRECEDING 
                               AND 1 PRECEDING) AS sal_pre_sum     
  FROM emp
 WHERE job IN ('MANAGER', 'SALESMAN')

 

 

WINDOWING 절을 사용하여 조회된 값의 행 순서를 지정하여 값을 집계할 수 있다.

예제쿼리(테이블포함).txt

0.00MB

 

[Oracle] ROWS와 RANGE (WINDOWING 절)

[Oracle] PARTITION BY 사용법 정리 (분석함수)