[240913] SQL 사전 강의 (~3강)

과제에서 필수 사용되는 문법 요약

• 3. [실습] 문자 데이터를 바꾸고, GROUP BY 사용하기

     1. [실습] 서울 지역의 음식 타입별 평균 음식 주문금액 구하기 (출력 : ‘서울’, ‘타입’, ‘평균 금액’)

        select SUBSTR(addr, 1, 2) as "서울", cuisine_type "타입", AVG(price) "평균 금액" 
        from food_orders fo 
        where fo.addr like '서울%'
        group by 1,2 ;

     2. [실습] 이메일 도메인별 고객 수와 평균 연령 구하기

        select SUBSTR(email, 10), COUNT(*), AVG(age) 
        from customers c 
        group by 1;

     3. [실습] ‘[지역(시도)] 음식점이름 (음식종류)’ 컬럼을 만들고, 총 주문건수 구하기

        select CONCAT('[',SUBSTR(addr,1,2),'] ', restaurant_name, ' (', cuisine_type,')'), COUNT(*)
        from food_orders fo 
        group by 1;

• 4. 조건에 따라 포맷을 다르게 변경해야한다면 (IF, CASE)
  • 1) 조건에 따라 다른 방법을 적용하고 싶을 때 - If 문 기초 (실습 포함)
    • 사용 방법

      if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)

    • [실습1]
      음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Korean’ 이 아닌 경우에는 ‘기타’ 라고 지정
      - [코드스니펫] If문 예시

           select restaurant_name,
                  cuisine_type "원래 음식 타입",
                  if(cuisine_type='Korean', '한식', '기타') "음식 타입"
           from food_orders

    • [실습2]
    2. 번 실습에서 ‘문곡리’ 가 평택에만 해당될 때, 평택 ‘문곡리’ 만 ‘문가리’ 로 수정
       • [코드스니펫] If문 예시2

         select addr "원래 주소",
                if(addr like '%평택군%', replace(addr, '문곡리', '문가리'), addr) "바뀐 주소"
         from food_orders
         where addr like '%문곡리%'

    • [실습3]
    3. 번 실습에서 잘못된 이메일 주소 (gmail) 만 수정을 해서 사용
       • [코드스니펫] If문 예시3

         select substring(if(email like '%gmail%', replace(email, 'gmail', '@gmail'), email), 10) "이메일 도메인",
                count(customer_id) "고객 수",
                avg(age) "평균 연령"
         from customers
         group by 1

  • 2) 조건을 여러가지 지정하고 싶을 때 - Case 문 기초 (실습 포함)
    • 함수명 : case
    • 사용 방법

      case when 조건1 then 값(수식)1
           when 조건2 then 값(수식)2
           else 값(수식)3
      end

    • [실습1]
      음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Japanese’ 혹은 ‘Chienese’ 일 때는 ‘아시아’, 그 외에는 ‘기타’ 라고 지정

        select restaurant_name,
               cuisine_type AS "원래 음식 타입",
               case when (cuisine_type='Korean') then '한식'
        			      when (cuisine_type in (Japanese, Chienese) then '아시아'
               else '기타'
               end as " 음식 타입"
        from food_orders

    • [실습2]
      음식 단가를 주문 수량이 1일 때는 음식 가격, 주문 수량이 2개 이상일 때는 음식가격/주문수량 으로 지정
      ⇒ If 로도 가능
      - [코드스니펫] Case When 실습 1

           select order_id,
                  price,
                  quantity,
                  case when quantity=1 then price
                       when quantity>=2 then price/quantity end "음식 단가"
           from food_orders

           select order_id,
                  price,
                  quantity,
                  if(quantity = 1, price, price/quantity) "음식 단가"
           from food_orders

    • [실습3]
      주소의 시도를 ‘경기도’ 일때는 ‘경기도’, ‘특별시’ 혹은 ‘광역시’ 일 때는 붙여서, 아닐 때는 앞의 두 글자만 사용
      - [코드스니펫] Case When 실습2

           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

  • 3) 조건을 사용할 수 있는 경우 알아보기
    • 새로운 카테고리 만들기
      • 음식 타입
        • cuisine_category
        • 한국 음식, 아시아 음식, 미국 음식, 유럽 음식
      • 고객 분류
        • User_segmentation
        • 10대 여성, 10대 남성, 20대 여성, 20대 남성 등
    • 연산식을 적용할 조건 지정하기
      • 현금일 때의 수수료율 <> 카드일 때의 수수료율
        • if 문 혹은 case 문으로 각각 다른 수수료율 혹은 수수료 계산 방식을 적용
    • 다른 문법 안에서 적용하기
      • if, case 문 안에 다른 문법이나 연산을 넣을 수도 있지만, 다른 문법 안에 조건문을 넣을 수도 있습니다
      • 예를 들어 concat 문으로 여러 컬럼을 합칠 때
        • rating 이 있을 때는 rating 을 넣어주고 없을 때는 아무것도 넣지 않도록, concat 안에 if 문을 넣어줄 수 있죠
• 5. [실습] SQL로 간단한 User Segmentation 해보기

     1. [실습] 10세 이상, 30세 미만의 고객의 나이와 성별로 그룹 나누기 (이름도 같이 출력)

        select name,
        		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 as category
        from customers c
        where age BETWEEN 10 and 29;

     2. [실습] 음식 단가, 음식 종류 별로 음식점 그룹 나누기

     • 음식 종류

       • Korean = 한식
       • Japanese, Chinese, Thai, Vietnamese, Indian = 아시아식
       • 그 외 = 기타

     • 가격

       • 5000
       • 15000
       • 그 이상

       select cuisine_type,
       	   price,
       	   case when cuisine_type = 'Korean' and price/quantity < 5000 then 'Cheap Korean food'
       	   		when cuisine_type = 'Korean' and (price/quantity BETWEEN 5000 and 15000) then 'Korean food'
       	   		when cuisine_type = 'Korean' and price/quantity > 15000 then 'Expensive Korean food'
       	   		when cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') and price/quantity < 5000 then 'Cheap Asian food'
       	   		when cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') and (price/quantity BETWEEN 5000 and 15000) then 'Asian food'
       	   		when cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') and price/quantity > 15000 then 'Expensive Asian food'
       	   		when cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') and price/quantity < 5000 then 'Cheap Western food'
       	   		when cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') and (price/quantity BETWEEN 5000 and 15000) then 'Western food'
       	   		when cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') and price/quantity > 15000 then 'Expensive Western food' end as category	   		
       from food_orders fo ;

• 6. [실습] 조건문으로 서로 다른 식을 적용한 수수료 구해보기

     1. [실습] 지역과 배달시간을 기반으로 배달수수료 구하기 (식당 이름, 주문 번호 함께 출력)

        • 지역 : 서울, 기타
          • 서울일 때는 수수료 계산 * 1.1
          • 기타일 때는 곱하는 값 없음
        • 시간 : 25분, 30분
          • 25분 초과하면 음식 가격의 5%
          • 30분 초과하면 음식 가격의 10%

        select restaurant_name,
        	   order_id,
        	   case when addr like '서울%' and delivery_time < 25 then price * 1.1
        	   		when addr LIKE '서울%' and (delivery_time BETWEEN 25 and 30) then price * 1.1 * 1.05
        	   		when addr LIKE '서울%' and delivery_time > 30 then price * 1.1 * 1.1
        	   		when addr not LIKE '서울%' and delivery_time < 25 then price
        	   		when addr not LIKE '서울%' and (delivery_time BETWEEN 25 and 30) then price * 1.05
        	   		when addr not LIKE '서울%' and delivery_time > 30 then price * 1.1
        	   		end as "수수료"
        from food_orders fo ;

        ⇒ 답안지

        select restaurant_name,
        	   order_id,
        	   addr,
        	   delivery_time,
        	   price,
        	   case when delivery_time between 26 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 as "수수료"
        from food_orders fo ;

     2. [실습] 주문 시기와 음식 수를 기반으로 배달할증료 구하기

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

        select day_of_the_week,
        	   quantity,
        	   case when day_of_the_week = 'Weekend' then 3000 * if(quantity > 3, 1.2, 1)
        	   		else 3500 * if(quantity > 3, 1.2, 1)
        	   		end as "배달할증료"
        from food_orders fo ;

• 7. SQL문에 문제가 없는 것 같은데 왜 오류가 나나요_ (Data Type 오류 해결하기)
  • Mysql 과 다르게, 다른 SQL 문법에서는 data type 이 다를 때 연산이 되지 않을 수 있음.
  • 따라서 문자, 숫자를 혼합하여 함수에 사용 할 때에는 데이터 타입을 변경해주어야 함.

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

• HW. 3주차 숙제 해결

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

  • 주중 : 25분 이상

  • 주말 : 30분 이상

    select day_of_the_week,
    	   delivery_time,
    	   case when day_of_the_week = 'Weekday' and fo.delivery_time >= 25 then "LATE"
    	   		when day_of_the_week = 'Weekend' and fo.delivery_time >= 30 then "LATE"
    	   		else "Okay" end as "지연여부"
    from food_orders fo ;

오답노트

• Group by 사용 시, 두 가지 컬럼 이상일 땐 Select 문의 순서대로 숫자 적어도 가능

  select SUBSTR(addr, 1, 2) as "서울", cuisine_type "타입", AVG(price) "평균 금액" 
  from food_orders fo 
  where fo.addr like '서울%'
  group by 1,2 ;

• SUBSTR 사용 시, 출력할 문자열이 기준점으로부터 순서가 모두 다를 경우, 세 번째 숫자 안적어도 괜찮음.

  select SUBSTR(email, 10), COUNT(*), AVG(age) 
  from customers c 
  group by 1;

• case when 사용 시, 유의 사항
  • 사용법

    Case when 1 then A
    		 when 2 then B
    		 else 3
    		 end
    
    /* Case문은 기본적으로 When 이후 괄호 사용 X
     여기서 아래처럼 And 사용 이후 Between A and B 와 같이
     And 가 두 번 사용되어야 할 경우만 괄호로 구분*/

  • 괄호 사용 유의
    • Case문은 기본적으로 When 이후 괄호 사용 X
    • 여기서 아래처럼 And 사용 이후 Between A and B 와 같이 And 가 두 번 사용되어야 할 경우만 괄호로 구분

      select name,
      		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 as category
      from customers c
      where age BETWEEN 10 and 29;

  • Case when 조건 끝날 때 End 사용
  • 특정 컬럼이 어떤 카테고리에도 해당되지 않을 경우, not in (A, B, C…) 사용
    • 음식 종류
      • Korean = 한식
      • Japanese, Chinese, Thai, Vietnamese, Indian = 아시아식
      • 그 외 = 기타
    • 가격
      • 5000
      • 15000
      • 그 이상
    • 위와 같이 컬럼 두 가지로 카테고리 분류 시, 그외 = 기타에 해당되는 조건문은 아래처럼 not in 사용

      select cuisine_type,
      	   price,
      	   case when cuisine_type = 'Korean' and price/quantity < 5000 then 'Cheap Korean food'
      	   		when cuisine_type = 'Korean' and (price/quantity BETWEEN 5000 and 15000) then 'Korean food'
      	   		when cuisine_type = 'Korean' and price/quantity > 15000 then 'Expensive Korean food'
      	   		when cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') and price/quantity < 5000 then 'Cheap Asian food'
      	   		when cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') and (price/quantity BETWEEN 5000 and 15000) then 'Asian food'
      	   		when cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') and price/quantity > 15000 then 'Expensive Asian food'
      	   		when cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') and price/quantity < 5000 then 'Cheap Western food'
      	   		when cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') and (price/quantity BETWEEN 5000 and 15000) then 'Western food'
      	   		when cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') and price/quantity > 15000 then 'Expensive Western food' end as category	   		
      from food_orders fo ;

• Case, If 혼합 사용

  select restaurant_name,
  	   order_id,
  	   addr,
  	   delivery_time,
  	   price,
  	   case when delivery_time between 26 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 as "수수료"
  from food_orders fo ;