| 강의명 : 엑셀보다 쉽고 빠른 SQL
3강. 데이터 가공하기
1.업무에 바로 쓸 수 있게 문자 데이터로 변환하기
*Replace
ex)
select restaurant_name "원래 상점명",
replace(restaurant_name, 'Blue', 'Pink') "바뀐 상점명"
from food_orders
where restaurant_name like '%Blue Ribbon%'
*Substring : 전체 문자열에서 일부를 추출한 부분 문자열
ex)
select addr "원래 주소",
substr(addr, 1, 2)"시도"
from food_orders
where addr like '%서울특별시%'
*Concatenate : v.사슬같이 잇다, 연결하다
ex)
select restaurant_name "원래 이름",
addr "원래 주소",
concat('[', substring(addr, 1, 2), '] ' , restaurant_name) "바뀐 이름"
from food_orders
where addr like '%서울%'
1-2. 실즙 중 난관에 봉착했다. 내가 쓰면 계속 오류가 뜨고 정답을 붙여넣으면 데이터가 나오는데...도대체 뭐가 틀린 것인지 모르겠다ㅠㅠ
Q. 서울 지역의 음식 타입별 평균 음식 주문금액 구하기 (출력 : ‘서울’, ‘타입’, ‘평균 금액’)
A1. (내가 쓴 오답)
A2. (정답)
-->이후 알게 된점 : "평균 금액" 다음에 ' , '가 있어서 안되었던 것임...
- 조건에 따라 다른 연산을 하는 방법
- 조건을 지정해주는 가장 기초 문법 ‘If’
ex) "음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Korean’ 이 아닌 경우에는 ‘기타’ 라고 지정"
select restaurant_name,
cuisine_type "원래 음식 타입",
if(cuisine_type='Korean', '한식', '기타') "음식 타입"
from food_orders
ex)"‘문곡리’ 가 평택에만 해당될 때, 평택 ‘문곡리’ 만 ‘문가리’ 로 수정"
select addr "원래 주소",
if(addr like '%평택군%', replace(addr, '문곡리', '문가리'), addr) "바뀐 주소"
from food_orders
where addr like '%문곡리%'
- 여러 개의 조건일 때 사용하는 case
ex)음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Japanese’ 혹은 ‘Chienese’ 일 때는 ‘아시아’, 그 외에는 ‘기타’ 라고 지정
select
case when cuisine_type = 'Korean' then '한식'
when cuisine_type in ('Japanese', 'Chinese') then '아시아'
else '기타'
end "음식 타입",
cuisine_type
from food_orders
ex) 음식 단가를 주문 수량이 1일 때는 음식 가격, 주문 수량이 2개 이상일 때는 음식가격/주문수량 으로 지정
select order_id,
price,
quantity,
case when quantity=1 then price
when quantity>=2 then price/quantity end "음식 단가"
from food_orders
cf. / : 나누기
ㄴ위와 같은 식은 if 로도 쓸 수 있다...추가 연습...._
select order_id,
price,
quantity,
if (quantity=1,price,price/quantity) "음식 단가"
from food_orders
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) "음식 단가, 음식 종류 별로 음식점 그룹 나누기
(Korean = 한식)(Japanese, Chinese, Thai, Vietnamese, Indian = 아시아식)(그외 = 기타)(가격 = 5000 미만, 5000 이상 15000 미만, 15000 이상)"
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ㄴ단가는 가격 나누기 수량.....
ㄴ'한식1', '아시아식1', '기타1' 등 카테고리를 생성해야 한다...
ㄴ여기서 또 'order_id'열이 왜 필요한 건지 모르겠다........
ex) "지역과 배달시간을 기반으로 배달수수료구하기 (식당 이름, 주문 번호 함께 출력)
(지역 : 서울, 기타 - 서울일 때는 수수료 계산 1.1, 기타일 때는 곱하는 값 없음
시간 : 25분, 30분 - 25분 초과하면 음식 가격의 5%, 30분 초과하면 음식 가격의 10%)"
A1. (내가 쓴 오답)
A2. (정답)
ㄴ배달수수료 = 시간수수료 X 지역 '계수'...
👉 시간에 따른 ‘추가비용’ 금액을 계산
👉 동일한 시간 수수료라도 지역에 따라 10% 더 붙거나 그대로ㄴ'기타일 때는 곱하는 값 없음' = 곱하기 1...
| 오답 풀이 (아직 이해 못 함)
1. delivery_time > 25 조건이 먼저 와서 >30인 경우도 0.05로 잘못 계산됨.
2. 지역 CASE가 ‘계수(1 또는 1.1)’가 아니라 ‘가격을 곱한 금액’을 만들어서 수수료 계산이 두 번 곱해지는 오류 발생.
3. 시간 CASE에서도 else price*0 을 사용하여 “수수료”가 아니라 “가격 기반 금액”을 만들고 있어 로직이 헷갈리고 계산이 비정상적으로 커짐.
- 숫자 계산이나 문자 가공 시 자주 발생하는 오류 (이후 활용)
"SQL 문을 회사에서 써봤는데요, avg, substring 등 함수를 썼더니 에러메세지에 ‘data type’ 단어가 뜨면서 실행되지 않아요"
ㄴ제일 상단 열의 이름 왼쪽에 쓰여있는 'abc'는 문자, '123'은 숫자 데이터이다.* 숫자로 변경하고 싶을 때
cast(if(rating='Not given', '1', rating) as decimal)
* 문자로 변경하고 싶을 때
concat(restaurant_name, '-', cast(order_id as char))
>>>3강 실습 문제
Q. 배달시간이 늦었는지 판단하는 값을 만들기
A1.
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
