#8. IF/CASE 실습

1. User  Segmentation (유저 분할)

1_1. 10세 이상, 30대 미만의 고객 나이와 성별로 그룹 나누기, 이름도 같이 출력

여기서 키워드는

1. 10세 이상, 30대 미만의 고객 특정

2. 고객 나이와 성별로 그룹 분할

로 들 수 있을 것이다.

 

코드

SELECT case when (age between 10 and 19) and gender = 'male' then '10대 남성'
		when (age between 10 and 19) and gender = 'female' then '10대 여성'
		when (age between 20 and 29) and gender = 'male' then '20대 남성'
		when (age between 20 and 29) and gender = 'female' then '20대 여성' 
	end '고객 분류',
	name, age, gender
FROM customers c 
WHERE age BETWEEN 10 and 29
order by 1

 

case 문을 통해 조건을 나누었다.

10대 남성, 10대 여성, 20대 남성, 20대 여성 총 4가지로 나누어 '고객 분류' 라는 이름으로 조회하고

name, age, gender 컬럼 또한 같이 출력시켰다.

 

where 절을 통해 키워드 1번인 10대 이상, 30대 미만의 고객을 특정하였다.

 

다만 출력 후 결과가 다소 보기 더러운 감이 있어 order by 1 을 통해 오름차순 정렬을 진행했다.

 

실행 결과

사진에는 10대 남성의 수가 많아 이만 보이지만, 나이대+성별 별로 조회된 결과임을 알 수 있다.

 

1_2. 음식 단가, 음식 종류 별로 음식점 그룹 나누기

조건1. korean = 한식, japanese, chinese, thai, vietnamese, indian = 아시안식, 그외 = 기타

조건2. 가격 = 5000, 15000, 그 이상

 

이 실습 문제는 코드 작성 자체는 어렵지 않지만 원하는 조건이 많아 내용이 길어진다.

키워드 먼저 살펴보면

1. 음식 종류를 CASE 문을 통해 한식, 아시안식, 기타 로 나누기

2. 음식 단가를 CASE 문을 통해 5000, 15000, 그 이상 으로 나누기

 

총 조건의 갯수가 1번 3개 * 2번 3개 = 9개로 많은 감이 있다.

 

코드

select restaurant_name,
       price/quantity "단가",
       cuisine_type,
       order_id,
       case when (price/quantity <5000) and cuisine_type='Korean' then '한식1'
            when (price/quantity between 5000 and 15000) and cuisine_type='Korean' then '한식2'
            when (price/quantity > 15000) and cuisine_type='Korean' then '한식3'
            when (price/quantity <5000) and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '아시아식1'
            when (price/quantity between 5000 and 15000) and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '아시아식2'
            when (price/quantity > 15000) and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '아시아식3'
            when (price/quantity <5000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '기타1'
            when (price/quantity between 5000 and 15000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '기타2'
            when (price/quantity > 15000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '기타3' end "식당 그룹"
from food_orders

 

실행 결과

어쨌거나 원했던 대로 출력된 모습을 알 수 있다.

 

2. 조건문을 통한 다른 수식 적용

2_1. 지역, 배달시간 기반으로 배달 수수료 구하기, 식당 이름/주문 번호 같이 출력

조건1. 지역 : 서울, 기타 - 서울일 때는 수수료 계산 * 1.1, 기타일 때는 곱하는 값 없음

조건2. 시간 : 25분, 30분 - 25분 초과하면 음식 가격의 5%, 30분 초과하면 음식 가격의 10%

 

식당 이름과 주문 번호를 출력하는 것은 문제가 되지 않는다.

가장 까다로운 것은 지역과 배달시간 기반으로 배달 수수료를 구하는 것인데 공식을 차분히 살펴보자

 

먼저 조건1의 경우 지역을 '서울'과 '기타' 로 나눈다.

주소가 서울일 경우 수수료를 원가의 1.1배를 곱하고 그렇지 않은 경우 1배를 그대로 적용한다.

이는 수수료가 구해진 이후 그에 추가적 계산을 더하는 부분이 될 것이다.

 

조건2의 경우 수수료 원가를 구하는 공식이 되는데,

먼저 25분, 30분 초과를 기준으로 2가지 케이스를 나눌 수 있다.

배달 시간이 25분을 초과하면 음식 가격의 0.05배, 30분을 초과하면 음식 가격의 0.1배이다.

 

이 조건들을 모두 따져 나온 코드는 다음과 같다.

SELECT case when delivery_time >30 then price * 0.1 * if(addr like '%서울%', 1.1, 1)
			when delivery_time > 25 then price * 0.05 * if(addr like '%서울%', 1.1, 1)
			else 0 end as "수수료",
	restaurant_name as "식당 이름",
	order_id as "주문 번호"
FROM food_orders fo

 

여기서 궁금한 점이 하나 생길 수 있는데, 이 delivery_time > N 과 관련해서이다.

배달 시간이 25분 초과인 경우하고, 30분 초과인 경우하고 같은 경우가 생길 수 있는데, 이를 어떻게 특정하냐 라고 물을 수 있지만

코드를 작동할 경우 먼저

delivery_time > 30을 통해 배달 시간이 30분을 초과하는 경우를 먼저 추출해낸다.

그 이후에 delivery_time > 25를 실행하기에 30분을 초과하는 경우 자체는 이미 모두 걸러져있어 26 ~ 29분의 경우만 따지게 되는 것이다.

 

더 명확하게 코드를 작성하고 싶다면

delivery_time between 26 and 30 then ~

다음과 같이도 사용할 수 있다.

 

실행 결과

 

 

2_2. 주문 시기와 음식 수를 기반으로 배달 할증료 구하기

조건1. 주문 시기 : 평일 기본료 = 3000, 주말 기본료 = 3500

조건2. 음식 수 : 3개 이하일 경우 할증X, 3개 초과이면 할증 = 기본료 * 1.2

 

코드 구조가 2_1번 실습과 유사해 설명은 없이 바로 코드와 실행 결과로 넘어간다.

 

코드

SELECT case when day_of_the_week = 'weekday' then 3000 * if(quantity > 3, 1.2, 1)
			when day_of_the_week = 'weekend' then 3500 * if(quantity > 3, 1.2, 1)
			end as "배달할증료",
	restaurant_name as "식당 이름",
	order_id as "주문 번호"
FROM food_orders fo

 

실행 결과