#8_140624_with

# WITH

l with 절 : 복합 query 내에서 동일한 select 문이 반복되는 경우 쓰임.

l with 절의 장점

           두번이상 반복되고 있는 쿼리가 있는 SQL의 성능을 높일 수 있다
l oracle temporary tablespace(임시저장영역) 아래의 데이터를 저장한다

문제319. 직업, 직업별 최대월급을 출력하는데 직업별 최대월급이 직업별 최대월급들에 대한 평균값보다 더 큰 것만 출력하시오

#튜닝 전

SELECT job, MAX(sal)

FROM EMP

GROUP BY job

HAVING MAX(sal) > (SELECT AVG(MAX(sal)) FROM EMP GROUP BY job);

#튜닝 후

WITH job_maxsal AS (SELECT job, MAX(sal) 최대값 FROM EMP GROUP BY job)

SELECT job, 최대값

FROM job_maxsal

WHERE 최대값 > (SELECT AVG(최대값) FROM job_maxsal);

SELECT job, MAX(sal) 최대값 FROM EMP GROUP BY job;
실행결과

문제320. 아래의 SQL을 with절로 변경하시오

SELECT d.loc, SUM(e.sal)

FROM EMP e, DEPT d

WHERE e.deptno=d.deptno

group BY d.loc

HAVING SUM(e.sal) > (SELECT AVG(SUM(e.sal))*0.2 FROM EMP e, DEPT d WHERE e.deptno=d.deptno GROUP BY d.loc);

WITH loc_sumsal AS (SELECT d.loc, SUM(e.sal) 토탈 FROM EMP e, DEPT d WHERE e.deptno=d.deptno group BY d.loc)

SELECT loc, 토탈

FROM loc_sumsal

WHERE 토탈 > (SELECT AVG(토탈)*0.2 FROM loc_sumsal);

문제321. 직업, 직업별, 인원수를 출력하는데 직업별 인원수의 평균값보다 더 큰것만 출력하시오

WITH job_avgcnt AS (SELECT job, COUNT(*) 인원수 FROM EMP GROUP BY job)

SELECT job, 인원수

FROM job_avgcnt

WHERE 인원수 > (SELECT AVG(인원수) FROM job_avgcnt);