Search

9-2. INLINE VIEW와 JOIN

9-2-1. INLINE VIEW와 JOIN

인라인 뷰에서 데이터를 GROUP BY 처리한 후에 조인을 할 수도 있다. 아래 SQL은 Ord 테이블을 인라인 뷰(T2)에서 ShopId로 GROUP BY 한 후에 조인을 하고 있다. 이와 같은 패턴은 조인을 조금 더 이해하기 쉽게 만들어주기도 한다.
-- [SQL-9-2-1-a] 인라인 뷰에서 GROUP BY 후 조인 처리 SELECT T1.ShopId ,T1.ShopNm ShopNm ,IFNULL(T2.OrdAmt,0) OrdAmt FROM startdb.Shop T1 LEFT OUTER JOIN ( SELECT A.ShopID ,SUM(A.OrdAmt) OrdAmt FROM startdb.Ord A WHERE A.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d') AND A.OrdDtm < STR_TO_DATE('20220201','%Y%m%d') GROUP BY A.ShopId ) T2 ON (T2.ShopId = T1.ShopId) WHERE T1.ShopStartYmd = '20180327' AND T1.ShopOperTp = 'FLAG' ORDER BY T1.ShopID; ShopId ShopNm OrdAmt ------ ------------ --------- S014 Columbus-1st 68500.000 S031 Austin-2nd 37500.000
SQL
복사

9-2-2. 1:M 조인 후 1쪽 Key로 GROUP BY 패턴

2018년 3월 27일에 개점한 매장의 매장별 2019년 10월 주문금액을 구하려고 한다. 아래와 같이 조인 후 GROUP BY를 처리해 원하는 결과를 구할 수 있다.
-- [SQL-9-2-2-a] 1:M(Shop:Ord) 조인 후 GROUP BY -- 20180327에 개점한 매장의 2019년 10월 주문금액 SELECT T1.ShopId ,MAX(T1.ShopNm) ShopNm ,IFNULL(SUM(T2.OrdAmt),0) OrdAmt FROM startdb.Shop T1 LEFT OUTER JOIN startdb.Ord T2 ON (T2.ShopID = T1.ShopId AND T2.OrdDtm >= STR_TO_DATE('20191001','%Y%m%d') AND T2.OrdDtm < STR_TO_DATE('20191101','%Y%m%d')) WHERE T1.ShopStartYmd = '20180327' GROUP BY T1.ShopID ORDER BY T1.ShopID; ShopId ShopNm OrdAmt ------ ------------- --------- S014 Columbus-1st 23000.000 S028 San Diego-2nd 8000.000 S031 Austin-2nd 0.000
SQL
복사
위 SQL은 아래 그림과 같은 과정으로 최종 데이터 집합이 만들어진다. Shop과 Ord는 1:M 관계다. 그러므로 Shop은 M쪽인 Ord만큼 데이터가 늘어났다가, GROUP BY로 인해 다시 Shop쪽 만큼 데이터가 줄어들게 된다.
이와 같은 SQL 패턴을 “1:M 조인 후 1쪽 Key로 GROUP BY” 패턴이라고 부르도록 하자.

9-2-3. 인라인 뷰를 활용한 1:1 조인 패턴

“1:M 조인 후 1쪽 Key로 GROUP BY 패턴”은 “인라인 뷰를 활용해 1:1 조인”으로 변경해서 처리할 수도 이다. 위에서 살펴본 SQL은 아래와 같은 패턴으로 변경해 볼 수 있다.
-- [SQL-9-2-3-a] 인라인 뷰로 1:1 조인 -- Ord를 인라인 뷰에서 GROUP BY 처리후 조인하는 SQL -- Shop과 인라인 뷰는 1:1로 조인이 이루어진다. SELECT T1.ShopId ,T1.ShopNm ShopNm ,IFNULL(T2.OrdAmt,0) OrdAmt FROM startdb.Shop T1 LEFT OUTER JOIN ( SELECT A.ShopID ,SUM(A.OrdAmt) OrdAmt FROM startdb.Ord A WHERE A.OrdDtm >= STR_TO_DATE('20191001','%Y%m%d') AND A.OrdDtm < STR_TO_DATE('20191101','%Y%m%d') GROUP BY A.ShopId ) T2 ON (T2.ShopId = T1.ShopId) WHERE T1.ShopStartYmd = '20180327' ORDER BY T1.ShopID;
SQL
복사
인라인 뷰 T2에서는 Ord의 2019년 10월 데이터만 조회해 ShopId로 GROUP BY 처리했다. GROUP BY 처리된 데이터 집합의 Key(레코드를 식별할 수 있는 속성)는 GROUP BY 컬럼이다. 다시 말해 인라인 뷰(T2)의 Key는 ShopId다. 조인에 참여하는 Shop 테이블의 Key 역시 ShopId다. 그러므로 Shop과 인라인 뷰(T2)는 Key가 같으므로 1:1로 조인이 된다.
위 SQL로 데이터 집합이 처리되는 과정을 표현해보면 다음과 같다.
원래의 SQL(1:M 조인 후 1쪽 Key로 GROUP BY 패턴)을 1:1 조인(인라인 뷰를 활용한 1:1 조인 패턴)이 되도록 어떻게 변경했는지 키포인트를 정리해보면 다음과 같다.
M쪽 테이블을 인라인 뷰에서 처리한다.
이때, 1쪽의 조인 조건 컬럼에 맞쳐 GROUP BY 처리해야 한다.
인라인 뷰와 원래의 1쪽 테이블과 조인 처리한다.
원래 SQL의 조인 후 GROUP BY가 필요하지 않다면 제거한다.
GROUP BY는 상황에 따라 유지해야 할 수도 있고 제거해야 할 수도 있다.
조인 후 결과를 보고 적절하게 판단해보기 바란다.
위의 설명과 아래 그림을 같이 살펴보기 바란다.
1:M 관계에서 조인은 1의 데이터가 M 건으로 늘어날 수 있다. 하지만 1:1 관계에서 조인은 데이터가 늘어나지 않는다. 그러므로 “인라인 뷰를 활용한 1:1 조인 패턴”은 조인 후 데이터가 늘어나지 않아 조인이 조금 더 쉽게 느껴질 수 있다.
이 패턴에서 주의할 점은 인라인 뷰에서 GROUP BY 하는 컬럼의 선택과 조인 후 다시 GROUP BY가 필요한지를 판단하는 것이다. 아래 SQL을 살펴보자. 아래는 2018년 3월에 오픈한 매장의 2019년 10월 주문을 매장운영유형(ShopOperTp)별로 집계하는 SQL이다.
-- [SQL-9-2-3-b] 1:M 조인 후 ShopOperTp로 GROUP BY SELECT T1.ShopOperTp ,IFNULL(SUM(T2.OrdAmt),0) SUM_OrdAmt FROM startdb.Shop T1 LEFT OUTER JOIN startdb.Ord T2 ON (T2.ShopId = T1.ShopId AND T2.OrdDtm >= STR_TO_DATE('20191001','%Y%m%d') AND T2.OrdDtm < STR_TO_DATE('20191101','%Y%m%d') ) WHERE T1.ShopStartYmd LIKE '201803%' -- > 2018년 3월에 오픈한 모든 매장 GROUP BY T1.ShopOperTp ORDER BY T1.ShopOperTp; ShopOperTp SUM_OrdAmt ---------- ---------- DIST 8000.000 DRCT 8000.000 FLAG 145500.000
SQL
복사
위 SQL을 “인라인 뷰를 활용한 1:1 조인 패턴”으로 변경하려면,
인라인 뷰에서 Ord의 GROUP BY 컬럼은,
조인 조건 컬럼이면서 Shop쪽의 Key인 ShopId가 된다.
인라인 뷰와 조인 후 GROUP BY 처리가 추가로 필요하다.
원래 SQL의 GROUP BY가 1쪽의 Key인 ShopId가 아닌, ShopOperTp이므로 반드시 GROUP BY 처리가 유지되어야 한다.
GROUP BY 처리를 할 때, Ord의 OrdAmt는 인라인 뷰에서 SUM 처리되었지만, 한 번 더 SUM 처리를 해야 한다.
위 내용에 주의해서 아래와 같이 SQL을 변형할 수 있다.
-- [SQL-9-2-3-c] 인라인 뷰를 활용한 1:1 조인 후 ShopOperTp로 GROUP BY -- 조인 컬럼인 ShopId로 GROUP BY 하는 것에 주의 필요 -- 조인 후 ShopOperTp로 GROUP BY를 반드시 해야 한다. SELECT T1.ShopOperTp ,IFNULL(SUM(T2.OrdAmt),0) SUM_OrdAmt FROM startdb.Shop T1 LEFT OUTER JOIN ( SELECT A.ShopID ,SUM(A.OrdAmt) OrdAmt FROM startdb.Ord A WHERE A.OrdDtm >= STR_TO_DATE('20191001','%Y%m%d') AND A.OrdDtm < STR_TO_DATE('20191101','%Y%m%d') GROUP BY A.ShopId ) T2 ON (T2.ShopId = T1.ShopId) WHERE T1.ShopStartYmd LIKE '201803%' -- > 2018년 3월에 오픈한 모든 매장 GROUP BY T1.ShopOperTp ORDER BY T1.ShopOperTp; ShopOperTp SUM_OrdAmt ---------- ---------- DIST 8000.000 DRCT 8000.000 FLAG 145500.000
SQL
복사
데이터 분석에 있어 매우 중요하고 자주 사용하는 패턴이므로 잘 익혀 놓기 바란다.

BOOSTER QUIZ

BOOSTER QUIZ ERD

BOOSTER QUIZ 9-2-1

아래 SQL을 인라인 뷰를 활용한 1:1 조인으로 변경하시오.
-- 2019년 3월 23일 가입한 회원의 2021년 주문 건수와 주문금액을 조회 -- 최종 결과는 MemberGd별 GROUP BY 처리 SELECT T1.MemberGd ,COUNT(*) OrdCnt ,SUM(T2.OrdAmt) OrdAmt FROM startdb.Member T1 INNER JOIN startdb.Ord T2 ON (T2.MemberId = T1.MemberId) WHERE T1.JoinDtm = STR_TO_DATE('20190323','%Y%m%d') AND T2.OrdDtm >= STR_TO_DATE('20210101','%Y%m%d') AND T2.OrdDtm < STR_TO_DATE('20220101','%Y%m%d') GROUP BY T1.MemberGd; MemberGd OrdCnt OrdAmt -------- ------ --------- GOLD 18 77500.000 PLAT 2 7000.000
SQL
복사