9-5-1. (1:M):M 관계 조인
아래 ERD를 살펴보자. Shop과 Ord는 1:M 관계다. 그리고 Ord와 OrdDet도 1:M이다. 이 세 테이블의 관계를 간략하게 (1:M):M 이라고 말할 수 있다. 정확히는 앞쪽의 1:M 조인 결과가 나머지 M과 조인시에 1 집합이 된다. 복잡하니 간단하게 (1:M):M 조인라고 하도록 하자.
위와 같이 1:M:M 조인은 M:1:M 을 조인할 때 처럼 주의가 필요하지는 않다. 1부터 시작해서 조인 컬럼만 잘 생각해서 조인 처리하면 된다. 하지만 조인 후 GROUP BY 할 때는 주의가 필요하다.
아래와 같은 결과를 얻으려고 한다. 매장시작일이 2018년 3월인 매장의 2022년 1월의 주문건수와 주문수량 합을 구하려고 한다. 여기서 주문 건수는 주문 테이블의 데이터를 카운트해야 한다. 그리고 주문수량의 합은 주문상세의 주문수량(OrdQty)을 SUM 처리해야 한다.
-- (1:M):M을 조인해 원하는 결과
ShopOperTp SUM_OrdCnt SUM_OrdQty
---------- ---------- ----------
DIST 20 27
DRCT 6 9
FLAG 135 182
SQL
복사
위 결과를 보면 주문건수와 주문수량이 다르다는 점에 주의가 필요하다. 하나의 주문에 여러 상품을 여러 개 주문할 수 있기 때문에 주문수량은 주문 건수보다 더 클 수 있다.
위와 같은 결과를 얻기 위해 우선 Shop과 Ord, OrdDet를 조인해보자. 다음과 같다.
-- [SQL-9-5-1-a]
SELECT T1.ShopId ,T1.ShopNm ,T1.ShopOperTp
,T2.OrdNo ,T2.OrdDtm
,T3.OrdDetNo ,T3.ItemId ,T3.OrdQty
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId)
INNER JOIN startdb.OrdDet T3
ON (T3.OrdNo = T2.OrdNo)
WHERE T1.ShopStartYmd LIKE '201803%'
AND T2.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20220201','%Y%m%d')
ORDER BY T1.ShopId ,T2.OrdNo ,T3.OrdDetNo;
ShopId ShopNm ShopOperTp OrdNo OrdDtm OrdDetNo ItemId OrdQty
------ ------------- ---------- ----- ------------------- -------- ------ ------
S008 San Diego-1st FLAG 25286 2022-01-02 09:30:00 1 BGLR 1
S008 San Diego-1st FLAG 25445 2022-01-05 11:30:00 1 HCHB 1
S008 San Diego-1st FLAG 25456 2022-01-05 11:30:00 1 CLB 1
S008 San Diego-1st FLAG 25504 2022-01-05 12:30:00 1 IAMR 1
S008 San Diego-1st FLAG 25612 2022-01-11 11:30:00 1 CMFR 1
-- > S008 San Diego-1st FLAG 25623 2022-01-11 11:30:00 1 BMFR 1
-- > S008 San Diego-1st FLAG 25623 2022-01-11 11:30:00 2 ICLB 1
S008 San Diego-1st FLAG 25671 2022-01-11 12:30:00 1 IAMB 1
S008 San Diego-1st FLAG 25797 2022-01-23 11:30:00 1 IAMB 1
... 생략 ...
SQL
복사
위 SQL의 조인은 정상이다. 다만, 위 SQL의 결과에 ‘-- >’로 표시한 부분처럼 하나의 주문에 여러 건의 주문 상세가 있을 수 있다는 점을 주의해야 한다.
이제 위 결과를 ShopOperTp별로 GROUP BY 처리해 주문건수와 주문수량 합계를 구해보자. 다음과 같이 SQL을 작성해볼 수 있다.
-- [SQL-9-5-1-b]
SELECT T1.ShopOperTp
,COUNT(T2.OrdNo) SUM_OrdCnt
,SUM(T3.OrdQty) SUM_OrdQty
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId)
INNER JOIN startdb.OrdDet T3
ON (T3.OrdNo = T2.OrdNo)
WHERE T1.ShopStartYmd LIKE '201803%'
AND T2.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20220201','%Y%m%d')
GROUP BY T1.ShopOperTp
ORDER BY T1.ShopOperTp;
ShopOperTp SUM_OrdCnt SUM_OrdQty
---------- ---------- ----------
DIST 27 27
DRCT 9 9
FLAG 182 182
SQL
복사
이제 SQL로 결과를 얻었으니, 보고서에 수치를 옮겨 적고 신나게 보고해보자. 진짜 이대로 보고 할 것인가? 잘 살펴보기 바란다. 위 수치는 문제가 있다. 주문건수와 주문수량 합계의 값이 같다. 조인 과정에서 Ord의 레코드가 OrdDet만큼 늘어났기 때문에, 주문건수를 단순 COUNT로 처리하면 이와 같은 문제가 발생된다.
데이터 결과를 얻은 후에 제대로 추출했는지 점검하는 것은 매우 중요한 일이다. 필자의 경우, 필자가 추출한 데이터에 오류가 있는지 모르고, 이 내용이 그대로 팀장을 거쳐 임원들에게 보고된 적이 있다. 보고 후 수치를 잘못 추출한 것을 알고 얼마나 마음을 조렸는지 모른다. 데이터 추출 후에는 반드시 수치 점검을 해야 한다. 데이터를 추출하는 것보다 데이터를 정상적으로 추출했는지 점검하는 것에 더 많은 시간을 할애하기 바란다.
위 SQL을 해결하기 위한 가장 간단한 방법은 COUNT(DISTINCT)를 사용하는 것이다. 아래와 같이 실행해보자.
-- [SQL-9-5-1-c]
SELECT T1.ShopOperTp
,COUNT(DISTINCT T2.OrdNo) SUM_OrdCnt -- > OrdNo를 DISTINCT 처리해서 카운트
,SUM(T3.OrdQty) SUM_OrdQty
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId)
INNER JOIN startdb.OrdDet T3
ON (T3.OrdNo = T2.OrdNo)
WHERE T1.ShopStartYmd LIKE '201803%'
AND T2.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20220201','%Y%m%d')
GROUP BY T1.ShopOperTp
ORDER BY T1.ShopOperTp;
ShopOperTp SUM_OrdCnt SUM_OrdQty
---------- ---------- ----------
DIST 20 27
DRCT 6 9
FLAG 135 182
SQL
복사
1:M:M 관계 조인 후 GROUP BY가 될 때는, 가운데 M 테이블의 컬럼에는 적절하게 COUNT(DISTINCT)를 사용해야 한다. COUNT(DISTINCT)가 성능적으로 좋지 않으므로 피하고 싶다면 아래와 같이 SQL을 작성해 볼 수 있다. 하지만 SQL이 너무 길어지고 Shop과 Ord를 두 번이나 반복해서 사용하므로 DISTINCT를 사용할 때 보다 성능이 나쁠 수도 있다.
-- [SQL-9-5-1-d]
SELECT T1.ShopOperTp
,T1.SUM_OrdCnt
,T2.SUM_OrdQty
FROM (
SELECT A.ShopOperTp
,COUNT(*) SUM_OrdCnt
FROM startdb.Shop A
INNER JOIN startdb.Ord B
ON (A.ShopId = B.ShopId)
WHERE A.ShopStartYmd LIKE '201803%'
AND B.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND B.OrdDtm < STR_TO_DATE('20220201','%Y%m%d')
GROUP BY A.ShopOperTp
ORDER BY A.ShopOperTp
) T1
INNER JOIN
(
SELECT A.ShopOperTp
,SUM(C.OrdQty) SUM_OrdQty
FROM startdb.Shop A
INNER JOIN startdb.Ord B
ON (A.ShopId = B.ShopId)
INNER JOIN startdb.OrdDet C
ON (C.OrdNo = B.OrdNo)
WHERE A.ShopStartYmd LIKE '201803%'
AND B.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND B.OrdDtm < STR_TO_DATE('20220201','%Y%m%d')
GROUP BY A.ShopOperTp
ORDER BY A.ShopOperTp
) T2
ON (T1.ShopOperTp = T2.ShopOperTp)
ORDER BY T1.ShopOperTp;
SQL
복사
앞에서 COUNT(DISTINCT)가 좋지 않은 방법이라고 설명했지만, 원하는 결과에 따라 불가피하게 사용해야 하는 경우도 있다는 것을 기억하기 바란다. 중요한 건 테이블 간의 조인에 따라 데이터가 변형되는 과정에서 적절한 집계 방법을 찾아내는 것이다.
