SQL 오늘 강의 내용 정리 3

[목차]

1) 3주차 오늘 배울것

2) 업무에 필요한 문자 포맷이 다를때, SQL로  가공하기(REPLACE, SUBSTRING, CONCAT)

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

4) 조건에 따라 포맷을 다르게 변경해야한다면? (IF, CASE)

5) [실습] SQL로 간단한 User Segmentation 해보기

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

7) SQL문에 문제가 없는 것 같은데 왜 오류가 날까? (Data Type 오류 해결하기)

*실습

---

1) 3주차 오늘 배울것

• 문자 데이터는 있는 그대로만 사용 가능할까?
• 배달 시간 구간에 따라서 수수료를 계산하고 싶은데, 시간을 조건으로 줄 수 있을까?
• 수업에서 배운대로 Query 를 썼는데 왜 오류가 나는걸까?

 

2)업무에 필요한 문자 포맷이 다를때, SQL로  가공하기
(REPLACE, SUBSTRING, CONCAT)

• Query 결과를 바로 사용할 수 없는 경우!
  • 데이터를 보니 잘못된 값이 있는데 하나하나 수동으로 하기엔 너무 많은데 SQL로 바꿀 수 있을까?
  • 주소 전체가 아닌 '시/도' 정보만 필요한데 전체 주소가 아닌 '서울'로 문자를 변경할 수 없을까?
  • 보고서 작성할때 사업장 명과 함께 지역이 나와야하는데 '사업장[지역]' 형태의 포맷으로  변경할 수 있을까? 
• 특정 문자를 다른 문자로 바꾸기 [실습]
  • 함수명 replace
  • 사용 방법 : replace(바꿀 컬럼, 현재 값, 바꿀 값)
• 실습 1.
  • replace 예시
  • select restaurant_name "원래 상점명",
           replace(restaurant_name, 'Blue', 'Pink') "바뀐 상점명"
    from food_orders
    where restaurant_name like '%Blue Ribbon%'

Blue를 Pink로 변경했다

 

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

'문곡리'를 '문가리'로 변경

 

• 원하는 문자만 남기기[실습]
  • SQL 에서는 특정 문자만 골라서 조회할 수 있는 기능을 제공한다
  • 함수명 : substring 또는 substr (둘중에 하나 선택해서 이용하면 된다)
  • 사용 방법 : substr(조회 할 컬럼, 시작 위치, 글자 수)
  • 실습 3. 
    • substring 예시
    • select addr "원래 주소",
             substr(addr, 1, 2) "시도"            //  (addr <- 조회 할 컬럼) , ( 1 <- 시작위치) , ( 2 <- 글자수)
      from food_orders
      where addr like '%서울특별시%'

전체주소의 앞 두글자를 가져왔다

 

 

 

 

• 여러 컬럼의 문자를 합치기 [실습]
  • 원하는 문자가 여러 컬럼에 있어도 하나로 합쳐서 업무에 필요한 형태로 만들 수 있다.
  • 함수명 concat
  • 사용방법 : concat(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은 값3, .....)
  • 붙일수 있는 문자의 종류
    • 컬럼
    • 한글
    • 영어
    • 숫자
    • 기타 특수문자
  • 실습 4.
    • concat 예시 코드 :
    • select restaurant_name "원래 이름",   
             addr "원래 주소",
             concat('[', substring(addr, 1, 2), '] ', restaurant_name) "바뀐 이름"
      from food_orders
      where addr like '%서울%'

 

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

• 문자 데이터 변경과 Group by 절을 한 번에 사용해 보자
  • 실습 1.
  • 서울 지역의 음식 타입별 평균 음식 주문금액 구하기
  • select substring(addr, 1, 2) "시도",
           cuisine_type "음식 종류",
           avg(price) "평균 금액"
    from food_orders
    where addr like '%서울%'
    group by 1, 2

 

 

• 실습 2
  • 이메일 도메인별 고객 수와 평균 연령 구하기
  • select substring(email, 10) "도메인",
           count(customer_id) "고객 수",
           avg(age) "평균 연령"
    from customers
    group by 1

 

mail.com 이 된 이유는 4 실습에서 알아보자

 

• 실습 3
  • '[지역(시도)] 음식점이름 (음식종류)' 컬럼을 만들고, 총 주문건수 구하기
  • select concat('[', substring(addr, 1, 2), '] ', restaurant_name, ' (', cuisine_type, ')') "바뀐이름",
           count(1) "주문건수"
    from food_orders
    group by 1

지역의 앞 두글자와 가계이름 음식 종류를 하나로 합치고 제목을 바뀐이름으로 변경

 

 

 

4) 조건에 따라 포맷을 다르게 변경해야한다면? (IF, CASE)

• Group by 처럼 조건도 카테고리별로 줄 수 있을까?
• 조건에 따라 다른 방법을 적용하고 싶을때 - if 문 기초[실습]
  • if 문은 원하는 조건에 충족할 때 적용할 방법과 아닌 방법을 지정해 줄 수 있다.
  • 함수명 if
  • 사용 방법
    • if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)
  • 실습 1
    • 음식 타입을 'Korean' 일 때는 '한식', 'Korean'이 아닌 경우에는 '기타' 라고 지정
    • select restaurant_name,
             cuisine_type "원래 음식 타입",
             if(cuisine_type='Korean', '한식', '기타') "음식 타입"
      from food_orders

• 아까 실습에서 잘못된 메일 주소 gmail 만 수정하는 방법
  • select substring(if(email like '%gmail%', replace(email, 'gmail', '@gmail'), email), 10) "이메일 도메인",
           count(customer_id) "고객 수",
           avg(age) "평균 연령"
    from customers
    group by 1

 

* if 문안에 like문을 이용해서 %gmail% 이 들어간 메일을 찾고 replace를 통해 email 컬럼에 gmail을 @gmail로 변경함

• 조건을 여러가지 지정하고 싶을때 - Case 문 기초 [실습]
  • case문은 각 조건별로 적용 할 겂을 지정해 줄 수 있다.
  • 사용방법
    • case when 조건1 then 값(수식)1
               when 조건2 then 값(수식)2
               else 값(수식)3
      end  // 끝날때는 end로 마무리
  • 실습 1
    • 음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Japanese’ 혹은 ‘Chienese’ 일 때는 ‘아시아’, 그 외에는 ‘기타’ 라고 지정
      • select restaurant_name,
               cuisine_type AS "원래 음식 타입",
               case when (cuisine_type='Korean') then '한식'
               else '기타'
               end as " 음식 타입"
        from food_orders

 

• 실습 2
  • 음식 단가를 주문 수량이 1일 때는 음식 가격, 주문 수량이 2개 이상일 때는 음식가격/주문수량 으로 지정
    *위와 같은 식은 if 로도 쓸 수 있죠! 한 번 연습해보시는 것을 추천합니다!
  • select order_id,
           price,
           quantity,
           case when quantity=1 then price
                when quantity>=2 then price/quantity end "음식 단가"
    from food_orders

 

• 조건을 사용할 수 있는 경우 알아보기
  • 새로운 카테고리 만들기
    • 음식 타입과 같이 새로운 카테고리를 만들수있다.
  • 연산식을 적용할 조건 지정하기
    • 수수료를 계산할 때 흔히들 현금사용, 카드 사용을 나누는데
      현금과 카드의 수수료가 다르다면연산식을 만들 때 if 문 혹은
      case 문으로 각각 다른 수수료율 혹은 수수료 계산 방식을
      적용할수 있다
    • 다른 문법 안에서 적용하기
      • if, case 문 안에서 다른 문법이나 연산을 넣을 수도 있지만, 다른 문법 안에 조건문을 넣을수도 있다

5) [실습] SQL로 간단한 User Segmentation 해보기

• [실습 1]
  • 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

 

 

 

• [실습 2]
  • 음식 단가, 음식 종류 별로 음식점 그룹 나누기(가격 = 5000 미만, 5000 이상 15000 미만, 15000 이상)
  • (Korean = 한식 Japanese, Chinese, Thai, Vietnamese, Indian = 아시아식 그외 = 기타)
    • 예제 코드
    • 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

 

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

• 조건문을 이용하여 다른 수식을 적용해보기
  • 지역과 배달시간을 기반으로 배달수수료 구하기
  • (식당 이름, 주문 번호 함께 출력)
    (지역 : 서울, 기타 - 서울일 때는 수수료 계산 * 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

 

• 주문 시기와 음식 수를 기반으로 배달할증료 구하기
  • (주문 시기 : 평일 기본료 = 3000 / 주말 기본료 = 3500
    음식 수 : 3개 이하이면 할증 없음 / 3개 초과이면 기본료 * 1.2)
    • 예제 코드
    • 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

 

 

7) SQL문에 문제가 없는 것 같은데 왜 오류가 날까? (Data Type 오류 해결하기)

• 숫자 계산이나 문자 가공 시 자주 발생하는 오류에 대해서 알아보자
  • 문자/숫자 계산을 했더니 오류가 났다!
    • 오류 코드 : 'data tpye'
    • SQL 문법에서는 data type 이 다를 때 연산이 되지 않을 수 있다.
    • 사진1) 을보면 rating 은 숫자로 되있지만 문자형으로 저장되있다는것을 알수있다.

사진 1) 컬럼명 옆에 abc 또는 123을 확인해 주세요!!! (abc는 문자로 저장된 의미입니다)

 

• 따라서 문자, 숫자를 혼합하여 함수에 사용하려면 데이터 타입을 변경해야한다!
  • 코드
    • --숫자로 변경
      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