METHOD: [IF,CASE]

범주별로 값을 구할 때 썼던 Group by 처럼 조건도 카테고리별로 줄 수 있는 방법

예를 들어 범주별로 다른 연산(계산, 문자 바꾸기) 를 적용하는 방법

-> '원하는 범주'를 조건으로 주고, 해당 범주에 적용하고 싶은 것을 지정해주는 방식으로 가능

 

[IF] 조건을 지정해주는 가장 기초 문법(엑셀 기능과 유사) 

 

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

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

 

 

 

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

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

 

 

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

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

 

문제3. 잘못된 이메일 주소 (gmail) 만 수정을 해서 사용

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

 

 

---

[CASE] 조건을 여러가지 지정하고 싶을 때

 

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

 

• Case 문은 각 조건별로 적용 할 값을 지정해 줄 수 있음
• 조건별로 지정을 해주기 때문에 if 문을 여러번 쓴 효과를 낼 수 있음: case(조건1, 값1, case(조건2, 값2, 값3))

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

case when 조건1 then 값(수식)1
     when 조건2 then 값(수식)2
     else 값(수식)3
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

 

 

 

문제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

 

---

조건을 사용할 수 있는 경우

 

1. 새로운 카테고리 만들 때

• 음식 타입과 같이 새로운 카테고리 만들기 가능 
• 고개들의 분류도 만들기 가능

 

2. 연산식을 적용할 조건 지정하기

• 수수료를 계산할 때 흔히들 현금 사용, 카드사용을 나누는데 현금일 때의 수수료율과 카드일 때의 수수료율이 다르다면, 연산식을 만들 때 if 문 혹은 case 문으로 각각 다른 수수료율 혹은 수수료 계산 방식을 적용할 수 있음

3. 다른 분법 안에서 적용하기

• if, case 문 안에 다른 문법이나 연산을 넣을 수도 있지만, 다른 문법 안에 조건문을 넣을 수도 있음
  예를 들어 concat 문으로 여러 컬럼을 합칠 때, rating 이 있을 때는 rating 을 넣어주고 없을 때는 아무것도 넣지 않도록, concat 안에 if 문을 넣어줄 수 있음

---

연습문제 풀어보기

 

1. 새로운 카테고리 만들기 - 조건문과 수식을 이용하여 간단한 User Segmentation 해보기

 

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

• Query 를 적기 전에 흐름을 정리해보기 
  1. 어떤 테이블에서 데이터를 뽑을 것인가 → 고객 테이블
  2. 어떤 컬럼을 이용할 것인가 → 이름, 나이, 성별
  3. 어떤 조건을 지정해야 하는가 → 나이가 10세 이상, 30세 미만
  4. 어떤 함수 (수식) 을 이용해야 하는가 → 조건문
• 구문으로 만들기 
  1. 어떤 테이블에서 데이터를 뽑을 것인가 → from customers
  2. 어떤 컬럼을 이용할 것인가 → name, age, gender
  3. 어떤 조건을 지정해야 하는가 → where age between 10 and 29
  4. 어떤 함수 (수식) 을 이용해야 하는가 → case when …. end

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. 음식 단가, 음식 종류 별로 음식점 그룹 나누기 

(Korean = 한식 Japanese, Chinese, Thai, Vietnamese, Indian = 아시아식 그외 = 기타)

(가격 = 5000, 15000, 그 이상)

 

• Query 를 적기 전에 흐름을 정리해보기 
  1. 어떤 테이블에서 데이터를 뽑을 것인가 → 주문 테이블
  2. 어떤 컬럼을 이용할 것인가 → 주문 금액, 주문 수량, 음식 종류
  3. 어떤 조건을 지정해야 하는가 → X
  4. 어떤 함수 (수식) 을 이용해야 하는가 → 조건문
• 구문으로 만들기 
  1. 어떤 테이블에서 데이터를 뽑을 것인가 → from food_orders
  2. 어떤 컬럼을 이용할 것인가 → price, quantity, cuisine_type
  3. 어떤 조건을 지정해야 하는가 → X
  4. 어떤 함수 (수식) 을 이용해야 하는가 → case when …. end

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

 

---

2. 조건문으로 서로 다른 식을 적용한 수수료 구해보기 - 조건문을 이용하여 다른 수식을 적용해보기

문제1.

• 지역과 배달시간을 기반으로 배달수수료 구하기 (식당 이름, 주문 번호 함께 출력)
• (지역 : 서울, 기타 - 서울일 때는 수수료 계산 * 1.1, 기타일 때는 곱하는 값 없음 시간 : 25분, 30분 - 25분 초과하면 음식 가격의 5%, 30분 초과하면 음식 가격의 10%)

• Query 를 적기 전에 흐름을 정리해보기 (해답)
  1. 어떤 테이블에서 데이터를 뽑을 것인가 → 주문 테이블
  2. 어떤 컬럼을 이용할 것인가 → 식당 이름, 주문 번호, 지역, 배달시간, 주문 금액
  3. 어떤 조건을 지정해야 하는가 → X
  4. 어떤 함수 (수식) 을 이용해야 하는가 → 조건문
• 구문으로 만들기 
  1. 어떤 테이블에서 데이터를 뽑을 것인가 → from food_orders
  2. 어떤 컬럼을 이용할 것인가 → restaurant_name, order_id, addr, derlivery_time, price
  3. 어떤 조건을 지정해야 하는가 → X
  4. 어떤 함수 (수식) 을 이용해야 하는가 → case when, if

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

 

---

 

문제2. 주문 시기와 음식 수를 기반으로 배달할증료 구하기

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

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

 

• Query 를 적기 전에 흐름을 정리해보기 
  1. 어떤 테이블에서 데이터를 뽑을 것인가 → 주문 테이블
  2. 어떤 컬럼을 이용할 것인가 → 주문 수량, 주문 시기
  3. 어떤 조건을 지정해야 하는가 → X
  4. 어떤 함수 (수식) 을 이용해야 하는가 → 조건문
• 구문으로 만들기 
  1. 어떤 테이블에서 데이터를 뽑을 것인가 → from food_orders
  2. 어떤 컬럼을 이용할 것인가 → quantity, day_of_the_week
  3. 어떤 조건을 지정해야 하는가 → X
  4. 어떤 함수 (수식) 을 이용해야 하는가 → if

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

 

---

Data Type 오류 해결하기

숫자 계산이나 문자 가공 시 자주 발생하는 오류

 

문자/숫자 계산을 할 때 avg, substring 등 함수를 썼더니 에러메세지에 ‘data type’ 단어가 뜨면서 실행되지 않는 경우

• 실습하는 Mysql 과 다르게, 다른 SQL 문법에서는 data type 이 다를 때 연산이 되지 않을 수 있음
  rating 은 숫자가 포함되어 있지만 문자 형으로 저장이 되어있음
  (출력 결과 컬럼명 옆의 ‘ABC’ 혹은 ‘123’ 을 확인. ABC’ 는 문자로 저장이 되어있다는 의미)

 

• 따라서 문자, 숫자를 혼합하여 함수에 사용 할 때에는 데이터 타입을 변경해주어야 함

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

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