11-3-1. GROUP BY와 분석함수
GROUP BY와 분석함수를 동시에 사용할 때 원하는 결과가 나오지 않거나 SQL 자체가 실행되지 않는 경우를 만나기도 한다. 이와 같은 문제를 만나지 않기 위해 GROUP BY와 분석함수의 특징을 잘 이해하고 SQL을 작성해야 한다.
아래 SQL은 ItemCat별 상품수를 조회하는 SQL이다. GROUP BY를 사용해 ItemCat별 데이터를 집계 처리하고 있다.
-- [SQL-11-3-1-a] 기본 리스트
SELECT T1.ItemCat ,T2.ItemCatNm ,T1.ItemId ,T1.ItemNm
FROM startdb.Item T1
INNER JOIN startdb.ItemCat T2
ON (T2.ItemCat = T1.ItemCat)
WHERE T1.LaunchDt = STR_TO_DATE('20230401','%Y%m%d')
ORDER BY T1.ItemCat ,T1.ItemId;
ItemCat ItemCatNm ItemId ItemNm
------- --------- ------ ------------------
BEV Beverage CITR Yuzu Ade(R)
BEV Beverage ZAMB Grapefruit Ade(R)
BKR Bakery MACA Macaron(R)
COF Coffee EINR Einspanner(R)
COF Coffee FLTR Flat White(R)
COF Coffee IEINR Iced Einspanner(R)
COF Coffee IFLTR Iced Flat White(R)
-- [SQL-11-3-1-b] GROUP BY 처리
SELECT T1.ItemCat
,MAX(T2.ItemCatNm) ItemCatNm
,COUNT(*) ItemCnt
FROM startdb.Item T1
INNER JOIN startdb.ItemCat T2
ON (T2.ItemCat = T1.ItemCat)
WHERE T1.LaunchDt = STR_TO_DATE('20230401','%Y%m%d')
GROUP BY T1.ItemCat
ORDER BY T1.ItemCat;
ItemCat ItemCatNm ItemCnt
------- --------- -------
BEV Beverage 2
BKR Bakery 1
COF Coffee 4
SQL
복사
이제 위 SQL에 아래와 같이 COUNT(*) OVER()를 추가해보자. 어떤 값이 나올 것 같은가? SQL을 실행하지 말고 생각해보기 바란다.
-- [SQL-11-3-1-c] GROUP BY와 분석함수 동시 사용
SELECT T1.ItemCat
,MAX(T2.ItemCatNm) ItemCatNm
,COUNT(*) ItemCnt
,COUNT(*) OVER() CntOver
FROM startdb.Item T1
INNER JOIN startdb.ItemCat T2
ON (T2.ItemCat = T1.ItemCat)
WHERE T1.LaunchDt = STR_TO_DATE('20230401','%Y%m%d')
GROUP BY T1.ItemCat
ORDER BY T1.ItemCat;
ItemCat ItemCatNm ItemCnt CntOver
------- --------- ------- -------
BEV Beverage 2 ?
BKR Bakery 1 ?
COF Coffee 4 ?
SQL
복사
앞에서 여러번 이야기해듯이 분석 대상은 “분석함수를 제외한 SQL의 결과 집합” 이다. 그러므로 COUNT OVER의 분석 대상은 분석함수를 제외한 GROUP BY 까지 처리된 결과 집합이다. 그러므로 위 SQL을 실행하면 조회된 레코드 별로 CntOver에는 3이 출력된다.
GROUP BY가 포함된 SQL은 GROUP BY까지 처리된 결과 집합이 분석 대상이 된다는 점을 잘 기억하기 바란다.
11-3-2. GROUP BY와 SUM 분석함수
COUNT OVER 분석함수는 카운트 대상을 “*”를 사용해 레코드 자체를 지정할 수 있다. 반면에 SUM, MIN, MAX와 같은 분석함수는 분석을 처리할 컬럼을 반드시 지정해야 한다. 이 과정에서 분석함수와 집계함수 간에 혼란이 증폭된다.
아래는 2020년 1월에 개장한 매장에 대해, ShopOperTp별로 TableQty 합계를 구하는 SQL이다. 아직 분석함수는 사용하지 않았다.
-- [SQL-11-3-2-a] ShopOperTp별 테이블수량
SELECT T1.ShopOperTp
,SUM(T1.TableQty) SUMTableQty
,COUNT(*) ShopCnt
FROM startdb.Shop T1
WHERE T1.ShopStartYmd LIKE '202001%'
GROUP BY T1.ShopOperTp;
ShopOperTp SUMTableQty ShopCnt
---------- ----------- -------
DRCT 198 9
DIST 205 10
SQL
복사
이제, 아래와 같이 SUM OVER() 분석함수를 추가해보자. 아래 SQL은 에러가 난다. (MySQL의 SQL_MODE에 ONLY_FULL_GROUP_BY가 생략되어 있다면 오류 없이 실행 될 수도 있다.)
-- [SQL-11-3-2-b] SUM 분석함수 추가, 에러 발생
SELECT T1.ShopOperTp
,SUM(T1.TableQty) SUMTableQty
,COUNT(*) ShopCnt
,SUM(T1.TableQty) OVER() SUMOverTableQty
FROM startdb.Shop T1
WHERE T1.ShopStartYmd LIKE '202001%'
GROUP BY T1.ShopOperTp;
SQL
복사
Tip. ONLY_FULL_GROUP_BY
MySQL은 SQL_MODE를 지정할 수 있다. SQL_MODE에 ONLY_FULL_GROUP_BY를 추가하면 엄격한 GROUP BY 룰을 따르게 된다. SQL_MODE에 ONLY_FULL_GROUP_BY를 제거하면, GROUP BY에 없는 컬럼을 SELECT 절에서 집계함수 없이 사용할 수 있다. 이 경우 비즈니스 로직에 맞지 않는 데이터가 출력될 위험성이 생긴다. 가능하면 ONLY_FULL_GROUP_BY를 지정해 놓는 것을 권장한다. 일반적으로 다른 DBMS는 이와 같은 옵션을 지원하지 않으며, 기본적으로 엄격한 GROUP BY 룰을 따른다.
왜 에러가 났는지 이해하기 위해, 아래와 같이 SUM OVER의 대상인 TableQty컬럼은 그대로 남겨놓고 SUM OVER를 제거해보자.
-- [SQL-11-3-2-c] 분석함수 부분만 제거
SELECT T1.ShopOperTp
,SUM(T1.TableQty) SUMTableQty
,COUNT(*) ShopCnt
,T1.TableQty -- > GROUP BY 규칙에 어긋난다.
FROM startdb.Shop T1
WHERE T1.ShopStartYmd LIKE '202001%'
GROUP BY T1.ShopOperTp;
SQL
복사
SUM OVER를 제거한 위의 SQL 역시 에러가 발생한다. SUM OVER를 제거하니 SELECT 절의 TableQty 컬럼이 GROUP BY 규칙에 어긋나는 것을 알 수 있다. TableQty는 GROUP BY로 정의된 컬럼이 아니므로 SELECT 절에서 사용하려면 아래와 같이 집계함수 처리를 해야 한다.
-- [SQL-11-3-2-d] 집계함수 처리
SELECT T1.ShopOperTp
,SUM(T1.TableQty) SUMTableQty
,COUNT(*) ShopCnt
,SUM(T1.TableQty) SUMOverTableQty
FROM startdb.Shop T1
WHERE T1.ShopStartYmd LIKE '202001%'
GROUP BY T1.ShopOperTp;
ShopOperTp SUMTableQty ShopCnt SUMOverTableQty
---------- ----------- ------- ---------------
DRCT 198 9 198
DIST 205 10 205
SQL
복사
분석함수는 분석함수를 제외한 결과를 분석대상으로 한다. 그러므로 분석함수를 사용하려면 위 SQL에 그대로 분석함수를 추가해야 한다. 집계 처리 된 SUM(T1.TableQty) 위에 분석함수를 적용해야 한다는 뜻이다. 아래와 같이 SUM을 중복해서 사용해야 한다. 안쪽의 SUM은 집계함수이고 바깥쪽의 SUM은 분석함수다.
•
SUM( SUM(T1.TableQty) ) OVER()
-- [SQL-11-3-2-e] 집계함수에 분석함수 적용
SELECT T1.ShopOperTp
,SUM(T1.TableQty) SUMTableQty
,COUNT(*) ShopCnt
,SUM( SUM(T1.TableQty) ) OVER() SUMOverTableQty
FROM startdb.Shop T1
WHERE T1.ShopStartYmd LIKE '202001%'
GROUP BY T1.ShopOperTp;
ShopOperTp SUMTableQty ShopCnt SUMOverTableQty
---------- ----------- ------- ---------------
DRCT 198 9 403
DIST 205 10 403
SQL
복사
이처럼 집계함수와 분석함수를 중첩해서 사용하는 방법이 쉽게 이해될 수도 있고 안될 수도 있다. 바로 이해하지 못해도, 계속해서 분석함수와 집계함수를 사용하다 보면 어느새 자연스럽게 깨닫게 될 것이다. 지금은 잘 이해가 안되고 어렵다면, 아래와 같이 GROUP BY SQL은 인라인 뷰 처리하고 분석함수는 인라인 뷰 바깥에서 사용하기 바란다. SQL이 조금 더 길어지지만 분석함수의 분석대상을 명확히 알 수 있어 오히려 좋을 수 있다.
-- [SQL-11-3-2-f]
SELECT T2.ShopOperTp
,T2.SUMTableQty
,T2.ShopCnt
,SUM(T2.SUMTableQty) OVER() SUMOverTableQty
FROM (
SELECT T1.ShopOperTp
,SUM(T1.TableQty) SUMTableQty
,COUNT(*) ShopCnt
,SUM( SUM(T1.TableQty) ) OVER() SUMOverTableQty
FROM startdb.Shop T1
WHERE T1.ShopStartYmd LIKE '202001%'
GROUP BY T1.ShopOperTp
) T2;
SQL
복사
11-3-3. GROUP BY와 분석함수 응용해보기
2021년의 주문에 대해 주문년월별로 주문금액 순위를 구하려고 한다. 이를 위해 가장 먼저 할일은 주문년월별 주문금액을 집계하는 것이다. 그리고 그 결과에 대해 주문금액별 순위를 구하면 된다. 다음과 같이 SQL을 작성하면 된다.
-- [SQL-11-3-3-a] 주문년월별 순위 구하기(인라인 뷰 활용)
SELECT T2.OrdYm ,T2.OrdAmt
,RANK() OVER(ORDER BY T2.OrdAmt DESC) RankOverAmt
FROM (
SELECT DATE_FORMAT(T1.OrdDtm,'%Y%m') OrdYm
,SUM(T1.OrdAmt) OrdAmt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20210101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20220101','%Y%m%d')
GROUP BY DATE_FORMAT(T1.OrdDtm,'%Y%m')
) T2;
OrdYm OrdAmt RankOverAmt
------ ------------ -----------
202112 27390500.000 1
202108 6018500.000 2
202107 6016500.000 3
202109 5999500.000 4
202110 5997000.000 5
202111 5970000.000 6
202105 5542500.000 7
202103 3858000.000 8
202102 3154500.000 9
202101 2590000.000 10
202106 1751000.000 11
202104 468500.000 12
SQL
복사
인라인 뷰 안쪽에서는 년월별 주문금액을 구하고, 인라인 뷰의 결과에 대해 RANK 분석함수를 적용해 순위를 구현했다. 위 SQL은 아래와 같이 인라인 뷰 없이도 처리할 수 있다.
-- [SQL-11-3-3-b] 주문년월별 순위 구하기(인라인 뷰 제외)
SELECT DATE_FORMAT(T1.OrdDtm,'%Y%m') OrdYm
,SUM(T1.OrdAmt) OrdAmt
,RANK() OVER(ORDER BY SUM(T1.OrdAmt) DESC) RankOverAmt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20210101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20220101','%Y%m%d')
GROUP BY DATE_FORMAT(T1.OrdDtm,'%Y%m');
SQL
복사
이번에는 2021년의 주문에 대해, 상품별주문비율(ItemPercentage)을 구하려고 한다. 다음과 같이 분석함수를 사용할 수 있다.
-- [SQL-11-3-3-c] 상품별 전체대비 비율 구하기(인라인 뷰 활용)
SELECT T3.ItemId ,T4.ItemNm ,T3.ItemQty
,ROUND(T3.ItemQty
/ SUM(T3.ItemQty) OVER() * 100,2) ItemPercentage
FROM (
SELECT T2.ItemID
,SUM(T2.OrdQty) ItemQty
FROM startdb.Ord T1
INNER JOIN startdb.OrdDet T2
ON (T2.OrdNo = T1.OrdNo)
WHERE T1.OrdDtm >= STR_TO_DATE('20210101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20220101','%Y%m%d')
GROUP BY T2.ItemID
) T3
INNER JOIN startdb.Item T4
ON (T4.ItemId = T3.ItemId)
ORDER BY ItemPercentage DESC;
ItemId ItemNm ItemQty ItemPercentage
------ ------------------- ------- --------------
AMR Americano(R) 1479 7.63
IAMR Iced Americano(R) 1479 7.63
ICLR Iced Cafe Latte(R) 1478 7.63
CLR Cafe Latte(R) 1428 7.37
CMFR Chocolate Muffin(R) 1427 7.36
HCHR Hot Chocolate(R) 1427 7.36
LEMR Lemonade(R) 1427 7.36
AMB Americano(B) 1343 6.93
HCHB Hot Chocolate(B) 1342 6.93
BGLR Bagel(R) 1341 6.92
BMFR Blueberry Muffin(R) 1341 6.92
CLB Cafe Latte(B) 1289 6.65
IAMB Iced Americano(B) 1288 6.65
ICLB Iced Cafe Latte(B) 1288 6.65
SQL
복사
GROUP BY와 분석함수가 같이 사용되어야 하는 결과라면, 두 기능을 한 블록의 쿼리에서 모두 해결하기 보다는 위와 같이 인라인 뷰에서는 GROUP BY만, 인라인 뷰 바깥에서 분석함수를 사용하기 바란다. 이러한 방법이 익숙해지면 한 블록의 쿼리에서 GROUP BY와 분석함수를 동시에 사용할 수 있게 될 것이다.
BOOSTER QUIZ
BOOSTER QUIZ ERD
BOOSTER QUIZ 11-3-1
2022년 1월 주문에 대해, 상품카테고리별 주문수량을 구해주세요, 상품카테고리별 주문수량에 대한 순위도 구해주세요.
•
대상 테이블: 주문(Ord), 주문상세(OrdDet), 상품(Item), 상품카테고리(ItemCat)
•
조회 조건: OrdDtm이 2022년 1월인 주문데이터
•
조회 컬럼: ItemCat ,ItemCatNm ,SumOrdQty, RankOrdQty
•
추가 조건
◦
ItemCat별 GROUP BY 처리하시오.
◦
SumOrdQty: ItemCat별 OrdQty를 SUM 집계함수 처리한 결과입니다.
◦
RankOrdQty: SumOrdQty에 따른 순위로서 판매수량이 가장 많으면 1이 됩니다.
ItemCat ItemCatNm SumOrdQty RankOrdQty
------- --------- ------------- --------------
COF Coffee 664 1
BEV Beverage 252 2
BKR Bakery 248 3
SQL
복사
BOOSTER QUIZ 11-3-2
2022년 1월 주문에 대해, 매장운영유형별 주문금액을 구해주세요. 조회된 데이터의 전체주문금액도 컬럼으로 추가해주시고, 매장운영유형별 주문금액 비율도 구해주세요.
•
대상 테이블: 매장(Shop), 주문(Ord), 기준코드(BaseCd)
•
조회 조건: OrdDtm이 2022년 1월인 주문데이터
•
조회 컬럼: ShopOperTp, ShopOperTpNm, SumOrdAmt, SumOrdAmtPercent
•
추가 조건
◦
ShopOperTp별 GROUP BY 처리하세요.
◦
ShopOperTpNm은 ShopOperTp에 대한 명칭으로 BaseCd에서 가져오세요. (서브쿼리 사용)
◦
SumOrdAmt: ShopOperTp별 OrdAmt를 SUM 집계함수 처리한 결과입니다.
◦
SumOverOrdAmt: SumOrdAmt의 전체 합계로서 분석함수로 처리합니다.
◦
SumOrdAmtPercent = SumOrdAmt / SumOverOrdAmt * 100
ShopOperTp ShopOperTpNm SumOrdAmt SumOverOrdAmt SumOrdAmtPercent
---------- ------------ ----------- ------------- ----------------
FLAG Flagship 2753000.000 4488500.000 61.33
DIST Distributor 991500.000 4488500.000 22.09
DRCT Directly 744000.000 4488500.000 16.58
SQL
복사
