[4주차]SQL_subquery, inner join, left join

• subquery : 여러번의 연산을 수행할 때, 조건문에 연산문을 사용할 때, 조건에 Query 결과를 사용하고 싶을 때
• join : 엑셀의 vlookup과 비슷한 개념
  from 테이블1 inner(left) join 테이블2 on 테이블1.컬럼1=테이블2.컬럼2
  

[실습] 음식점의 평균 단가별 segmentation 을 진행하고, 그룹에 따라 수수료 연산하기
(수수료 구간 -
~5000원 미만 0.05%
~20000원 미만 1%
~30000원 미만 2%
30000원 초과 3%)

select restaurant_name,
case when avg_price < 5000 then 0.005 
            when avg_price >= 5000 and avg_price < 20000 then 0.01
            when avg_price >= 20000 and avg_price < 30000 then 0.02
            else 0.03 end rate
from 
(
select restaurant_name, 
avg(price) avg_price
from food_orders 
group by 1
) a 

틀렸다. 이렇게 하면 식당별 수수료율만 나온다.
조건이 복잡해질때는 머리로만 하려고 하지말자. 무엇을 구해야 하는지 먼저 정리해보자.

• 음식의 평균 단가 : price/quantity
• 음식점 별 : group by
• 수수료율 : case when
• 1) 음식점별 평균 단가 구하기
  2) 평균단가별 수수료율 구하기
  3) 음식점별 수수료 연산하기(평균단가*수수료율)

select restaurant_name,
avg_price * rate "수수료"  
from 
(
select restaurant_name,
price, 
quantity,
avg_price,
case when avg_price < 5000 then 0.005 
            when avg_price >= 5000 and avg_price < 20000 then 0.01
            when avg_price >= 20000 and avg_price < 30000 then 0.02
            else 0.03 end rate 
from 
(
select restaurant_name, 
price,
quantity,
avg(price/quantity) avg_price
from food_orders
group by 1
) a
) b 

[실습] 음식점의 지역과 평균 배달시간으로 segmentation 하기
아래 어떻게 풀지 먼저 써보기

• 음식점 지역 : substring
• 평균 배달시간 : avg
• 평균 배달시간으로 segment (20분이하, 20-30분, 30분 초과)
• 지역별 group by
  1) 음식점 지역 뽑기
  2) 평균 배달시간 segment

select restaurant_name,
       location,
       avg_del,
       case when avg_del <= 20 then 'delivery1'
            when avg_del > 20 and avg_del <=30 then 'delivery2'
            else 'delivery3' end del_segment 
from 
(
select restaurant_name,
substring(addr, 1,2) location,
avg(delivery_time) avg_del
from food_orders
group by restaurant_name, location 
) a 

오타, 쉼표 등으로 자꾸 에러가 뜬다.
시간은 오래 걸렸지만 정답은 맞아서 기분이 좋네.

[실습] 음식 타입별 지역별 총 주문수량과 음식점 수를 연산하고, 주문수량과 음식점수 별 수수료율을 산정하기
(음식점수 5개 이상, 주문수 30개 이상 → 수수료 0.05%
음식점수 5개 이상, 주문수 30개 미만 → 수수료 0.08%
음식점수 5개 미만, 주문수 30개 이상 → 수수료 1%
음식점수 5개 미만, 주문수 30개 미만 → 수수로 2%

• 음식타입별, 지역별 : group by, substring
• 총 주분수량, 음식점 수 : sum, count
• 수수료율 : case when

select location,
cuisine_type,
count_res,
sum_quantity,
case when count_res >= 5 and sum_quantity >= 30 then 0.0005
            when count_res >= 5 and sum_quantity < 30 then 0.0008
            when count_res < 5 and sum_quantity >= 30 then 0.001
            when count_res < 5 and sum_quantity < 30 then 0.002
            end rate 
from 
(
select substring(addr, 1, 2) location,
cuisine_type,
count(restaurant_name) count_res,
sum(quantity) sum_quantity
from food_orders
group by 1, 2
) a 

해답지에는 지역을 연산하는 구문이 빠진듯 하지만 맞은것 같다. 굿~

[실습] 음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기
(할인조건 : 수량이 5개 이하 → 10%
수량이 15개 초과, 총 주문금액이 300000 이상 → 0.5%
이 외에는 일괄 1%)

• 총 주문수량, 주문금액 : sum
• 수수료할인율 : case when
• 조회컬럼 : restaurant_name, quantity, price

  select restaurant_name,
  sum_quan, 
  sum_price,
  case when sum_quan <=5 then 0.1
             when sum_quan > 15 and sum_price >= 300000 then 0.005
             else 0.01 end rate
  from 
  (
  select restaurant_name, 
  sum(quantity) sum_quan,
  sum(price) sum_price
  from food_orders 
  group by 1
  ) a

subquery 구문 쓸 때 group by 잊지말자.
맞았지만 시간을 단축할 필요는 있다.

[실습] 한국 음식의 주문별 결제 수단과 수수료율을 조회하기
(조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 결제 수단, 수수료율)
*결제 정보가 없는 경우도 포함하여 조회

• 한국음식 : where
• 주문테이블, 결제테이블 left join (order_id 컬럼)
• 조회컬럼 : order_id, restaurant_name, price, pay_type, vat

select 
f.order_id,
f.restaurant_name,
f.price,
p.pay_type,
p.vat
from food_orders f left join payments p on f.order_id  = p.order_id 
where f.cuisine_type = 'korean'

join 구문과 관련된 기본 실습이다.

[실습] - 고객의 주문 식당 조회하기
(조회 컬럼 : 고객 이름, 연령, 성별, 주문 식당)
*고객명으로 정렬, 중복 없도록 조회

• 주문테이블, 고객테이블, inner join(customer_id)

select c.name,
c.age,
c.gender,
f.restaurant_name
from food_orders f inner join customers c on f.customer_id = c.customer_id 

[실습] 주문 가격과 수수료율을 곱하여 주문별 수수료 구하기
(조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 수수료율, 수수료)
*수수료율이 있는 경우만 조회

• 주문테이블, 결제테이블 inner join(order_id)

select f.order_id,
f.restaurant_name,
f.price,
p.vat,
f.price * p.vat price_rate
from food_orders f inner join payments p on f.order_id = p.order_id 

[실습] 50세 이상 고객의 연령에 따라 경로 할인율을 적용하고, 음식 타입별로 원래 가격과 할인 적용 가격 합을 구하기
(조회 컬럼 : 음식 타입, 원래 가격, 할인 적용 가격, 할인 가격)
할인 : 나이-50 x 0.005
고객 정보가 없는 경우도 포함하여 조회, 할인 금액이 큰 순서대로 정렬

• 주문테이블, 고객테이블 left join(customer_id)
• 50세이상 고객 연령 경로할인율: if
• 할인가격 : price*경로할인율
• 할인 적용가격 : price - 할인가격

select cuisine_type,
price, 
price*rate discount_rate
price-(price*rate) discounted_price
from 
(
select
f.cuisine_type,
c.age,
f.price, 
if(c.age-50 >= 0, (c.age-50)*0.005, 0) rate 
from food_orders f left join customers c on f.customer_id=c.customer_id 
) a
group by 1

할인율까지는 잘 구 했는데 문제 파악을 잘못 했다. 가격의 합을 구해야 한다.
내림차순도 해야 하는 것을 놓쳤다.

select 
cuisine_type, 
sum(price) "원래가격",
sum(price)*rate "할인가격",
sum(price)-sum(price)*rate "할인적용가격"
from 
(
select
f.cuisine_type,
f.price, 
if(c.age-50 > 0, (c.age-50)*0.005, 1) rate 
from food_orders f left join customers c on f.customer_id=c.customer_id 
) a
group by 1
order by 3

할인가격을 잘못 구했다. 가격할인율의 합을 구해야 한다.
sum(price * rate)

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

위의 해답지에서는 where으로 50세 이상만 대상으로 구했으나
문제에서는 50세이상의 고객의 연령에 따라 -> 50이상이라면 이라 해석하여 if절을 사용했다. if절의 조건이 아닐 때, 0으로 수정한 쿼리는 아래와 같다.
내림차순도 다시 적용해주었다.

select 
cuisine_type, 
sum(price) "원래가격",
sum(price*rate) "할인가격",
sum(price)-sum(price*rate) "할인적용가격"
from 
(
select
f.cuisine_type,
f.price, 
if(c.age-50 > 0, (c.age-50)*0.005, 0) rate 
from food_orders f left join customers c on f.customer_id=c.customer_id 
) a
group by 1
order by 3 desc 

[과제] 식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기
평균 음식 주문 금액 기준 : 5,000 / 10,000 / 30,000 / 30,000 초과
평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상
두 테이블 모두에 데이터가 있는 경우만 조회, 식당 이름 순으로 오름차순 정렬

• 주문테이블, 고객테이블 inner join (customer_id)
• 식당이름 group by, order by
• subquery 주문금액, 연령 avg
• segmentation : case when

select restaurant_name,
avg_price,
avg_age,
case when avg_price <=5000 then '5천이하' 
     when avg_price >5000 and avg_price <=10000 then '1만이하' 
     when avg_price >10000 and avg_price <=30000 then '3만이하'  
     else '3만원 초과'  end "가격대구분", 
case when avg_age <= 29 then '20대 이하'
     when avg_age >= 30 and avg_age <= 39 then '30대'
     when avg_age >= 40 and avg_age <= 40 then '40대'
     else '50대 이상' end "나이대"
from 
(
select f.restaurant_name,
avg(f.price) avg_price,
avg(c.age) avg_age
from food_orders f inner join customers c on f.customer_id=c.customer_id
group by f.restaurant_name 
) a 
order by 1