앞의 3.9절과 같은 문제로 시작한다.
기존 'EMP_BONUS' 테이블을 보면, 부서값이 10인 사원들에게만 보너스를 주게 되어있다.
이를 수정하여 부서값이 10인 일부 사원에게만 보너스가 주어지도록 할 것이다.

select * from emp_bonus;
>>
EMPNO	RECEIVED	TYPE
7934	15-MAR-05	2
7934	17-MAR-05	1
2 rows selected.

Q. 부서값이 10인 모든 사원의 급여 합계와 부서값이 10인 모든 사원에 대한 보너스의 합계를 찾는 쿼리는?

/* 급여의 합계를 구하는 쿼리 */
select sum(sal) from emp where deptno = 10;
>>
SUM(SAL)
8750

/* 보너스의 합계를 구하는 쿼리 */
select sum(e.sal * case when eb.type=1 then .1
						when eb.type=2 then .2
                        else .3
                        end) as total_bonus_dept10
       from emp e, emp_bonus eb
       where e.deptno = 10;
>>
TOTAL_BONUS_DEPT10
2625

이 두 쿼리를 절묘하게 합칠 방법을 찾을 것이다.

select sum(e.sal) as total_sal_dept10
	   ,sum(e.sal * case when eb.type=1 then .1
						when eb.type=2 then .2
                        else .3
                        end) as total_bonus_dept10
       from emp e, emp_bonus eb
       where e.deptno = 10;
>>

TOTAL_SAL_DEPT10	TOTAL_BONUS_DEPT10
17500				2625

엥?
급여의 총합이 멸망했다.
다시 보니 'EMPNO'의 값을 주지 않았다. 이렇게 되면 데카르트 곱이 나올 것이다.
'EMP_BONUS' 테이블의 두 행이 'EMP' 테이블의 한 행에 두 번 들어가게 되는 것이다.

SUM()은 그룹함수이므로, 다른 칼럼까지 조회하기 위해서는 GROUP BY로 묶어줘야 한다.

select e.ename, sum(e.sal) as total_sal_dept10
	   ,sum(e.sal * case when eb.type=1 then .1
						when eb.type=2 then .2
                        else .3
                        end) as total_bonus_dept10
       from emp e, emp_bonus eb
       where e.deptno = 10 and e.empno = eb.empno
       group by e.ename;
>>
ENAME	TOTAL_SAL_DEPT10	TOTAL_BONUS_DEPT10
MILLER	2600				390

으음...
생각해보니 e.deptno=10이면서 e.empno = eb.empno를 만족하는 건 MILLER뿐이다.
그래서 부서값이 10인 다른 사원들은 조회조차 되지 않았고 당연히 합산도 되지 않았다.

A. 부서값이 10인 모든 사원이 포함되도록 'EMP_BONUS'에 외부 JOIN 한다

select deptno, sum(distinct sal) as total_sal, sum(bonus) as total_bonus
	from(
    	select e.empno, e.ename, e.sal, e.deptno,
        	   e.sal * case when eb.type=1 then .1
               				when eb.type=2 then .2
                            else .3
                            end as bonus
               from emp e left outer join emp_bonus eb
               			  on e.empno = eb.empno
               where e.deptno = 10
        )
        group by deptno;
>>
DEPTNO	TOTAL_SAL	TOTAL_BONUS
10		8750		2625

되게 잘 나온다...
서브쿼리에서 alias로 받아온 행을 외부쿼리에서 사용하는 걸 1장에서 본 것 같기도 한데 기억이 아니 난다.

/* 서브쿼리 */
select e.empno, e.ename, e.sal, e.deptno,
       e.sal * case when eb.type=1 then .1
               		when eb.type=2 then .2
                  		 else .3
                         end as bonus
       from emp e left outer join emp_bonus eb
               	  on e.empno = eb.empno
               	  where e.deptno = 10;
>>
7934	MILLER	1300	10	130.0
7934	MILLER	1300	10	260.0
7839	KING	5000	10	1500.0
7782	CLARK	2450	10	245.0
7777	IREN	9000	10	2700.0

이건 저번 해법에서도 한 번 다뤘던 건데 까먹었다.

• OUTER JOIN을 사용하면 한 쪽 테이블의 행을 모두 반환할 수 있다
• 'EMP' 테이블의 행 중에서, 보너스의 대상에 포함되든 아니 되든 일단 불러와야 급여 총합을 구할 수 있다
• 이후 DISTINCT키워드를 사용하여 중복이되는 MILLER의 급여는 한 번만 더하는 것으로 처리했다

다음은 SUM OVER()를 활용한 풀이법이다.

select distinct deptno, total_sal, total_bonus
	from (
    		select sum(distinct e.sal) over (partition by e.deptno) as total_sal,
            	   e.deptno,
                   sum(e.sal * case when eb.type is null then 0
                   					when eb.type = 1 then .1
                                    when eb.type = 2 then .2
                                    else .3
                                    end) over (partition by deptno) as total_bonus
                   from emp e left outer join emp_bonus eb
                   			  on e.empno = eb.empno
                   where e.deptno = 10);
>>

DEPTNO	TOTAL_SAL	TOTAL_BONUS
10		8750		390

---

정리

집계 중복 제거에서 SUM OVER()와 OUTER JOIN이 굉장히 유용하게 사용된다는 사실을 계속 책에서 보여주고 있다.

쿼리를 짜는 것도 훌륭한 편이 아니고, 함수에 대해서도 모르는 부분이 너무나 많다.

어떤 문제에 대한 아이디어에 어떤 것이 있다는 정도를 떠올릴 수 있도록 노력해야겠다.

• 집계에는 SUM() OVER (partition by)를 활용해보자
• OUTER JOIN은 한쪽 테이블을 모두 반환한다
  조건을 맞추면 중복도 제거하고 원하는 필드도 모두 반환할 수 있다