일반적으로, 집계 작업을 수행하려는 쿼리에는 여러 테이블이 관련되어 있다.
이 때, 진행 중인 집계 작업이 JOIN 수행으로 인해 멈추는 일이 없도록 하고 싶다.
Q. 부서값이 10에 해당하는 사원의 급여 합계와 보너스 합계를 찾고자 한다
일부 사원은 보너스를 두 번 받는다.
만약, 'EMP' 테이블과 'EMP_BONUS' 테이블 간의 조인 때문에, 집계 함수 SUM에서 잘못된 값이 반환된다면?
먼저 'EMP_BONUS' 테이블을 생성해야겠다.
create table emp_bonus(
empno number,
received date,
type number,
constraint pk_eb primary key (empno),
constraint fk_empno foreign key emp (empno)
)이미 만들어 놓았는데 깜빡했다.
그런데 내부 값이 달라졌다.
한 번 비우고 다시 집어넣도록 한다.
truncate table emp_bonus;
select * from emp_bonus;
>>
none이제 데이터를 insert할 것이다.
/* Oracle에 입력 */
/* Maria에는 날짜를 'yyyy-mm-dd' 형식으로 넣는다 */
insert into emp_bonus
values(
7934,
to_date('17-3-2005','dd-mm-yyyy'),
1
);
insert into emp_bonus
values(
7934,
to_date('15-2-2005','dd-mm-yyyy'),
2
);
insert into emp_bonus
values(
7839,
to_date('15-2-2005','dd-mm-yyyy'),
3
);
insert into emp_bonus
values(
7782,
to_date('15-2-2005','dd-mm-yyyy'),
1
);'EMPNO'를 Primary Key로 지정해놓는 바람에 값이 7934인 행이 중복으로 안 들어간다...
Primary Key부터 삭제하고 다시 넣어야겠다.
alter table emp_bonus drop primary key
>>
Table altered.다시 위의 insert 쿼리를 입력.
>>
1 row(s) inserted.
1 row(s) inserted.
1 row(s) inserted.
1 row(s) inserted.
select * from emp_bonus;
>>
+-------+------------+------+
| empno | received | type |
+-------+------------+------+
| 7934 | 2005-03-17 | 1 |
| 7934 | 2005-02-15 | 2 |
| 7839 | 2005-02-15 | 3 |
| 7782 | 2005-02-15 | 1 |
+-------+------------+------+굿!
이제 부서값이 10인 모든 사원에 대한 급여 및 보너스를 반환하는 쿼리를 작성해야 한다.
'TYPE'의 값은 보너스 %를 결정한다.
1은 10%, 2는 20%, 3은 30%.
이 수치를 쿼리에서 바로 곱해서 출력할 수 있다.
select e.empno, e.ename, e.sal, e.deptno,
e.sal * case when eb.type = 1 then .1
when eb.type = 2 then .2
when eb.type = 3 then .3
end as bonus
from emp e, emp_bonus eb
where e.empno = eb.empno
and e.deptno = 10;
>>
+-------+--------+------+--------+--------+
| empno | ename | sal | deptno | bonus |
+-------+--------+------+--------+--------+
| 7934 | MILLER | 1300 | 10 | 130.0 |
| 7934 | MILLER | 1300 | 10 | 260.0 |
| 7839 | KING | 5000 | 10 | 1500.0 |
| 7782 | CLARK | 2450 | 10 | 245.0 |
+-------+--------+------+--------+--------+- 사원번호를 일치시켜야하므로
FROM절에서 'EMP_BONUS' 테이블에 alias를 붙여 가져왔다 - 사원번호로 보너스 받을 사원을 한정하고 부서값을 조건으로 부쳤다.
여기까지는 잘 나오지만, 보너스 금액을 합산하려고 'EMP_BONUS' 테이블에 JOIN을 수행하려 하면 문제가 발생한다.
부서값이 10인 부서에 지급된 급여의 총계와 보너스의 총계를 구하려고 한다.
select deptno, sum(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, emp_bonus eb
where e.empno = eb.empno
and e.deptno = 10
) x
group by deptno;
>>
+--------+-----------+-------------+
| deptno | total_sal | total_bonus |
+--------+-----------+-------------+
| 10 | 10050 | 2135.0 |
+--------+-----------+-------------+총계가 맞는지 점검해본다.
select sum(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
when eb.type = 3 then .3
end as bonus
from emp e, emp_bonus eb
where e.empno = eb.empno
and e.deptno = 10
) x
>>
+------------+
| sum(bonus) |
+------------+
| 2135.0 |
+------------+
select sum(sal)
from emp
where deptno=10;
>>
+----------+
| sum(sal) |
+----------+
| 8750.0 |
+----------+다르다. 급여 총계는 다른 걸 볼 수가 있다.
저 위의 쿼리에서 무엇이 틀렸길래 'TOTAL_SAL'이 잘못 출력되었을까?
'EMP_BONUS' 테이블을 보면 'EMPNO'가 동일한 행이 있는 것을 볼 수 있다.
JOIN 과정에서 중복 행이 생긴 것이다.
select e.ename, e.sal
from emp e, emp_bonus eb
where e.empno = eb.empno
and e.deptno = 10
>>
+--------+------+
| ename | sal |
+--------+------+
| MILLER | 1300 |
| MILLER | 1300 |
| KING | 5000 |
| CLARK | 2450 |
+--------+------+'ENAME'의 값이 MILLER인 행이 두 줄 나온 것을 볼 수 있다.
A. DISTINCT 키워드를 사용한다
급여에서만 오차가 있었다. 급여에서 고유값만 더한다면 집계가 정확해질 것이다.
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, emp_bonus eb
where e.empno = eb.empno
and e.deptno = 10
) x
group by deptno;
>>
+--------+-----------+-------------+
| deptno | total_sal | total_bonus |
+--------+-----------+-------------+
| 10 | 8750 | 21350 |
+--------+-----------+-------------+sum(sal)에 DISTINCT를 추가한 이 해법은 Oracle에서도 당연히 정상작동한다.
추가적으로, 윈도우 함수 SUM OVER의 사용도 지원한다.
또한 다른 쿼리 방식도 해법이 될 수 있다.
- 부서값이 10인 모든 급여합계를 먼저 계산한다
- 1에서 반환된 행을 'EMP'테이블에 JOIN한다
- 2의 결과셋을 'EMP_BONUS' 테이블에 조인한다
이 쿼리는 모든 DBMS에서 작동한다
오늘 쿼리의 난도는 그렇게 높지 않았던 것 같다.
내일 볼 쿼리가 중요하게 보인다.
'열에 중복되는 값이 있을 때 필요한 대체 해법'이라고 되어 있다.
내일은 저 두 해법에 대해 간단히 살펴봐야겠다.
정리
CASE WHEN THEN END구문은 매우매우 멋지다DISTINCT키워드는 함수 내에서도 쓸 수 있었다
JOIN에서 합계를 계산할 때는 중복된 반환에 유의하자
