rubus0304 님의 블로그
［데이터 분석 3일차 ①］ Subquery, JOIN 본문
1. 여러 번의 연산을 한 번의 SQL 문으로 수행하기 (Subquery)
ex) 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 'over_time'
from food_orders
) a
1-1. User Segmentation 과 조건별 수수료를 Subquery 로 결합해보기
ex) ★ ★ ★ 음식점의 평균 단가별 segmentation 을 진행하고, 그룹에 따라 수수료 연산하기 ★ ★ ★
- 수수료 구간 -
~5000원 미만 0.05%
~20000원 미만 1%
~30000원 미만 2%
30000원 초과 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
**어렵다 어려워 ㄷㄷ,, 일주일에 한 번씩 반복해서 쳐보면서 이해해보자
ex) 음식점의 지역과 평균 배달시간으로 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
1-2. 복잡한 연산을 Subquery로 수행하기
ex) 음식 타입별 총 주문수량과 음식점 수를 연산하고, 주문수량과 음식점수 별 수수료율을 산정하기
- (음식점수 5개 이상, 주문수 30개 이상 → 수수료 0.5% 음식점수 5개 이상, 주문수 30개 미만 → 수수료 0.8% 음식점수 5개 미만, 주문수 30개 이상 → 수수료 1% 음식점수 5개 미만, 주문수 30개 미만 → 수수로 2%)
select cuisine_type,
total_quantity,
count_res,
case when count_res>=5 and total_quantity>=30 then 0.005
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
ex) 음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기
- (할인조건 수량이 5개 이하 → 10% 수량이 15개 초과, 총 주문금액이 300000 이상 → 0.5% 이 외에는 일괄 1%)
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
2. 필요한 데이터가 서로 다른 테이블에 있을 때 조회하기 JOIN
LEFT JOIN : 공동 컬럼을 기준으로 다른 테이블에 값이 없더라도 모두 조회.
INNER JOIN : 공통 컬럼을 기준으로 두 테이블에 모두 있는 값만 조회.
ex) 주문 테이블과 고객 테이블을 cusomer_id 를 기준으로 left join 으로 묶어보기
(조회 컬럼 : order_id, customer_id, restaurant_name, price, name, age, gender)
select a.order_id,
a.customer_id,
a.restaurant_name,
a.price,
b.name,
b.age,
b.gender
from food_orders a left join customers b on a.customer_id=b.customer_id
2-1. JOIN 으로 두 테이블의 데이터 조회하기
ex) 한국 음식의 주문별 결제 수단과 수수료율을 조회하기
- (조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 결제 수단, 수수료율) *결제 정보가 없는 경우도 포함하여 조회
select a.order_id,
a.restaurant_name,
a.price,
b.pay_type,
b.vat
from food_orders a left join payments b on a.order_id=b.order_id
where cuisine_type='Korean'
ex) 고객의 주문 식당 조회하기 (NULL 값 제거 포함)
- (조회 컬럼 : 고객 이름, 연령, 성별, 주문 식당) *고객명으로 정렬, 중복 없도록 조회
select distinct c.name,
c.age,
c.gender,
f.restaurant_name
from food_orders f left join customers c on f.customer_id=c.customer_id
where c.name is not null
order by c.name
2-2. JOIN 으로 두 테이블의 값을 연산하기
ex) 주문 가격과 수수료율을 곱하여 주문별 수수료 구하기
- (조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 수수료율, 수수료) *수수료율이 있는 경우만 조회
select a.order_id,
a.restaurant_name,
a.price,
b.vat,
a.price*b.vat "수수료율"
from food_orders a inner join payments b on a.order_id=b.order_id
ex) 50세 이상 고객의 연령에 따라 경로 할인율을 적용하고, 음식 타입별로 원래 가격과 할인 적용 가격 합을 구하기
- 조회 컬럼 : 음식 타입, 원래 가격, 할인 적용 가격, 할인 가격) 할인 : 나이-50* 0.005
- 고객 정보가 없는 경우도 포함하여 조회, 할인 금액이 큰 순서대로 정렬
select cuisine_type,
sum(price) "원래 가격",
sum(price)-sum(discount_price) "할인 적용 가격",
sum(discount_price) "할인 가격"
from
(
select a.cuisine_type,
price,
price*((b.age-50)*0.005) discount_price
from food_orders a inner join customers b on a.customer_id=b.customer_id
where b.age>=50
) t
group by 1
order by 4 desc
<과제> 식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기
- 평균 음식 주문 금액 기준 : 5,000 / 10,000 / 30,000 / 30,000 초과 - 평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상
select restaurant_name,
case when price <=5000 then 'price_group1'
when price >5000 and price <=10000 then 'price_group2'
when price >10000 and price <=30000 then 'price_group3'
when price >30000 then 'price_group4' end price_group,
case when age <30 then 'age_group1'
when age between 30 and 39 then 'age_group2'
when age between 40 and 49 then 'age_group3'
else 'age_group4' end age_group
from
(
select a.restaurant_name,
avg(price) price,
avg(age) age
from food_orders a inner join customers b on a.customer_id=b.customer_id
group by 1
) t
order by 1
과제 .. 역시..난이도 중
평균 음식 주문금액 case 하고 , !! 콤마 +_+
평균 연력대 case 구문 나이는 > 초과랑 between 으로
subquery는 group by 까지 묶고
전체 정렬은 subquery 밖에서 마지막에 order by
