앞의 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.
/* 급여의 합계를 구하는 쿼리 */
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인 다른 사원들은 조회조차 되지 않았고 당연히 합산도 되지 않았다.
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
을 사용하면 한 쪽 테이블의 행을 모두 반환할 수 있다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
은 한쪽 테이블을 모두 반환한다