sql중급 / with절(subauery factoring)

ㅁwith절을 썼을 때 장점

1. 대용량 데이터를 조회하는 하나의 SQL안에서 반복적으로 사용되는 쿼리문이 존재할 때 성능을 높이기 위한 방법으로 with절을 사용하면 유용함

2. 간단한 테스트를 할 때 굳이 테이블을 생성하지 않고 with절로 임시 테이블을 만들어서 테스트를 진행해 볼 수 있음.

 

회사에 데이터 분석가로 가면 접속해서 SQL을 실행하는 서버가 크게 2가지가 있음.

1. OLTP서버(OnLine Transaction Processing) : 실시간으로 발생하는 데이터

2. DW서버(Data Waredouse) : 그동안 쌓아온 과거 데이터

with절은 보통 DW서버에서 날림. OLTP서버에서 날릴경우 서버 터질 수 있음.

 

110 WITH절 사용하기 2(SUBQUERY FACTORING)

 

1. from 절의 서브쿼리 > 쿼리문의 결과가 메모리(memory)에 올라감

2. wieh절의 쿼리 > 쿼리문의 결과가 디스크(disk)에 저장

 

내가 from 절의 서브쿼리를 사용할 수도 있고 with절을 사용할 수도 있는데 2개의 SQL중 어떤 SQL을 사용할 것인가?

 - 지금 데이터가 있는 서버가 OLTP서버인가? DW서버인가?

 - from 절 안의 쿼리문의 결과 데이터가 대용량 데이터이면 with절을 사용하는게 바람직함.

 

-- 아래의 SQL은 실행되는가?
select deptno, sum(sal)
 from ( select job, sum(sal) 토탈
         from emp
         group by job ) as job_sumsal,
    (select deptno, sum(sal) 토탈
         from emp
         group by deptno
         having sum(sal) > (select avg(토탈) + 3000 
                              from job_sumsal )
     );

위의 SQL은 실행되지 않음. from 절의 서브쿼리로 할 수 없음. 이 쿼리를 실행하고 싶다면 with절을 사용해야함.

-- 수정된 쿼리
with job_sumsal as ( select job, sum(sal) 토탈
                      from emp
                      group by job ),
     deptno_sumsal as (select deptno, sum(sal) 토탈
                        from emp
                        group by deptno
                        having sum(sal) > (select avg(토탈) + 3000
                                             from job_sumsal ) )
select deptno, 토탈
 from deptno_sumsal ;