동일테이블을 JOIN 통한 데이터 추출

 

 

 

1. 같은테이블 JOIN 통한 데이터 추출 테크닉

HR 로 접속해서 기본 스키마 테이블 EMPLOYEES 를 이용해서 같은 테이블간에
JOIN을 통한 테크닉을 알아보자. 역시 INLINE을쓰면 쉽게 해결된다.

예들들어 현재 EMPLOYEES 테이블에는 다음과같은 데이터들이있다.

 

 

 

우리가 만일 여기서 개인별의 직종(JOB_ID)별로 평균 급여를 알고 싶다면 어찌해야할까?

즉 이름     직종   직종별평균급여
   ---------------------------
   아무개   마케팅    2000

 

이런식으로 데이터를 추출하고 싶다는 것이다.

GROUP 함수를 이용하면 쉽게 해결될것같아 보인다.

 

SELECT FIRST_NAME , AVG(SALARY)
FROM EMPLOYEE
GROUP BY JOB_ID;

 

그러나 이렇게 해서는 오류가난다.

물론 직종별로 평균급여를 보여주는것만으로는 다음처럼 쉽게 가능하다.

 

SELECT JOB_ID , AVG(SALARY)
FROM EMPLOYEE
GROUP BY JOB_ID;

 

개인별로는 그것을 나타낼 방도가 없다.

이럴때는 같은테이블에서 조인을 활용한다. 다름 쿼리문을 참고해보자

SELECT
NAME, B.JOB_ID , B.AVGSAL

FROM
(
SELECT FIRST_NAME ||' ' || LAST_NAME AS "NAME",
JOB_ID,
HIRE_DATE, SALARY
FROM EMPLOYEES) A,

(
SELECT JOB_ID,
AVG(SALARY) AS "AVGSAL"
FROM EMPLOYEES
GROUP BY JOB_ID) B
WHERE A.JOB_ID=B.JOB_ID

 

(위에서는 FIRST_NAME 와 LAST_NAME을 둘다 보여주기위해 '||'연산자로 이름을 묶어서 출력해줌)

INLINE을 활용해서 NAME을 보여주는 table(A로 앨리어싱), AVG(SAL)을 출력해주는 table(B로 앨리어싱)
로 나눈것처럼 생각한후 WHERE절에서 JOB_ID를 이용해서 JOIN해 버린다.

이렇게 하면 해결할수 있다.

<참고> 참고로 이런식으로 활용하면 특정수치에 대한 데이터 추출도 가능한데 예를들어 직원들이 자신의 직종별 평균연봉과

자신의 연봉차이가 더 높은 사람을 구한다고 생각하면 다름과 같이 WHERE 쿼리문을 추가하면된다.

SELECT NAME, B.JOB_ID , A.HIRE_DATE, A.SALARY , B.AVGSAL , A.SALARY-B.AVGSAL AS "DIFSAL"
FROM
(
SELECT FIRST_NAME ||' ' || LAST_NAME AS "NAME",
JOB_ID,
HIRE_DATE, SALARY
FROM EMPLOYEES) A,

(
SELECT JOB_ID,
AVG(SALARY) AS "AVGSAL"
FROM EMPLOYEES
GROUP BY JOB_ID) B
WHERE A.JOB_ID=B.JOB_ID AND A.SALARY-B.AVGSAL > 0;

 

 

 

 

<참고>

1. RANGE INTERVAL BETWEEN  A AND B

최근 몇년간의 통계를 낼때 많이 쓰는 구문으로
만일 위의 EMPLOYEES 에서 입사일 전후로 1년간의 연봉 평균 데이터를 추출하기위해서
는 다음처럼하면된다.

AVG(SALARY) OVER (ORDER BY HIRE_DATE RANGE BETWEEN INTERVAL '1' YEAR PRECEDING AND
             INTERVAL '1' YEARYEAR FOLLOWING)