02 | WITH 절

개선된 서브 쿼리

6장에서 여러 형태의 서브 쿼리를 학습했었는데 서브 쿼리에는 한 가지 제약 사항이있다. 예를 들어, kor_loan_status 테이블에서 연도별 최종월 기준 가장 대출이 많은 도시와 잔액을 구해야 한다고 해보자. 간단히 쿼리를 작성할 수 있을 것 같지만 생각 외로 복잡한 측면이 있으므로 앞에서 배운 “분할해서 정복하라” 기법을 사용해 보자.

ⓐ 연도별 최종: 2011년의 최종년도는 12월이지만 2013년은 11월이므로 연도별 최종월을 알아야 한다.
구현 방안: 그룹 쿼리로 연도별로 가장 큰 월을 구한다. → MAX(period)

ⓑ 연도별 최종월을 대상으로 대출잔액이 가장 큰 금액을 추출해야 한다.
구현 방안: ⓐ와 조인을 해서 연도별로 가장 큰 잔액을 구한다. → MAX(loan_jan_amt)

ⓒ 월별, 지역별 대출잔액과 ⓑ 결과를 비교해 금액이 같은 건을 추출한다.
구현 방안: ⓑ와 조인을 해서 두 금액이 같은 건을 구한다.

위 시나리오대로 작성한 쿼리는 다음과 같다.

입력

    SELECT b2.*
    FROM ( SELECT period, region, sum(loan_jan_amt) jan_amt
             FROM kor_loan_status
             GROUP BY period, region
          ) b2,
          ( SELECT b.period,  MAX(b.jan_amt) max_jan_amt
             FROM ( SELECT period, region, sum(loan_jan_amt) jan_amt
                      FROM kor_loan_status
                     GROUP BY period, region
                  ) b,ⓑ
                  ( SELECT MAX(PERIOD) max_monthⓒ
                      FROM kor_loan_statusⓐ
                     GROUP BY SUBSTR(PERIOD, 1, 4)
                  ) a
             WHERE b.period = a.max_month
             GROUP BY b.period
          ) c
     WHERE b2.period = c.period
       AND b2.jan_amt = c.max_jan_amt
     ORDER BY 1;

결과

    PERIOD   REGION     JAN_AMT
    -------- ---------- ----------
    201112   서울       204275.7
    201212   서울       203344.9
    201311   서울       205644.3

생각보다는 쿼리가 좀 복잡해졌는데, 자세히 보면 색으로 표시된 부분(연도, 지역별 잔액총액)은 같은 구문이 두 번 사용되었음을 알 수 있다. 동일한 구문을 한 번만 사용할 수 있다면 쿼리가 훨씬 간단해질텐데, 좋은 방법이 없을까? 기존 서브 쿼리로는 해결할 방법이 없지만 WITH 절을 사용하면 가능하다. WITH 구문의 형식은 다음과 같다.

    WITH 별칭1 AS (SELECT 문),
         별칭2 AS (SELECT 문)
    ...
    SELECT
    FROM 별칭1, 별칭2 ...

WITH이 맨 앞에 오고 별칭을 앞에 명시한다는 점만 제외하면 기본 형태는 일반 서브 쿼리와 별반 다르지 않다. 하지만 WITH 절은, 별칭으로 사용한 SELECT 문의 FROM 절에 다른 SELECT 구문의 별칭 참조가 가능하다.하다.

먼저 위 쿼리를 그대로 WITH 구문 형태로 변경해 보자.

입력

    WITH b2 AS ( SELECT period, region, sum(loan_jan_amt) jan_amt
                   FROM kor_loan_status
                  GROUP BY period, region
               ),
         c AS ( SELECT b.period,  MAX(b.jan_amt) max_jan_amt
                  FROM ( SELECT period, region, sum(loan_jan_amt) jan_amt
                          FROM kor_loan_status
                         GROUP BY period, region
                       ) b,
                       ( SELECT MAX(PERIOD) max_month
                           FROM kor_loan_status
                          GROUP BY SUBSTR(PERIOD, 1, 4)
                       ) a
                 WHERE b.period = a.max_month
                 GROUP BY b.period
               )
    SELECT b2.*
      FROM b2, c
     WHERE b2.period = c.period
       AND b2.jan_amt = c.max_jan_amt
     ORDER BY 1;

WITH 절에서는 색으로 표시한 두 번째 SELECT 문인 b대신 바로 직전에 명시한 서브 쿼리인 b2를 참조할 수 있다.

입력

    WITH b2 AS ( SELECT period, region, sum(loan_jan_amt) jan_amt
                   FROM kor_loan_status
                  GROUP BY period, region
               ),
         c AS ( SELECT b2.period,  MAX(b2.jan_amt) max_jan_amt
                  FROM b2,
                       ( SELECT MAX(PERIOD) max_month
                           FROM kor_loan_status
                          GROUP BY SUBSTR(PERIOD, 1, 4)
                       ) a
                 WHERE b2.period = a.max_month
                 GROUP BY b.period
               )
    SELECT b2.*
      FROM b2, c
     WHERE b2.period = c.period
       AND b2.jan_amt = c.max_jan_amt
     ORDER BY 1;

결과

    PERIOD   REGION     JAN_AMT
    -------- ---------- ------------
    201112   서울       204275.7
    201212   서울       203344.9
    201311   서울       205644.3

쿼리가 훨씬 간단해지면서 동일한 결과가 조회된 것을 확인할 수 있다. 일반 서브 쿼리와 중복 구문이 필요 없는 WITH 절을 그림으로 나타내면 다음과 같다.

[일반 서브 쿼리] / [WITH 절]