SQL 4주차①(subquery)

subquery

select price/quantity

from

(

select price, quantity

from food_orders

) a

순서 괄호 안의 내용을 써주고 →  괄호 추가+ a처럼 별명 지어줘야 함 → 위에 select from 쓰기

해석 

1. food_orders 에서 price와 quantity를 추출

2. subquery이므로 앞 뒤 괄호() 추가하고 이를 'a'라고 별명 지어주기

3. subquery를 이용(from)해서 price/quantity를 추출(select)

중요: 맨 위의 select는 from의 값들만 가져올 수 있으므로, 중간 select의 값들만 불러오기 가능

결과

 

[실습 1] User Segmentation

 

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

수수료 구간:

~5000원 미만 0.5%

~20000원 미만 1%

~30000원 미만 2%

30000원 초과 3%)

 

풀이 일단 어떤 subquery가 + 몇 개 필요한지 파악

→ 1) 평균 단가: avg(price/quantity)  price_per_plate

→ 2) 수수료 구간 ratio_of_add

     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,

→ 3) 수수료 계산: price_per_plate * ratio_of_add 수수료

    ★중요: 앞 단계에서 신규 명명한 값들을 입력해야 함

→ 각 단계 넘어갈 떼 subquery

 

입력

1) 평균 단가: avg(price/quantity)  price_per_plate

→ 주의 group by 1 안 해주면, 맨 첫번째 값(Hangawi 16,498어쩌구)만 나옴

select restaurant_name,
       avg(price/quantity) price_per_plate
from food_orders
group by 1
# group by 1을 안 하면, 맨 처음 값 하나만 나옴!!!

 

1-s) subquery 만들어주기

select

from

(

select restaurant_name,
avg(price/quantity) price_per_plate
from food_orders
group by 1

) a   ★중요: 안 해주면 에러 뜸

 

2) 수수료 구간 ratio_of_add

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

 

2-s) subquery 만들어주기   ★중요: 앞 단계에서 신규 명명한 값들을 입력해야 함(거기서부터 from임)

select

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) 수수료 계산: price_per_plate * ratio_of_add 수수료

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

 

 

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

지역: 주소지의 앞 두 글자만 추출

 

풀이 일단 어떤 subquery가 + 몇 개 필요한지 파악

→ 1) 주소 변경 sido, 평균 배달시간 avg_time

→ 2) 배달시간별 segmentation

     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_segement

→ 각 단계 넘어갈 떼 subquery

입력

1) 주소변경, 평균 배달 시간

select restaurant_name,

substr(addr, 1, 2) sido,            ★이제는 좀 외울 때 되지 않았나

avg(delivery_time) avg_time 

from food_orders

group by 1, 2                           ★중요: 문제에서 '음식점의 지역별'이라고 해서!!!

1-s) subquery 만들어주기

select

from

(

select restaurant_name,

substr(addr, 1, 2) sido,

avg(delivery_time) avg_time

from food_orders

group by 1, 2

) a    ★중요: 안 해주면 에러 뜸

 

2) 배달시간별 segmentation

★중요: 앞 단계에서 신규 명명한 값들(sido, avg_time)을 입력해야 함(거기서부터 from임)

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

 

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

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

음식점수 5개 이상, 주문수 30개 이상 → 수수료 0.5%

음식점수 5개 이상, 주문수 30개 미만 → 수수료 0.8%

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

음식점수 5개 미만, 주문수 30개 미만 → 수수로 2%

 

풀이 일단 어떤 subquery가 + 몇 개 필요한지 파악

1) 음식 타입별 총 주문수량 및 음식점 수

select cuisine_type,

sum(quantity) total_quantity,                                            ★총 주문수량을 구하기 위함

count(distinct restaurant_name) count_of_restaurant     ★특정 행의 합계: count(distint 컬럼)

from food_orders

group by 1

 

1-s) subquery 만들어주기

select

from

(

select cuisine_type,

sum(quantity) total_quantity,                           

count(distinct restaurant_name) count_res     

from food_orders

group by 1

) a   ★중요: 안 해주면 에러 뜸

2) 음식점 및 주문수량 별 segmentation

★중요: 앞 단계에서 신규 명명한 값(total_quantity, count_of_restaurant)들을 입력해야 함(거기서부터 from임)

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

 

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

할인조건 수량이 5개 이하 → 10%

수량이 15개 초과, 총 주문금액이 300000 이상 → 0.5%

이 외에는 일괄 1%

입력

1) 음식점의 총 주문수량 및 주문금액

select restaurant_name,
       sum(quantity) sum_of_qty,
       sum(price) sum_of_price
from food_orders
group by 1

1-s) subquery 만들어주기

2) 수수료 할인률 구하기

★중요: 앞 단계에서 신규 명명한 값(sum_of_qty, sum_of_price)들을 입력해야 함(거기서부터 from임)

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