[Oracle] 누적 합계 구하기, WINDOWING 행 기준 범위 연산

누적 합계 구하기

EMP테이블에서 사원의 정보와 부서번호로 정렬되어 있을때 급여의 누적합계도 같이 출력해라.

ORACLE SCOTT 계정으로 실습을 진행합니다.

1
2
3
4
5
6
7
8
9
10

SELECT
    DEPTNO
    ,EMPNO
    ,ENAME
    ,SAL
    ,SUM(SAL) OVER(
        ORDER BY DEPTNO
        ROWS UNBOUNDED PRECEDING
    ) AS SUM_SAL
FROM EMP;

1
2
3
4
5
6
7
8
9
10
11

SELECT
    DEPTNO
    ,EMPNO
    ,ENAME
    ,SAL
    ,SUM(SAL) OVER(
        ORDER BY DEPTNO
        ROWS BETWEEN UNBOUNDED PRECEDING
        AND CURRENT ROW
    ) AS SUM_SAL
FROM EMP;

누적 합계를 구하기 위해 분석함수 OVER절을 사용합니다. 그리고 ORDER BY 절 뒤로 WINDOWING 절을 사용하는 것이 핵심입니다. WINDOWING절에서는 분석함수의 대상이 되는 행들의 범위 조정이 가능합니다. 누적 합계를 구하기 위해서는 첫번째 행부터 읽고 있는 행까지(ROWS UNBOUNDED PRECEDING)를 분석함수의 범위 대상으로 지정한 것 입니다.

WINDOWING절에서는 물리적(ROWS), 논리적(RANGE) 범위를 지정할 수 있습니다.

WINDOWING 물리적 범위

물리적 범위는 행을 기준으로 범위를 지정하는 것 입니다. ROWS를 사용해서 행으로 부터 전(PRECEDING)과 후(FOLLOWING)의 범위를 지정할 수 있습니다. 위의 누적 합계를 구하는 예시도 물리적 범위의 WINDOWING입니다.

사원의 정보를 부서 번호 기준으로 정렬 했을때 바로 전과 후 행의 급여평균 값과 함께 출력해라.

1
2
3
4
5
6
7
8
9
10
11
12
13

SELECT
    DEPTNO
    ,EMPNO
    ,ENAME
    ,SAL
    ,ROUND(
        AVG(SAL) OVER(
            ORDER BY DEPTNO
            ROWS BETWEEN 1 PRECEDING
            AND 1 FOLLOWING
        )
    ) AS AVG_SAL
FROM EMP;

WINDOWING 논리적 범위

논리적 범위는 값을 기준으로 범위를 지정하는 것 입니다. RANGE를 사용해서 해당 행의 값을 기준으로 전(PRECEDING)과 후(FOLLOWING)의 범위를 지정할 수 있습니다. 예를 들어 2번째 행에서 급여 4000의 1000 PRECEDING과 1000 FOLLOWING 범위는 3000 ~ 5000까지의 값을 분석함수에 적용하는 것 입니다.

사원의 정보를 부서 번호 기준으로 정렬 했을때 기준 행의 -1000 ~ 1000에 해당하는 값들의 급여평균 값과 함께 출력해라.

1
2
3
4
5
6
7
8
9
10
11
12
13

SELECT
    DEPTNO
    ,EMPNO
    ,ENAME
    ,SAL
    ,ROUND(
        AVG(SAL) OVER(
            ORDER BY SAL --어떤 컬럼으로 정렬하느냐에 따라서 논리적인 값이 변경된다.
            RANGE BETWEEN 1000 PRECEDING
            AND 1000 FOLLOWING
        )
    ) AS AVG_SAL
FROM EMP;
 