[SQL]3주차_IF, CASE (조건에 따라 포맷을 다르게 지정하기)

조건에 따라 다른 방법을 적용하고 싶을 때 - If 문

기본 구조

if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)

 

[실습1] 음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Korean’ 이 아닌 경우에는 ‘기타’ 라고 지정

select restaurant_name,
       cuisine_type "원래 음식 타입",
       if(cuisine_type='Korean', '한식', '기타') "음식 타입"
from food_orders

 

[실습2] 02. 번 실습에서 ‘문곡리’ 가 평택에만 해당될 때, 평택 ‘문곡리’ 만 ‘문가리’ 로 수정

select addr "원래 주소",
       if(addr like '%평택군%', replace(addr, '문곡리', '문가리'), addr) "바뀐 주소"
from food_orders
where addr like '%문곡리%'

 

[실습3] 03. 번 실습에서 잘못된 이메일 주소 (gmail) 만 수정을 해서 사용

select substring(if(email like '%gmail%', replace(email, 'gmail', '@gmail'), email), 10) "이메일 도메인",
       count(customer_id) "고객 수",
       avg(age) "평균 연령"
from customers
group by 1

 

---

조건을 여러가지 지정하고 싶을 때 - Case 문

 

기본 구조

case when 조건1 then 값(수식)1
     when 조건2 then 값(수식)2
     else 값(수식)3
end

 

[실습1] 음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Japanese’ 혹은 ‘Chienese’ 일 때는 ‘아시아’, 그 외에는 ‘기타’ 라고 지정

select restaurant_name,
       cuisine_type AS "원래 음식 타입",
       case when (cuisine_type='Korean') then '한식'
       else '기타'
       end as " 음식 타입"
from food_orders

 

[실습2] 음식 단가를 주문 수량이 1일 때는 음식 가격, 주문 수량이 2개 이상일 때는 음식가격/주문수량 으로 지정

select price,
	   quantity,
	   CASE WHEN (quantity = 1) THEN price
	   	    WHEN (quantity >= 2) THEN price/quantity END "음식단가"
from food_orders
/* else는 조건에 맞는게 없다면 생략가능

 

[실습3] 주소의 시도를 ‘경기도’ 일때는 ‘경기도’, ‘특별시’ 혹은 ‘광역시’ 일 때는 붙여서, 아닐 때는 앞의 두 글자만 사용

select restaurant_name,
       addr,
       case when addr like '%경기도%' then '경기도'
            when addr like '%특별%' or addr like '%광역%' then substring(addr, 1, 5)
            else substring(addr, 1, 2) end "변경된 주소"
from food_orders

 

* '=' 와 'LIKE'의 차이

'=' 는 완벽히 일치할 때, 'LIKE' 는 그보다 조금 유연하게 사용 가능.

위 실습에서는 '%경기도%'와 완벽히 일치하는게 아니라 경기도가 들어간 글자를 찾아야 하므로 'LIKE'를 사용해야 한다.

---

조건을 사용할 수 있는 경우 

• 새로운 카테고리 만들기
  • 음식 타입과 같이 새로운 카테고리를 만들 수 있습니다.
    • 한국 음식, 아시아 음식, 미국 음식, 유럽 음식 이런 식의 새로운 cuisine_category 를 생성할 수 있음
  • 고객들의 분류도 만들 수 있습니다.
    • 10대 여성, 10대 남성, 20대 여성, 20대 남성 등, 이런 식의 성별과 나이별로 새로운 고객 군 카테고리를 생성 가능
• 연산식을 적용할 조건 지정하기
  • 수수료를 계산시 현금 사용, 카드사용을 나누고자 할 때
  • 현금일 때의 수수료율과 카드일 때의 수수료율이 다르다면, 연산식을 만들 때 if 문 혹은 case 문으로 각각 다른 수수료율 혹은 수수료 계산 방식을 적용할 수 있습니다.
• 다른 문법 안에서 적용하기
  • if, case 문 안에 다른 문법이나 연산을 넣을 수도 있지만, 다른 문법 안에 조건문을 넣을 수도 있습니다.
  • 예를 들어 concat 문으로 여러 컬럼을 합칠 때, rating 이 있을 때는 rating 을 넣어주고 없을 때는 아무것도 넣지 않도록, concat 안에 if 문을 넣어줄 수 있습니다.

---

SQL로 간단한 User Segmentation 해보기

 

[실습] 10세 이상, 30세 미만의 고객의 나잇대와 성별로 그룹 나누기 (이름도 같이 출력)

select name,
       age,
       gender,
       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 "그룹" 
from customers
where age between 10 and 29

 

 

[실습] 지역과 배달시간을 기반으로 배달수수료 구하기 (식당 이름, 주문 번호 함께 출력)

(지역 : 서울, 기타 - 서울일 때는 수수료 계산 * 1.1, 기타일 때는 곱하는 값 없음 시간 : 25분, 30분 - 25분 초과하면 음식 가격의 5%, 30분 초과하면 음식 가격의 10%)

select  restaurant_name,
		order_id ,
		price ,
		delivery_time ,
		addr,
		case when delivery_time > 30 then price * 0.1 * if(addr like '%서울%', 1.1, 1)
			when delivery_time between 26 and 30 then price * 0.05 * if(addr like '%서울%', 1.1, 1)
			else 0 END "수수료"
from food_orders

 

 

 

[실습] 주문 시기와 음식 수를 기반으로 배달할증료 구하기

(주문 시기 : 평일 기본료 = 3000 / 주말 기본료 = 3500 음식 수 : 3개 이하이면 할증 없음 / 3개 초과이면 기본료 * 1.2)

--if문 활용
select order_id,
       price,
       quantity,
       day_of_the_week,
	IF (day_of_the_week = 'Weekday',3000,3500) * IF(quantity > 3,1.2,1) "할증료"
from food_orders


--case문 활용
select  restaurant_name,
		order_id ,
		day_of_the_week ,
		quantity,
		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 "배달할증료"
from food_orders