SQL 5일차

User Segmentation 과 조건별 수수료를 Subquery 로 결합해보기

조건문과 Subquery 를 결합하여 user segmentation 과 연산해보자

1. 음식점의 평균 단가별 segmentation 을 진행하고 그룹에 따라 수수료 연산

   • 수수료 구간
     • ~5000원 미만 = 0.05%
     • ~20000원 미만 = 1%
     • ~30000원 미만 = 2%
     • 30000원 초과 3%

   select restaurant_name,
       price_per_plate*ratio_of_add as "수수료"
   from
   (
   select restaurant_name, price_per_plate,
       case when price_per_plate < 5000 then 0.05
       when price_per_plate between 5000 and 19999 then 0.01
       when price_per_plate between 20000 and 29999 then 0.02
       else 0.03 end ratio_of_add
   from
   (
   select restaurant_name, 
       avg(price/quantity) as price_per_plate
   from food_orders
   group by restaurant_name
   ) as a
   ) as b

2. 음식점의 지역과 평균 배달시간으로 segmentation 진행

   select restaurant_name, sido, avg_delivery_time,
       case when avg_delivery_time <= 20 then '<=20'
       when avg_delivery_time > 20 and avg_delivery_time <= 30 then '20<x<=30'
       else '>30' end avg_delivery_time_segment
   from
   (
   select restaurant_name,
       substr(addr, 1, 2) as sido,
       avg(delivery_time) as avg_delivery_time
   from food_orders
   group by restaurant_name, sido
   ) as a

---

복잡한 연산을 Subquery로 수행하기

하나의 쿼리문에서 수행하기 어려운 복잡한 연산을 Subquery로 실행

1. 음식 타입별 총 주문수량과 음식점 수를 연산하고, 주문수량과 음식점수 별 수수료율을 산정하기
   • 음식점수 5개 이상, 주문수 30개 이상 > 수수료 0.5%
   • 음식점수 5개 이상, 주문수 30개 미만 > 수수료 0.8%
   • 음식점수 5개 미만, 주문수 30개 이상 > 수수료 1%
   • 음식점수 5개 미만, 주문수 30개 미만 > 수수료 2%

   select cuisine_type,
       total_quantity, count_res,
       case when count_res >= 5 and total_quantity >= 30 then 0.005
       when count_res >= 5 and total_quantity < 30 then 0.008
       when count_res < 5 and total_quantity >= 30 then 0.01
       when count_res < 5 and total_quantity < 30 then 0.02
       end as rate
   from
   (
   select cuisine_type,
       sum(quantity) as total_quantity,
       count(distinct restaurant_name) as count_res
   from food_orders
   group by cuisine_type
   ) as a;

2. 음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기
   • 할인조건 : 수량이 5개 이하 > 10%
   • 수량이 15개 초과, 총 주문금액이 300,000 이상 > 0.5%
   • 이 외에는 일괄 1%

   select restaurant_name,
       total_quantity, total_price,
       case when total_quantity <= 5 then 0.1
       when total_quantity > 15 and total_price >= 300000 then 0.05
       else 0.1 end discount_rate
   from 
   (
   select restaurant_name, 
       sum(quantity) as total_quantity,
       sum(price) as total_price
   from food_orders
   group by restaurant_name
   ) as a;

---

필요한 데이터가 서로 다른 테이블에 있을 때 조회하기(JOIN)

1. JOIN의 기본 원리와 종류

   • 두 테이블이 가진 공통 컬럼을 기준으로 합쳐서 각각의 테이블에서 필요한 데이터를 조회할 수 잇도록 만들어주는 기능
   • LEFT JOIN : 공통 컬럼(키 값)을 기준으로 하나의 테이블에 값이 없더라도 모두 조회
   • INNER JOIM : 투 테이블 모두에 있는 값만 조회

2. JOIN의 기본 구조

   - LEFT JOIN -
   select 조회할 컬럼
   from 테이블1 a left join 테이블2 b on a.공통컬럼명 = b.공통컬럼명;
   
   - INNER JOIN -
   select 조회할 컬럼
   from 테이블1 a inner join 테이블2 b on a.공통컬럼명 = b.공통컬럼명;

   공통컬럼은 묶어 주기 위한 '공통 값' 이기 때문에 두 테이블의 컬럼명은 달라도 괜찮다.
   예를 들어 주문정보에는 '고객ID', 고객정보에는 '고객아이디'라고 컬럼명이 되어 있다면 주문정보.고객ID = 고객정보.고객아이디와 같이 묶어줄 수 있다.

3. JOIN의 사용예시시

   • 주문 테이블과 고객 테이블을 customer_id 를 기준으로 left join으로 묶어보기 (조회할 컬럼 : order_id, customer_id, restaurant_name, price, name, age, gender)

   select a.order_id,
      a.customer_id,
      a.restaurant_name,
      a.price,
      b.name,
      b.age,
      b.gender
   from food_orders a left join customers b on 
   a.customer_id=b.customer_id

---

JOIN 으로 두 테이블의 데이터 조회하기

1. 한국 음식의 주문별 결제 수단과 수수료율을 조회하기

   • 조회할 컬럼 : 주문번호, 식당이름, 주문가격, 결제수단, 수수료율
   • 결제 정보가 없는 경우도 포함해서 조회

   select a.order_id,
      a.restaurant_name,
      a.price,
      b.pay_type,
      b.vat
   from food_orders as a left join payments as b
   on a.order_id = b.order_id
   where cuisine_type='korean';

2. 고객의 주문 식당 조회하기

   • 조회할 컬럼 : 고객이름, 연령, 성별, 주문식당
   • 고객명으로 정렬, 중복 없도록 조회

   select distinct b.name,
       b.age,
       b.gender,
       a.restaurant_name
   from food_orders as a left join customers as b
   on a.customer_id = b.customer_id
   order by b.name;

---

JOIN으로 두 테이블의 값을 연산하기

1. 주문 가격과 수수료율을 곱하여 주문별 수수료 구하기
   • 조회할 컬럼 : 주문번호, 식당이름, 주문가격, 수루료율, 수수료
   • 수수료율이 있는 경우만 조회

   select f.order_id,
       f.restaurant_name,
       f.price,
       p.vat,
       f.price * p.vat as vat2
   from food_orders as f inner join payments as p
   on f.order_id = p.order_id;

2. 50세 이상 고객의 연령에 따라 경로 할인율을 적용하고, 음식 타입별로 원래 가격과 할인 적용 가격 합을 구하기
   • 조회할 컬럼 : 음식타입, 원래 가격, 할인적용가격{(나이-50)*0.005}
   • 고객 정보가 없는 경우도 포함하여 조회, 할인 금액이 큰 순서대로 정렬

   select cuisine_type,
       sum(price) as price,
   	sum(price*discount_rate) as discounted_price
   from
   (
   select f.cuisine_type,
       f.price,
       c.age,
       (c.age-50)*0.005 as discount_rate
   from food_orders as f left join customers as c
   on f.customer_id = c.customer_id
   where c.age >= 50
   ) as a
   group by cuisine_type
   order by discounted_price desc;

---