［데이터 분석 2일차 ②］ (Replace, Substring, Concat, 문자데이터 바꾸기 , IF, Case, User Segmentation, 오류해결)

Replace / Substring (Substr) / Concat / 문자데이터 바꾸고 Group by) / If / Case (when~then, else, end) / User Segmentation / 오류해결

 

 

1. 특정문자를 다른 문자로 바꾸기

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

 

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

select restaurant_name as "원래 상점명",

           replace(restaurant_name, 'Blue', 'Pink') as "바뀐 상점명"

from food_orders

where restaurant_name like '%blue Ribbon%'

 

ex) 주소의 ‘문곡리’ 를 ‘문가리’ 로 바꾸기

select addr as "원래 주소",

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

from food_orders

where addr like '%문곡리'

 

2. 원하는 문자만 남기기 

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

 

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

select addr "원래주소",

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

from food_orders

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

 

3. 여러 컬럼의 문자를 합치기

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

 

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

select restaurant_name "원래 이름",

          addr "원래 주소",

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

from food_orders

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

**concat (문자는 ' ', 함수 붙일 땐 ' ' 제외)

 

4. 문자데이터 바꾸고 Group by

 

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

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

          cuisine_type "음식 종류",

          avg(price) "평균금액"

from food_orders

where addr like '%서울특별시%

group by substr(addr,1,2), cuisine_type  (=  group by 1, 2  (1번째, 2번째 컬럼))

 

ex) 이메일 도메인별 고객 수와 평균 연령 구하기 (아이디부분 8로 통일된 예제)

select substr(email, 10) "도메인",

           count(distinct customer_id) "고객 수",

           avg(age) "평균 연령"

from customers

group by 1

** 10인 이유 @ 없는 메일주소도 있음 도메인별로 묶이려면 @ 제외한 10번째자리 부터

** count(distinct 해도 되고), count(customer_id)  컬럼 그대로 써도 됨.

 

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

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

count(1) "주문건수"

from food_orders

group by 1

** 총 주문''건수"는 음식점별 주문건수이므로 sum이 아닌 count(1).

 

 

5. 조건에 따라 다른 방법을 적용하고 싶을 때

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

 

ex) 음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Korean’ 이 아닌 경우에는 ‘기타’ 라고 지정

select restaurant_name,

cuisine_type '원래음식타입',

if(cuisine_type='Korean', '한식', '기타') '음식타입'

FROM food_orders

**오타조심! cuisine

 

 

ex) Replace 예시에서 ‘문곡리’ 가 평택에만 해당될 때, 평택 ‘문곡리’ 만 ‘문가리’ 로 수정

select addr as "원래 주소",

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

FROM food_orders

where addr like '%문곡리'

**정답에선 addr 대신 '문가리' (참고)

ex) ★ ★ ★ Group by 2번째 예시에서 잘못된 이메일 주소 (gmail) 만 수정을 해서 사용 ★ ★ ★  

select substr(if(email like '%gmail%', replace(email, 'gmail', '@gmail'), email), 10) "도메인",

count(customer_id) "고객수",

avg(age) "평균연령"

from customers

group by 1

** 어려웠다 substr 안에 if 를 넣을 줄은...!!!

잘못된 이메일 주소만 수정할 땐, if(email like '%gmail%', replace ('gmail, '@gmail', email (그 외 그냥 email) //

그리고 substr(범위 (if 절 조건 넣을 수 있음, 그 조건으로 바꾼 범위), 10) - 10번째 이하부터 끝까지 추출★★

 

 

6. 조건을 여러가지 지정하고 싶을 때

Case (when~then, else, end)

 

ex) 음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Japanese’ 혹은 ‘Chienese’ 일 때는 ‘아시아’, 그 외에는 ‘기타’ 라고 지정

select restaurant_name,

cuisine_type as "원래 음식타입",

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

when cuisine_type='Japanese' then '일식'

when cuisine_type='Chinese' then '중식'

else '기타'

end as "음식 타입"

 

ex) 음식 단가를 주문 수량이 1일 때는 음식 가격, 주문 수량이 2개 이상일 때는 음식가격/주문수량 으로 지정

select order_id,

price,

quantity,

case when quantity=1 then price

when quantity>=2 then price/quantity

end '음식단가'

from food_orders

 

*위와 같은 식은 if 로도 쓸 수 있죠! 한 번 연습해보시는 것을 추천합니다!

select order_id,

price,

quantity,

if(quantity>=2, price/quantity, price) '음식단가'

from food_orders

 

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

select restaurant_name,

addr '원래주소',

case when addr like '경기도%' then '경기도'

when addr like '%특별시%' or addr like '%광역시%' then substr(addr,1,5)

else substr(addr,1,2)

end '시도'

from food_orders

** 경기도 는 단순하니까 Substr 할 필요 없이 그냥 '경기도'로 별명// '특별시' 혹은 '광역시' 는 요청결과가 동일하므로 case when 안에서 or로 묶어서 간단하게! 여기서는 시도가 다르므로 subsr 로 5섯글자까지 추출!

 

< 조건을 사용할 수 있는 경우 >

  1) 새로운 카테고리 만들기  (ex. 음식타입, 고객분류 등)

  2) 연산식 적용할 조건 지정하기 (ex. 현금일 때 수수료율, 카드일 때 수수료율 혹은 계산방식 다르게 적용시)

  3) 다른 문법 안에서 적용하기 (- if, case 문 안에 다른 문법이나 연산 적용가능

      (ex. concat문으로 여러 컬럼 합칠 때, rating 있는 경우 rating을 넣어주고 없는 경우 아무것도 넣지 않도록 concat문 안에 if문 넣을 수 있음)

 

7. User Segmentation 

 

ex) ★ ★ ★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

** 나이와 성별로 그룹 나누라고 하는데 when 안에 '괄호'로 조건1 (나이범위) 묶어주고 and로 조건2 추가 then !!

** 그 외 나이들은 NULL 이 나와서 요거 어떡하지 ? 했는데,, where 로 딱 그 범위만 지정해줄 수 있음!!

 

 

ex) ★ ★ ★ 음식 단가, 음식 종류 별로 음식점 그룹 나누기 ★ ★ ★

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

 

** 단가를 price/quantity 로 기재!  아시아식 할 때 in 으로 묶은 것. 그외를 역발상으로 not in 으로 앞에꺼 다 묶어서 이게 아닌 것들 '기타' 로 보내버림.   segmentation 은 case로 2개 이상 조건 묶어서 한번에 나누기!

 

<조건문으로 서로 다른 식 적용한 수수료 구해보기>

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

 

** 식을 세울 때, 앞에 있다고 먼저 쓰는게 아니라, 효율적으로 식 세우는 연습! // 곱하는 값이 없는 것도 있는 '서울' 조건은 if 르로 추후에 조건에 따라 곱하기로 뒤에 붙히고// 25분 초과일 때, 30분 초과일 때 조건이 다르므로 이 식으로 case when은 나눔!!// 마지막으로 NULL 안 나오게 그 밖에 수수료는 else 0 적어주기! 

 

 

 

• 주문 시기와 음식 수를 기반으로 배달할증료 구하기
• (주문 시기 : 평일 기본료 = 3000 / 주말 기본료 = 3500 음식 수 : 3개 이하이면 할증 없음 / 3개 초과이면 기본료 * 1.2)

** CASE 문으로 혼자

SELECT order_id,

price,

quantity,

day_of_the_week,

case when day_of_the_week = 'weekday' then 3000*if(quantity>3,1.2,1)

when day_of_the_week = 'weekend' then 3500*if(quantity>3,1.2,1)

end '배달할증료'

FROM food_orders

 

** IF문 으로 (정답) 

SELECT order_id,

price,

quantity,

day_of_the_week,

if(day_of_the_week='weekday',3000,3500)*(if(quantity>3,1.2,1)) '배달할증료'

FROM food_orders

 

** 조건이 주중 아니면 주말이므로 IF 문 가능!! 곱하기 뒤 IF문 또 가능// 조건이 YES of NO 일 때 IF문 으로 좀더 간단하게 게!! 

 

8. 오류해결

 

문자/숫자 계산을 했더니 오류 -> avg, substring 등 함수를 썼더니 에러메세지에 ‘data type’ 단어가 뜨면서 실행되지 않아요

->  우리가 실습하는 Mysql 과 다르게, 다른 SQL 문법에서는 data type 이 다를 때 연산이 되지 않을 수 있음.

예를 들면, rating 은 숫자가 포함되어 있지만 문자 형으로 저장이 되어있음.

(출력 결과 컬럼명 옆의 ‘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' and delivery_time>=25 then 'Late'

when day_of_the_week='weekend' and delivery_time>=30 then 'Late'

else "On_time"

end '지연여부'

from food_orders

 

** restaurante 이름추가 // 지연되지 않은 값은 else 'On_time'으로 지정//