[3주차]SQL_replace, substring, concat, if, case 조건문

• replace(바꿀컬럼, 현재 값, 바꿀 값)
• substring(조회컬럼, 시작위치, 글자 수)
• cancat(붙일컬럼1, 붙일컬럼2, 붙일컬럼3)
• if(조건, 조건 충족할 때, 조건 충족하지 않을 때)
• case when 조건1 then 값1
  when 조건2 then 2
  else(나머지 조건 없다면 생략 가능) 값3
  end
  

[실습] 식당 명의 ‘Blue Ribbon’ 을 ‘Pink Ribbon’ 으로 바꾸기

select restaurant_name "원래이름",
replace (restaurant_name, 'Blue Ribbon', 'Pink Ribbon') "바뀐이름"
from food_orders

여기까지 맞았고, 잘 바뀌었는지 확인하기 위해 where로 필터

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

[실습] 주소의 ‘문곡리’ 를 ‘문가리’ 로 바꾸기

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

[실습] 서울 음식점들의 주소를 전체가 아닌 ‘시도’ 만 나오도록 수정

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

이 구문에서 헤맨 이유, 함수 에는 공백 없이 바로 괄호'(' 써줘야 함
where + 컬럼명 먼저 와야 함

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

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

[실습] 서울 지역의 음식 타입별 평균 음식 주문금액 구하기 (출력 : ‘서울’, ‘타입’, ‘평균 금액’)

select cuisine_type,
addr,
avg(price)
from food_orders 
where addr like '%서울%'
group by 1

이 경우에 서울 추출 데이터는 맞지만 좀더 깔끔히 하기 위해서 주소를 '서울'만 다시 뽑아보자

select cuisine_type "음식종류",
substring(addr, 1, 2) "주소",
avg(price)
from food_orders 
where addr like '%서울%'
group by 1

[실습] 이메일 도메인별 고객 수와 평균 연령 구하기

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

substring 구문 연습을 위해 이메일 id가 8자로 통일됨

[실습] [지역(시도)] 음식점이름 (음식종류)’ 컬럼을 만들고, 총 주문건수 구하기

elect concat('[',substring(addr, 1, 2),'] ', restaurant_name, ' (', cuisine_type, ')') "지역별음식점", 
count(order_id)
from food_orders
group by 1

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

select restaurant_name, 
if(cuisine_type = 'Korean', '한식', '기타') "음식종류",
cuisine_type
from food_orders 

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

select addr,
if(addr like '%평택%', replace(addr, '문곡리', '문가리'), addr)
from food_orders

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

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

위에는 내가 쓴 답, 아래는 해답
도메인만을 추출해야 하기 때문에 substring도 써줘야 함.

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

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

select cuisine_type,
restaurant_name,
case when cuisine_type = 'Korean' then '한식'
	        when cuisine_type = 'Japanese' or 'Chienese' then '아시아'
	        else '기타' end "음식점분류"
from food_orders

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

select if(quantity >= 2, price/quantity, price) "음식단가",
price,
quantity 
from food_orders 

case함수도 가능하지만 조건이 복잡하지 않을 때는 if가 낫다

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

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

또는 특별시, 광역시 조건을 한줄에 붙여서도 가능하다

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세 이상, 30세 미만의 고객의 나이와 성별로 그룹 나누기 (이름도 같이 출력)

select name,
gender,
age
from customers
where age between 10 and 29
group by 2, 3

이건 문제의 뜻을 전혀 잘못 이해함. 10대남자, 20대여자 등으로 새로운 컬럼을 생성해야 한다.

select name,
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

[실습] 음식 단가, 음식 종류 별로 음식점 그룹 나누기
(Korean = 한식 / Japanese, Chinese, Thai, Vietnamese, Indian = 아시아식 / 그외 = 기타)
(가격 = 5000, 15000, 그 이상)

select price,
cuisine_type,
restaurant_name,
quantity,
price/quantity "음식단가", 
case when cuisine_type = 'Korean' and price/quantity <= 5000 then '저가한식'
	 when cuisine_type = 'Korean' and price/quantity <= 15000 then '중가한식'
	 when cuisine_type = 'Korean' and price/quantity > 15000 then '고가한식'
	 when cuisine_type in ('Japanese' 'Chinese', 'Thai', 'Vietnamese', 'Indian') and price/quantity <= 5000 then '저가아시아식'
	 when cuisine_type in ('Japanese' 'Chinese', 'Thai', 'Vietnamese', 'Indian') and price/quantity <=15000 then '중가아시아식'
	 when cuisine_type in ('Japanese' 'Chinese', 'Thai', 'Vietnamese', 'Indian') and price/quantity > 15000 then '고가아시아식'
	 when cuisine_type not in ('Korean', 'Japanese' 'Chinese', 'Thai', 'Vietnamese', 'Indian') and price/quantity <= 5000 then '저가기타'
	 when cuisine_type not in ('Korean', 'Japanese' 'Chinese', 'Thai', 'Vietnamese', 'Indian') and price/quantity <=15000 then '중가기타'
	 when cuisine_type not in ('Korean', 'Japanese' 'Chinese', 'Thai', 'Vietnamese', 'Indian') and price/quantity > 15000 then '고가기타'
	 end 
from food_orders

[실습] 지역과 배달시간을 기반으로 배달수수료 구하기 (식당 이름, 주문 번호 함께 출력)
(지역 : 서울, 기타 - 서울일 때는 수수료 계산 1.1, 기타일 때는 곱하는 값 없음
시간 : 25분, 30분 - 25분 초과하면 음식 가격의 5%, 30분 초과하면 음식 가격의 10%)

이러한 경우 산식을 먼저 구해야 한다
배달수수료 = price 배달시간 수수료율 * 지역 수수료율

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

2) [실습] 주문 시기와 음식 수를 기반으로 배달할증료 구하기
(주문 시기 : 평일 기본료 = 3000 / 주말 기본료 = 3500
음식 수 : 3개 이하이면 할증 없음 / 3개 초과이면 기본료 * 1.2)
배달할증료 = 주문시기 기본료 + 음식수 할증

select day_of_the_week,
quantity, 
price,
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 

if문만 써서 해결하는 답
각 조건이 2개씩만 있을 때 유용할 것 같다

select order_id,
       price,
       quantity,
       day_of_the_week,
       if(day_of_the_week='Weekday', 3000, 3500)*(if(quantity<=3, 1, 1.2)) "할증료"
from food_orders

[실습] 다음의 조건으로 배달시간이 늦었는지 판단하는 값을 만들어주세요.

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

select delivery_time,
day_of_the_week,
case when day_of_the_week = 'Weekday' and delivery_time >= 25 then '늦음'
	 when day_of_the_week = 'Weekend' and delivery_time >= 30 then '늦음'
	 else'정상도착' end 
from food_orders