SQL Subquery 활용하기 - 여러 번의 연산을 한 번에

데이터를 다루다 보면 한 번에 연산이 끝나지 않고, 문자를 가공한 뒤 그 결과로 또 계산을 하거나, 여러 조건을 단계적으로 적용해야 하는 상황이 자주 생긴다. 이럴 때는 긴 쿼리문을 늘어뜨리기보다는 Subquery를 활용하는 게 훨씬 효율적이고 가독성도 좋다. 오늘은 Subquery의 기본 구조부터, 실제로 조건문이나 집계 결과오 ㅏ결합해 복잡한 연산을 풀어내는 방법을 배워볼 수 있었다.

 

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

1. Subquery가 필요한 경우

• 여러번의 연산을 수행해야 하는 경우
• 조건문에 연산 결과를 사용해야 하는 경우
• 조건에 Query 결과를 사용하고 싶은 경우

즉, 하나의 SQL문만으로는 복잡하거나 중첩된 계산을 표현하기 어려울 때 Subquery가 필요하다.

 

2. Subquery문의 기본 구조

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)

 

3. Subquery문을 이용하여 연산문 적어보기

[실습 1] 음식 준비시간 초과 여부 계산하기

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

 

 

[실습 2] 평균 단가별 Segmentation 후 수수료 계산하기

음식점별 평균 단가를 구한 뒤, 그 결과를 Subquery에 담아 수수료를 계산해 보았다.

• 5,000원 미만  → 0.05%
• 5,000원 이상 ~ 20,000원 미만  → 1%
• 20,000원 이상 ~ 30,000원 미만  → 2%
• 30,000원 이상  → 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

 

Subquery를 단계별로 나누면, 먼저 평균 단가를 구한 결과(a), 그에 따른 수수료 비율을 지정한 결과(b),

Subquery a 결과 (왼쪽), Subquery b 결과 (오른쪽)

 

마지막으로 수수료까지 적용한 최종 결과를 확인할 수 있었다.

전체 query 결과

 

 

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

주소에서 시·도를 추출해 지역을 나눈 뒤, 평균 배달 시간을 구하고 구간별로 분류했다.

 

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,
       substring(addr, 1, 2) sido,
       avg(delivery_time) avg_time
from food_orders
group by 1, 2
) a

 

 

 

하나의 쿼리문에서 수행하기 어려운 복잡한 연산을 Subquery로 실행해보는 실습도 진행해 보았다.

 

[실습 4] 음식 타입·지역별 주문수량과 음식점 수 기반 수수료 산정

• 음식점 수 5개 이상, 주문수 30개 이상 → 0.5%
• 음식점 수 5개 이상, 주문수 30개 미만  → 0.8%
• 음식점 수 5개 미만, 주문수 30개 이상  → 1%
• 음식점 수 5개 미만, 주문수 30개 미만  → 2%

select *, 
       case when cnt_res>=5 and sum_quant>=30 then 0.005
            when cnt_res>=5 and sum_quant<30 then 0.008
            when cnt_res<5 and sum_quant>=30 then 0.01
            when cnt_res<5 and sum_quant<30 then 0.02 end rate
from
(
select cuisine_type, substr(addr,1,2) sido, count(distinct restaurant_name) cnt_res, sum(quantity) sum_quant
from food_orders
group by 1, 2
) a

 

 

[실습 5] 주문 수량과 주문 금액 기반 수수료 할인율 구하기

• 수량이 5개 이하  → 10%
• 수량이 15개 초과, 총 주문금액이 300,000이상  → 0.5%
• 그 외  → 1%

select restaurant_name,
       sum_of_price,
       sum_of_quantity,
       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

 

 

 

마무리

오늘은 Subquery를 이용해 여러 번의 연산을 한 번에 묶어내는 방법을 배웠다. 특히 SELECT에서 만든 별칭을 다른 구문에서 쓰지 못해 어려움을 겪었던 부분도 Subquery로 자연스럽게 해결할 수 있었다. 단순히 데이터를 뽑는 수준을 넘어, 단계적인 계산과 조건별 분류까지 할 수 있다는 점에서 SQL의 응용력을 한층 더 느낄 수 있었다. 앞으로는 Subquery와 조건문을 함께 활용해 더 복잡한 분석도 깔끔하게 풀어낼 수 있도록 연습을 이어가야겠다.