Oracle 집계함수(Analytic Functions) SUM을 이용한 누적집계 구하기

안녕하세요.

오라클 집계함수(Analytic Functions) 중 [SUM OVER]를 이용하여 누적집계 구하는 쿼리 입니다.

SUM() OVER() 절의 Grouping 담당 PARTITION BY와  정렬담당 ORDER BY 에 따라 누적집계를 구할수 있습니다.

Sample Data

/*
	EMPNO : 사번
	DEPTNO : 부서번호
	ENAME : 사원명
	SAL : 급여
*/

WITH SRC
AS
(
    SELECT '7839' AS EMPNO, '10' AS DEPTNO, 'KING' AS ENAME, '5000' AS SAL FROM DUAL UNION ALL
    SELECT '7698', '30', 'BLAKE', '2850' FROM DUAL UNION ALL
    SELECT '7782', '10', 'CLARK', '2450' FROM DUAL UNION ALL
    SELECT '7566', '20', 'JONES', '2975' FROM DUAL UNION ALL
    SELECT '7788', '20', 'SCOTT', '3000' FROM DUAL UNION ALL
    SELECT '7902', '20', 'FORD', '3000' FROM DUAL UNION ALL
    SELECT '7369', '20', 'SMITH', '800' FROM DUAL UNION ALL
    SELECT '7499', '30', 'ALLEN', '1600' FROM DUAL UNION ALL
    SELECT '7521', '30', 'WARD', '1250' FROM DUAL UNION ALL
    SELECT '7654', '30', 'MARTIN', '1250' FROM DUAL UNION ALL
    SELECT '7844', '30', 'TURNER', '1500' FROM DUAL UNION ALL
    SELECT '7876', '20', 'ADAMS', '1100' FROM DUAL UNION ALL
    SELECT '7900', '30', 'JAMES', '950' FROM DUAL UNION ALL
    SELECT '7934', '10', 'MILLER', '1300' FROM DUAL UNION ALL
    SELECT '8001', '30', '8001', '3000' FROM DUAL UNION ALL
    SELECT '8000', '30', '8000', '3000' FROM DUAL
)
SELECT *
  FROM SRC

▼ Sample 데이터 실행결과

Oracle 누적 집계 샘플 데이터

 

누적집계 구하기

  

WITH SRC
AS
(
    SELECT '7839' AS EMPNO, '10' AS DEPTNO, 'KING' AS ENAME, '5000' AS SAL FROM DUAL UNION ALL
    SELECT '7698', '30', 'BLAKE', '2850' FROM DUAL UNION ALL
    SELECT '7782', '10', 'CLARK', '2450' FROM DUAL UNION ALL
    SELECT '7566', '20', 'JONES', '2975' FROM DUAL UNION ALL
    SELECT '7788', '20', 'SCOTT', '3000' FROM DUAL UNION ALL
    SELECT '7902', '20', 'FORD', '3000' FROM DUAL UNION ALL
    SELECT '7369', '20', 'SMITH', '800' FROM DUAL UNION ALL
    SELECT '7499', '30', 'ALLEN', '1600' FROM DUAL UNION ALL
    SELECT '7521', '30', 'WARD', '1250' FROM DUAL UNION ALL
    SELECT '7654', '30', 'MARTIN', '1250' FROM DUAL UNION ALL
    SELECT '7844', '30', 'TURNER', '1500' FROM DUAL UNION ALL
    SELECT '7876', '20', 'ADAMS', '1100' FROM DUAL UNION ALL
    SELECT '7900', '30', 'JAMES', '950' FROM DUAL UNION ALL
    SELECT '7934', '10', 'MILLER', '1300' FROM DUAL UNION ALL
    SELECT '8001', '30', '8001', '3000' FROM DUAL UNION ALL
    SELECT '8000', '30', '8000', '3000' FROM DUAL
)
SELECT EMPNO, DEPTNO, ENAME, SAL
     , SUM(SAL) OVER(ORDER BY DEPTNO, EMPNO) AS "누적합계"                         /*  부서, 사원 순서  누적 */
     , SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY EMPNO) AS "부서별 누적합계"      /*  부서 기준 누적    */   
     , SUM(SAL) OVER() AS "전체 합계"   /*  전체 합계    */
  FROM SRC
 ORDER BY DEPTNO, EMPNO;

 

▼ 누적집계 실행 결과

① 샘플데이터 : 사번, 부서번호, 사원명, 급여

② 부서와 사원 정렬기준 급여 누적 => SUM(SAL) OVER(ORDER BY DEPTNO, EMPNO)

③ 부서 기준 급여 누적 => SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY EMPNO)

④ 전체 합계 => SUM(SAL) OVER()

누적 집계 실행 결과