문제5. SQL / 서브쿼리 튜닝 및 조인

[코드를 쓴 상황]

사원이름, 월급, 부서번호가 20번인 사원들의 토탈월급, 부서번호가 20번인 사원들의 최대월급, 부서번호가 20번인 사원들의 최소월급을 출력하시오.

 

 

[내가 쓴 코드 + 에러]

-- 내가 쓴 코드 1
select ename, sal, sum(sal) over() as sum_s,
                   max(sal) over() as max_s, 
                   min(sal) over() as min_s
 from emp;

sum,max,min 그룹함수를 over()를 사용하여 ename,sal 컬럼과 같이 출력시키기까지는 풀었으나 deptno=20이라는 조건을 충족시키지 못함.

-- 내가 쓴 코드 2
select ename, sal, (select sum(sal), max(sal), min(sal)
                     from emp
                     where deptno = 20)
 from emp;

select절에 서브쿼리를 이용해 조건을 충족시키려했으나 select절 서브쿼리는 한 개의 컬럼만 불러올 수 있으므로 에러가 발생

 

 

[다른사람이 쓴 코드]

--선생님이 쓴 코드 
select ename,sal, substr(totalsal,1,10) as sum, substr(totalsal,11,10) as max ,substr(totalsal,21,10) as min                                                    
from ( select ename, sal,(select rpad(sum(sal),10,' ')|| rpad(max(sal),10,' ')||rpad(min(sal),10,' ')
                     from emp
                     where deptno = 20) as totalsal 
       from emp);

1. 그룹함수를 서브쿼리를 사용하여 불러오기 위해 연결연산자를 사용 

2. 값의 구분을 위해 rpad를 이용하여 공백을 줌

3. from절로 서브쿼리를 가져온 뒤 substr로 공백을 잘라줌

 

-- 다른 사람이 쓴 코드1
select ename, sal, s, m1, m2
 from emp natural join (select sum(sal) as s, max(sal) as m1, min(sal) as m2
                           from emp
                           where deptno = 20) ;

natural join을 사용해서 from 절에 그룹함수 및 조건을 불러옴.

(natural join에서 조인조건 없이 조인할 경우, 오라클이 같은 컬럼을 찾아내지 못하므로 같은 값이 반복되서 출력됨. emp 테이블의 데이터행 14 x 서브쿼리 데이터행 1 로 서브쿼리 데이터가 emp테이블의 데이터 행 수만큼 반복되어 출력 )

 

-- 다른 사람이 쓴 코드2
select ename, sal, sum(decode(deptno,20,sal,null)) over() 토탈월급,
                   max(decode(deptno,20,sal,null)) over() 최대월급,
                   min(decode(deptno,20,sal,null)) over() 최소월급
 from emp;

 

1.그룹함수(sum,max,min)를 over()를 사용하여 확장시킴.

2. 그룹함수에 decode함수를 넣어 조건을 넣음.

 

 

[유의사항]

1. 그룹함수에 decode함수나 case문을 사용하여 조건 값을 선별할 수 있음(단, avg 사용시 null값 주의)

2. 함수 사용시 다양한 수식을 넣어보는 시도를 할 것. 함수 안에 무조건 컬럼만 들어간다는 생각하지 말 것.

3. join의 방법을 다양하게 알아두기 > 1:1매칭이 정답은 아님. 조인조건없이 조인하여 데이터를 원하는 방향으로 만드는 것도 에러가 아닌 하나의 방법일 수 있음.

4. sum()과 sum() over()의 쓰임 차이를 명확하게 알아둘 것

 

 

[해결]

-- 다시 쓴 코드 1
select ename, sal, sum(decode(deptno, 20, sal, null)) over () as sum_s,
                   max(decode(deptno, 20, sal, null)) over () as max_s,
                   min(decode(deptno, 20, sal, null)) over () as min_s
 from emp;


-- 다시 쓴 코드 2
-- oracle join 
select e.ename, e.sal, v.sum_s, v.max_s, v.min_s 
from emp e, ( select sum(sal) as sum_s, max(sal) as max_s , min(sal) as min_s 
               from emp
               where deptno = 20 ) v ;
              
-- 1999 ANSI cross join
select e.ename, e.sal, v.sum_s, v.max_s, v.min_s 
from emp e cross join ( select sum(sal) as sum_s, max(sal) as max_s , min(sal) as min_s 
                         from emp
                         where deptno = 20 ) v ;

1. 다시 쓴 코드1에서 decode 함수가 아닌 case문을 써도 상관 없음. 

2. 다시 쓴 코드 2에서 1999ANSI에서 조인조건없이 테이블의 각 행을 곱하여 데이터를 만드는 cross join을 사용함 < 조인조건이 없기 때문에 natural join과 같은 결과를 나타냄. 

 

 

 

 

[참고 - 이 문제를 푸는 악성 SQL]

select ename, sal, (select sum(sal) 
                     from emp
                     where deptno = 20) as sum_s,
                   (select max(sal) 
                     from emp
                     where deptno = 20) as max_s,
                   (select min(sal) 
                     from emp
                     where deptno = 20) as min_s
 from emp;

위와 같은 SQL은 emp값을  총 4번 select하기 때문에 성능이 많이 느려짐. 굳이 이렇게 풀 필요 없음.