3주차 목표
Query 결과를 업무에 바로 사용할 수 있도록 문자 (워딩) 를 다듬는다
수치계산 및 문자를 다듬을 때, 조건별로 다르게 적용한다
수치계산과 문자 연산이 되지 않는 경우를 배우고, 에러를 수정한다
Query 결과를 바로 사용할 수 있도록 문자 데이터의 형태로 변경
( REPLACE, SUBSTRING, CONCAT )
REPLACE
특정 문자를 다른 문자로 바꾸기
replace(바꿀 컬럼, 현재 값, 바꿀 값)
EX) 식당 명의 ‘Blue Ribbon’ 을 ‘Pink Ribbon’ 으로 바꾸기
select restaurant_name "원래 상점명",
replace(restaurant_name, 'Blue', 'Pink') "바뀐 상점명"
from food_orders
where restaurant_name like '%Blue Ribbon%'
SUBSTRING(SUBSTR)
원하는 문자만 남기기
substr(조회 할 컬럼, 시작 위치, 글자 수)
EX) 서울 음식점들의 주소를 전체가 아닌 ‘시도’ 만 나오도록 수정
select addr "원래 주소",
substr(addr, 1, 2) "시도"
from food_orders
where addr like '%서울특별시%'
CONCAT
여러 컬럼의 문자를 합치기
concat(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은 값3, .....)붙일 수있는문자의 종류 : 컬럼, 한글, 영어, 숫자, 기타 특수문자
EX) 서울시에 있는 음식점은 ‘[서울] 음식점명’ 이라고 수정
select restaurant_name "원래 이름",
addr "원래 주소",
concat('[', substring(addr, 1, 2), '] ', restaurant_name) "바뀐 이름"
from food_orders
where addr like '%서울%'
GROUP BY
EX) 서울 지역의 음식 타입별 평균 음식 주문금액 구하기
select substring(addr, 1, 2) "시도",
cuisine_type "음식 종류",
avg(price) "평균 금액"
from food_orders
where addr like '%서울%'
group by 1, 2
EX) 이메일 도메인별 고객 수와 평균 연령 구하기
select substring(email, 10) "도메인",
count(customer_id) "고객 수",
avg(age) "평균 연령"
from customers
group by 1
EX) ‘[지역(시도)] 음식점이름 (음식종류)’ 컬럼을 만들고, 총 주문건수 구하기
select concat('[', substring(addr, 1, 2), '] ', restaurant_name, ' (', cuisine_type, ')') "바뀐이름",
count(1) "주문건수"
from food_orders
group by 1
IF, CASE
IF
원하는 조건에 충족할 때 적용할 방법과 아닌 방법을 지정할 수 있음
if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)
EX) 음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Korean’ 이 아닌 경우에는 ‘기타’ 라고 지정
select restaurant_name,
cuisine_type "원래 음식 타입",
if(cuisine_type='Korean', '한식', '기타') "음식 타입"
from food_orders
CASE
각 조건별로 적용 할 값을 지정
case when 조건1 then 값(수식)1
when 조건2 then 값(수식)2
else 값(수식)3
end
EX) 음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Japanese’ 혹은 ‘Chienese’ 일 때는 ‘아시아’, 그 외에는 ‘기타’ 라고 지정
select restaurant_name,
cuisine_type AS "원래 음식 타입",
case when (cuisine_type='Korean') then '한식'
else '기타'
end as " 음식 타입"
from food_orders
EX) 주소의 시도를 ‘경기도’ 일때는 ‘경기도’, ‘특별시’ 혹은 ‘광역시’ 일 때는 붙여서, 아닐 때는 앞의 두 글자만 사용
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
SQL로 간단한 User Segmentation 해보기
EX) 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
EX) 음식 단가, 음식 종류 별로 음식점 그룹 나누기(가격 = 5000 미만, 5000 이상 15000 미만, 15000 이상)
(Korean = 한식 Japanese, Chinese, Thai, Vietnamese, Indian = 아시아식 그외 = 기타)
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
조건문으로 서로 다른 식을 적용한 수수료 구해보기
EX) 지역과 배달시간을 기반으로 배달수수료 구하기 (식당 이름, 주문 번호 함께 출력)
(지역 : 서울, 기타 - 서울일 때는 수수료 계산 * 1.1, 기타일 때는 곱하는 값 없음 시간 : 25분, 30분 - 25분 초과하면 음식 가격의 5%, 30분 초과하면 음식 가격의 10%)
select restaurant_name,
order_id,
delivery_time,
price,
addr,
case when delivery_time>25 and delivery_time<=30 then price*0.05*(if(addr like '%서울%', 1.1, 1))
when delivery_time>30 then price*1.1*(if(addr like '%서울%', 1.1, 1))
else 0 end "수수료"
from food_orders
EX) 주문 시기와 음식 수를 기반으로 배달할증료 구하기
(주문 시기 : 평일 기본료 = 3000 / 주말 기본료 = 3500 음식 수 : 3개 이하이면 할증 없음 / 3개 초과이면 기본료 * 1.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 order_id,
restaurant_name,
day_of_the_week,
delivery_time,
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 'On-time' end "지연여부"
from food_orders