[SQL]REPLACE, SUBSTRING, CONCAT,IF, CASE

1,2주차 복습하기

• 1. SQL 기본 구조 복습 및 데이터 조회 조건과 매칭해보기
  • SQL문의 기본 구조
  • select from where group by order by
  • 조회 조건과 매칭해보기
    • 주문 테이블에서 → from
    • 주문 수량이 1건인 주문건의 → where
    • 음식 가격의 평균을 음식 종류별로 조회하여 → avg, group by
    • 음식 가격이 높은 순서대로 정렬하기 → order by

• 조건을 지정하는 방법
  • 비교연산자 : <, >, =, <>
  • 다양한 구문 : IN, BETWEEN, LIKE
  • 여러가지 조건의 적용 : AND, OR, NOT

---

 

3주차에는 문자 데이터의 형태를 변경하고 , 조건(if)에 따라 다른 연산을 하는 방법을 알아보자.

 

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

데이터를 조회하다보면, Query 결과를 그대로 이용하지 못하는 경우가 있다.한 번 각각의 케이스와 해결 방법을 알아보자.

 

REPLACE: 특정 문자를 다른 것으로 바꿀 수 있는 기능을 제공

사용방법

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

ex)

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

 

SUBSTRING(SUBSTR): 특정 문자만 골라서 조회할 수 있는 기능을 제공

사용방법

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

ex)

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

 

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

select SUBSTR(addr,1,2) as '지역',
       cuisine_type as '타입',
       avg(price) as '평균 금액'
from food_orders
where addr like '%서울%'
group by cuisine_type

 

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

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

 

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

select concat('[',SUBSTR(addr,1,2),']',restaurant_name,'(',cuisine_type,')'),
       COUNT(order_id) as '총 주문건수'
from food_orders
group by cuisine_type

 

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

 

IF: 조건에 따라 연산을 적용할 수 있는 기능

사용방법

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

 

 

★select 부분 해석해보기

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

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

 

 

select substr(if(email like '%gmail%',replace(email,'gmail','@gmail'),email),10),

count(customer_id) as '고객수',

avg(age) as 평균연령

FROM customers

group by 1

 

 

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

 

★ ★ select 부분 해석:

if(평택이 포함된 addr에서, replace (addr에서,문곡리가 있다면 문가리로 바꾸고,아니면 addr 그대로 출력)  

 

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

 

 

 

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

 

이게 조금 어려웠다. 문제 요지 자체가 substr을 사용할때 아이디가 동일하게 8글자라고 가정을 하고 풀어야하기 때문에 좋은 문제는 아니라고 생각했다.

 

★ ★ select 부분 해석:

substr(if('gmail'이 포함된 email에서,email에서,gmail이 있다면 @gmail로 바꾸고,아니면 emal그대로 출력),10번째 숫자부터 출력)

select substr(if(email like '%gmail%',replace(email,'gmail','@gmail'),email),10),
       count(customer_id) as '고객수',
       avg(age) as 평균연령
FROM customers
group by 1

 

 

CASE: 조건을 지정하다보면, 두 개 이상 지정을 해야 할 경우가 생긴다. 이 때는 case 문을 이용하여 여러번의 if 문을 적용 한 효과를 낼 수 있다.

 

사용방법

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

 

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

select restaurant_name ,
       case when (cuisine_type='korean') then  '한식'
            when (cuisine_type='Japanese' or 'Chienese') then '아시아'
            else '기타'
       end as '음식타입'
FROM food_orders

 

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

2-1 Case when을 사용한방법

select order_id,price,quantity,
       case when quantity=1 then price
            when quantity>=2 then price/quantity
       end as '음식단가'
FROM food_orders

 

 

2-2 IF를 사용한 방법

select order_id,price,quantity,
    if(quantity=1,price,price/quantity) as '음식 단가'
FROM food_orders

 

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

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

이 코드는 그닥 좋은 코드는 아니라고 생각했다. 왜냐하면 수동적으로 주소의 시도의 텍스트 글자수를 기반으로 코드를 작성 했기 때문이다. 만약 경상남도 , 전라남도 등이 있다면 어떤 코드로 추출할 수 있을까

 

 

 

지금까지 배운것들을 종합하여 간단한 User Segmentation 을 만들어 볼려고한다.

 

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

select name,age,gender,
       Case when (age between 10 and 19) and gender='male' then '20대남자'
            when (age between 10 and 19) and gender='female' then '20대여자'
            when (age between 20 and 29) and gender='male' then '30대남자'
            when (age between 20 and 29) and gender='female' then '30대여자'
       end as '그룹'
from customers
where age between 10 and 29

 

2. [실습] 지역과 배달시간을 기반으로 배달수수료 구하기 (식당 이름, 주문 번호 함께 출력)

 

(지역 : 서울, 기타 - 서울일 때는 수수료 계산 * 1.1, 기타일 때는 곱하는 값 없음 시간 : 25분, 30분 - 25분 초과하면 음식 가격의 5%, 30분 초과하면 음식 가격의 10%)

 

이 문제는 위에처럼 수동으로 일일이 입력하여 작성하였는데 if 문을 쓰니 코드를 최적화 시킬수 있었다.

select order_id,restaurant_name ,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*0.1*if(addr like '%서울%',1.1,1)
            else 0
       end as '수수료'        
from food_orders

 

 

3. [실습] 주문 시기와 음식 수를 기반으로 배달할증료 구하기

(주문 시기 : 평일 기본료 = 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) as '할증료'
from food_orders

 

3주차 마무리 문제

다음의 조건으로 배달시간이 늦었는지 판단하는 값을 만들어주세요.

주중 : 25분 이상

주말 : 30분 이상

 

select order_id,restaurant_name ,day_of_the_week ,delivery_time ,

Case when day_of_the_week='Weekend' and delivery_time>=30 then 'Late'

when day_of_the_week='Weekday' and delivery_time>=25 then 'Late'

else 'On-time'

End as '지연여부'

from food_orders