[데이터분석] REPLACE, SUBSTRING, CONCAT, IF, CASE

• 함수명 : replace
• 언제 사용?
• 다른 문자로 수정하고 싶을 때, 하나하나 수동은 양이 많음
• 주소 전체가 아닌 '시도' 정보만 필요. 서울 통계만 필요한데 전체주소->서울로 문자 변경

​

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

​

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

select restaurant_name "원래 상점명", replace(restaurant_name, 'Blue', 'Pink') "바뀐 상점명" from food_orders where restaurant_name like '%Blue Ribbon%'

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

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

​

• 함수명 : substring (substr)
• 전체 데이터가 아닌 특정 문자만 필요할 때, SQL 로 필요한 부분만 조회
• 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 '%서울%'

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

1. 어떤 테이블에서 데이터를 뽑을 것인가 → 주문 테이블 from food_orders
2. 어떤 컬럼을 이용할 것인가 → 주문 금액, 음식 타입, 주소 price, cuisine_type, addr
3. 어떤 조건을 지정해야 하는가 → 서울 지역 where addr like ‘%서울%’
4. 어떤 함수 (수식) 을 이용해야 하는가 → avg(price), substring(addr, 1, 2)

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

[실습] 이메일 도메인별 고객 수와 평균 연령 구하기

1. 어떤 테이블에서 데이터를 뽑을 것인가 → 고객 테이블 from customers
2. 어떤 컬럼을 이용할 것인가 → 이메일 주소, 나이, 고객번호 email, age, customer_id
3. 어떤 조건을 지정해야 하는가 → 없음
4. 어떤 함수 (수식) 을 이용해야 하는가 → avg(age), count(customer_id) or count(1), substring(email, 10)

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

왜 substring(email, 10) "이메일 도메인" 대신 1로 적어 줄 수 있는건지 이해가 안 된다 ㅠ

​

​

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

1. 어떤 테이블에서 데이터를 뽑을 것인가 → from food_orders
2. 어떤 컬럼을 이용할 것인가 → addr, restaurant_name, cuisine_type, order_id
3. 어떤 조건을 지정해야 하는가 → X
4. 어떤 함수 (수식) 을 이용해야 하는가 → count(1), substring(addr, 1, 2), concat(’[’, 뽑은시도, ‘] ‘, restaurant_name, ‘ (’, cuisine_type, ‘)’)

select concat('[', substr(addr, 1, 2),']', restaurant_name,'(',cuisine_type, ')') "음식점", count(1) "주문건수" from food_orders group by 1

​

​

• 조건에 따라 포맷을 다르게 변경해야 한다면 (IF, CASE)
• 언제사용?

1. 음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Korean’ 이 아닌 경우에는 ‘기타’ 라고 지정하고 싶어요
2. 주소의 시도를 ‘경기도’ 일때는 ‘경기도’, 아닐 때는 앞의 두 글자만 사용하고 싶어요
3. 음식 단가를 주문 수량이 1일 때는 음식 가격, 주문 수량이 2개 이상일 때는 음식가격/주문수량 으로 지정하고 싶어요

​

• 조건에 따라 다른 방법을 적용하고 싶을 때 - If 문 기초
• 예시) 음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Korean’ 이 아닌 경우에는 ‘기타’ 라고 지정하고 싶어요
• if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)

​

​

[실습1] 음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Korean’ 이 아닌 경우에는 ‘기타’ 라고 지정

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

[실습2] 02. 번 실습에서 ‘문곡리’ 가 평택에만 해당될 때, 평택 ‘문곡리’ 만 ‘문가리’ 로 수정

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

• 코드 해석: 만약 주소에 평택군 포함하면, 문곡리를 -> 문가리로 바꿔주고,
• 아니라면 그냥 주소만 나타내줘.
• where like : 문곡리를 포함한 주소

​

[실습3] 03. 번 실습에서 잘못된 이메일 주소 (gmail) 만 수정을 해서 사용

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

조건: 이메일이 쥐메일 포함하면, 쥐메일을 @gmail로 바꾸고, 나머지는 그냥 email로 써줘.

10번째 글자부터 불러오겠다.

​

• 조건을 여러가지 지정하고 싶을 때 - Case 문 기초
• 조건별로 지정을 해주기 때문에 아래와 같이 if 문을 여러번 쓴 효과를 낼 수 있습니다.
• if(조건1, 값1, if(조건2, 값2, 값3))
• 예시) 음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Japanese’ 혹은 ‘Chienese’ 일 때는 ‘아시아’, 그 외에는 ‘기타’ 라고 지정

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

[실습1] 음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Japanese’ 혹은 ‘Chienese’ 일 때는 ‘아시아’, 그 외에는 ‘기타’ 라고 지정

select case when cuisine_type='Korean' then '한식' when cuisine_type in ('Japanese', 'Chinese')then '아시아' else '기타' end "음식타입", cuisine_type from food_orders

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

select order_id, price, quantity, case when quantity=1 then price when quantity>=2 then price/quantity end "음식 단가" from food_orders

else는 혹시 모를 조건에 부합하질 않을 경우를 위한 것으로 생략이 가능하다!

​

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

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

​

​

[실습] 10세 이상, 30세 미만의 고객의 나이와 성별로 그룹 나누기 (이름도 같이 출력)

select 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 "고객 분류", name, age, gender 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

not in 여기에 해당하지 않으면!

​