업무 필요한 문자 포맷이 다를 때

01. REPLACE

레스토랑 이름에서 'Blue'를 'Pink'로 바꾸기

select restaurant_name "원래 상점명",
       replace(restaurant_name, 'Blue', 'Pink') "바뀐 상점명"
from food_orders
where restaurant_name like '%Blue Ribbon%' 

주소명에서 문곡리를 문가리로 바꾸기

SELECT addr, 
	   REPLACE(addr, '문곡리', '문가리') "바뀐주소"
from food_orders 
where addr LIKE "%문곡리%"

02. SUBSTRING과 CONCAT

주소명에서 첫번째순서(1)에서 글자갯수(2) 만큼만 추출하기

select addr "원래 주소",
       substr(addr, 1,2) "시도"
from food_orders
where addr like '%서울특별시%'

여러칼럼의 텍스트를 concat으로 합치기

select restaurant_name "원래 이름",   
       addr "원래 주소",
       concat('[', substring(addr, 1, 2), '] ', restaurant_name) "바뀐 이름"
from food_orders
where addr like '%서울%'

이메일 칼럼에서 도메인만 빼오기

select substring(email,10) "도메인", 
	   COUNT(1) as "고객수",
	   avg(age) as "평균연령"
from customers 
group by 1

실습 : (서울시에 있는 음식점은 ‘[서울] 음식점명’ 이라고 수정)

select concat('[', substr(addr, 1,2),']',restaurant_name, ' (',cuisine_type,')') "음식", 
	  count(1) "주문건수"  
from food_orders
group by 1

조건문

01. 조건에 따라 다른 방법을 적용하고 싶을때 쓰는 IF

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

IF문 안에 REPLACE, SUBSTRING 문을 삽입할 수 있다!

평택군에 있는 문곡리만 문가리로 바꿔주기

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

메일안에서 gmail > @gmail 로 대체한뒤 이메일 도메인만 추출하기

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

02. 여러개의 조건이 생길때는 CASE 문 (Case When else end)

음식 타입이 'Korean'일 때는 한식, Japanese, Chinese 일경우는 아시아, 아닐 경우는 기타

select CASE when cuisine_type ='Korean' then '한식'
			when cuisine_type in ('Japanese','Chinese') then '아시아'
		    else '기타' end "음식타입"
from food_orders

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

select order_id,
       price,
       quantity,
       case when quantity=1 then price
            when quantity>=2 then price/quantity end "음식 단가"
from food_orders

주소가 경기도를 포함할 때는 경기도로/특별이나 광역을 포함할 때는 첫번째 글자부터 5개 글자 추출, 나머지는 첫 두글자 추출

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

10대/20대/여/남으로 군집화(세분화도 가능하다)

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
where age between 10 and 29

실습

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

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

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 "수수료", 
		restaurant_name,
		order_id,
		price,
		delivery_time,
		addr
from food_orders

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

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

SELECT case when day_of_the_week='weekday' then 3000 * if(quantity>3,1.1,1)
			when day_of_the_week='weekend' then 3500 * if(quantity>3,1.1,1) end "배달할증료",
		restaurant_name,
		order_id,
		day_of_the_week,
		quantity
from food_orders

03(숙제) 다음의 조건으로 배달시간이 늦었는지 판단하는 값을 만들어주세요.

• 주중 : 25분 이상
• 주말 : 30분 이상

SELECT order_id, 
		restaurant_name ,
		day_of_the_week ,
		delivery_time,
		case when day_of_the_week = 'weekday' then if(delivery_time>25, 'late', 'On-time')
			when day_of_the_week = 'weekend' then if(delivery_time>30, 'late', 'On-time') end "지연여부"
from food_orders