[MySQL] 문제(SubQuery, JOIN)

관련 설명 :
https://velog.io/@dyllis_lin/MySQL-%EA%B8%B0%EB%A1%9D4

- Subquery 문 안을 수정해서, 음식 주문시간이 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 AS over_time
FROM food_orders
) a  

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

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

SELECT restaurant_name
      ,price_per_plate * ratio_of_add AS "수수료"
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 AS ratio_of_add,
        price_per_plate
    FROM 
    
    (
        SELECT restaurant_name, AVG(price / quantity) AS price_per_plate
        FROM food_orders fo 
        GROUP BY restaurant_name
    ) AS a
) AS b

구조

1. 서브쿼리 'a':
   • 각 음식점의 평균 가격을 계산
   • 데이터를 그룹화하고(GROUP BY), 평균 가격 계산
   • 'restaurant_name' 열과 'price_per_plate' 열을 결과로 반환
     
2. 외부 서브쿼리 'b':
   • 서브쿼리 'a'에서 생성된 결과를 바탕으로 각 음식점의 수수료 계산
   • 수수료 계산을 위해 CASE 문을 사용하여 가격대에 따라 다른 수수료 비율을 적용
     
3. 가장 바깥에 있는 쿼리:
   • 주어진 데이터를 가지고 최종 결과를 생성
   • 서브쿼리 'b'의 결과를 활용하여 음식점 이름과 수수료 출력
     

---

- 음식점의 지역과 평균 배달시간으로 segmentation 하기

SELECT restaurant_name,
       adress,
       CASE WHEN avg_time <= 20 THEN '<= 20'
            WHEN avg_time > 20 AND avg_time <= 30 THEN '20 < x <= 30'
            WHEN avg_time >30 THEN '> 30' END AS "time_segment"
FROM 
    (
    SELECT restaurant_name,
           SUBSTR(addr, 1, 2) AS "adress",
           AVG(delivery_time) AS "avg_time"
    FROM food_orders
    GROUP BY 1, 2
    ) a                                     

---

- 음식 타입별 지역별 총 주문수량과 음식점 수를 연산하고, 주문수량과 음식점수 별 수수료율을 산정하기

음식점수 5개 이상, 주문수 30개 이상 → 수수료 0.05%
음식점수 5개 이상, 주문수 30개 미만 → 수수료 0.08%
음식점수 5개 미만, 주문수 30개 이상 → 수수료 1%
음식점수 5개 미만, 주문수 30개 미만 → 수수로 2%

SELECT cuisine_type
       , total_quantity
       , count_res
       , CASE WHEN count_res >= 5 AND total_quantity >= 30 THEN 0.0005
              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 "discount_rate"
FROM 
(
SELECT cuisine_type
       , SUM(quantity) AS total_quantity
       , COUNT(DISTINCT restaurant_name) AS count_res
FROM food_orders
GROUP BY cuisine_type
) a;

---

- 음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기

할인조건
수량이 5개 이하 → 10%
수량이 15개 초과, 총 주문금액이 300000 이상 → 0.5%
이 외에는 일괄 1%

SELECT restaurant_name
       , CASE WHEN total_quantity < 5 THEN 0.1
              WHEN total_quantity > 15 AND sum_of_price >= 300000 THEN 0.05
              ELSE 0.1
         END AS "discount_rate"
FROM 
    (
    SELECT restaurant_name
           , SUM(quantity) AS "total_quantity"
           , sum(price) AS "sum_of_price"
    FROM food_orders fo
    GROUP BY 1
    ) a;

---

- 주문 가격과 수수료율을 곱하여 주문별 수수료 구하기]

* 수수료율이 있는 경우만 조회

SELECT fo.order_id 
      , fo.restaurant_name 
      , fo.price 
      , p.vat 
      , (fo.price * p.vat) AS "수수료" 
FROM food_orders fo 
INNER JOIN payments p ON fo.order_id = p.order_id 

수수료율이 있는 경우에만 조회하라고 했기 때문에 INNER JOIN 사용(교집합!)

---

오늘의 TMI
SQL은 하면 할수록 쉬우면서 어려운것 같다...........ㅋㅋㅋㅋ