누적 집계

SUM() OVER와 윈도 절(ROWS, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)로 누적 합계를 구하는 방법과, ROWS와 RANGE가 같은 정렬값에서 다르게 동작하는 점

SUM OVER윈도 절ROWSRANGEUNBOUNDED PRECEDINGCURRENT ROW누적치

#핵심 요약

SUM(sal) OVER (ORDER BY empno ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)는 첫 행부터 현재 행까지의 누적 합임

PARTITION BY를 넣으면 그 값마다 따로 누적됨

ROWS는 행을 기준으로, RANGE는 값의 범위를 기준으로 누적함
정렬값이 같은 행이 여러 개면 RANGE는 그 행들을 한꺼번에 더함

윈도 절을 생략하면 기본값은 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW임

#개념과 동작 원리

키워드	뜻
`UNBOUNDED PRECEDING`	제일 첫 번째 행
`UNBOUNDED FOLLOWING`	제일 마지막 행
`CURRENT ROW`	현재 행
`ROWS`	행을 기준으로 누적
`RANGE`	범위를 기준으로 누적

ROWS는 윈도를 물리적 단위(행)로, RANGE는 논리적 오프셋으로 정함
윈도는 결과 집합이나 파티션 안에서 위에서 아래로 움직임

윈도 절을 통째로 생략하면 기본값은 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW임
그래서 ORDER BY에 같은 값이 있으면 SUM() OVER (ORDER BY...)만 써도 RANGE처럼 같은 값 행들이 같은 누적치를 가짐

#필수 명령어 또는 SQL

SQL Developer 또는 SQL*Plus조회만 함

SELECT empno, ename, sal,
       SUM(sal) OVER (ORDER BY empno ASC ROWS
                      BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 누적치
  FROM emp
 WHERE job IN ('ANALYST', 'MANAGER');

SELECT deptno, ename, sal,
       SUM(sal) OVER (PARTITION BY deptno
                      ORDER BY empno ASC ROWS
                      BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 누적치
  FROM emp;

결과 해석: 개별 실행 출력은 기재 없음

#ROWS와 RANGE 비교

실습에서는 emp를 부서 순으로 복사한 emp2를 만들고, 같은 부서 사원의 입사일을 모두 같은 날로 바꾼 뒤 비교했음

SQL Developer 또는 SQL*Plus객체 또는 설정을 변경함

CREATE TABLE emp2
AS
SELECT * FROM emp ORDER BY deptno ASC;

UPDATE emp2 SET hiredate = '81/01/05' WHERE deptno = 10;
UPDATE emp2 SET hiredate = '81/02/17' WHERE deptno = 20;
UPDATE emp2 SET hiredate = '81/03/21' WHERE deptno = 30;
COMMIT;

결과 해석:

입사일 문자열은 세션 날짜 형식이 RR/MM/DD일 때 날짜로 바뀜(형 변환과 날짜 포맷)

SQL Developer 또는 SQL*Plus조회만 함

SELECT ename, hiredate, sal,
       SUM(sal) OVER (ORDER BY hiredate ASC ROWS
                      BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 누적치
  FROM emp2;

SELECT ename, hiredate, sal,
       SUM(sal) OVER (ORDER BY hiredate ASC RANGE
                      BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 누적치
  FROM emp2;

결과 해석: ROWS는 한 행씩 더해 같은 입사일 안에서도 누적치가 행마다 다름
RANGE는 같은 입사일 행들을 한 범위로 보아 그 행들이 모두 같은 누적치(그 날까지의 합)를 가짐

실습 복습 문제의 답(부서별 직업별, 부서별, 전체 합계):

SQL Developer 또는 SQL*Plus조회만 함

SELECT deptno, job, SUM(sal)
  FROM emp
 GROUP BY GROUPING SETS((deptno, job), (deptno), ());

결과 해석: 개별 실행 출력은 기재 없음