9. 연습(3)
- 샘플 데이터 베이스 개요
- 앞서 부서별로 직원 수를 구하는 문제를 3가지 방법으로 살펴 보았다.
- 조인(Join) 4번, 서브쿼리(SubQuery) 1번과 4번 문제로
- 정리하는 의미에서 부서 이동 문제를 이용하여 다음과 같이 정리하였다.
1. 그룹(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;
2. 이 문제는 좀 더 많은 설명과 구현이 필요한 SQL문이다. 먼저, 다음 그림과 같이 실행 결과에서 직원의 이름과 부서 명이 필요하다.
NOTE : 아래의 설명을 읽는 것도 좋겠지만 위와 같이 출력되게 하는 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;
NOTE : 직원의 이름(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;
NOTE : 부서는 조인을 이용하지만 개인정보는 서브쿼리를 사용하는 것이 좋다.
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
NOTE : 제대로 완성 시키려면, 현재 근무하는 직원만 출력해야 한다.
현재 근무하는 직원은 부서 할당(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;
NOTE : 부서 테이블과의 조인은 D.DEPT_NO=DE2.DEPT_NO
부서 이동 정보를 가져오는 테이블은 DEPT_EMP DE2 이다.
출처 : SW 개발이 좋은 사람
'Programming > SQL' 카테고리의 다른 글
| 대용량 테이블 삭제 방법 (0) | 2017.05.02 |
|---|---|
| 8. 연습(2) (0) | 2017.03.11 |
| 7. 연습(1) (0) | 2017.03.11 |
| 6. 실습(4) - SubQuery (0) | 2017.02.28 |
| 5. 실습(3) - Join (0) | 2017.02.28 |
