WITH ~AS 서브쿼리문
- WITH ~ AS 절은 한번의 SELECT 구문에서 자주 사용되는 부분의 SELECT문을 가상의 VIEW로 메모리에 저장해 놓는 역할을
한다.
구문형태는 다음과 같다.
WITH
view이름 as 'SELECT... FROM 쿼리문'
SELECT FROM view이름
view는 2개 이상의 가상view를 만들수도 있다. 그럴때는 컴마(,) 로 연결한다.
WITH
view이름1 as 'SELECT... FROM 쿼리문'
view이름2 as 'SELECT... FROM 쿼리문',
SELECT ... FROM view이름1 ( SELECT... FROM view이름2 )
그럼 예제를 통해서 WITH AS절의 사용법을 익혀보자.
참고로 우린 이 예제에서 2개의 가상 VIEW를 만들것이다.
부서번호 판매량 판매일
DEPTNO SELLED SELLDAY
-----------------------------
10 5 2010-9 - 11
10 15 2010-10 - 30
. . .
. . .
20 20 2010-8 - 20
20 1 2010-9 - 1
. . .
. . .
30 10 2010-6 - 5
30 12 2010-7 - 7
30 50 2010-8 - 9
위와같이 부서당 매월 상품판매량을 입력해 놓은 테이블을 생각해보자. 이테이블은 TBL_SALES라고하자.
그럼 우리가 부서당 가장 많은 판매량을 얻고자 한다면 어찌해야할까?
STET 1> 일단 아래와같은 쿼리문을 생각해보자
SELECT TEAMNO, TO_CHAR(SELLDAY, 'YYYY-MM'),SUM(SELLED)
FROM TBL_SALES
GROUP BY TEAMNO, TO_CHAR(SELLDAY, 'YYYY-MM')
일단 이 쿼리문은 모든 부서의 매월 전체판매량을 수치로 보여준다.
그러나 우리가 워하는것은 각부서마다 최고 판매량을 알고 싶은것이다.
예를들면 10번부서는 10월에 가장 많은 판매량을 보이고 있고, 20번 부서는 5월에
30번 부서는 1월에 가장 많은 판매량보이는 등의 답을 원하는것이다.
일단 그러나 위와같은 쿼리문결과물을 이용해서 얻어야하므로이 쿼리문을
하나의 VIEW 로 생각하도록하자.
WITH AS절을 이용해서
WITH MAIN
AS
(
SELECT T.TEAMNO AS "팀번호",
TO_CHAR(T.SALESDAY, 'YYYY-MM') AS "판매월",
SUM(T.SALESYANG) AS "판매량"
FROM TBL_SALES
GROUP BY T.TEAMNO, TO_CHAR(T.SALESDAY, 'YYYY-MM')
)
위와같이 AS 이하 구문을 MAIN 이라는 VIEW 이름으로 지정되였으며
MAIN 이라는 view이름을 가진 결과물은 아래와 같은 것임을 기억하자.
<STEP2>
STEP1에서 나온 결과물은 위테이블과 같은 부서별 월별판매량이다.
이 결과물자체를 하나의 새로운 A라는 테이블로 간주해보면(정확히는 VIEW이다)
월별판매량으로 정리된 A라는 테이블을 다시 부서번호별로 GROUY BY해서 부서별 최대 판매량을 알수 있다.
이것을 다시 하나의 뷰로 생각해서 STEP1에서 했던것 처럼 처리하자. 이제 이 쿼리문은 SUB라는 이름
으로 간주한다.
SUB
AS
(
SELECT TEAMNO, MAX(SELLED)
FROM
(
SELECT TEAMNO, SUM(SELLED)
FROM TBL_SALES
GROUP BY TEAMNO, TO_CHAR(SELLDAY, 'YYYY-MM')
)
GROUP BY TEAMNO
)
<STEP3> 이제 마지막으로 WITH ~ AS구문에 맞게 처리하자. 위 STEP1과 STEP2의 쿼리문이
','(컴마)로 연결되고 이 2가지 VIEW를 이용해서 우리가 원하는 결과물을 얻기위한 SELECT구문으로 구성해보자
WITH
MAIN AS
(
SELECT T.TEAMNO AS "팀번호",
TO_CHAR(T.SELLDAY, 'YYYY-MM') AS "판매월",
SUM(T.SELLED) AS "판매량"
FROM TBL_SALES T
GROUP BY T.TEAMNO, TO_CHAR(T.SELLDAY, 'YYYY-MM')
)
, -- MAIN쿼리
SUB AS
(
SELECT S2.TEAMNO, MAX(팀별최다판매량) AS "월별최댜판매량"
FROM
(
SELECT S.TEAMNO, SUM(SELLED) AS "팀별최다판매량"
FROM TBL_SALES S
)
GROUP BY S.TEAMNO, TO_CHAR(SELLDAY, 'YYYY-MM')
) S2
GROUP BY S2.TEAMNO
) --- SUB쿼리
SELECT *
FROM MAIN
WHERE (팀번호 , 판매량) IN (SELECT TEAMNO , '월별최다판매량'
FROM SUB);
일단 MAIN TABLE 내에서 우리가 원하는 결과값을 찾아야하므로 FROM 구문에 들어가고
그 중에 (팀번호,판매량) 이므로 이를 FAIRWISE 서브쿼리를 써서 묶어 처리하면 위와같이
SELECT 구문을 구성할수 있다.
또한 같은 이름의 TEAMNO 가 많이 나오기떄문에 명시적으로 알게하기위해 앨리어싱을 추가했다.
(그런데 내가할때는 굳이 앨리어싱 추가하지 않아도 에러는 나지 않았다..)
<참고> WITH ~ AS구문말고 상관 서브쿼리를 이용할수 있다.
이것은 오라클에서 그룹함수가 1번에 한해서는 중첩이 가능하다는 사실을 이용한것이다..
다른 DB에서는 지원하지 않고 ORACLEl에서는 지원하고 있다.
중요한것은 단 1번만 중첩될수 있다.
SELECT T.TEAMNO AS "팀번호",
TO_CHAR(T.SELLDAY, 'YYYY-MM') AS "최저판매월",
SUM(T.SELLED) AS "최저판매량"
FROM TBL_SALES T
GROUP BY T.TEAMNO, TO_CHAR(T.SELLDAY, 'YYYY-MM')
HAVING SUM(T.SELLED) = (
SELECT MAX(SUM(SELLED))
FROM TBL_SALES S
WHERE S.TEAMNO = T.TEAMNO
GROUP BY S.TEAMNO, TO_CHAR(SELLDAY, 'YYYY-MM')
);