앞의 해법들이 순수 쿼리 중심이었다면, 이번엔 함수를 사용하여 보다 손쉽게 결과셋을 반환하는 쿼리를 확인하겠다.

SUM OVER

누적 합계를 구한다.
기본적인 구조는 다음과 같다.

sum(누적 합계를 실행할 칼럼) over (partition by 나눌 기준이 될 칼럼) 

• PARTITION BY
  칼럼을 기준으로 그룹화해준다

이게 무슨 뜻이냐!

아래의 쿼리와 결과셋을 보면서 확인하는 게 제일 편하겠다.

/* MARIA DB (EMP 테이블에 IREN이 커스텀되어 있는 상태) */
select e.empno, e.ename, e.deptno, sum(sal) over (partition by e.deptno) 
from emp e;
>>
+-------+--------+--------+---------------------------------------+
| empno | ename  | deptno | sum(sal) over (partition by e.deptno) |
+-------+--------+--------+---------------------------------------+
|  7777 | IREN   |     10 |                                 17750 |
|  7839 | KING   |     10 |                                 17750 |
|  7934 | MILLER |     10 |                                 17750 |
|  7782 | CLARK  |     10 |                                 17750 |
|  7902 | FORD   |     20 |                                 10875 |
|  7369 | SMITH  |     20 |                                 10875 |
|  7566 | JONES  |     20 |                                 10875 |
|  7788 | SCOTT  |     20 |                                 10875 |
|  7876 | ADAMS  |     20 |                                 10875 |
|  7900 | JAMES  |     30 |                                  9400 |
|  7521 | WARD   |     30 |                                  9400 |
|  7698 | BLAKE  |     30 |                                  9400 |
|  7499 | ALLEN  |     30 |                                  9400 |
|  7654 | MARTIN |     30 |                                  9400 |
|  7844 | TURNER |     30 |                                  9400 |
+-------+--------+--------+---------------------------------------+

• 'DEPTNO'를 PARTITION BY의 대상으로 삼고,
  'DEPTNO'가 일치하는 행끼리 모아서 그 합계를 구한 것을 볼 수 있다

• 특정 칼럼을 기준으로 그룹화해주는 효과를 가진 것이다

• 'DEPTNO'의 값이 10인 부서의 'SAL' 필드의 총합은 17750으로 나타나고 있다
  다른 값을 가진 행들의 합계도 동일한 원리로 반환되고 있다

• 해당 열을 기준으로 정렬의 기준을 제시하는 GROUP BY와는 결을 달리한다는 것에 주의해야 하겠다

이 함수를 이용하여 보너스의 합계도 구할 수 있을 것이다.

/* Oracle Live SQL */
select e.empno, e.ename,
	   sum(distinct e.sal) over (partition by e.deptno) as total_sal,
       sum(e.sal * case when eb.type=1 then .1
       					when eb.type=2 then .2
                        when eb.type=3 then .3
                        end) over (partition by deptno) as total_bonus
		from emp e, emp_bonus eb
        where e.empno = eb.empno
        	  and e.deptno =10;
>>
+-------+--------+-----------+-------------+
| empno | ename  | total_sal | total_bonus |
+-------+--------+-----------+-------------+
|  7934 | MILLER |     10050 |      2135.0 |
|  7782 | CLARK  |     10050 |      2135.0 |
|  7839 | KING   |     10050 |      2135.0 |
|  7934 | MILLER |     10050 |      2135.0 |
+-------+--------+-----------+-------------+

• DISTINCT를 사용하여, 'SAL'칼럼에서 독립된 값을 챙겼다
• 따라서 사원명 'MILLER'에 두 번의 보너스가 지급되어도 따로 표시할 수 있게 되었다
  + 그런데! DISTINCT를 빼도 반환값은 동일하다
  + 보너스가 두 번 들어가기 때문에 데카르트 곱이 나오는 것 같다
• SUM(합계 대상 칼럼) OVER (PARTITION BY 기준 칼럼) 구문을 잘 확인하기
• (PARTITION BY 기준 칼럼 ORDER BY 기준)과 같이 ORDER BY를 함께 사용하는 것도 가능하다

정리

• 자주 느끼게 되는 점이지만, 함수 중심의 쿼리는 굉장히 편리하다
• 다만, 함수가 어떤 기능을 하는지 알 수 없는 상태라면 명확하지가 않다 ㅠㅠ
• 그렇다고 순수 쿼리로 해결하기엔 길이가 너무 길어지는 경우도 많다

적절한 쓰임을 찾는다는 게 가능이나 할지 궁금하다...

그렇다 해도...
SUM(합계 대상 칼럼) OVER (PARTITION BY 기준 칼럼) 구문은 굉장히 훌륭했다 👏