일반적으로 PK/FK 관계 컬럼을 사용해 조인을 하지만, PK/FK가 아니어도 얼마든지 조인할 수 있다. 특히 날짜 관련 속성은 데이터 분석을 위해 조인 컬럼으로 자주 사용하게 된다.
일자별로 주문금액과 신규로 가입한 회원수를 구해보자. 아래와 같은 결과를 얻으려고 한다.
-- 일자별 주문금액과 신규 가입한 회원
Ymd SumOrdAmt NewJoinCnt
-------- --------- ----------
20200420 17000.000 9
20200421 7000.000 25
20200422 8000.000 23
20200423 8500.000 33
20200424 16000.000 27
위 데이터는 아래와 같은 로직으로 얻는 데이터다.
위 결과를 얻기 위해 Member와 Ord를 직접 조인해서는 안된다. 위 결과는 당일 가입한 회원에 대한 주문금액을 구하는 것이 아니다. 단순히 일별로 주문이 얼만큼 되었으며, 일별로 신규로 가입한 회원이 몇 명인지 확인하는 대쉬보드성 데이터다. 위 결과를 한 번에 만들지 말고, 우선은 일자별 주문금액합계와 가입일자별 회원수를 각각 구해보자.
-- [SQL-9-6-1-a] OrdDtm별 주문금액 구하기
SELECT DATE_FORMAT(A.OrdDtm,'%Y%m%d') Ymd
,SUM(A.OrdAmt) SumOrdAmt
FROM startdb.Ord A
WHERE A.OrdDtm >= STR_TO_DATE('20200420','%Y%m%d')
AND A.OrdDtm < STR_TO_DATE('20200425','%Y%m%d')
GROUP BY DATE_FORMAT(A.OrdDtm,'%Y%m%d');
Ymd SumOrdAmt
-------- ---------
20200420 17000.000
20200421 7000.000
20200422 8000.000
20200423 8500.000
20200424 16000.000
-- [SQL-9-6-1-b] JoinDtm별 가입회원수 구하기
SELECT DATE_FORMAT(B.JoinDtm,'%Y%m%d') Ymd
,COUNT(*) NewJoinCnt
FROM startdb.Member B
WHERE B.JoinDtm >= STR_TO_DATE('20200420','%Y%m%d')
AND B.JoinDtm < STR_TO_DATE('20200425','%Y%m%d')
GROUP BY DATE_FORMAT(B.JoinDtm,'%Y%m%d');
Ymd NewJoinCnt
-------- ----------
20200424 27
20200421 25
20200423 33
20200420 9
20200422 23
두 SQL로 얻은 각각의 결과를 이제 조인으로 연결한다고 생각해보자. 어떤 컬럼을 조인으로 사용하면 되겠는가? 조인 컬럼에는 반드시 어느 한쪽의 데이터를 식별하는 Key가 사용되어야 한다. 위 데이터 집합을 살펴보면 각 데이터 집합을 식별하는 Key가 Ymd임을 알 수 있다. 그러므로 두 데이터 집합은 Ymd로 조인을 처리하면 된다. 다음과 같이 두 SQL을 각각의 인라인 뷰로 처리한 후에 조인하면 된다.
-- [SQL-9-6-1-c]
SELECT T1.Ymd
,T1.SumOrdAmt
,T2.NewJoinCnt
FROM (
-- [SQL-9-6-1-a]
SELECT DATE_FORMAT(A.OrdDtm,'%Y%m%d') Ymd
,SUM(A.OrdAmt) SumOrdAmt
FROM startdb.Ord A
WHERE A.OrdDtm >= STR_TO_DATE('20200420','%Y%m%d')
AND A.OrdDtm < STR_TO_DATE('20200425','%Y%m%d')
GROUP BY DATE_FORMAT(A.OrdDtm,'%Y%m%d')
) T1
LEFT OUTER JOIN (
-- [SQL-9-6-1-b]
SELECT DATE_FORMAT(B.JoinDtm,'%Y%m%d') Ymd
,COUNT(*) NewJoinCnt
FROM startdb.Member B
WHERE B.JoinDtm >= STR_TO_DATE('20200420','%Y%m%d')
AND B.JoinDtm < STR_TO_DATE('20200425','%Y%m%d')
GROUP BY DATE_FORMAT(B.JoinDtm,'%Y%m%d')
) T2
ON (T2.Ymd = T1.Ymd)
ORDER BY T1.Ymd;
Ymd SumOrdAmt NewJoinCnt
-------- --------- ----------
20200420 17000.000 9
20200421 7000.000 25
20200422 8000.000 23
20200423 8500.000 33
20200424 16000.000 27
인라인 뷰에서 특정 형식의 날짜로 GROUP BY 처리한 후에 조인하는 패턴은 분석을 위해 자주 사용되니 잘 기억해두기 바란다. 조인 조건을 찾기 위해 무작정 PK/FK 만 살피지 않기 바란다. 데이터를 인라인 뷰에서 각각 그룹핑한 후에 조인을 해야 하는 것은 아니지 고민해보기 바란다.