[사전캠프/SQL] SubQuery(추가 내용 수정중)

1. 3주차 복습

• 문자 데이터 형태 변경
  • REPLACE : 지정한 문자를 다른 문자로 변경
  • SUBSTRING : 특정 문자만 추출
  • CONCAT : 여러 문자를 합침

• 조건문
  • IF : if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)
  • CASE WHEN END : case when 조건1 then 값(수식)1 when 조건2 then 값(수식)2 else 값(수식)3 end

---

2. SubQuery

• 사용해야하는 상황
  • 여러번의 연산을 수행해야할 때
  • 조건문에 연산 결과를 사용해야 할 때
  • 조건에 Query 결과를 사용하고 싶을 때
• 기본 구조

SELECT column1, special_column
FROM
    ( /* subquery */
    SELECT column1, column2 special_column
    FROM table1
    ) a

SELECT column1, column2
FROM table1
WHERE column1 = (SELECT col1 FROM table2)

---

• 예시 : 문 테이블에서 주문 번호, 음식점명, 음식 준비시간을 가져오기 

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

 

---

3. [실습] User Segmentation 와 조건별 수수료를 Subquery 로 결합해보기

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

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

1. 어떤 테이블에서 데이터를 뽑을 것인가
2. 어떤 컬럼을 이용할 것인가
3. 어떤 조건을 지정해야 하는가
4. 어떤 함수 (수식) 을 이용해야 하는가

• 전체 구조로 합치기

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

---

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

1. 어떤 테이블에서 데이터를 뽑을 것인가
2. 어떤 컬럼을 이용할 것인가
3. 어떤 조건을 지정해야 하는가
4. 어떤 함수 (수식) 을 이용해야 하는가

• 전체 구조로 합치기

SELECT restaurant_name,
       sido,
       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 time_segment
FROM 
(
SELECT restaurant_name,
       SUBSTR(addr, 1, 2) sido,
       AVG(delivery_time) avg_time
FROM food_orders
GROUP BY 1, 2
) a

---

4. [실습]  복잡한 연산을 Subquery 로 수행하기

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

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

1. 어떤 테이블에서 데이터를 뽑을 것인가
2. 어떤 컬럼을 이용할 것인가
3. 어떤 조건을 지정해야 하는가
4. 어떤 함수 (수식) 을 이용해야 하는가

• 전체 구조로 합치기

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 rate
FROM
(
SELECT cuisine_type,
       SUM(quantity) total_quantity,
       COUNT(distinct restaurant_name) count_res
FROM food_orders
GROUP BY 1
) a

 

---

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

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

1. 어떤 테이블에서 데이터를 뽑을 것인가
2. 어떤 컬럼을 이용할 것인가
3. 어떤 조건을 지정해야 하는가
4. 어떤 함수 (수식) 을 이용해야 하는가

• 전체 구조로 합치기

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

 

---

5. 회고

앞의 내용들을 제대로 기억하지 못하면, 서브쿼리는 많이 빡셀 것 같아서 우선 정리해두고! 앞의 내용 복습돌릴려고 한다!

목요일에 5주차까지 진도 다 나간후 실습 문제는 복습하면서 같이 풀어 블로그에 내용 수정+추가 하는걸로 정했다

내용이 생각보다 단면적으로는 괜찮았는데, 긴 쿼리를 보니까 막막해지기도 하더라 ^^ ㅠ... 

최대한 빨리 5주차 끝내고, 복습 돌리기