JOIN과 GROUP BY
하나의 SQL에서 조인과 GROUP BY를 동시에 사용할 수 있다. 조인과 GROUP BY를 동시 사용하는 패턴은 데이터 분석이나 리포트를 만들때 매우 많이 사용된다. 조인은 데이터 집합을 좌우로 연결시키고, GROUP BY는 데이터를 그룹핑한다. 테이블에 저장된 원천 데이터는 조인과 GROUP BY를 거치면서 우리에게 좀 더 유용한 가치를 전달해 줄 수 있다.
SQL로 아래와 같은 데이터를 만들어보자.
•
ShopStartYmd(매장시작일자)가 2018년 4월 5일인 매장들에 대해,
•
ShopOperTp(매장운영유형)별 2019년 11월의 주문 건수를 구하시오.
위의 데이터를 만들기 위해서는 매장과 주문을 조인해야 한다. 매장에서는 ShopStartYmd가 2018년 4월 5일인 데이터를, 주문에서는 OrdDtm이 2019년 11월인 주문을 찾아 조인해야 한다. 조인 SQL을 만들어 보면 다음과 같다.
-- [SQL-7-5-1] 조인만 작성
SELECT T1.ShopId ,T1.ShopOperTp ,T2.OrdNo ,T2.OrdDtm
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId)
WHERE T1.ShopStartYmd = '20180405'
AND T2.OrdDtm >= STR_TO_DATE('20191101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20191201','%Y%m%d')
ORDER BY T1.ShopOperTp ,T2.OrdDtm;
ShopId ShopOperTp OrdNo OrdDtm
------ ---------- ----- -------------------
S019 DIST 236 2019-11-02 13:00:00
S019 DIST 389 2019-11-30 13:00:00
S047 DRCT 240 2019-11-04 14:00:00
S047 DRCT 269 2019-11-06 14:00:00
S047 DRCT 273 2019-11-08 14:00:00
S047 DRCT 305 2019-11-13 14:00:00
S047 DRCT 315 2019-11-21 14:00:00
S005 FLAG 246 2019-11-05 10:00:00
S005 FLAG 281 2019-11-11 10:00:00
S005 FLAG 322 2019-11-23 10:00:00
S005 FLAG 353 2019-11-29 10:00:00
SQL
복사
최종 결과는 ShopOperTp별 주문 건수다. 조인으로 얻은 위 데이터 결과를 ShopOperTp로 GROUP BY 처리한다고 생각하면 된다. 위 SQL을 다음과 같이 GROUP BY로 변경하자.
-- [SQL 7-5-2] 조인 결과를 ShopOperTp별 GROUP BY 처리
SELECT T1.ShopOperTp -- > GROUP BY 컬럼을 SELECT 절에 사용
,COUNT(*) OrdCnt -- > 주문 건수를 카운트
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId)
WHERE T1.ShopStartYmd = '20180405'
AND T2.OrdDtm >= STR_TO_DATE('20191101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20191201','%Y%m%d')
GROUP BY T1.ShopOperTp -- > GROUP BY 추가
ORDER BY T1.ShopOperTp;
ShopOperTp OrdCnt
---------- ------
DIST 2
DRCT 5
FLAG 4
SQL
복사
조인과 GROUP BY가 필요한 데이터 집합을 얻기 위해 SQL을 두 번에 나누어서 작성했다. 조인과 GROUP BY가 아직 익숙하지 않다면 이처럼 나누어서 작성하기 바란다. 그리고 두 테이블을 조인해 GROUP BY 처리한다고 생각하기 보다는 두 테이블을 조인해서 데이터 집합을 만들고, 만들어진 하나의 데이터 집합에 대해 GROUP BY를 처리한다고 생각하기 바란다. 조금 더 쉽게 조인과 GROUP BY를 혼합해서 사용할 수 있을 것이다.
지금 작성한 SQL에 따라 데이터 집합이 변해가는 과정을 표현해보면 다음과 같다.
또 다른 SQL로 연습해보자. 아래 조건의 SQL을 작성해보자.
•
MemberGd(회원등급)별 주문년월별 OrdAmt(주문금액) 합계 구하기
◦
회원은 현재 MemberSt(회원상태)가 ACTV(활성)인 경우만
◦
주문은 OrdDtm(주문일시)가 2022년인 경우만
위 조건의 결과를 만들기 위해, Member(회원) 테이블과 Ord(주문) 테이블을 조인까지만 해보고 데이터를 살펴보자.
-- [SQL-7-5-3] 조인만 작성한 SQL
SELECT T1.MemberId ,T1.MemberGd
,T2.OrdNo ,T2.OrdDtm ,DATE_FORMAT(T2.OrdDtm,'%Y%m') OrdYm ,T2.OrdAmt
FROM startdb.Member T1
INNER JOIN startdb.Ord T2
ON (T1.MemberId = T2.MemberId)
WHERE T1.MemberSt = 'ACTV'
AND T2.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20230101','%Y%m%d');
MemberId MemberGd OrdNo OrdDtm OrdYm OrdAmt
-------- -------- ----- ------------------- ------ ---------
M0001 PLAT 25278 2022-01-01 08:00:00 202201 4000.000
M0001 PLAT 25281 2022-01-02 08:00:00 202201 4500.000
M0001 PLAT 25373 2022-01-03 08:00:00 202201 4500.000
M0001 PLAT 25376 2022-01-04 08:00:00 202201 4000.000
M0001 PLAT 25379 2022-01-05 08:00:00 202201 4500.000
M0001 PLAT 25531 2022-01-06 08:00:00 202201 8500.000
… 생략 …
SQL
복사
이제 위 결과 데이터 집합에 대해 GROUP BY를 처리하면 된다. MemberGd별, 주문년월별이므로 두 개 컬럼으로 GROUP BY 처리가 필요하다. 아래와 같이 최종 SQL을 만들 수 있다.
-- [SQL-7-5-4] 조인 결과를 MemberGd별로 GROUP BY 처리
SELECT T1.MemberGd ,DATE_FORMAT(T2.OrdDtm,'%Y%m') OrdYm
,SUM(T2.OrdAmt) OrdAmt
FROM startdb.Member T1
INNER JOIN startdb.Ord T2
ON (T1.MemberId = T2.MemberId)
WHERE T1.MemberSt = 'ACTV'
AND T2.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20230101','%Y%m%d')
GROUP BY T1.MemberGd ,DATE_FORMAT(T2.OrdDtm,'%Y%m');
MemberGd OrdYm OrdAmt
-------- ------ ------------
PLAT 202201 4488500.000
PLAT 202202 4702500.000
PLAT 202203 5995000.000
PLAT 202204 476500.000
PLAT 202205 9270000.000
… 생략 …
SQL
복사
위 SQL을 통해 데이터 집합이 변해가는 과정을 그려보면 다음과 같다.
데이터 집합의 변형
지금까지 조인과 GROUP BY를 혼합해서 사용해봤다. 아래 그림을 머릿속에 넣고 조인과 GROUP BY를 같이 사용하는 SQL을 작성하기 바란다. 조인을 통해 얻은 결과 데이터 집합에, 우리는 GROUP BY만 잘 처리하면 된다.
적절한 집계함수
1:M 관계에서 조인을 하면 1쪽의 데이터는 M건으로 늘어날 수 있다. 이때 조인 후 GROUP BY 를 한다면 1쪽 테이블 컬럼의 집계함수 처리에 주의가 필요하다.
Shop과 Ord를 조인해 ShopId별로 GROUP BY해서 주문금액 합계를 출력해보자. 이때, Shop의 정보인 ShopNm과 ShopSize도 보여주려 한다. 지금까지 해 온 것처럼 먼저 조인만 해보고 결과를 살펴보자.
-- [SQL-7-5-5] 조인만 처리한 SQL
SELECT T1.ShopId ,T1.ShopNm ,T1.ShopSize
,T2.OrdNo ,T2.OrdDtm ,T2.OrdAmt
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId)
WHERE T1.ShopOperTp = 'DRCT'
AND T1.ShopSize >= 190
AND T2.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20220106','%Y%m%d')
ORDER BY T1.ShopId ,T2.OrdNo;
ShopId ShopNm ShopSize OrdNo OrdDtm OrdAmt
------ ------------------ -------- ----- ------------------- --------
S196 San Francisco-10th 197 25420 2022-01-05 11:00:00 7000.000
S196 San Francisco-10th 197 25435 2022-01-05 11:00:00 7500.000
S196 San Francisco-10th 197 25473 2022-01-05 12:00:00 6500.000
S197 Indianapolis-10th 199 25348 2022-01-02 13:00:00 4000.000
S197 Indianapolis-10th 199 25395 2022-01-05 10:00:00 3500.000
SQL
복사
이제 위 결과를 ShopId별로 GROUP BY 처리하면 되는데, ShopNm과 ShopSize도 같이 보여주어야 한다. 두 컬럼은 GROUP BY 하려는 ShopId에 종속적인 컬럼이다. 그러므로 ShopId별 집계 처리를 하면서 ShopNm과 ShopSize도 같이 보여주려 한다면,
•
ShopNm과 ShopSize는 MIN이나 MAX로 처리해야 한다.
•
또는, ShopNm과 ShopSize도 GROUP BY 컬럼에 같이 사용해야 한다.
-- [SQL-7-5-6] GROUP BY 처리
-- ShopNm, ShopSize에 적절한 집계함수는?
SELECT T1.ShopId
,MAX(T1.ShopNm) ShopNm -- > ShopNm을 MAX 처리
,MAX(T1.ShopSize) ShopSize -- > ShopSize를 MAX 처리, SUM 처리하면?
,SUM(T2.OrdAmt) SumOrdAmt
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId)
WHERE T1.ShopOperTp = 'DRCT'
AND T1.ShopSize >= 190
AND T2.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20220106','%Y%m%d')
GROUP BY T1.ShopId
ORDER BY SumOrdAmt DESC;
ShopId ShopNm ShopSize SumOrdAmt
------ ------------------ -------- ---------
S196 San Francisco-10th 197 21000.000
S197 Indianapolis-10th 199 7500.000
SQL
복사
위에서는 ShopNm과 ShopSize에 MAX 처리를 했다. 만약에 ShopSize에 SUM 처리를 하면 어떻게 될까? 아래 결과를 확인해보기 바란다. 원래의 ShopSize보다 더 큰 값으로 나온 것을 알 수 있다.
-- [SQL-7-5-7] ShopSize에 SUM 처리
SELECT T1.ShopId
,MAX(T1.ShopNm) ShopNm
,SUM(T1.ShopSize) ShopSize -- > ShopSize를 SUM 처리
,SUM(T2.OrdAmt) SumOrdAmt
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId)
WHERE T1.ShopOperTp = 'DRCT'
AND T1.ShopSize >= 190
AND T2.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20220106','%Y%m%d')
GROUP BY T1.ShopId
ORDER BY SumOrdAmt DESC;
ShopId ShopNm ShopSize SumOrdAmt
------ ------------------ -------- ---------
S196 San Francisco-10th 591 21000.000
S197 Indianapolis-10th 398 7500.000
SQL
복사
1:M 조인에서 1쪽 집합은 M쪽 만큼 데이터가 늘어날 수 있다. 같은 데이터가 복사되어 증가된다. 그러므로 조인 후 1쪽의 PK 컬럼으로 GROUP BY가 이루어진다면 1쪽의 PK에 종속적인 컬럼에 대해서는 집계함수를 주의해서 사용해야 한다.
아래 그림을 통해 GROUP BY 전 Shop쪽의 ShopSize를 살펴보고, GROUP BY 후 ShopSize를 어떻게 처리해야 맞는 결과인지 생각해보기 바란다.
아래도 같은 경우다. Ord와 OrdDet는 1:M 관계다. 그러므로 조인을 하면 1쪽(Ord)의 데이터가 M쪽(OrdDet) 데이터의 건수만큼 늘어날 수 있다. 아래의 조인 결과를 보면 OrdNo(주문번호)가 1172인 데이터가 두 건 있는 것을 알 수 있다.
-- [SQL-7-5-8] Ord와 OrdDet를 조인
SELECT T1.ShopID ,T1.OrdNo ,T1.OrdDtm ,T1.OrdAmt
,T2.OrdNo ,T2.OrdDetNo ,T2.ItemId ,T2.OrdQty
FROM startdb.Ord T1
INNER JOIN startdb.OrdDet T2
ON (T2.OrdNo = T1.OrdNo)
WHERE T1.ShopId = 'S006'
AND T1.OrdDtm >= STR_TO_DATE('20191224','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20200101','%Y%m%d')
ORDER BY T1.OrdNo ,T2.OrdDetNo;
ShopID OrdNo OrdDtm OrdAmt OrdNo OrdDetNo ItemId OrdQty
------ ----- ------------------- -------- ----- -------- ------ ------
S006 908 2019-12-24 16:00:00 4500.000 908 1 ICLR 1
S006 1172 2019-12-30 16:00:00 7000.000 1172 1 IAMR 1
S006 1172 2019-12-30 16:00:00 7000.000 1172 2 CMFR 1
SQL
복사
이제 위 SQL을 GROUP BY 처리해서, OrdNo별 주문금액합계와 주문상세건수를 구해보자. OrdAmt는 Ord 테이블에 있는 컬럼이다. Ord 테이블의 PK인 OrdNo에 종속적인 컬럼이다. 그러므로 주문금액합계를 구하기 위해 OrdAmt를 SUM 처리하면 적절하지 않은 결과가 만들어진다. 아래 SQL과 결과를 살펴보기 바란다. 주문번호 1172의 주문금액은 원래 7,000원이다. 조인 후 SUM 처리로 인해 14,000원으로 금액이 늘어나 버렸다.
-- [SQL-7-5-9] 조인 결과를 GROUP BY
-- OrdAmt는 SUM? MAX?
SELECT T1.OrdNo
,SUM(T1.OrdAmt) OrdAmt -- > SUM? MAX?
,COUNT(*) OrdDetCnt
FROM startdb.Ord T1
INNER JOIN startdb.OrdDet T2
ON (T2.OrdNo = T1.OrdNo)
WHERE T1.ShopId = 'S006'
AND T1.OrdDtm >= STR_TO_DATE('20191224','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20200101','%Y%m%d')
GROUP BY T1.OrdNo;
OrdNo OrdAmt OrdDetCnt
----- --------- ---------
908 4500.000 1
1172 14000.000 2
SQL
복사
조인 후 GROUP BY를 사용할 때 무턱대고 SUM이나 AVG 처리를 하지 않기 바란다. 조인의 결과 데이터를 잘 살펴보고 어떤 집계함수를 사용해야 하는지 결정하기 바란다.
BOOSTER QUIZ
BOOSTER QUIZ ERD
BOOSTER QUIZ 7-5-1
2022년 1월 주문을 매장운영유형별로 금액을 집계해 주세요.
•
대상 테이블: 매장(Shop)과 주문(Ord)
•
조회 조건: OrdDtm이 2022년 1월인 데이터
•
조회 컬럼: ShopOperTp, OrdAmtSum
•
추가 조건
◦
ShopOperTp별 GROUP BY 처리합니다.
◦
OrdAmtSum은 ShopOperTp별 SUM 처리한 OrdAmt입니다.
ShopOperTp OrdAmtSum
---------- -----------
DIST 991500.000
DRCT 744000.000
FLAG 2753000.000
SQL
복사
BOOSTER QUIZ 7-5-2
폐점한 매장의 매장운영유형별 주문년도별 주문건수를 구해주세요.
•
대상 테이블: 매장(Shop)과 주문(Ord)
•
조회 조건: ShopSt가 CLSD인 매장의 모든 주문 데이터
•
조회 컬럼: ShopOperTp, OrdYear, OrdCnt
•
추가 조건
◦
OrdYear는 OrdDtm 컬럼에 DATE_FORMAT을 사용해 년도만 추출한 항목입니다.
◦
ShopOperTp, OrdYear별 GROUP BY 처리합니다.
◦
OrdCnt는 ShopOperTp, OrdYear별 데이터 건수입니다.
•
정렬 기준: ShopOperTp로 오름차순후, OrdYear로 오름차순 정렬하시오.
ShopOperTp OrdYear OrdCnt
---------- ----- ------
DIST 2019 3
DIST 2020 84
DIST 2021 106
DIST 2022 149
DRCT 2019 7
DRCT 2020 192
DRCT 2021 288
DRCT 2022 308
SQL
복사
BOOSTER QUIZ 7-5-3
ShopStartYmd가 20180405인 매장에 대해, 매장별 2019년 12월 23일의 주문금액합계를 구하시오. 매장명칭과 매장크기, 매장크기1m2당 주문금액합계도 출력하시오.
•
대상 테이블: 매장(Shop)과 주문(Ord)
•
조회 조건
◦
Shop의 ShopStartYmd가 20180405
◦
Ord의 OrdDtm이 2019년 12월 23일
•
조회 컬럼: ShopId, ShopNm, ShopSize, SumOrdAmt, AmtPerSize
•
추가 조건
◦
ShopId별 GROUP BY 처리하시오.
◦
SumOrdAmt는 Ord의 OrdAmt를 SUM 처리한 값이다.
◦
AmtPerSize는 매장크기1m2당 주문금액합계로서, SumOrdAmt를 ShopSize로 나눈 값이다.
•
정렬 기준: ShopSize로 내림차순
ShopID ShopNm ShopSize SumOrdAmt AmtPerSize
------ --------------- -------- --------- ----------
S047 San Antonio-3rd 119 8000.000 67.23
S019 Denver-1st 63 17000.000 269.84
S005 Phoenix-1st 30 24000.000 800.00
SQL
복사
Upper: 7. JOIN
