9-9. WITH | Notion

9-9-1. WITH

인라인 뷰와 같이 단독 실행 가능한 SQL을 SELECT 절 전에 WITH 절로 정의해서 사용할 수 있다. WITH 절은 CTE(Common Table Expression) 절로 부르기도 한다. WITH 절로 선언된 데이터 집합은 같은 SQL 내에서 테이블처럼 사용할 수 있다. 인라인 뷰와 다르게 WITH 절의 데이터 집합은 같은 SQL에서 여러 번 반복해서 사용할 수 있다.

-- [SQL-9-9-1-a]
-- 2022년 12월에 가장 주문이 많은 회원 Top-3
SELECT  T1.MemberId
        ,MAX(T1.NickNm) NickNm ,COUNT(*) OrdCnt
FROM    startdb.Member T1
        INNER JOIN startdb.Ord T2
            ON (T2.MemberId = T1.MemberId)
WHERE   T2.OrdDtm >= STR_TO_DATE('20221201','%Y%m%d')
AND     T2.OrdDtm <  STR_TO_DATE('20230101','%Y%m%d')
GROUP BY T1.MemberId
ORDER BY OrdCnt DESC
LIMIT 3;

-- [SQL-9-9-1-b]
-- 위 결과를 WITH절로 정의해서 조회.
WITH W1 AS(
SELECT  T1.MemberId
        ,MAX(T1.NickNm) NickNm ,COUNT(*) OrdCnt
FROM    startdb.Member T1
        INNER JOIN startdb.Ord T2
            ON (T2.MemberId = T1.MemberId)
WHERE   T2.OrdDtm >= STR_TO_DATE('20221201','%Y%m%d')
AND     T2.OrdDtm <  STR_TO_DATE('20230101','%Y%m%d')
GROUP BY T1.MemberId
ORDER BY OrdCnt DESC
LIMIT 3
)
SELECT  *
FROM    W1;

Untitled

아래는 WITH 절을 사용해 복잡한 SQL을 단계적으로 작성하는 예제다. WITH 절을 사용해 2022년 12월에 주문 건수가 가장 많은 회원 Top-3를 처리하고, WITH절로 찾은 회원의 2023년 1월 아이템별 주문수량을 조회한 SQL이다. 간단히 말하면 2022년 12월에 주문이 많았던 회원이 2023년 1월에는 어떤 상품을 가장 많이 주문했는지 살펴보는 것이다.

-- [SQL-9-9-1-c]
-- 2022년 12월 Top-3 회원이 2023년 1월에 가장 많이 주문한 상품
WITH W1 AS (
        SELECT  A.MemberId ,MAX(A.NickNm) NickNm ,COUNT(*) OrdCnt
        FROM    startdb.Member A
                INNER JOIN startdb.Ord B
                    ON (B.MemberId = A.MemberId)
        WHERE   B.OrdDtm >= STR_TO_DATE('20221201','%Y%m%d')
        AND     B.OrdDtm <  STR_TO_DATE('20230101','%Y%m%d')
        GROUP BY A.MemberId
        ORDER BY OrdCnt DESC
        LIMIT 3
)
SELECT  T4.ItemId ,MAX(T4.ItemNm) ItemNm ,SUM(T3.OrdQty) OrdQty
FROM    W1 T1
        INNER JOIN startdb.Ord T2 ON (T2.MemberId = T1.MemberId) 
        INNER JOIN startdb.OrdDet T3 ON (T3.OrdNo = T2.OrdNo)
        INNER JOIN startdb.Item T4 ON (T4.ItemId = T3.ItemId)
WHERE   T2.OrdDtm >= STR_TO_DATE('20230101','%Y%m%d')
AND     T2.OrdDtm <  STR_TO_DATE('20230201','%Y%m%d')
GROUP BY T4.ItemId
ORDER BY OrdQty DESC
LIMIT 1;

9-9-2. WITH 절 여러 개 사용하기

WITH 절은 여러 개 사용할 수 있다. 아래는 WITH절로 W1과 W2를 만들어 사용하는 SQL이다. 두 번째 WITH 절을 선언할 때는 WITH 없이 콤마로만 바로 이어서 선언하는 것에 주의하자. 위에서 선언된 WITH 블록(W1)은 바로 아래의 WITH 블록(W2)에서 사용할 수 있다. 그리고 메인 쿼리에서는 W1과 W2를 모두 사용할 수 있다. 아래 SQL의 내용을 스스로 해석해보기 바란다. SQL 실력 향상에 도움이 된다.

-- [SQL-9-9-2-a]
WITH W1 AS (
        SELECT  B.ItemId ,SUM(B.OrdQty) OrdQty
        FROM    startdb.Ord A
                INNER JOIN startdb.OrdDet B ON (B.OrdNo = A.OrdNo)
        WHERE   A.OrdDtm >= STR_TO_DATE('20210101','%Y%m%d')
        AND     A.OrdDtm <  STR_TO_DATE('20220101','%Y%m%d')
        GROUP BY B.ItemId LIMIT 3
)
,W2 AS (
        SELECT  A.MemberId, SUM(B.OrdQty) OrdQty
        FROM    startdb.Ord A
                INNER JOIN startdb.OrdDet B ON (B.OrdNo = A.OrdNo)
        WHERE   A.OrdDtm >= STR_TO_DATE('20210101','%Y%m%d')
        AND     A.OrdDtm <  STR_TO_DATE('20220101','%Y%m%d')
        AND     EXISTS(
                    SELECT * FROM W1 X WHERE X.ItemId = B.ItemId)
        GROUP BY A.MemberId
        ORDER BY OrdQty DESC LIMIT 3
        )
SELECT  T2.MemberID ,T2.NickNm ,T1.OrdQty
FROM    W2 T1
        INNER JOIN startdb.Member T2
            ON (T2.MemberId = T1.MemberId);

Untitled

WITH 절 안에서 인라인 뷰와 서브쿼리를 얼마든지 사용할 수 있다. WITH 로 선언된 블록을 메인쿼리에서 인라인 뷰나 서브쿼리에서 사용할 수도 있다. 이와 같은 확장성은 SQL 한 문장을 이용해 원하는 데이터 집합 무엇이든 추출할 수 있게 해준다. 하지만 인라인 뷰와 WITH 절을 너무 남발하면 필요 이상으로 SQL이 복잡해지고 성능에도 악영향이 생길 가능성이 있다. 가능하다면 WITH 절이나 인라인 뷰 없이 SQL 개발을 생각하고, 불가피하게 WITH 절이나 인라인 뷰를 사용해야만 한다거나, 이를 사용하는 것이 SQL을 좀 더 간단하게 해줄 때만 선택적으로 사용하기 바란다.