inline View와 with문 예시
학생들의 시험점수가 담긴 테이블 ted_Score가 있다고 하자.
앞으로 할 일은 kor, eng, math 총 3개의 과목의 평균과 평균에 따른 등급을 메기는 일을 할 것이다.
-----------------------------------------------------------------------------------------------------------------------------------
#일반적인 쿼리 작성
select student_no as 학생번호
, (nvl(kor,0) + nvl(eng,0) + nvl(math,0))/3 as 평균
, case when (nvl(kor,0) + nvl(eng,0) + nvl(math,0))/3 >= 90 then 'A'
when (nvl(kor,0) + nvl(eng,0) + nvl(math,0))/3 >= 80 then 'B'
else 'C' end as 등급
from ted_Score
where year_mon = '2017/07' ;
#일반적인 쿼리 작성의 문제점:
만약 ted_score table에 시험점수 칼럼 값들이 변경되거나 or 과목들이 추가되거나 or 삭제된다고 가정해보자
kor/ math/ eng에 1~2개 과목이 추가되면 그려려니 하지만, 만약에 4개의 과목이 추가된다면
아래처럼 작성해주면 되는데, 겨우 과목 4개만 추가되는 것인데도 수정해야 할 곳이 많아진다.
-----------------------------------------------------------------------------------------------------------------------------------
select student_no as 학생번호
, (nvl(kor,0) + nvl(eng,0) + nvl(math,0)) + 과목1 + 과목2 + 과목3+ 과목4 /3 as 평균
, case when (nvl(kor,0) + nvl(eng,0) + nvl(math,0))+ 과목1 + 과목2 + 과목3+ 과목4 )/ 3 >= 90 then 'A'
when (nvl(kor,0) + nvl(eng,0) + nvl(math,0))+ 과목1 + 과목2 + 과목3+ 과목4 )/3 >= 80 then 'B'
else 'C' end as 등급
from ted_Score
where year_mon = '2017/07' ;
만약, 그럴 일은 없겠지만 예를들어 과목 100개가 추가된다면 2 line, 3 line, 4 line, 총3줄이나 수정해줘야 한다.
100*3 = 300번이나 힘들게 작성해야 하는것이다. 이럴 때, inline View를 써주게 되면 매우 간편해진다.
-----------------------------------------------------------------------------------------------------------------------------------
#인라인뷰(Inline View) 작성
select a.total/3 as 평균
, case when round(a.total/3,2) >= 90 then 'A'
when round(a.total/3,2) >= 80 then 'B'
else 'C' end as 등급
from (select nvl(kor,0) + nvl(eng,0) + nvl(math,0) as total
from ted_Score
where year_mon = '2017/07') a --inline View이름 지정
;
이때 만약에 과목이 추가되거나 수정되어도 from절에 쓴 인라인뷰의 total부분만 수정해주면 되니까 되게 간편해진다.
100개과목이 추가된다고 하면 inlin View부분에만 100개를 추가해주면
line1 , line 2, line3 부분은 total로 지정되어 있으니까 알아서 수정되어 편리하다.
---------------------------------------------------------------------------------------------------------------------------------
#with문 사용법
with 별칭 as (서브쿼리)
메인쿼리 ;
with문을 사용하기 앞서 간단한 특징을 정리하면 아래와 같다.
1. with구문내의 서브쿼리가 여러번 사용될때 유용하다.
2. 서브쿼리 블럭에 이름을 지정할 수 있도록 해준다.
3. 오라클 옵티마이저는 쿼리를 인라인뷰나 임시 테이블로 여긴다.
4. with문에서 사용한 서브쿼리의 칼럼을 다른 with문에서 참조가 가능하다. => Inlin View와 다른 장점
그럼 위의 ted_score테이블에서 평균과 그에따른 등급을 출력한는 쿼리를 이번에는 with문을 사용해보자
#with문 작성
with total as --total은 with문에 사용한 서브쿼리의 alias이다.
(select student_no
, nvl(kor,0) + nvl(math,0) + nvl(eng,0) as total
from ted_score
where year_mon = '2017/07'
)
select b.student_no as 학생번호
, round(b.total/3,2) as 평균
, case when round(b.total/3,2) >= 90 then 'A'
when round(b.total/3,2) >= 80 then 'B'
when round(b.total/3,2) >= 70 then 'C'
else 'F' end as 등급
from total b --with문의 서브쿼리명 써주기
;
#총정리1
inline View와 with문 둘다 하나의 칼럼을 만들어놓고 select절에 편리하게 이용할 수 있는 장점이 있다.
그런데 inlin View는 from절에 2개의 서브쿼리를 아래처럼 작성한다고 해보자.
select
from (select socre/3 as avg from table1) a
, (select a.avg + 100 from table1) b
이렇게 서브쿼리 a에서 작성한 칼럼 avg를 서브쿼리 b에서 막 쓸 수가 없다.
반면, with문에서는 아래처럼 가능하다.
with total_1 as
(select student_no
, nvl(kor,0) + nvl(math,0) + nvl(eng,0) as total
from ted_score
where year_mon = '2017/07'
) ,
total_2 as
(Select student_no
, total - 20 as total_20
from total_1) --이런식으로 total_1 서브쿼리를 이용해서 total_2 with문을 만든다.
select a.student_no
, a.total
, b.total_20
from total_1 a
, total_2 b
where a.student_no = b.student_no
;
#총정리 2
WITH절에 정의된 내용을 한번만 사용한다면 서브쿼리를 사용하는것과 크게 성능차이가 나지 않는다. 그러나 WITH문의 가장 큰장점은 한번 WITH절의 내용을 한번에 올려놓으면 계속 사용할 수 있다는 점이다. WITH절에 구문을 여러번 참조하는 쿼리를 만들어야 할때 효율이 좋다.