SQL 4일차

SQL로 간단한 User Segmentation 실습

조건문과 수식을 이용하여 간단한 User Segmentation를 해보자
1. 10세 이상, 30세 미만의 고객의 나이와 성별로 그룹 나누기(이름도 같이 출력)

```sql
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 as "고객 분류"
from customers
where age between 10 and 29;
```

2. 음식 단가, 음식 종류 별로 음식점 그룹 나누기
   • korean = 한식, japanese, chinese, thai, vietnamese, indian = 아시아식 그외 = 기타
   • 가격 : 5000 미만, 5000 이상, 150000 미만, 150000 이상

   select restaurant_name, price/quantity as "단가", cuisine_type,
       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 as "식당 그룹"
   from food_orders;

---

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

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

   select restaurant_name, order_id, price, delivery_time, addr,
       case when delivery_time > 30 
       then price*0.1*if(addr like '%서울%', 1.1, 1)
       when delivery_time between 25 and 30 
       then price*0.05*if(addr like '%서울%', 1.1, 1)
       else 0 end as "수수료"
   from food_orders;

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

   select restaurant_name, order_id, price, quantity, day_of_the_week,
       case when day_of_the_week = 'weekend' 
       then 3500*if(quantity > 3, 1.2, 1)
       when day_of_the_week = 'weekday' 
       then 3000*if(quantity > 3, 1.2, 1)
       end as "할증료"
   from food_orders;

   select restaurant_name, order_id, price, quantity, day_of_the_week,
       if(day_of_the_week='weekend', 3500, 3000)*
       if(quantity > 3, 1.2, 1) as "할증료"
   from food_orders;

---

1. 여러번의 연산을 한 번에 SQL 문으로 수행하기(SUBQUERY)

연산이 여러번 필요할 때 긴 쿼리문 보다 조금 더 효율적인 방법
1. Subquery 가 필요한 경우
- 여러번의 연산을 수행해야 할때
- 예시

```
1. 수수료를 부과할 수 있는 시간을 구하고
2. 구해진 시간에 주문 금액별로 가중치를 주고
3. 가중치를 적용한 결과로 최종 예살 배달비를 계산할 때
```

2. Subquery 문의 기본 구조
   • 이름 그대로 Query 안에 sub로 들어간 구문이라 생각하면 편하다

   select column1, special_column
   from (/*subquery*/
       select column1, column2 as special_column
       from table1) as a;

3. Subquery 문 이용 실습
   • 주문 테이블에서 주문 번호, 음식점명, 음식 준비시간 가져오기

   select order_id, restaurant_name, food_preparation_time
   from (
       select order_id, restaurant_name, food_preparation_time
       from food_orders
   ) as a;

   • Subquery 문 안을 수정해서, 음식 준비시간이 25분 초과한 시간 조회

   select order_id, restaurant_name, if(over_time>=0, over_time, 0)
   as over_time
   from (
       select order_id, restaurant_name, food_preparation_time-25
       as over_time
       from food_orders
   ) as a;