[3주차] REPLACE, SUBSTR, CONCAT, IF CASE

3주차에서는 문자를 바꿀 수 있는 REPLACE와 원하는 글자를 뽑아올 수 있는 SUBSTR

문자들을 막 그냥 합칠 수 있는 CONCAT, 그리고 조건문인 IF와 CASE 문을 배웠다

 

 

select restaurant_name "원래 상점명",

replace(restaurant_name, 'Blue', 'Pink') "바뀐 상점명" # 원래 상점명에서 Blue가 들어간 것을 Pink로 바꿈

from food_orders

where restaurant_name like '%Blue Ribbon%'

위 쿼리는 가게 이름에 Blue Ribbon이 들어간 것들을 대상으로 Blue를 -> Pink로 replace 해준다.

함수의 의미가 상당히 직관적이어서 좋다.

 

SELECT addr "원래 주소",

REPLACE (addr, '문곡리','문가리')"바뀐 주소"

from food_orders fo

WHERE addr like '%문곡리%'

이것도 주소에 문곡리가 들어간 것들을 대상으로 문곡리를 문가리로 바꿔준다.

 

# 주소 컬럼에서 첫번째 글자부터 두글자만 가져와서 출력

# = 서울

SELECT addr "원래 주소"

,substr(addr, 1, 2) "시도" #substr(컬럼,시작 위치,글자 수)

from food_orders

where addr LIKE '%서울특별시%'

SUBSTR은 SUBSTR(컬럼명, 시작위치, 글자 수) 이렇게 사용하는데

위와 같이 서울특별시라는 값이 있고 SUBSTR(addr, 1, 2)를 주면

첫 번째부터 시작해서 2글자를 가져오기 때문에 "서울"이 출력된다.

 

## concat (문자들을 합치는 함수)

# concat(붙이고 싶은 값1, 붙이고 싶은 값2, ......) 안에 substr도 넣어도된다.

select restaurant_name "원래 이름",

addr "원래 주소",

concat(restaurant_name, '-', cuisine_type) "음식타입별 음식점",

concat('[', substring(addr, 1, 2), '] ', restaurant_name) "바뀐 이름"

from food_orders

where addr like '%서울%'

concat은 문자들을 합 칠때 사용한다.

위 쿼리에서 "바뀐 이름"을 조회하게 되면 음식점 이름을 조회하게 되는데

앞에 [서울]이 붙어서 조회가 된다.

concat 안에 substr을 사용할 수도 있다.

"음식 타입별 음식점" 을 조회하면 음식점 이름과 음식점 타입 사이에 하이픈이 추가되어 조회된다

-> Hangawi - Korean 이런 식

 

 

##실습

#서울 지역의 음식 타입별 평균 음식 주문금액 구하기

SELECT addr,cuisine_type ,avg(price)

from food_orders fo

where addr like "%서울%"

group by cuisine_type

서울 지역 가게들을 대상으로 하기 때문에

where 절에 %서울% 조건을 주고

평균 주문금액을 구하고 타입별로 group by를 해준다.

 

SELECT SUBSTR(addr,1,2)"지역", cuisine_type , avg(price)"평균 금액"

from food_orders fo

where addr LIKE "%서울%"

GROUP by 1,2 # 이렇게 해도 된다.

이렇게 하면 "지역" 컬럼엔 "서울"이 들어가고 음식 타입과 평균 금액이 순차적으로 조회된다.

그룹화를 할 때 저렇게 번호로 명시해도 된다.

 

 

#이메일 도메인별 고객 수와 평균 연령 구하기

SELECT substr(email,10)"도메인" ,avg(age), count(1) "고객 수"

from customers c

group by

 1

order by 3 desc

#group by 1

이메일 도메인별로 고객수와 평균 연령을 구해야 하는데

일단 이메일의 형식을 보면 10번째 글자 이후로 도메인이 온다.

그래서 substr로 email의 10번째 글자 이후를 "도메인"에 담고 도메인별로 평균 연령과 고객 수를 조회한다.

 

 

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

 

SELECT concat('[',SUBSTR(addr,1,2),']',restaurant_name,' (',cuisine_type,')') "음식점", count(*) "총 주문 건수"

from food_orders fo

group by 1

이거는 위에서 배운 concat을 사용하면 된다.

 

그러면 이렇게 보기 좋게 출력이 된다.

 

---

IF, CASE

##IF,CASE

#if (조건, 참일 때 쓸 값, 거짓일 때 쓸 값)

select restaurant_name,

cuisine_type "원래 음식 타입",

if(cuisine_type='Korean', '한식', '기타') "음식 타입" #if cuisine_type이 'korean' 이라면 '한식' 아니면 '기타' 로 음식 타입 컬럼에 명시

from food_orders

IF 문의 형식은 IF( 조건, 조건O -> 값, 조건X -> 값)이다.

위 예시는 요리 타입이 'Korean'이면 '한식' 아니면 '기타'를 할당하게 된다.

 

# addr에 평택군이 들어가있으면, 문곡리를 문가리로 바꾸고, 들어있지 않다면 그냥 addr 그대로 출력

select addr "원래 주소",

if(addr like '%평택군%', replace(addr, '문곡리', '문가리'), addr) "바뀐 주소"

from food_orders

where addr like '%문곡리%'

IF문에 like 와 replace 같은 것들도 함께 사용이 가능하다.

위 예시는 주소에 평택군이 들어가 있으면 주소의 문곡리를 문가리로 바꾸고 그렇지 않다면 addr을

그대로 사용하겠다는 의미이다.

 

#만약 email에 gmail을 포함하고 있을때는 gmail을 @gmail로 바꿔라 그리고 10번째 글자부터 끝까지 가져와라

select substr(if(email like '%gmail%', replace(email, 'gmail', '@gmail'), email), 10) "이메일 도메인",

count(customer_id) "고객 수",

avg(age) "평균 연령"

from customers

group by 1

이것도 복잡한 거 같지만 천천히 읽어보면 답이 나온다.

만약 email에 gmail이 들어간다면 gmail을 @gmail로 바꾸고 10번째 글자부터 끝까지 가져오라는 의미이다.

앞서 보여준 도메인별 고객 수와 평균 연령 구하기에서

gmail만 g가 잘려서 나왔기 때문에 위 방법을 통해 해결을 했다.

 

#case when ~ end

 

#음식 타입이 korean이면 한식, 재패니즈, 차이니즈면 아시아 그 외는 기타로 음식 타입 컬럼 형성

SELECT case when cuisine_type = 'Korean' then '한식'

when cuisine_type in ('Japanese','Chinese')then '아시아'

else '기타' end "음식 타입"        해당하는 경우가 없을 때

,cuisine_type

FROM food_orders fo

Case 문은 case when 조건 then 원하는 것1

                  case when 조건 then 원하는 것2

                  else 위에서 해당하는 경우가 없을 때 end 컬럼 명

이런 식이다.

 

# 퀀티티가 1일때는 가격을 그대로 쓰고, 퀀티티가 2 이상일 때는 퀀티티를 가격으로 나누고 음식 단가 컬럼을 형성

select order_id,

price,

quantity,

case when quantity=1 then price

when quantity>=2 then price/quantity end "음식 단가"

from food_orders

퀀티티가 1일 때는 가격을 그대로 사용하고

퀀티티가 2이상일 때는 가격을 퀀티티로 나누어서 음식 단가에 할당한다.

 

# 10세 이상, 30세 미만의 고객의 나이와 성별로 그룹 나누기

SELECT case when (gender = 'male') and (age BETWEEN 10 and 19) then "10대 남성"

when (gender = 'female') and (age between 10 and 19) then "10대 여성"

when (gender = 'male') and (age BETWEEN 20 and 29) then "20대 남성"

when (gender = 'female') and (age BETWEEN 20 and 29) then "20대 여성"

end "고객 분류",

name,

age,

gender

FROM customers

WHERE age BETWEEN 10 and 29

10세 이상, 30세 미만이므로 10대 남성,10대 여성, 20대 남성, 20대 남성으로 나누었다.

10세 이상 30세 미만의 데이터만 필요하기 때문에 where 조건으로 우선 필터링 해주고,

case문을 이용해 조건으로는 성별이 남자고 between을 이용해 10살 이상 19살 이하면 10대 남성

이런 식으로 각각 나누었다.

 

# 음식 단가, 음식 종류 별로 음식점 그룹 나누기

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

위 실습은 그냥 노가다라 복붙했는데

이때까지 한 것과 다를 거 없는 case 문이다.

case문 안에 산술연산, between, in, not in도 함께 사용이 가능하다.

 

 

## 지역과 배달시간을 기반으로 배달 수수료 구하기

# 배달 시간이 30분 초과면 수수료 10%, 25분 초과이면 0.05%, 배달 지역이 서울이라면 * 1.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 "수수료",

restaurant_name,

order_id ,

price,

delivery_time ,

addr

FROM food_orders fo

case문을 이용하여 배달 시간이 30분 초과면 수수료 10%가 생기게 했다. 근데 만약 배달 지역이 서울이라면

*1.1을 하라는 조건이 있기 때문에 if문에 like를 사용하여 서울지역인지 확인하고 1.1을 곱해주었다.

 

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

# (평일 기본료 = 3000, 주말 기본료 = 3500 / 음식수 3개 이상이면 기본료 * 1.2, 3개 이하이면 할증 x)

SELECT case when day_of_the_week = 'Weekend' then 3500 * if(quantity > 3, 1.2, 1)

when day_of_the_week = 'Weekday' then 3000 * if(quantity > 3, 1.2, 1)

end "할증료",

restaurant_name ,

order_id

FROM food_orders fo

위 문제와 비슷하게 주말이면 3500 평일이면 3000원의 배달료를 가지고

음식 수에 따른 추가 할증을 if 문으로 만들었다.

 

## 3주차 숙제

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

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

 

SELECT case when day_of_the_week = 'Weekday' and delivery_time >= 25 then "late"

when day_of_the_week = 'Weekend' and delivery_time >= 30 then "late"

else "not late" end as "late or not",

day_of_the_week ,

delivery_time

FROM food_orders fo

3주차 숙제

 

case 문과 and를 이용하여 주말이면서 배달 시간이 25분 이상이면 지각

평일이면서 30분 이상이면 지각

그 외의 경우는 지각하지 않은 경우로 판단