조건문, Subquery, JOIN 예제

1. 포맷 변경과 조건문 복습

문자 변경

1. REPLACE : 지정한 문자를 다른 문자로 변경
2. SUBSTRING : 특정 문자만 추출
3. CONCAT : 여러 문자를 합하여 포맷팅

조건문

1. IF : if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)
2. CASE WHEN END : case when 조건1 then 값(수식)1 when 조건2 then 값(수식)2 else 값(수식)3 end

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

Subquery 문의 기본 구조

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)

Subquery 문을 이용한 연산문 예시

-- 주문 테이블에서  주문 번호, 음식점명, 음식 준비시간을 가져오기
select order_id, restaurant_name, food_preparation_time
from 
(
select order_id, restaurant_name, food_preparation_time
from food_orders
) a

-- 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

3. User Segmentation 와 조건별 수수료를 Subquery 로 결합해보기

-- 음식점의 평균 단가별 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

-- 음식점의 지역과 평균 배달시간으로 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

4. 복잡한 연산을 Subquery 로 수행하기

-- 음식 타입별 지역별 총 주문수량과 음식점 수를 연산하고, 
--    주문수량과 음식점수 별 수수료율을 산정하기
--    (음식점수 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

-- 음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기
--    (할인조건
--    수량이 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

5. 필요한 데이터가 서로 다른 테이블에 있을 때 조회하기 (JOIN)

JOIN 의 기본 원리와 종류

-- LEFT JOIN
select 조회 할 컬럼
from 테이블1 a left join 테이블2 b on a.공통컬럼명=b.공통컬럼명

-- INNER JOIN
select 조회 할 컬럼
from 테이블1 a inner join 테이블2 b on a.공통컬럼명=b.공통컬럼명

JOIN 을 이용하여 두 개의 테이블에서 데이터를 조회해보기

-- 주문 테이블과 고객 테이블을 cusomer_id 를 기준으로 left join 으로 묶어보기
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

6. JOIN 으로 두 테이블의 데이터 조회하기

-- 한국 음식의 주문별 결제 수단과 수수료율을 조회하기
-- 결제 정보가 없는 경우도 포함하여 조회
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'

-- 고객의 주문 식당 조회하기
-- 고객명으로 정렬, 중복 없도록 조회
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
order by c.name

7. JOIN 으로 두 테이블의 값을 연산하기

-- 주문 가격과 수수료율을 곱하여 주문별 수수료 구하기
-- 수수료율이 있는 경우만 조회
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

-- 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) as discount_price
from food_orders a left join customers b on a.customer_id=b.customer_id
where b.age>=50
) t
group by 1
order by 4 desc