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
복사
