무료 동영상 강의 https://youtu.be/I1WDnG9gNW8
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
아래는 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
컬럼 간의 계산 결과를 GROUP BY에 사용할 수도 있다. 아래는 주문일시부터 제조완료까지 몇 분이 걸렸는지 계산해 GROUP BY 항목으로 사용하는 SQL이다.
-- [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
이와 같이 시간을 계산하고, 계산된 시간 기준으로 데이터를 집계하는 것은 중요한 분석 포인트 중 하나다. 위 결과를 시각화 해보면 다음과 같다. 시각화를 통해 주문을 처리하는 시간이 8분에서 12분 사이에 집중되어 있는 것을 알 수 있다. 이를 통해 제조 완료 시간 단축이 필요한지, 지금도 괜찮은지에 대해 회의를 해볼 수 있을 것이다.
