9-4-1. M:1:M 관계 조인
앞에서는 1:M:1 관계의 조인을 살펴봤다. 1:M:1은 인라인 뷰를 활용해도 되고 인라인 뷰를 활용하지 않아도 된다. 이제 소개할 M:1:M 관계는 가능하면 인라인 뷰를 활용해 처리해야 한다.
다양한 사이트에서 개발된 SQL들을 보면 M:1:M 관계를 인라인 뷰 없이 직접 조인 처리하는 실수를 많이 목격하게 된다. M:1:M 관계의 세 테이블은 일반적으로 직접 조인할 수 없다. 적절한 인라인 뷰와 GROUP BY 처리가 필요하다.
아래는 1(회원)을 중심으로 두 개의 M 테이블(주문, 이벤트응모)이 있는 M:1:M 관계다. 시스템의 업무가 많고 복잡할 수록 이와 같은 M:1:M 관계가 자주 만들어진다. 테이블 구조와 관계 자체는 정상적이다. 다만, 이러한 관계의 세 개 테이블을 조인할 때는 주의가 필요하다.
M0253 회원에 대해 회원 정보와 주문 정보, 이벤트응모(EventEntry) 정보를 각각 조회해보자.
•
M0253의 회원(Member) 정보
•
M0253 회원의 주문금액이 8,000원 이상인 2019년 11월~12월 주문(Ord) 정보
•
M0253 회원의 2019년 11월~12월 이벤트응모(EventEntry) 정보
-- [SQL-9-4-1-a]
-- 회원 정보
SELECT T1.MemberId ,T1.NickNm
FROM startdb.Member T1
WHERE T1.MemberId = 'M0253';
MemberId NickNm
-------- ------
M0253 Cloud5
-- [SQL-9-4-1-b]
-- 주문 정보(2019.10~2019.12), 주문금액이 8,000 이상만
SELECT T2.MemberId ,T2.OrdNo ,T2.OrdDtm ,T2.OrdAmt
FROM startdb.Ord T2
WHERE T2.MemberId = 'M0253'
AND T2.OrdDtm >= STR_TO_DATE('20191101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20200101','%Y%m%d')
AND T2.OrdAmt >= 8000
ORDER BY T2.OrdNo;
MemberId OrdNo OrdDtm OrdAmt
-------- ----- ------------------- ---------
M0253 284 2019-11-11 11:00:00 8500.000
M0253 730 2019-12-23 11:00:00 10000.000
M0253 970 2019-12-25 11:00:00 12500.000
-- [SQL-9-4-1-c]
-- 이벤트응모 정보(2019.10~2019.12)
SELECT T3.MemberId ,T3.EventId, T3.EntryDtm
FROM startdb.EventEntry T3
WHERE T3.MemberId = 'M0253'
AND T3.EntryDtm >= STR_TO_DATE('20191101','%Y%m%d')
AND T3.EntryDtm < STR_TO_DATE('20200101','%Y%m%d')
ORDER BY T3.EventId;
MemberId EventId EntryDtm
-------- ------- -------------------
M0253 EV0007 2019-11-07 08:07:13
M0253 EV0008 2019-12-15 06:07:13
SQL
복사
위 세 테이블의 데이터를 조인해 아래와 같이 회원의 2019년 11월~12월 주문 건수(8,000원 이상의 주문)와 같은 기간의 이벤트 응모 횟수를 보여주려고 한다.
MemberId NickNm OrdCnt EntryCnt
-------- ------ ------ --------
M0253 Cloud5 3 2
SQL
복사
위 결과를 얻기 위해 각각의 데이터를 차례대로 조인해보자. 먼저 회원과 주문을 조인한다. 여기까지는 회원이 1, 주문이 M인 1:M 관계이므로 특별한 이상은 없다. 회원 데이터 한 건이 주문 세 건과 조인해 세 건의 결과 데이터 집합이 만들어진다.
-- [SQL-9-4-1-d]
-- 회원과 주문을 조인
SELECT T1.MemberId ,T1.NickNm ,T2.OrdNo ,T2.OrdDtm ,T2.OrdAmt
FROM startdb.Member T1
LEFT OUTER JOIN startdb.Ord T2
ON (T2.MemberId = T1.MemberId
AND T2.OrdDtm >= STR_TO_DATE('20191101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20200101','%Y%m%d')
AND T2.OrdAmt >= 8000)
WHERE T1.MemberId = 'M0253'
ORDER BY T2.OrdNo;
MemberId NickNm OrdNo OrdDtm OrdAmt
-------- ------ ----- ------------------- ---------
M0253 Cloud5 284 2019-11-11 11:00:00 8500.000
M0253 Cloud5 730 2019-12-23 11:00:00 10000.000
M0253 Cloud5 970 2019-12-25 11:00:00 12500.000
SQL
복사
조인으로 얻은 결과 데이터의 Key는 OrdNo다. 회원의 Key였던 MemberId는 조인을 거쳐 세 건으로 늘어났기 때문에, 위 결과에서는 더 이상 Key 역할을 할 수 없다. M 집합의 PK였던 OrdNo가 조인 결과의 Key 속성이 된다.
이제 위 SQL에 이벤트응모(EventEntry) 조인을 추가해보자. 정상적인 조인을 위해서는 조인 조건으로 어느 한 데이터 집합의 Key(PK)를 사용해야 한다. 조인에 앞서 각 데이터 집합의 Key를 정리해보면 다음과 같다.
•
Member: MemberId가 Key(=PK)
•
Ord: OrdNo가 Key(=PK)
•
Member+Ord: M쪽의 PK인 OrdNo가 Key가 된다.
•
EventEntry: EventId+MemberId 가 Key
지금은 조인 처리된 (Member+Ord) 데이터 집합에 EventEntry를 조인하려고 한다. 그러므로 두 데이터 집합의 연결에 사용할 Key가 있어야 하는데, 적절한 Key가 없다.
조인 조건 컬럼은 조인에 참여하는 데이터 집합의 어느 한쪽의 Key 역할이어야 한다. Member와 Ord를 조인해 얻은 데이터 집합의 Key는 OrdNo지만, 이벤트응모(EventEntry)에는 OrdNo가 없으므로 이 컬럼으로 조인을 할 수 없다. 반대로 이벤트응모의 Key는 (EventId+MemberId)인데, Member와 Ord의 조인 결과에는 MemberId만 있다. 결과적으로 두 데이터 집합은 정상적인 조인을 할 수 없다. 어찌됐든 공통적으로 존재하는 MemberId만 사용해 조인을 처리해보자. 다음과 같다.
-- [SQL-9-4-1-e]
SELECT T1.MemberId ,T1.NickNm
,T2.OrdNo ,T2.OrdDtm ,T2.OrdAmt
,T3.EventId ,T3.EntryDtm
FROM startdb.Member T1
LEFT OUTER JOIN startdb.Ord T2
ON (T2.MemberId = T1.MemberId
AND T2.OrdDtm >= STR_TO_DATE('20191101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20200101','%Y%m%d')
AND T2.OrdAmt >= 8000)
LEFT OUTER JOIN startdb.EventEntry T3
ON (T3.MemberId = T1.MemberId
AND T3.EntryDtm >= STR_TO_DATE('20191101','%Y%m%d')
AND T3.EntryDtm < STR_TO_DATE('20200101','%Y%m%d'))
WHERE T1.MemberId = 'M0253'
ORDER BY T2.OrdNo ,T3.EventId;
MemberId NickNm OrdNo OrdDtm OrdAmt EventId EntryDtm
-------- ------ ----- ------------------- --------- ------- -------------------
M0253 Cloud5 284 2019-11-11 11:00:00 8500.000 EV0007 2019-11-07 08:07:13
M0253 Cloud5 284 2019-11-11 11:00:00 8500.000 EV0008 2019-12-15 06:07:13
M0253 Cloud5 730 2019-12-23 11:00:00 10000.000 EV0007 2019-11-07 08:07:13
M0253 Cloud5 730 2019-12-23 11:00:00 10000.000 EV0008 2019-12-15 06:07:13
M0253 Cloud5 970 2019-12-25 11:00:00 12500.000 EV0007 2019-11-07 08:07:13
M0253 Cloud5 970 2019-12-25 11:00:00 12500.000 EV0008 2019-12-15 06:07:13
SQL
복사
결과를 보면 세 건(Member+Ord)이었던 데이터가 EventEntry와 조인하면서 6건으로 늘어난 것을 알 수 있다. 두 데이터 집합 간에 적절하지 않은 컬럼으로 조인을 처리했기 때문이다. 이처럼 비정상적으로 데이터가 중복되어 늘어난 상황에서 MemberId별로 집계해서 최종 결과를 출력해보자.
-- [SQL-9-4-1-f]
SELECT T1.MemberId ,MAX(T1.NickNm) NickNm
,COUNT(T2.OrdNo) OrdCnt
,COUNT(T3.EventId) EntryCnt
FROM startdb.Member T1
LEFT OUTER JOIN startdb.Ord T2
ON (T2.MemberId = T1.MemberId
AND T2.OrdDtm >= STR_TO_DATE('20191101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20200101','%Y%m%d')
AND T2.OrdAmt >= 8000)
LEFT OUTER JOIN startdb.EventEntry T3
ON (T3.MemberId = T1.MemberId
AND T3.EntryDtm >= STR_TO_DATE('20191101','%Y%m%d')
AND T3.EntryDtm < STR_TO_DATE('20200101','%Y%m%d'))
WHERE T1.MemberId = 'M0253'
GROUP BY T1.MemberId;
MemberId NickNm OrdCnt EntryCnt
-------- ------ ------ --------
M0253 Cloud5 6 6
SQL
복사
OrdCnt는 3이고 EntryCnt는 2가 나와야 정상적인 결과다. 조인 과정에서 중복된 데이터가 늘어나면서 올바르지 않게 카운트가 된 것이다. 이와 같인 상황에서 문제를 해결하기 위해 사용하는 꼼수가 있다. 바로 COUNT(DISTINCT)다. 아래와 같이 COUNT부분을 변경해보자.
-- [SQL-9-4-1-g]
SELECT T1.MemberId ,MAX(T1.NickNm) NickNm
,COUNT(DISTINCT T2.OrdNo) OrdCnt
,COUNT(DISTINCT T3.EventId) EntryCnt
FROM startdb.Member T1
LEFT OUTER JOIN startdb.Ord T2
ON (T2.MemberId = T1.MemberId
AND T2.OrdDtm >= STR_TO_DATE('20191101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20200101','%Y%m%d')
AND T2.OrdAmt >= 8000)
LEFT OUTER JOIN startdb.EventEntry T3
ON (T3.MemberId = T1.MemberId
AND T3.EntryDtm >= STR_TO_DATE('20191101','%Y%m%d')
AND T3.EntryDtm < STR_TO_DATE('20200101','%Y%m%d'))
WHERE T1.MemberId = 'M0253'
GROUP BY T1.MemberId;
MemberId NickNm OrdCnt EntryCnt
-------- ------ ------ --------
M0253 Cloud5 3 2
SQL
복사
COUNT(DISTINCT)는 중복된 데이터를 제거하고 카운트를 처리한다. 그러므로 부적절하게 데이터가 중복되어 늘어났지만 우리가 원하는 결과를 얻을 수 있다. 하지만 이와 같은 방법은 좋지 않다. 우선 COUNT(DISTINCT)는 성능적으로 부하가 큰 작업이다. 데이터가 많을 수록 부담이 되는 작업이다. 무엇보다 조인을 하다 보니 원하는 결과가 나오지 않아 이처럼 처리한 것이 가장 큰 문제다.
조인을 통해 데이터가 어떻게 변경 될지 예측하며 조인을 작성해야 한다. 실제 많은 사이트에서 M:1:M 관계를 적절하지 않게 조인 처리한 후에 어쩔 수 없이 GROUP BY 와 COUNT(DISTINCT)로 꼼수 처리하는 것을 많이 목격했다. 이처럼 SQL이 작성되지 않도록 적절하게 인라인 뷰를 활용하는 것을 권장한다.
9-4-2. M:1:M 관계 조인에 인라인 뷰 활용
M:1:M의 관계 조인에서 인라인 뷰를 활용하지 않으면, M과 1이 조인한 결과와 나머지 M이 조인할 때 M:M 관계처럼 조인이 이루어질 수 있다. 조인은 1:M 또는 1:1 관계에서만 이루어져야 한다. 의도적인 상황이 아니라면 M:M 관계의 조인이 발생해서는 안된다.
아래는 M:1:M 관계에서, M:1인 Ord와 Member를 먼저 인라인 뷰에서 처리 후 EventEntry와 조인해 원하는 결과를 만드는 방법이다.
-- [SQL-9-4-2-a]
-- Ord와 Member를 별도 처리 후 EventEntry와 조인
-- 최종 GROUP BY 할때, T1쪽의 OrdCnt를 MIN/MAX 처리해야 함에 주의 필요
SELECT T1.MemberId
,MAX(T1.NickNm) NickNm
,MAX(T1.OrdCnt) OrdCnt
,COUNT(T2.EventId) EntryCnt
FROM (
SELECT A.MemberId ,MAX(A.NickNm) NickNm ,COUNT(B.OrdNo) OrdCnt
FROM startdb.Member A
LEFT OUTER JOIN startdb.Ord B
ON (B.MemberId = A.MemberId
AND B.OrdDtm >= STR_TO_DATE('20191101','%Y%m%d')
AND B.OrdDtm < STR_TO_DATE('20200101','%Y%m%d')
AND B.OrdAmt >= 8000
)
WHERE A.MemberId = 'M0253'
GROUP BY A.MemberId
) T1
LEFT OUTER JOIN startdb.EventEntry T2
ON (T2.MemberId = T1.MemberId
AND T2.EntryDtm >= STR_TO_DATE('20191101','%Y%m%d')
AND T2.EntryDtm < STR_TO_DATE('20200101','%Y%m%d')
)
GROUP BY T1.MemberId;
SQL
복사
M:1:M 관계 조인을 처리하는 가장 쉬운 방법은 인라인 뷰를 활용해 1:1:1 조인으로 처리하는 것이다. 아래 SQL을 살펴보기 바란다. 주문과 이벤트응모 각각을 인라인 뷰에서 GROUP BY 처리해 Member 테이블과 1:1:1 조인이 되도록 하고 있다.
-- [SQL-9-4-2-b]
-- Member, Ord, EventEntry 각각을 MemberId로 식별되도록 처리후 조인
-- 1:1:1 조인이 된다.
SELECT T1.MemberId ,T1.NickNm ,T2.OrdCnt ,T3.EntryCnt
FROM startdb.Member T1
LEFT OUTER JOIN (
SELECT A.MemberID
,COUNT(*) OrdCnt
FROM startdb.Ord A
WHERE A.MemberId = 'M0253'
AND A.OrdDtm >= STR_TO_DATE('20191101','%Y%m%d')
AND A.OrdDtm < STR_TO_DATE('20200101','%Y%m%d')
AND A.OrdAmt >= 8000
GROUP BY A.MemberId
) T2
ON (T2.MemberId = T1.MemberId)
LEFT OUTER JOIN (
SELECT B.MemberId ,COUNT(*) EntryCnt
FROM startdb.EventEntry B
WHERE B.MemberId = 'M0253'
AND B.EntryDtm >= STR_TO_DATE('20191101','%Y%m%d')
AND B.EntryDtm < STR_TO_DATE('20200101','%Y%m%d')
GROUP BY B.MemberId
) T3
ON (T3.MemberId = T1.MemberId)
WHERE T1.MemberId = 'M0253';
SQL
복사
BOOSTER QUIZ
BOOSTER QUIZ ERD
BOOSTER QUIZ 9-4-1
2020년 1월 3일에 가입한 PLAT 등급의 회원의 2020년부터 2022년 까지의 주문금액과 2020년부터 2022년까지의 이벤트응모횟수를 보여주세요. 응모나 주문이 한번도 없는 회원도 출력되도록 해주세요.
•
대상 테이블: 회원(Member), 주문(Ord), 이벤트응모(EventEntry)
•
조회 조건
◦
JoinDtm이 2020년 1월 3일이면서, MemberGd가 PLAT인 회원
◦
회원별 OrdDtm이 2020년 1월 1일부터 2022년 12월 31일까지의 주문
◦
회원별 EntryDtm이 2020년 1월 1일부터 2022년 12월 31일까지의 이벤트응모
•
조회 컬럼: MemberId, NickNm, MemberGd, JoinDtm, SumOrdAmt, EntryCnt
◦
SumOrdAmt: OrdAm의 SUM
◦
EntryCnt: EventEntry의 카운트
•
추가 조건
◦
인라인 뷰를 두 개 활용
▪
인라인 뷰 안에서 Ord를 MemberId로 GROUP BY 처리
▪
인라인 뷰 안에서 EventEntry를 MemberId로 GROUP BY 처리
◦
인라인 뷰 바깥에서 Member와 인라인 뷰 두 개를 아우터 조인 처리(Member가 기준집합)
MemberId NickNm MemberGd JoinDtm SumOrdAmt EntryCnt
-------- --------- -------- ------------------- ---------- --------
M1209 Diamond24 PLAT 2020-01-03 00:00:00 388500.000 6
M1220 Lake24 PLAT 2020-01-03 00:00:00 194500.000 NULL
M1225 Meteor24 PLAT 2020-01-03 00:00:00 386000.000 5
M1262 Fire25 PLAT 2020-01-03 00:00:00 345500.000 NULL
M1267 Gold25 PLAT 2020-01-03 00:00:00 574500.000 3
M1281 Pika25 PLAT 2020-01-03 00:00:00 640000.000 6
M1290 Spark25 PLAT 2020-01-03 00:00:00 338500.000 NULL
M1295 Sun25 PLAT 2020-01-03 00:00:00 667000.000 8
M1348 Thunder26 PLAT 2020-01-03 00:00:00 817000.000 11
SQL
복사
