연습 3

앞서 부서별로 직원 수를 구하는 문제를

3가지 방법으로 살펴 보았다.

조인(Join) 4번, 하위쿼리(SubQuery)에서 1번과 4번 문제로

정리하는 의미에서

부서 이동 문제를 이용하여 다음과 같이 정리하였다.

실습 2 - GROUP의 6번 문제에서

[다음 그림과 같이 직원들의 부서 이동 과정을 출력하시오] 라는 문제를 풀었다.

SELECT DE.EMP_NO, GROUP_CONCAT(DE.DEPT_NO SEPARATOR ' > ' ) PATH
  FROM DEPT_EMP DE
GROUP BY DE.EMP_NO
HAVING COUNT(*)>1
ORDER BY COUNT(*) DESC;

이 문제는 좀 더 많은 설명과 구현이 필요한 SQL문이다.

먼저, 다음 그림과 같이 실행 결과에서 직원의 이름과 부서 명이 필요하다.

(아래의 설명을 읽는 것도 좋겠지만

위와 같이 출력되게 하는 SQL문을 직접 작성해 보면 실력향상에 도움이 될 것이다.)

SELECT DE.EMP_NO, E.FIRST_NAME, E.LAST_NAME, GROUP_CONCAT(D.DEPT_NAME SEPARATOR ' > ' ) PATH
  FROM DEPT_EMP DE
 INNER JOIN DEPARTMENTS D ON D.DEPT_NO=DE.DEPT_NO
 INNER JOIN EMPLOYEES E ON E.EMP_NO=DE.EMP_NO
GROUP BY DE.EMP_NO, E.FIRST_NAME, E.LAST_NAME
HAVING COUNT(*)>1
ORDER BY COUNT(*) DESC;

직원의 이름(FIRST_NAME, LAST_NAME)과

부서 명(DEPT_NAME)이 출력되기 위해서는

직원 테이블(EMPLOYEES)과 부서 테이블(DEPARTMENTS)을 추가해 주면 된다.

그리고, 직원 정보로 GROUP화하고

부서 명(DEPT_NAME)을 묶으면 된다.

여기서 생각해야 할 것이 GROUP으로 직원 정보를 사용하는 것이다.

직원명외에 생일을 추가한다고 할 경우

GROUP에 생일 필드를 추가해야 한다.

GROUP에 가변길이 문자열을 많은 추가하는 것은

SQL 성능(속도)상 추천되지 않는다.

다음과 같이 하위쿼리(SubQuery)를 이용하는 것이 바람직하다.

SELECT DS.EMP_NO, E.FIRST_NAME, E.LAST_NAME, PATH
  FROM (
        SELECT DE.EMP_NO, GROUP_CONCAT(D.DEPT_NAME SEPARATOR ' > ' ) PATH, COUNT(*) CNT
          FROM DEPT_EMP DE
          INNER JOIN DEPARTMENTS D ON D.DEPT_NO=DE.DEPT_NO
        GROUP BY DE.EMP_NO
        HAVING COUNT(*)>1
) DS
 INNER JOIN EMPLOYEES E ON E.EMP_NO=DS.EMP_NO
 ORDER BY CNT DESC;

부서는 조인을 이용하지만

개인정보는 하위쿼리를 사용하는 것이 좋다.

GROUP에 사용된 다는 것은 인덱스가 필요하다는 의미로

좀 더 복잡한 처리가 필요한 것도 있지만,

단순하게 데이터 개수의 의미도 있다.

조인을 사용할 경우

직원 수가 약 30만(300,024)이고,

부서 할당 정보가 약 33만 (331,603)이니

30만 * 33만의 처리가 발생한다.

하위 쿼리로 실행하면,

다음 SQL에서 알수 있듯이

처리 개수가 3만(31,579)개로 줄어든다.

부서 이동이 발생한 직원들만 처리하기 때문이며,

당연이 조인시 발생하는 처리 수도 줄어 든다.

SELECT COUNT(*)
  FROM (
        SELECT DE.EMP_NO, GROUP_CONCAT(D.DEPT_NAME SEPARATOR ' > ' ) PATH, COUNT(*) CNT
          FROM DEPT_EMP DE
          INNER JOIN DEPARTMENTS D ON D.DEPT_NO=DE.DEPT_NO
         GROUP BY DE.EMP_NO
         HAVING COUNT(*)>1
) DS

제대로 완성 시키려면,

현재 근무하는 직원만 출력해야 한다.

현재 근무하는 직원은

부서 할당(DEPT_EMP) 테이블에

근무 종료(TO_DATE)가 9999-01-01인 직원들이므로

다음과 같이 두개의 부서 할당 테이블이 필요하다.

하나는 현재 근무 중인 직원을 가져오는 테이블(DEPT_EMP DE1),

나머지는 부서 이동 정보를 가져오는 테이블(DEPT_EMP DE2)이다.

SELECT DS.EMP_NO, E.FIRST_NAME, E.LAST_NAME, PATH
  FROM (
        SELECT DE1.EMP_NO, GROUP_CONCAT(D.DEPT_NAME SEPARATOR ' > ') PATH, COUNT(*) CNT
          FROM DEPT_EMP DE1
         INNER JOIN DEPT_EMP DE2 ON DE2.EMP_NO=DE1.EMP_NO
         INNER JOIN DEPARTMENTS D ON D.DEPT_NO=DE2.DEPT_NO
         WHERE DE1.TO_DATE='9999-01-01'
         GROUP BY DE1.EMP_NO
        HAVING COUNT(*)>1
) DS
 INNER JOIN EMPLOYEES E ON E.EMP_NO=DS.EMP_NO
 ORDER BY CNT DESC;

부서 테이블과의 조인은(D.DEPT_NO=DE2.DEPT_NO)

부서 이동 정보를 가져오는 테이블(DEPT_EMP DE2)이다.