TIL - SQL (4주차)

1. Subquery

필요한 경우

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

기본 구조

select column1, special_column
from
    ( /* subquery */
    select column1, column2 special_column
    from table1
    ) a

 

2. User Segmention + Subquery

 

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

 

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

 

3. 복잡한 연산

 

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

 

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

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

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

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

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

 

4. JOIN

 

 

 

• LEFT JOIN : 공통 컬럼을 기준으로, 하나의 테이블에 값이 없더라도 모두 조회되는 경우를 의미함
• INNER 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.공통컬럼명

 

5. 두 테이블 조회

 

1) 한국 음식의 주문별 결제 수단과 수수료율을 조회하기

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'

 

2) 고객의 주문 식당 조회하기

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

* null 값 제거하려면 is not null

 

6. 두 테이블의 값 연산

 

1) 주문 가격과 수수료율을 곱하여 주문별 수수료 구하기

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

 

 

2) 50세 이상 고객의 연령에 따라 경로 할인율을 적용하고, 음식 타입별로 원래 가격과 할인 적용 가격 합을 구하기

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