240628(+23) | SQL 3주차 강의 (2)

조건문 마법사 되기.SQL

조건문을 이용하여 다양한 수식 적용해보기

Q1. 스파르타 잇츠 배달 수수료

• 지역과 배달시간을 기반으로 배달수수료 구하기 (식당 이름, 주문 번호 함께 출력)
  지역 : 서울, 기타 | 서울일 때는 수수료 계산 * 1.1, 기타일 때는 곱하는 값 없음(x1)
  시간 : 25분, 30분 | 25분 초과하면 음식 가격의 5%, 30분 초과하면 음식 가격의 10%
• 서울지역이라도 배달 시간이 25분 이하면 부과되는 수수료가 없다.
• 산술적인 부분인데.. 1.1은 기본 수수료(100%)에 추가수수료 10%가 더 부과되는 것.

select concat('no.', order_id) '주문 번호',
	   restaurant_name '가게명',
       delivery_time '배달 시간',
       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 "수수료",
       price '가격',
       if(addr like '%직할시%', replace(addr, '직할시', '광역시'), addr) "배달 주소"
from food_orders
where delivery_time BETWEEN 29 and 30
order by restaurant_name

동일한 배달 시간이라도 서울 지역이 수수료가 110% 더 붙는 것을 확인 할 수 있다.
수수료 계산 이해 하는거 어렵네. 나 바본듯 ㅜ-ㅜ

Q2. 스파르타 잇츠 배달 할증료

주문 요일과 음식 수에 따른 배달할증료 구하기

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

select concat('no.', order_id) '주문 번호', 
	   if(addr like '%직할시%', replace(addr, '직할시', '광역시'), addr) "배달 주소",
       if(day_of_the_week = 'Weekday', '평일', '주말') '배달 요일',
       quantity '주문 수량',
	CASE
		WHEN quantity <= 3 and day_of_the_week = 'Weekend' then 3500
		WHEN quantity > 3 and day_of_the_week = 'Weekend' then 3500*1.2
		WHEN quantity > 3 and day_of_the_week = 'Weekday' then 3000*1.2
		else 3000
	END '배달 할증료',
	restaurant_name '가게명'
from food_orders
order by restaurant_name 

쩝... if문으로 더 간단하게 표현 할 수 있다.

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

3주차 강의 과제

주중 : 25분 이상
주말 : 30분 이상
조건을 여러번 적용할 때 if, case 문 중 어떤 것을 이용할지 결정
조건에 ‘주중, 주말’ 조건과 ‘배달시간’ 조건을 동시에 줄 때 사용 할 논리연산자 결정

select concat('no.', order_id) '주문 번호',
	   restaurant_name '가게명', 
	   if(day_of_the_week = 'Weekday', '평일', '주말') '주중or주말', 
	   delivery_time '배달 소요 시간',
	CASE 
		when delivery_time >= 25 and day_of_the_week = 'Weekday' then '지연'
		when delivery_time >= 30 and day_of_the_week = 'Weekend' then '지연'
		ELSE '정상 배달'
	END '지연 여부'	
from food_orders;

IF vs CASE

substr과 함께 사용되는 IF문과 LIKE 연산자와 함께 사용하는 CASE문 성능은?

• LIKE 연산자가 포함된 CASE문
  LIKE 연산자는 패턴 매칭을 사용, 문자열 일부분에 대한 매칭은 성능 저하에 영향을 미칠 수 있다. 하지만 LIKE '%직할시%'같은 패턴 매칭은 SUBSTR함수와 비교 했을 때 비슷한 성능을 보일 수 있다.
  문자열 전체를 검색해야 해서 INDEX가 없으면 성능이 저하 될 수 있다.
• IF와 SUBSTR 함수를 사용하는 쿼리
  SUBSTR함수는 문자열의 처음 몇 글자만 비교 할 때 효율적 일 수 있다. 간단한 연산이다.
• 데이터 크기에 따른 성능 차이
  a.데이터 크기가 작은 경우 (수백에서 수천행): 두 쿼리의 성능 차이는 거의 없다.
  b.데이터 크기가 매우 큰 경우 (수백만 행 이상): substr > like
• 패턴 매칭은 인덱스를 사용 할 수 없는 경우 성능이 크게 저하되고, 일반적으로 와일드 카드(%)가 있는 경우 인덱스를 사용 할 수 없다.
  (출처: 배형호 튜터님)

Data Type 오류

하나의 컬럼에 2개의 데이터 타입이 저장 되어 있을때 (예) int, str
변환해서 사용해 주면 된다.

--숫자로 변경
cast(if(rating='Not given', '1', rating) as decimal) 
/
--문자로 변경
concat(restaurant_name, '-', cast(order_id as char))