GROUP BY 컬럼의 변형
GROUP BY 컬럼을 변형해서 처리할 수 있다.
-- [SQL-5-5-1] GROUP BY 처리전 SQL
-- ItemNm의 뒤의 세 글자를 ItemSizeNm으로 표시합니다.
SELECT T1.ItemId
,T1.ItemNm
,SUBSTR(T1.ItemNm,-3) ItemSizeNm
FROM startdb.Item T1
WHERE T1.ItemCat = 'COF'
AND T1.LaunchDt = STR_TO_DATE('20190101','%Y%m%d')
ORDER BY T1.ItemId;
-- GROUP BY 처리전 결과
ItemId ItemNm ItemSizeNm
------ ------------------ ----------
AMB Americano(B) (B)
AMR Americano(R) (R)
CLB Cafe Latte(B) (B)
CLR Cafe Latte(R) (R)
IAMB Iced Americano(B) (B)
IAMR Iced Americano(R) (R)
ICLB Iced Cafe Latte(B) (B)
ICLR Iced Cafe Latte(R) (R)
-- [SQL-5-5-2] SUBSTR(T1.ItemNm,-3)으로 GROUP BY 처리후
SELECT SUBSTR(T1.ItemNm,-3) ItemSizeNm
,COUNT(*) ItemCnt
FROM startdb.Item T1
WHERE T1.ItemCat = 'COF'
AND T1.LaunchDt = STR_TO_DATE('20190101','%Y%m%d')
GROUP BY SUBSTR(T1.ItemNm,-3)
ORDER BY ItemCnt DESC;
-- GROUP BY 처리후 결과
ItemSizeNm ItemCnt
---------- -------
(B) 4
(R) 4
SQL
복사
아래는 OrdDtm(주문일시) 값에 DATE_FORMAT을 사용해 년월 형태의 문자로 변환해 GROUP BY 처리하는 SQL입니다. 이처럼 일자 데이터를 년월로 변형해 데이터를 집계하는 패턴은 매우 자주 사용된다.
-- [SQL-5-5-3] GROUP BY 처리전 SQL(OrdDtm을 OrdYm으로 변환)
SELECT T1.OrdNo, T1.OrdDtm ,T1.OrdAmt
,DATE_FORMAT(T1.OrdDtm,'%Y%m') OrdYm
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20221001','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20230101','%Y%m%d')
AND T1.ShopId = 'S263'
ORDER BY T1.OrdNo;
-- GROUP BY 처리전 결과
OrdNo OrdDtm OrdAmt OrdYm
----- ------------------- -------- ------
42456 2022-10-02 13:00:00 4000.000 202210
45886 2022-10-30 13:00:00 4000.000 202210
46204 2022-11-02 13:00:00 4500.000 202211
49621 2022-11-30 13:00:00 8500.000 202211
51228 2022-12-02 13:00:00 2500.000 202212
55909 2022-12-22 13:00:00 4000.000 202212
63040 2022-12-25 13:00:00 8500.000 202212
66019 2022-12-30 13:00:00 4000.000 202212
-- [SQL-5-5-4]
-- GROUP BY 처리후 SQL
-- 주문년월(OrdYm)별 주문 데이터를 집계합니다.
SELECT DATE_FORMAT(T1.OrdDtm,'%Y%m') OrdYm
,COUNT(*) OrdCnt
,SUM(T1.OrdAmt) OrdAmt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20221001','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20230101','%Y%m%d')
AND T1.ShopId = 'S263'
GROUP BY DATE_FORMAT(T1.OrdDtm,'%Y%m')
ORDER BY DATE_FORMAT(T1.OrdDtm,'%Y%m');
OrdYm OrdCnt OrdAmt
------ ------ ---------
202210 2 8000.000
202211 2 13000.000
202212 4 19000.000
SQL
복사
컬럼 간의 계산 결과를 GROUP BY에 사용할 수도 있다. 아래는 주문일시부터 제조완료까지 몇 분이 걸렸는지 계산해 GROUP BY 항목으로 사용하는 SQL이다.
•
CompMin(제조완료분수)
◦
TIMESTAMPDIFF를 사용해 주문일시(OrdDtm)부터 제조완료(PrepareCmpDtm)까지 몇 분이 걸렸는지 계산한 값.
-- [SQL-5-5-5]
-- GROUP BY 처리전 SQL
-- CompMin: TIMESTAMPDIFF를 사용해 주문일시부터 제조완료까지 몇 분이 걸렸는지 계산
SELECT T1.OrdNo, T1.OrdDtm ,T1.PrepareCmpDtm
,TIMESTAMPDIFF(MINUTE,T1.OrdDtm,T1.PrepareCmpDtm) CompMin
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20221224','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20221225','%Y%m%d')
ORDER BY CompMin ASC;
OrdNo OrdDtm PrepareCmpDtm CompMin
----- ------------------- ------------------- -------
59830 2022-12-24 07:30:00 2022-12-24 07:32:00 2
59865 2022-12-24 08:00:00 2022-12-24 08:02:00 2
59978 2022-12-24 09:00:00 2022-12-24 09:02:00 2
60008 2022-12-24 09:00:00 2022-12-24 09:02:00 2
60060 2022-12-24 10:00:00 2022-12-24 10:02:00 2
... 생략 ...
-- [SQL-5-5-6]
-- GROUP BY 처리된 SQL
SELECT TIMESTAMPDIFF(MINUTE,T1.OrdDtm,T1. PrepareCmpDtm) CompMin
,COUNT(*) OrdCnt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20221224','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20221225','%Y%m%d')
GROUP BY TIMESTAMPDIFF(MINUTE,T1.OrdDtm,T1. PrepareCmpDtm)
ORDER BY TIMESTAMPDIFF(MINUTE,T1.OrdDtm,T1. PrepareCmpDtm);
-- GROUP BY 처리후 결과
CompMin OrdCnt
------- ------
2 24
3 99
4 122
5 149
6 141
7 147
8 172
9 139
10 173
11 221
12 192
13 144
14 112
15 87
16 87
17 58
18 56
19 57
20 32
SQL
복사
이와 같이 시간을 계산하고, 계산된 시간 기준으로 데이터를 집계하는 것은 중요한 분석 포인트 중 하나다. 위 결과를 시각화 해보면 다음과 같다. 시각화를 통해 주문을 처리하는 시간이 8분에서 12분 사이에 집중되어 있는 것을 알 수 있다. 이를 통해 제조 완료 시간 단축이 필요한지, 지금도 괜찮은지에 대해 회의를 해볼 수 있을 것이다.
이처럼 계산 결과 값을 그룹핑하는 패턴은 새로운 관점으로 데이터를 분석하게 해준다. 많은 사람들이 GROUP BY를 사용할 줄 알지만, 데이터 분석을 위해 특정 값이 계산된 내용을 GROUP BY에 사용하는 것은 잘 모른다. 이처럼 분석해볼 수 있다는 것도 잘 떠올리지 못한다. GROUP BY에 계산된 결과를 사용할 수 있다는 점을 꼭 기억하기 바란다.
별칭 처리 주의 사항
•
GROUP BY에서는 컬럼의 별칭을 사용할 수 없다.
•
하지만 MySQL은 버전이나 설정에 따라 가능(권장하지 않는다.), 오라클은 불가능
•
GROUP BY에서 컬럼의 별칭을 정의할 수 없다.
◦
ORDER BY와 마찬가지로 컬럼의 별칭 정의는 SELECT 절에서만 가능하다.
-- [SQL-5-5-7]
-- GROUP BY에 별칭을 사용, 권장하지 않음
SELECT DATE_FORMAT(T1.OrdDtm,'%Y%m') OrdYm ,COUNT(*) OrdCnt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20221001','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20221231','%Y%m%d')
AND T1.ShopId = 'S263'
GROUP BY OrdYm -- > 권장하지 않음!
ORDER BY OrdYm;
-- [SQL-5-5-8]
-- GROUP BY에 원래 컬럼을 사용, 권장
SELECT DATE_FORMAT(T1.OrdDtm,'%Y%m') OrdYm ,COUNT(*) OrdCnt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20221001','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20221231','%Y%m%d')
AND T1.ShopId = 'S263'
GROUP BY DATE_FORMAT(T1.OrdDtm,'%Y%m') -- > 권장
ORDER BY OrdYm;
-- [SQL-5-5-9]
-- GROUP BY 에서 컬럼 별칭을 정의할 수 없다.
-- ORDER BY도 마찬가지다.
SELECT DATE_FORMAT(T1.OrdDtm,'%Y%m') OrdYm ,COUNT(*) OrdCnt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20221001','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20221231','%Y%m%d')
AND T1.ShopId = 'S263'
GROUP BY DATE_FORMAT(T1.OrdDtm,'%Y%m') OrdYm -- > 별칭으로 오류
ORDER BY OrdYm;
SQL
복사
BOOSTER QUIZ
BOOSTER QUIZ ERD
BOOSTER QUIZ 5-5-1
(SQL-1)과 (결과-1)을 참고해, (SQL-2)를 실행하면 나올 (결과-2)를 채우시오.
•
SQL을 실행하지 않고 (결과-2)를 예측해서 채우시오.
•
(SQL-2)는 (SQL-1)을 GROUP BY 처리한 SQL이다.
-- (SQL-1)
SELECT T1.OrdNo ,T1.OrdDtm
FROM startdb.Ord T1
WHERE T1.ShopId = 'S092'
AND T1.OrdDtm>=STR_TO_DATE('20210901','%Y%m%d')
AND T1.OrdDtm< STR_TO_DATE('20211101','%Y%m%d')
ORDER BY T1.OrdNo ASC;
-- (결과-1)
OrdNo OrdDtm
----- -------------------
16617 2021-09-04 13:30:00
16831 2021-09-06 13:30:00
16896 2021-09-08 13:30:00
17119 2021-09-13 13:30:00
17202 2021-09-21 13:30:00
17776 2021-10-04 13:30:00
17990 2021-10-06 13:30:00
SQL
복사
-- (SQL-2)
SELECT DATE_FORMAT(T1.OrdDtm,'%Y%m') YM ,COUNT(*) CNT
FROM startdb.Ord T1
WHERE T1.ShopId = 'S092'
AND T1.OrdDtm>=STR_TO_DATE('20210901','%Y%m%d')
AND T1.OrdDtm< STR_TO_DATE('20211101','%Y%m%d')
GROUP BY DATE_FORMAT(T1.OrdDtm,'%Y%m')
ORDER BY YM ASC;
-- (결과-2)
SQL
복사
BOOSTER QUIZ 5-5-2
S019 매장의 2022년 주문 데이터를 주문년월별로 주문 건수와 주문금액 합계를 구해주세요.
•
대상 테이블: 주문(Ord)
•
조회 조건: ShopId가 S019 이면서 OrdDtm이 2022년인 주문 데이터
•
조회 컬럼: OrdYm, OrdCnt ,OrdAmtSum
•
추가 조건
◦
OrdYm은 OrdDtm을 년월(YYYYMM) 형태의 문자로 변형한 항목
◦
OrdYm 별로 GROUP BY 처리하시오.
◦
OrdCnt는 OrdYm 별 데이터 건수
◦
OrdAmt는 OrdYm 별 OrdAmt를 SUM 집계한 값
•
정렬 기준: OrdYm으로 내림차순 정렬하시오.
OrdYm OrdCnt OrdAmtSum
------ ------ ----------
202012 25 136500.000
202011 8 35000.000
202010 8 57000.000
… 생략 …
SQL
복사
BOOSTER QUIZ 5-5-3
2020년 주문에 대해, 주문 시간대별로 주문 건수를 알고 싶어, 가장 주문 건수가 많은 시간대를 확인하려고요.
•
대상 테이블: 주문(Ord)
•
조회 조건: OrdDtm이 2020년인 주문 데이터
•
조회 컬럼: OrdHour, OrdCnt
•
추가 조건
◦
OrdHour는 OrdDtm에서 DATE_FORMAT을 사용해 시간(%H)만 추출한 항목
◦
OrdHour 별로 GROUP BY 처리하시오.
◦
OrdCnt는 OrdHour 별 데이터 건수
•
정렬 기준: OrdCnt로 내림차순 정렬하시오.
OrdHour OrdCnt
------- ------
14 1886
13 1348
11 1305
12 1242
… 생략 …
SQL
복사
