SQL 3일차

1. 필요한 문자 포맷이 다를 때, SQL로 가공하기 (REPLACE, SUBSTRING, CONCAT)

REPLACE

• 특정 문자를 다른 것으로 바꿔주는 기능

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

  • 사용예시

    select restaurant_name "원래 상점명",
    replace(restaurant_name, 'Blue', 'Pink') "바뀐 상점명"
    from food_orders
    where restaurant_name like '%Blue Ribbon%'
    //Blue Ribbon이 포함된 데이터에서 Blue > Pink 로 변경해서 조회해줌

SUBSTRING(substr)

• 특정 문자만 필요할 때, 필요한 부분만 조회하는 기능

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

  • 사용 예시

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

CONCAT

• 여러 컬럼의 값을 하나로 합칠 수 있는 기능

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

  • 붙일 수 있는 문자의 종류
    • 컬럼
    • 한글
    • 영어
    • 숫자
    • 기타 특수문자
  • 사용예시

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

문자 데이터를 바꾸고 GROUP BY 사용 실습

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

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

• 이메일 도메인별 고객 수와 평균 연령 구하기

  select substr(email, 10) as "도메인",
      count(name) as "고객 수", avg(age) as "평균 연령"
  from customers
  group by substr(email, 10);

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

  select concat('[',substr(addr, 1, 2),']',restaurant_name,'(',
  cuisine_type,')') as "[지역(시도)] 음식점이름 (음식종류)",
      count(1) as "주문건수"
  from food_orders
  group by concat('[',substr(addr, 1, 2),']',restaurant_name,'(',
  cuisine_type,')')

---

조건에 따라 포맷을 다르게 변경할 때 (IF, CASE)

1. IF 문

   • 조건에 충족할 때 적용할 방법과 아닌 방법을 지정할 수 있다.

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

   • 사용예시 : 음식 타입이 'korean' 일 때 '한식' 아닌 경우 '기타'라고 지정

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

2. CASE 문

   • 각 조건별로 적용할 값을 지정해 줄 수 있다.

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

   • 사용예시 : 음식 타입이 'korean'일 때는 '한식', 'japanese' 혹은 'chinese'일 때는 '아시아', 그 외에는 '기타'로 지정

   select restaurant_name,
       cuisine_type as "원래 음식 타입",
       case when (cuisine_type='korean') then '한식'
       when cuisine_type in ('japanese','chinese') then '아시아'
       else '기타'
       end as "음식 타입"
   from food_orders;

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

   select restaurant_name,
       addr as "원래 주소",
       case when (addr like '%경기도%') then '경기도'
       when (addr like '%특별시%' or  addr like '%광역시%') 
       then substr(addr, 1, 5)
       else substr(addr, 1, 2)
       end as "변경된 주소"
   from food_orders;