[SQL]Subquery로 여러 번의 연산을 한 번에

Subquery란?

내부 쿼리의 결과를 기반으로 데이터를 필터링, 검색 또는 조작하는 데 자주 사용되는 다른 쿼리 내에 포함된 쿼리입니다.

출처: https://easyitwanner.tistory.com/274 [IT 시작해보기:티스토리]

 

Subquery가 필요한 경우

• 여러번의 연산을 수행해야 할 때

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

• 조건문에 연산 결과를 사용해야 할 때

예) 음식 타입별 평균 음식 주문금액 따라 음식비 상/중/하 를 나누고 싶을 때

• 조건에 Query 결과를 사용하고 싶을 때

예) 30대 이상이 주문한 결과만 조회하고 싶을 때

 

Subquery문의 기본 구조

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

 

[실습] 주문 테이블에서 주문 번호, 음식점명, 음식 준비시간을 가져오기. 음식 주문시간이 25분보다 초과한 시간을 가져오기.

• select 기본문
• 가져올 컬럼 적기
• subquery 문으로 추가

select order_id, restaurant_name, if(over_time>=0, over_time, 0) over_time -- 2. 연산 결과가 0보다 크면 over_time, 작으면 0을 출력
from 
(
select order_id, restaurant_name, food_preparation_time-25 over_time -- 1. 25분 보다 얼마나 초과했는지 계산
from food_orders
) a --컬럼 이름

 

 

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

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

(수수료 구간 -

~5000원 미만 0.05%

~20000원 미만 1%

~30000원 미만 2%

30000원 초과 3%)

 

select restaurant_name,
       price_per_plate*ratio_of_add "수수료" -- 3. 평균단가*수수료비율
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 -- 2. 평균단가별 수수료비율 계산=ratio_of_add
from
	(
	select restaurant_name, avg(price/quantity) price_per_plate
	from food_orders
	group by 1 -- 1. 레스토랑 이름별 평균단가 계산=price_per_plate
	) a
) b

 

 

[실습]음식점의 지역과 평균 배달시간으로 segmentation 하기

지역: 시도만 이용

배달시간: 20분 / 30분 / 30분 초과

 

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 delivery_time_segment
FROM 
(
select restaurant_name,
	SUBSTR(addr,1,2) "sido", 
	AVG(delivery_time) "avg_delivery_time"
from food_orders
group by 1
) a -- alias 지정 안한 경우 에러 "SQL Error [1248] [42000]: Every derived table must have its own alias"

 

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

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

SELECT restaurant_name,
	CASE WHEN sum_quantity <= 5 THEN 0.1
		 WHEN sum_quantity > 15 AND sum_price >= 300000 THEN 0.005
		 ELSE 0.01 END ratio_add
FROM 
(
SELECT restaurant_name,
	   SUM(quantity) sum_quantity,
	   SUM(price) sum_price 
FROM food_orders
GROUP BY restaurant_name
) a