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

• SUBQUERY 필요한 경우

1. 여러 번의 연산을 수행해야 할 때
   ex)
   수수료를 부과할 수 있는 시간을 구하고
   → 구해진 시간에 주문 금액별로 가중치를 주고
   → 가중치를 적용한 결과로 최종 예상 배달비를 계산할 때
2. 조건문에 연산 결과를 사용해야 할 때
   ex) 음식 타입별 평균 음식 주문금액 따라 음식비 상/중/하 를 나누고 싶을 때
3. 조건에 Query 결과를 사용하고 싶을 때
   ex) 30대 이상이 주문한 결과만 조회하고 싶을 때

• SUBQUERY 기본 구조

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

괄호 뒤에 a 라는 별명을 붙여준거임!

또는

select column1, column2
from table1
where column1 = (select col1 from table2)

• 예시

// 주문 테이블에서  주문 번호, 음식점명, 음식 준비시간을 가져오기 
select order_id, restaurant_name, food_preparation_time
from 
(
select order_id, restaurant_name, food_preparation_time
from food_orders
) a

// 위 예시에 추가해서 음식 준비시간이 25분보다 초과한 시간을 가져오기
select order_id, restaurant_name, if(over_time>=0, over_time, 0) over_time
from 
(
select order_id, restaurant_name, food_preparation_time-25 over_time
from food_orders
) a

두번째 예시에서 첫번째 줄에 if문을 쓰는 이유는 subquery에서 계산한 food_preparation_time - 25가 음수(늦지 않는 경우)가 나오기 때문에 if 문을 사용하여 양수(늦은 경우)는 그대로, 음수는 0으로 변환시켜주기 위함!

---

실습 1

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

• 문제 : 음식점의 평균 단가별 segmentation 을 진행하고, 그룹에 따라 수수료 연산하기
  (수수료 구간 -
  ~5000원 미만 0.05%
  ~20000원 미만 1%
  ~30000원 미만 2%
  30000원 초과 3%)
• 답

select restaurant_name,
       price_per_plate*ratio_of_add "수수료"
from 
(
select restaurant_name,
       case when price_per_plate<5000 then 0.005
            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,
       price_per_plate
from 
(
select restaurant_name, avg(price/quantity) price_per_plate
from food_orders
group by 1
) a
) b

코드 이해할 때는 맨 밑 select -> 가운데 select -> 맨 위 select 순으로 이해하기

---

실습 2

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

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

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

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

select restaurant_name,
       case when sum_of_quantity<=5 then 0.1
            when sum_of_quantity>15 and sum_of_price>=300000 then 0.005
            else 0.01 end ratio_of_add
from 
(
select restaurant_name,
       sum(quantity) sum_of_quantity,
       sum(price) sum_of_price
from food_orders
group by 1
) a

---

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

• JOIN이 필요한 경우

1. 주문 가격은 주문테이블에 있지만, 어떤 수단으로 결제를 했는지는 결제테이블에 있을 때
2. 주문을 한 사람을 확인하기 위해, 주문 테이블과 고객 테이블에서 각각 정보를 가져와서 엑셀에서 합쳐줘야할 때
3. 주문 건 별 수수료를 계산하려면 수수료율이 필요한데, 결제 테이블에 있어서 어떻게 연산할 수 있을지 모를 때

• JOIN의 기본 원리
  <JOIN 전>
  
  위처럼 각각 주문 정보와 고객 정보가 테이블이라고 할 때, 고객 ID 를 기준으로 필요한 값을 가져와 주는 원리!
  <JOIN 후>
  

• JOIN의 종류
  

1. LEFT JOIN : 공통 컬럼 (키값) 을 기준으로, 하나의 테이블에 값이 없더라도 모두 조회되는 경우를 의미 -> 모든 행을 조인
   
2. INNER JOIN : 공통 컬럼 (키값) 을 기준으로, 두 테이블 모두에 있는 값만 조회합니다. -> 공통된 행만을 조인

• 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.공통컬럼명

🍀 공통컬럼은 묶어주기 위한 ‘공통 값’ 이기 때문에 두 테이블의 컬럼명은 달라도 괜찮음! 🍀
ex) 주문정보에는 ‘고객ID’, 고객정보에는 ‘고객아이디’ 라고 컬럼명이 되어있다면 -> 테이블1.고객ID=테이블2.고객아이디

각 테이블1과 테이블2를 a, b로 별명 설정 가능!

• 예시

// 주문 테이블과 고객 테이블을 cusomer_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

---

실습 3

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

• 문제 : 한국 음식의 주문별 결제 수단과 수수료율을 조회하기
  (조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 결제 수단, 수수료율)
  결제 정보가 없는 경우도 포함하여 조회 -> "left join" 하라는 소리
• 답

select distinct c.name, c.age, c.gender, f.restaurant_name
from food_orders f left join customers c on f.customer_id = c.customer_id
order by c.name

• 문제 : 고객의 주문 식당 조회하기 (조회 컬럼 : 고객 이름, 연령, 성별, 주문 식당)
  고객명으로 정렬, 중복 없도록 조회 -> "inner join" 하라는 소리 + DISTINCT 쓰라는 소리

DISTINCT 는 중복을 제거해줌 !

• 답

select distinct c.name, c.age, c.gender, f.restaurant_name
from food_orders f left join customers c on f.customer_id = c.customer_id
order by c.name

NULL 값 제거하고 싶다면? where c.name is not null 추가 !

select distinct c.name,
       c.age,
       c.gender,
       f.restaurant_name
from food_orders f left join customers c on f.customer_id=c.customer_id
where c.name is not null // null 값 제거
order by c.name

---

실습 4

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

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

• 답

select a.order_id,
       a.restaurant_name,
       a.price,
       b.vat,
       a.price*b.vat "수수료율"
from food_orders a inner join payments b on a.order_id=b.order_id

• 문제 : 50세 이상 고객의 연령에 따라 경로 할인율을 적용하고, 음식 타입별로 원래 가격과 할인 적용 가격 합을 구하기
  (조회 컬럼 : 음식 타입, 원래 가격, 할인 적용 가격, 할인 가격)
  할인 : (나이-50)* 0.005,
  고객 정보가 없는 경우도 포함하여 조회, 할인 금액이 큰 순서대로 정렬 -> "left join", 내림차순으로 해야함

• 답

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

---

숙제

• 문제 : 식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기
  평균 음식 주문 금액 기준 : 5,000 이하 / ~10,000 / ~30,000 / 30,000 초과
  평균 연령 : ~20대 / 30대 / 40대 / 50대 이상

• 답

select f.restaurant_name, 
case when avg(f.price) <= 5000 then 'price_group1'
     when avg(f.price) <= 10000 then 'price_group2'
     when avg(f.price) <= 30000 then 'price_group3'
     else 'price_group4' end price_group,
case when avg(c.age) <= 29 then 'age_group1'
     when avg(c.age) <= 39 then 'age_group2'
     when avg(c.age) <= 49 then 'age_group3'
     else 'age_group4' end age_group
from food_orders f inner join customers c on f.customer_id = c.customer_id
group by f.restaurant_name 
order by f.restaurant_name