CASE는 생각보다 다양한 곳에 사용할 수 있다.
GROUP BY 항목에 CASE 사용
GROUP BY에 컬럼을 변형해 사용할 수 있는 것처럼 GROUP BY에 CASE도 사용할 수 있다. 아래는 CASE를 적용한 항목 자체를 GROUP BY 처리하는 예제다. CASE를 사용해 ShopDv를 처리하고 ShopDv에 따라 데이터를 그룹핑한다.
-- [SQL-6-2-1]
-- GROUP BY 적용 전 SQL
-- CASE를 사용해 S001 매장과 나머지 매장으로 구분해서 ShopDv를 출력
SELECT T1.ShopId
,CASE WHEN T1.ShopId = 'S001' THEN 'S001매장' ELSE '나머지매장' END ShopDv
,T1.OrdDtm
,T1.OrdNo
,T1.OrdAmt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20230330','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20230331','%Y%m%d')
ORDER BY T1.ShopId;
ShopId ShopDv OrdDtm OrdNo OrdAmt
------ -------- ------------------- ----- ---------
S001 S001매장 2023-03-30 16:00:00 89036 4500.000
S001 S001매장 2023-03-30 16:00:00 89094 5000.000
... 생략 ...
S272 나머지매장 2023-03-30 12:30:00 88283 4000.000
S274 나머지매장 2023-03-30 12:30:00 88284 4500.000
-- [SQL-6-2-2]
-- CASE를 사용한 ShopDv 로직을 GROUP BY에 그대로 사용
SELECT CASE WHEN T1.ShopId = 'S001' THEN 'S001매장' ELSE '나머지매장' END ShopDv
,SUM(T1.OrdAmt) OrdAtm
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20230330','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20230331','%Y%m%d')
GROUP BY CASE WHEN T1.ShopId = 'S001' THEN 'S001매장' ELSE '나머지매장' END;
ShopDv OrdAtm
---------- ------------
나머지매장 13209500.000
S001매장 1399000.000
SQL
복사
아래는 주문시간대구분을 CASE로 처리한 후에, GROUP BY 처리한 예다. 어느 시간대 주문이 많은지 확인하는데 도움이 될 수 있다.
-- [SQL-6-2-3]
-- GROUP BY 적용전 SQL
-- DATE_FORMAT을 사용해 OrdDtm의 시간 부분을 추출하고 그에 따라 주문시간대구분을 처리
SELECT T1.OrdNo ,T1.OrdDtm ,DATE_FORMAT(T1.OrdDtm,'%H') 주문시간
,CASE WHEN DATE_FORMAT(T1.OrdDtm,'%H') < '10' THEN '10시이전주문'
WHEN DATE_FORMAT(T1.OrdDtm,'%H') < '12' THEN '12시이전주문'
WHEN DATE_FORMAT(T1.OrdDtm,'%H') < '17' THEN '17시이전주문'
ELSE '17시이후주문'
END 주문시간대구분
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20201205','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20201206','%Y%m%d')
ORDER BY T1.OrdDtm ASC;
OrdNo OrdDtm 주문시간 주문시간대구분
----- ------------------- -------- --------------
7585 2020-12-05 08:00:00 08 10시이전주문
7586 2020-12-05 08:00:00 08 10시이전주문
... 생략 ...
7663 2020-12-05 13:00:00 13 17시이전주문
7664 2020-12-05 13:00:00 13 17시이전주문
-- [SQL-6-2-4]
-- GROUP BY 적용후 SQL, CASE를 GROUP BY 항목으로 사용
SELECT CASE WHEN DATE_FORMAT(T1.OrdDtm,'%H') < '10' THEN '10시이전주문'
WHEN DATE_FORMAT(T1.OrdDtm,'%H') < '12' THEN '12시이전주문'
WHEN DATE_FORMAT(T1.OrdDtm,'%H') < '17' THEN '17시이전주문'
ELSE '17시이후주문'
END 주문시간대구분
,COUNT(*) 주문시간대별주문건수
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20201205','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20201206','%Y%m%d')
GROUP BY CASE WHEN DATE_FORMAT(T1.OrdDtm,'%H') < '10' THEN '10시이전주문'
WHEN DATE_FORMAT(T1.OrdDtm,'%H') < '12' THEN '12시이전주문'
WHEN DATE_FORMAT(T1.OrdDtm,'%H') < '17' THEN '17시이전주문'
ELSE '17시이후주문'
END -- 주문시간대구분: GROUP BY에서는 컬럼의 별칭을 사용하지 않도록 주의
ORDER BY 주문시간대구분 ASC;
주문시간대구분 주문시간대별주문건수
-------------- --------------------
10시이전주문 5
12시이전주문 37
17시이전주문 38
SQL
복사
집계함수 처리된 값에 CASE 사용
아래는 GROUP BY가 아닌 집계함수가 처리된 값에 CASE를 사용하는 예제다. GROUP BY의 기본 원칙은 GROUP BY 절에 컬럼만 SELECT 절에 그대로 사용할 수 있고, 나머지 컬럼은 집계함수 처리해야 한다. CASE는 집계함수 처리된 결과에 사용되고 있으므로 문제 없이 실행된다.
-- [SQL-6-2-5]
-- 집계함수가 처리된 값에 CASE를 사용
SELECT T1.ShopId
,SUM(T1.OrdAmt) OrdAmt
,CASE WHEN SUM(T1.OrdAmt) >= 300000 THEN '30만이상'
WHEN SUM(T1.OrdAmt) >= 200000 THEN '20만이상'
ELSE '20만미만' END AmtDv
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20230201','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20230301','%Y%m%d')
GROUP BY T1.ShopId
ORDER BY OrdAmt DESC;
ShopId OrdAmt AmtDv
------ ---------- --------
S001 709500.000 30만이상
S200 386500.000 30만이상
S202 348500.000 30만이상
S016 256000.000 20만이상
... 생략 ...
SQL
복사
ORDER BY에 CASE 사용
CASE는 ORDER BY 절에도 사용할 수 있다. 아래는 특정 회원의 경우 0으로 치환하고 나머지는 1로 치환해 특정 회원이 무조건 먼저 나오도록 처리한 SQL이다. 나머지 회원은 OrdAmt 순서에 따라 출력 된다.
-- [SQL-6-2-6]
SELECT T1.OrdNo
,T1.OrdDtm
,T1.MemberId
,T1.OrdAmt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= '20201224'
AND T1.OrdDtm < '20201225'
AND T1.ShopId = 'S001'
ORDER BY CASE WHEN T1.MemberId = 'M0273' THEN 0 ELSE 1 END
,T1.OrdAmt DESC;
OrdNo OrdDtm MemberId OrdAmt
----- ------------------- -------- ---------
9239 2020-12-24 11:00:00 M0273 3500.000
9370 2020-12-24 13:00:00 M1500 12500.000
9490 2020-12-24 16:00:00 M0528 12500.000
9470 2020-12-24 15:00:00 M1515 9000.000
... 생략 ...
SQL
복사
BOOSTER QUIZ
BOOSTER QUIZ ERD
BOOSTER QUIZ 6-2-1
2021년 12월 24일의 주문을 보여주세요. 그런데, S010 매장의 주문이 가장 먼저 조회되도록 해주시고, 나머지는 주문번호순으로 보여주세요.
•
대상 테이블: 주문(Ord)
•
조회 조건: OrdDtm이 2021년 12월 24일인 데이터
•
조회 컬럼: OrdNo, OrdDtm, ShopId, OrdAmt
•
추가 조건
◦
데이터 정렬시, S010 매장의 주문은 무조건 위에 나오도록 예외 처리하시오.
•
정렬 기준: S010 매장의 주문이 무조건 먼저 나오도록 처리하고, 나머지는 OrdNo 순으로 오름차순 정렬하시오.
OrdNo OrdDtm ShopId OrdAmt
----- ------------------- ------ ---------
23070 2021-12-24 10:00:00 S010 8500.000
23270 2021-12-24 13:00:00 S010 10000.000
22974 2021-12-24 07:30:00 S023 4000.000
22975 2021-12-24 07:30:00 S050 12500.000
22976 2021-12-24 07:30:00 S065 2500.000
22977 2021-12-24 08:00:00 S003 3000.000
22978 2021-12-24 08:00:00 S021 4500.000
… 생략 …
SQL
복사
BOOSTER QUIZ 6-2-2
2021년 12월 1일 주문에 대해, 주문일시와 픽업일시를 보여주고, 주문에서 픽업까지 걸린 시간에 대해 "10분초과"와 "10분이하"로 구분자를 넣어주세요.
•
대상 테이블: 주문(Ord)
•
조회 조건: OrdDtm이 2021년 12월 1일인 데이터
•
조회 컬럼: OrdNo, OrdDtm, PickupDtm, 픽업완료시간구분
•
추가 조건
◦
픽업완료시간구분은 OrdDtm에서 PickupDtm까지 걸린 시간에 따라,
◦
'10분초과' 또는 '10분이하'로 구분
◦
OrdDtm에서 PickupDtm까지 걸린 시간은 TIMESTAMPDIFF로 처리합니다.
•
정렬 기준: OrdNo로 오름차순 정렬하시오.
OrdNo OrdDtm PickupDtm 픽업완료시간구분
----- ------------------- ------------------- ----------------
19987 2021-12-01 08:00:00 2021-12-01 08:07:00 10분이하
19988 2021-12-01 08:00:00 2021-12-01 08:11:00 10분초과
19989 2021-12-01 08:00:00 2021-12-01 08:05:00 10분이하
SQL
복사
BOOSTER QUIZ 6-2-3
[BOOSTER QUIZ 6-2-2] 풀이 SQL을 활용해, 픽업완료시간구분별 주문건수를 구하시오.
픽업완료시간구분 주문건수
---------------- --------
10분이하 2
10분초과 1
SQL
복사
BOOSTER QUIZ 6-2-4
S001과 S002 매장의 2022년 주문을, 매장별로 상반기, 하반기로 구분해 주문건수를 구해주세요.
•
대상 테이블: 주문(Ord)
•
조회 조건: ShopId가 S001, S002인 매장의 OrdDtm이 2022년인 모든 데이터
•
조회 컬럼: ShopId, 반기구분, OrdCnt
•
추가 조건
◦
반기구분은 OrdDtm을 CASE 처리
▪
22년1월부터 22년 6월말까지는 '상반기', 22년 7월부터는 '하반기'로 구분
◦
ShopId, 반기구분별 GROUP BY 처리합니다.
◦
OrdCnt는 ShopId, 반기구분별 주문건수입니다.
•
정렬 기준: ShopId로 오름차순 후에, 반기구분으로 오름차순하시오.
ShopId 반기구분 OrdCnt
------ -------- ------
S001 상반기 551
S001 하반기 2444
S002 상반기 3
S002 하반기 73
SQL
복사
