[내일배움캠프/사전캠프] SQL 강의 3주차

업무에 필요한 문자 포맷이 다를때,
SQL로 가공하기
Replace, Substring, Concat

 

-Query 결과를 바로 사용할 수 없는 경우

• 주소 전체가 아닌 '시도' 정보만 필요할때, 전체 주소가 아닌 '서울'로 문자를 변경하고 싶을 경우
• 보고서를 작성할 때 사업장 명과 함께 지역이 나와야하는데 이때 '사업장[지역]' 과 같은 형태로 문자 포맷을 변경하고 싶을 경우

 

[REPLACE]

-바뀐 상점이름, 지역이름 한번에 변경

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

 

※예시

select addr "원래 주소",

           replace(addr, '문곡리', '문가리') "바뀐 주소"

from food_orders

where addr like '%문곡리%'

--------------food_orders 테이블에서 addr(주소)에 문곡리가 들어가는 값을 문가리로 변경

[SUBSTRING]

-전체 데이터가 아닌 특정 문자만 필요할 때, 그 부분만 조회

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

 

※예시

select addr "원래 주소",

           substr(addr, 1, 2) "시도"

from food_orders

where addr like '%서울특별시%'

--------------food_orders 테이블에서 addr(주소)에 서울특별시가 들어가는 값들을 '서울'만 나오도록 수정

 

[CONCAT]

-원하는 문자가 여러 컬럼에 있을 때, 하나로 합쳐서 업무에 필요한 형태로 만들 수 있다.

-합칠(붙일) 수 있는 문자의 종류

•  컬럼
• 한글
• 영어
• 숫자
• 기타 특수문자

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

 

※예시

select restaurant_name "원래 이름",

           addr "원래 주소"

           concat(' [', substring(addr,1,2), '] ' restaurant_name) "바뀐 이름"

from food_orders

where addr like '%서울%'

--------------food_orders 테이블에서 서울시에 있는 음식점을 '[서울] 음식점명' 이라고 수정

 

 

문자데이터를 바꾸고 
Group by 사용하기

 

-문자데이터 변경하고 컬럼을 Group by로 묶기 (group by 사용해서 범주별로 수식함수로 연산)

 

※예시

select substr(addr,1,2) "시도",

           cuisine_type "음식 종류"

           avg(price) "평균 금액"

from food_orders

where addr like '%서울%'

group by 1,2

--------------food_orders 테이블에서 서울시에 있는 음식타입별 평균 음식 주문금액 구하기

 

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

 

-Group by 처럼 조건도 카테고리별로 지정

-예시) 주소의 시도를 '경기도'일때는 '경기도' 아닐 경우 앞의 두글자만 사용할 때

[IF]

-조건을 지정해주는 문법

-원하는 조건에 충족할 때 적용할 방법과 아닌 방법을 지정해 줄 수 있음

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

 

※예시

select addr "원래 주소",

           if(addr like '%평택군%', replace(addr, '문곡리', '문가리' ), addr) "바뀐 주소"

from food_orders

where addr like '%문곡리%'

--------------food_orders 테이블에서 addr(주소) 문곡리가 평택에만 해당될때, 평택 문곡리만 문가리로 변경

 

[CASE]

-조건을 두개 이상 지정을 해야 할 경우 case 문을 이용하여 여러번의 if문을 적용한 효과를 낼 수 있음

-조건별로 적용할 값을 지정해 줄 수 있음

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

 

※예시1

select restaurant_name,

          cuisine_type as "원래 음식 타입",

          case when (cuisine_type='Korean') then '한식'

          else '기타'

          end "음식타입"

from food_orders

--------------food_orders 테이블에서 음식타입이 korean 일때는 한식 그 외에는 기타라고 지정

 

※예시2

select order_id,
          price,
          quantity,
         case when quantity=1 then price
                 when quantity>=2 then price/quantity

         end "음식 단가"
from food_orders

--------------food_orders 테이블에서 주문수량이 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

--------------food_orders 테이블에서 주문시기와 음식 수를 기반으로 배달할증료 구하기

주문시기: 평일 기본료=3000, 주말 기본료=3500

음식수: 3개 이하면 할증 없음, 3개 초과면 기본료*1.2

 

SQL문에 문제가 없는 것 같은데 오류가 나는 이유
Data Type 오류 해결하기

 

-avg, substring 등 함수를 썼더니 에러메시지에 data type 뜨면서 실행되지 않을 경우

-사진을 보면 rating 컬럼 명 옆에 abc 라고 표기 되어있는데, 이 표시는 문자로 저장이 되었다는 의미이다.

-출력결과에서 컬럼명 옆의 'ABC' 혹은 '123'을 확인

-문자, 숫자를 혼합하여 함수에 사용할 때에는 데이터 타입을 변경해줘야한다.

 

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

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