[2024.07.23 TIL] sql 조건문 및 subquery

조건문과 수식을 이용하여 실습 해보기

• 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, 15000, 그 이상)

  SELECT restaurant_name "음식점",
  	   price "단가",
  	   cuisine_type "음식 종류",
  	   order_id "주문 번호",
  	   case when cuisine_type = 'Korean' and price/quantity < 5000 then "5000원 미만 한식"
  	   		when cuisine_type = 'Korean' and price/quantity BETWEEN 5000 and 15000 then "5000원 이상 한식"
  	   		when cuisine_type = 'Korean' and price/quantity > 15000 then "15000원 초과 한식"
  	   		when cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian')
  	   		and price/quantity < 5000 then "5000원 미만 아시아"
  	   		when cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian')
  	   		and price/quantity BETWEEN 5000 and 15000 then "5000원 이상 아시아"
  	   		when cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian')
  	   		and price/quantity > 15000 then "15000원 초과 아시아"
  	   		when cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian')
  	   		and price/quantity < 5000 then "5000원 미만 기타"
  	   		when cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian')
  	   		and price/quantity BETWEEN 5000 and 15000 then "5000원 이상 기타"
  	   		when cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian')
  	   		and price/quantity > 15000 then "15000원 초과 기타" end "단가별 음식 그룹"
  FROM food_orders

  

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

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

  SELECT restaurant_name '식당 이름',
  	   order_id '주문 번호',
  	   delivery_time '배달 시간',
  	   subString(addr, 1, 2) "주소",
  	   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 * 0.10 * 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 "주문 시기",
  	   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

  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

  

숫자 계산이나 문자 가공 시 자주 발생하는 오류

• 문자, 숫자를 혼합하여 함수에 사용 할 때에는 데이터 타입을 변경

  --숫자로 변경
  cast(if(rating='Not given', '1', rating) as decimal) 
  

  --문자로 변경
  concat(restaurant_name, '-', cast(order_id as char))

직접 해보기

• 다음의 조건으로 배달시간이 늦었는지 판단하는 값을 만들기
  주중 : 25분 이상
  주말 : 30분 이상
  예시)
  
  답

  select order_id,
  		restaurant_name,
  		day_of_the_week ,
  		delivery_time ,
  		case when day_of_the_week = 'Weekday' then if(delivery_time >=25, 'Late', 'On-time')
  			 when day_of_the_week = 'Weekend' then if(delivery_time >=30, 'Late', 'On-time')
  			 end "지연 여부"
  from food_orders

정리

• 문자 변경

  REPLACE :  지정한 문자를 다른 문자로 변경
  SUBSTRING : 특정 문자만 추출
  CONCAT : 여러 문자를 합하여 포맷팅

• 조건문

  IF : if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)
  CASE WHEN END :
  case when 조건1 then 값(수식)1
      when 조건2 then 값(수식)2
      else 값(수식)3
  end

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

여러번의 연산을 한 번의 SQL문으로 수행하기(Subquery)

• subquery가 필요한 경우

  여러번의 연산을 수행해야 할 때
  조건문에 연산 결과를 사용해야 할 때
  조건에 Query 결과를 사용하고 싶을 때 

  기본 구조

  sql
  select column1, special_column
  from
     ( /* subquery */
     select column1, column2 special_column
     from table1
     ) a
  
   sql
   select column1, column2
   from table1
   where column1 = (select col1 from table2)