9-3-1. 1:M:1 관계 조인
두 테이블의 관계 차수를 파악하고, 두 테이블을 조인하는 것은 크게 어렵지 않다. 그리고 두 테이블은 1:M 관계로 이루어진 경우가 대부분이다. 결국, 두 테이블 간에 조인은 1:M 관계일 가능성이 높다. 이제는 난이도를 조금 높여서 세 테이블의 관계 차수를 살펴보고 그에 맞게 조인을 작성해보자.
아래는 매장, 주문, 회원 테이블이다. 매장과 주문은 1:M, 주문과 회원이 M:1이다. 그러므로 세 테이블은 주문을 중심으로 1:M:1 관계가 된다.
1:M:1과 같은 관계에서는 테이블 간에 조인 컬럼만 잘 고려해서 SQL을 작성하면 된다. 아래는 2018년 3월 27일에 오픈한 매장에 대해, 플래티넘 회원의 2020년 1월 주문을 조회하는 SQL이다.
-- [SQL-9-3-1-a] 1:M:1 조인
SELECT T2.OrdNo ,T2.OrdDtm ,T2.OrdAmt
,T1.ShopId ,T1.ShopNm
,T3.MemberId ,T3.NickNm
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId)
INNER JOIN startdb.Member T3
ON (T3.MemberId = T2.MemberId)
WHERE T1.ShopStartYmd = '20180327'
AND T2.OrdDtm >= STR_TO_DATE('20200101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20200201','%Y%m%d')
AND T3.MemberGd = 'PLAT';
OrdNo OrdDtm OrdAmt ShopId ShopNm MemberId NickNm
----- ------------------- -------- ------ ------------- -------- ---------
1232 2020-01-02 13:00:00 8000.000 S028 San Diego-2nd M0298 Thunder5
1244 2020-01-05 10:00:00 8500.000 S014 Columbus-1st M0291 Star5
1285 2020-01-11 10:00:00 7500.000 S014 Columbus-1st M0291 Star5
1298 2020-01-11 11:00:00 4000.000 S014 Columbus-1st M1283 Quantum25
1315 2020-01-11 12:00:00 7500.000 S031 Austin-2nd M1297 Swift25
1352 2020-01-23 10:00:00 8500.000 S014 Columbus-1st M0291 Star5
1367 2020-01-23 11:00:00 4000.000 S014 Columbus-1st M1283 Quantum25
1387 2020-01-23 12:00:00 4000.000 S031 Austin-2nd M1297 Swift25
1414 2020-01-29 10:00:00 4000.000 S014 Columbus-1st M0291 Star5
1429 2020-01-29 11:00:00 4000.000 S014 Columbus-1st M1283 Quantum25
1449 2020-01-29 12:00:00 4000.000 S031 Austin-2nd M1297 Swift25
1486 2020-01-30 13:00:00 2500.000 S028 San Diego-2nd M0298 Thunder5
SQL
복사
위 SQL은 아래 그림과 같이, Shop은 Ord만큼 데이터가 늘어나고, Member는 Shop과 Ord의 조인 결과만큼 데이터가 늘어난게 된다. 1:M:1 조인에서 1쪽은 M만큼 데이터가 늘어날 수 있다.
위 SQL을 조금더 발전시켜, MemberId별로 주문금액 합계를 구해보자. 다음과 같이 SQL을 변경하면 된다.
-- [SQL-9-3-1-b] 1:M:1 조인후 GROUP BY
SELECT T3.MemberId ,MAX(T3.NickNm) NickNm
,SUM(T2.OrdAmt) SumOrdAmt
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId)
INNER JOIN startdb.Member T3
ON (T3.MemberId = T2.MemberId)
WHERE T1.ShopStartYmd = '20180327'
AND T2.OrdDtm >= STR_TO_DATE('20200101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20200201','%Y%m%d')
AND T3.MemberGd = 'PLAT'
GROUP BY T3.MemberId
ORDER BY T3.MemberId;
MemberId NickNm SumOrdAmt
-------- --------- ----------
M0291 Star5 28500.000
M0298 Thunder5 10500.000
M1283 Quantum25 12000.000
M1297 Swift25 15500.000
SQL
복사
1:M:1 관계를 모두 조인 후 GROUP BY 처리했다. 데이터 집합이 처리되는 과정을 개념적으로 그려보면 다음과 같다. 1인 데이터 집합은 M쪽 만큼 데이터가 늘어난 후, GROUP BY 컬럼의 값 종류만큼 줄어들게 된다.
Tip. 1:M 관계를 조인하면 1인 데이터 집합은 M쪽 만큼 데이터가 늘어난다.
이 말에는 1:M 관계에서 1쪽의 레코드 한 건이 “M 쪽의 여러 건과 조인에 성공한다면”이라는 전제가 있다.
1쪽의 레코드 한 건이 “M 쪽의 한 건하고만 조인한다면” 조인 결과는 그대로 한 건이다. 그리고 1쪽의 레코드 한 건이 “M쪽과 조인에 실패한다면” 결과에 출력되지 못한다. 그러므로 1쪽의 레코드 한 건이 M쪽의 여러 건과 조인에 성공할 때만 M쪽만큼 데이터가 늘어난다.
1:M:1 조인 후 GROUP BY 패턴은 인라인 뷰를 활용할 1:1 조인 패턴으로 변경할 수 있다. 1:M이나 M:1 어느 한쪽만 먼저 인라인 뷰에서 조인후 GROUP BY 한 후에 나머지 1과 조인 처리하면 된다. 앞에서 살펴본 SQL은 다음과 같이 변경할 수 있다. Shop과 Ord를 인라인 뷰에서 별도 조인 처리 후에 GROUP BY 처리하고 Member와 조인을 처리한다.
-- [SQL-9-3-1-c] Shop과 Ord를 조인해 GROUP BY 후 Member와 조인 처리
SELECT T1.MemberId ,T3.NickNm ,T1.SumOrdAmt
FROM (
SELECT B.MemberId
,SUM(B.OrdAmt) SumOrdAmt
FROM startdb.Shop A
INNER JOIN startdb.Ord B
ON (A.ShopId = B.ShopId)
WHERE A.ShopStartYmd = '20180327'
AND B.OrdDtm >= STR_TO_DATE('20200101','%Y%m%d')
AND B.OrdDtm < STR_TO_DATE('20200201','%Y%m%d')
GROUP BY B.MemberId
) T1
INNER JOIN startdb.Member T3
ON (T3.MemberId = T1.MemberId)
WHERE T3.MemberGd = 'PLAT'
ORDER BY T1.MemberId;
MemberId NickNm SumOrdAmt
-------- --------- ----------
M0291 Star5 28500.000
M0298 Thunder5 10500.000
M1283 Quantum25 12000.000
M1297 Swift25 15500.000
SQL
복사
1:M:1 조인 후 GROUP BY가 필요할 때 사용할 수 있는 다음과 같은 두 가지 패턴을 살펴봤다.
조인 결과가 집계 처리되면서, 조인 대상이 많을수록, 위 그림의 오른쪽과 같이 인라인 뷰를 적극 활용하는 것이 SQL 작성에 유리하다. 복잡한 SQL의 실수를 줄일 수 있으며 SQL 가독성 측면에서 좋기도 하다.
그러나, 성능적으로는 고민이 필요하다. 상황에 따라서 인라인 뷰 패턴이 성능이 좋을 수도 있고 인라인 뷰를 사용하지 않는 패턴이 좋을 수도 있다. 이는 매우 긴 이야기이므로 여기서 할 수는 없다. 이러한 점을 인지만 하고 있기 바란다. 지금은 SQL을 이용해 필요한 데이터 집합을 정확히 추출하는 것이 우선이다. 자신에게 익숙한 방법으로 정확한 결과를 얻는데 집중하기 바란다.
BOOSTER QUIZ
BOOSTER QUIZ ERD
BOOSTER QUIZ 9-3-1
아래 SQL을 Ord와 OrdDet를 별도 인라인뷰에서 조인 후 GROUP BY 한 후에 Item과 조인되는 방식으로 수정하시오.
-- 1:M:1 조인
SELECT T3.HotColdCd
,SUM(T2.OrdQty) SUM_OrdQty
FROM startdb.Ord T1
INNER JOIN startdb.OrdDet T2
ON (T2.OrdNo = T1.OrdNo)
INNER JOIN startdb.Item T3
ON (T3.ItemId = T2.ItemId)
WHERE T1.OrdDtm >= STR_TO_DATE('20220103','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20220105','%Y%m%d')
AND T3.ItemCat = 'COF'
GROUP BY T3.HotColdCd;
HotColdCd SUM_OrdQty
--------- ----------
COLD 3
HOT 1
SQL
복사
