오라클 SQL 문법 공부18- SELF JOIN, 분석함수(RANK() OVER)

서론

이번에는 자기 자신 테이블을 두개 JOIN을 하여 처리하는 SELF JOIN에 대해서 알아보고자 한다. 보통 한 테이블에 기본키 외에 기본키에 대한 상위 관리자등의 리스트가 있거나 테이블 내에서 얻고자 하는 정보가 있을경우 사용된다. 

본론

SELF JOIN

--조인하고자 하는 대상이 보인 테이블에 존재하는 경우의 조인이다.
--단 한번의 테이블 ACCESS로는 원하는 결과값을 가져올 수 없을 때 사용한다.
--OUTER 조인을 자주 사용함

SELECT E1.ENAME AS 사원이름, E1.SAL, E2.ENAME AS 상위관리자명, E2.SAL
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.EMPNO(+);
--AND E1.SAL > E2.SAL;

SELECT D1.DNAME, D2.DNAME FROM DEPT2 D1, DEPT2 D2 WHERE D1.PDEPT = D2.DCODE;

동일한 테이블 이기 때문에 테이블 애칭으로 잘 구분을 해서 처리하는 것이 좋다. 또한 애칭으로 구분했다 하더라도 대상과 비교할 대상을 잘 구분해야지 원하는 결과값이 나올 것이다. 특히나 대상과 비교할 대상의 컬럼이 동일할 경우 대상보다 값이 적은 값들 등의 조건을 사용한다면 부호 방향에 따라 전혀 예상하지 않은 값이 나올 수도 있다.

보통 SELF JOIN에서는 아우터 조인을 사용할텐데 이유는, 만약 부서정보 테이블에 부서번호와 상위부서번호가 있다면 최상위 부서번호가 있을것이다. 때문에 최상위 부서번호에는 상위부서번호가 없어 EQUI JOIN을 사용한다면 최 상위 부서는 제외가 되고 출력될 것이기 때문이다.

RANK() OVER 분석함수

--분석함수
--RANK() OVER (ORDER BY COLUMN1, COLUMN2 DESC) 순위를 구하는 함수

--1.1 특정값의 RANK : RANK('VALUE') WITHIN GROUP (ORDER BY COL1, COL2)
SELECT RANK('송도권') WITHIN GROUP (ORDER BY NAME) "RANK"
FROM PROFESSOR;

--1.2 전체값의 RANK : RANK() OVER (ORDER BY COL1, COL2 DESC)
SELECT PROFNO, NAME, PAY, RANK() OVER (ORDER BY PAY DESC) "RANK"
FROM PROFESSOR;

--1.3 GROUP 별 RANK : RANK() OVER (PARTITION BY COL1 ORDER BY COL1 DESC)
SELECT EMPNO, ENAME, SAL, DEPTNO,
RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) "RANK"
FROM EMP; 
--RANK에 관해서 WHERE 절에 조건을 넣을 순 없고 인라인 뷰를 이용해야한다.

JOIN에 대해서는 16장부터 시작해 모두 소개한 것 같다. 이번에는 분석함수라는 특수한 함수에 대해서 알아보고자 한다. 다음 포스팅에서 몇가지 더 다루어볼 예정이다.

RANK() OVER 함수는 테이블내의 값들을 비교하여 큰 순서 혹은 작은 순서들에 대해 번호를 매기는 함수이다. 위처럼 특정값에 대해서만 지정할 수도 있고 그룹별로도 가능하다. 오라클에서는 이렇게 편한 함수를 제공해주며 다른 DBMS에서도 요즘나온 것들에 대해서는 제공을 해주는 것 같다.

SELECT NAME, EA,

RANK() OVER (ORDER BY EA DESC) "RANK";

NAME EA RANK

사과 4 1

바나나 2 2

키위 0 3

이런식으로 출력되며 랭크함수를 사용 할 건데, EA를 내림차순으로 정렬해서 차례대로 순위를 붙일 것이다 라고 생각하면 되겠다.

실습문제

--1)student, exam_01, hakjum 테이블을 이용하여 각 학생의 학점을 고루 분배했는지 확인하고자 한다.
--각 학점별 학생수를 다음과 같은 형식으로 출력하여라
--학점 학생수
--A 4
--B 12
--C 3
--D 1
SELECT SUBSTR(H.GRADE, 1, 1) AS "학점" ,
       COUNT(S.STUDNO) AS "학생수"
  FROM STUDENT S,
       EXAM_01 E,
       HAKJUM H
 WHERE S.STUDNO = E.STUDNO
   AND E.TOTAL BETWEEN H.MIN_POINT AND H.MAX_POINT
 GROUP BY SUBSTR(H.GRADE, 1, 1)
 ORDER BY "학점";

--2)emp2 테이블에서 각 사원의 취미생활이 같은 직원이 몇명인지를 사원이름, 사원번호,
--생년월일과 함께 출력하여라. 단, 본인은 제외하고 count하여라.
SELECT E1.NAME,
       E1.EMPNO,
       TO_CHAR(E1.BIRTHDAY, 'YYYYMMDD'),
       COUNT(E2.HOBBY)
  FROM EMP2 E1,
       EMP2 E2
 WHERE E1.HOBBY = E2.HOBBY(+)
   AND E1.EMPNO <> E2.EMPNO(+)
 GROUP BY E1.EMPNO,
       E1.NAME,
       TO_CHAR(E1.BIRTHDAY, 'YYYYMMDD');

--3)student 테이블에서 같은지역, 같은 성별의 친구가 몇명인지 구하고, 그학생의 담당 교수이름도 함께 출력되도록 하여라.
--단, 같은지역, 같은 셩별에 본인은 포함 될 수 없다.
SELECT S1.NAME,
       SUBSTR(S1.TEL, 1, INSTR(S1.TEL, ')') - 1),
       SUBSTR(S1.JUMIN, 7, 1),
       P.NAME,
       COUNT(S2.STUDNO)
  FROM STUDENT S1,
       STUDENT S2,
       PROFESSOR P
 WHERE SUBSTR(S1.TEL, 1, INSTR(S1.TEL, ')') - 1) = SUBSTR(S2.TEL(+), 1, INSTR(S2.TEL(+), ')') - 1)
   AND SUBSTR(S1.JUMIN, 7, 1) = SUBSTR(S2.JUMIN(+), 7, 1)
   AND S1.PROFNO = P.PROFNO(+)
   AND S1.STUDNO <> S2.STUDNO(+)
 GROUP BY S1.NAME,
       SUBSTR(S1.TEL, 1, INSTR(S1.TEL, ')') - 1),
       SUBSTR(S1.JUMIN, 7, 1),
       P.NAME;

--4)employees 테이블에서 같은해에 입사한사람 중 본인보다 salary가 높은 사람의 수와,
--본인의 같은 입사년도 내 연봉순위를 그 직원의 이름과 salary, 부서명과 함께 출력하여라
SELECT E1.FIRST_NAME,
       E1.DEPARTMENT_ID,
       E1.HIRE_DATE,
       E1.SALARY,
       COUNT(E2.EMPLOYEE_ID),
       RANK() OVER (PARTITION BY TO_CHAR(E1.HIRE_DATE, 'YYYY')
         ORDER BY E1.SALARY DESC) AS "RANK"
  FROM EMPLOYEES E1,
       EMPLOYEES E2
 WHERE TO_CHAR(E1.HIRE_DATE, 'YYYY') = TO_CHAR(E2.HIRE_DATE(+), 'YYYY')
   AND E1.SALARY < E2.SALARY(+)
 GROUP BY E1.FIRST_NAME,
       E1.DEPARTMENT_ID,
       E1.SALARY,
       E1.HIRE_DATE;

--5)EMP와 DEPT 테이블을 이용하여 각직원의 이름과 SAL, 부서명을 해당 직원의 매니저의 이름, SAL
--부서명과 함께 출력하여라. 단, 각 직원과 매니저의 소속 부서가 같은 사원에 대해서만 출력하도록 한다
SELECT E1.EMPNO AS "직원번호",
       E1.ENAME AS "직원이름",
       E1.SAL AS "직원월급",
       D.DNAME AS "직원부서",
       E1.MGR AS "직원의매니저번호",
       E2.EMPNO AS "매니저번호",
       E2.ENAME AS "매니저이름",
       E2.SAL AS "매니저월급",
       D.DNAME AS "직원부서"
  FROM EMP E1,
       EMP E2,
       DEPT D
 WHERE E1.MGR = E2.EMPNO(+)
   AND E1.DEPTNO = E2.DEPTNO
   AND E1.DEPTNO = D.DEPTNO
 ORDER BY "직원번호";

결론

SELF JOIN과 RANK() OVER 함수에 대해 알아보았다. 이제 몇가지 분석함수와 서브쿼리라는 것들을 배우면 기본적인 문법들을 공부한 것이다. 찾아보면 더 많은 문법들이 존재하겠지만 그건 필요할 때마다 찾아서 공부하면 되는 것이고 SQL문을 짜려고 했을때 어떤 함수들을 가지고 짤 수 있을 것 같다라는 감을 키우는게 공부하는 사람의 주 목적이라 생각한다.