Oracle DBA/SQL

20230629 (1) SQL 050 : 데이터 분석 함수로 누적 데이터 출력하기(SUM OVER)

Nuez 2023. 6. 29. 11:06
반응형

SUM OVER

전체 합산 대비 비율을 계산할 때 유용한 함수 

 

구조 :

 SUM(대상컬럼) OVER ()

 

OVER 옆 괄호를 빈 상태로 두면, 전체 합산 값을 모든 행에 보여줌

OVER 옆 괄호에 정렬 기준을 지정하면, 순서대로 나열하면서 누적치를 보여줌 

 

(보다 세밀한 사용과 설명은 중간쯤에 있음)

예제. 사원테이블에서 토탈 월급을 출력하시오. 

SELECT SUM(sal)
FROM emp;

예제. 이름, 월급, 사원 테이블의 토탈 월급을 출력하시오.  

SELECT ename, sal, SUM(sal)
FROM emp;

 

ORA-00937: 단일 그룹의 그룹 함수가 아닙니다
00937. 00000 -  "not a single-group group function"

 

ename, sal은 전체 행을 출력하지만, sum(sal)은 하나의 결과 값만을 출력하려고 하기 때문. 

▼

SELECT ename, sal, SUM(sal) OVER () AS 토탈월급
FROM emp;

예제. 이름, 월급, 월급의 누적치를 출력하시오. 

SELECT ename, sal, SUM(sal) OVER (ORDER BY sal ASC) AS 토탈월급
FROM emp;

 

* 참고: 단순하게 이렇게만 쓰면 

동순위 기준 가장 마지막에 나오는 행의 값을 sum해 동순위의 누적값을 동일하게 출력함. 

예)  4,5행 WARD와 MARTIN은 월급이 동일함. (정렬기준 동순위)

이 경우 5) MARTIN까지의 누적값을 4)WARD에도 동일하게 출력함. 

 

동순위 무시하고, 행별로 누적시키고 싶다면 ?

SELECT ename, sal, SUM(sal) 
                    OVER (ORDER BY sal ASC ROWS
                            BETWEEN UNBOUNDED PRECEDING
                            AND CURRENT ROW) AS 토탈월급
FROM emp;

SUM OVER 보다 자세한 설명 

윈도우 기준 윈도우 방식 설명 
ROW / RANGE UNBOUNDED PRECEDING 맨 첫번째 행을 가리킴
UNBOUNDED FOLLOWING 맨 마지막 행을 가리킴
CURRENT ROW 현재 행을 가리킴

SUM(sal)  OVER (ORDER BY sal ASC ROWS
                 BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW)

 

위의 코드를 예시로 해석하면, 

 

월급을 오름차순으로 정렬하여 행(ROWS)을 기준으로 누적치를 구하는데 

행별로 출력할 때, 맨 첫번째 행부터 현재행까지의 SUM을 출력하시오. 

문제 253. 부서번호, 이름, 월급, 월급에 대한 누적치를 출력하는데, 부서번호별로 각각 월급을 누적해서 출력하시오.

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

주의 사항

'특정 조건 별로 나눠서 누적한다.' 라는 말을 보고 무조건 GROUP BY 쓰면 안됨. PARTITION BY

위의 예시는 윈도우 기준 ROW를 기준으로 누적치 구함. 

이번에는 RANGE를 기준으로 구하는 예시 

 

예시를 위한 준비 과정 


1. emp 테이블을 부서번호 기준으로 asc하게 정렬해서 새로운 테이블 emp2 생성. 

DROP TABLE emp2;

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



2. emp2 테이블의 부서번호가 10번인 사원들의 입사일을 81/01/05로 변경 

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;
SELECT * FROM emp2;


문제 254. emp2 테이블에서 이름, 입사일, 월급, 월급에 대한 누적치를 출력하시오.
그런데 누적할 때 정렬 기준을 월급이 아니라, 입사한 사원 순으로 정렬 기준을 정해서 출력하시오. 

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


결과를 보면 그냥 행을 기준으로 월급을 누적시켰음. 입사일 기준으로 정렬만 하고, 그 안에 sal 기준으로는 순서없이 나열된 순으로 누적함. 


RANGE 기준으로 다시 하면,

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