SQL REPLACE & SUBSTRING & CONCAT / IF & CASE

엑셀보다 쉽고 빠른 SQL 3주차

• replace

  • 특정 문자를 다른 문자로 바꾸기

    replace(바꿀 컬럼, 현재 값, 바꿀 값)

    • (식당 명의 ‘Blue Ribbon’ 을 ‘Pink Ribbon’ 으로 바꾸기)

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

    

• substring(substr)

  • 원하는 문자만 남기기

    substr(조회 할 컬럼, 시작 위치, 글자 수)

    • (서울 음식점들의 주소를 전체가 아닌 ‘시도’ 만 나오도록 수정)

      select addr "원래 주소",
             substr(addr, 1, 2) "시도"
      from food_orders
      where addr like '%서울특별시%'

    

• concat

  • 여러 컴럼의 문자를 합치기

    concat(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은 값3, .....)

    • (서울시에 있는 음식점은 ‘[서울] 음식점명’ 이라고 수정)

    select restaurant_name "원래 이름",   
           addr "원래 주소",
           concat('[', substring(addr, 1, 2), '] ', restaurant_name) "바뀐 이름"
    from food_orders
    where addr like '%서울%'

    

• 문자 데이터를 바꾸고, GROUP BY 사용하기

  • 서울 지역의 음식 타입별 평균 음식 주문금액 구하기 (출력 : ‘서울’, ‘타입’, ‘평균 금액’)

    select substring(addr, 1, 2) "시도",
           cuisine_type "음식 종류",
           avg(price) "평균 금액"
    from food_orders
    where addr like '%서울%'
    group by 1, 2

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

  select concat('[', substring(addr, 1, 2), '] ', restaurant_name, ' (', cuisine_type, ')') "바뀐이름",
         count(1) "주문건수"
  from food_orders
  group by 1

  

• IF

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

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

    

• CASE(두 개 이상의 조건을 지정해야 할 경우)

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

    • 주소의 시도를 ‘경기도’ 일때는 ‘경기도’, ‘특별시’ 혹은 ‘광역시’ 일 때는 붙여서, 아닐 때는 앞의 두 글자만 사용

      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

      

• User Segmentation

  • 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

  

• 음식 단가, 음식 종류 별로 음식점 그룹 나누기

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

  (가격 = 5000, 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, 기타일 때는 곱하는 값 없음 시간 : 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

  

  회고: sql도 점점 어려워진다,, typora에 SQL이라 적었다가 VELOG에 옮길 때 애먹었음ㅜㅜ sql 소문자로 적기!! 내일부터 자바인데 화이팅해보자!!