엑셀보다 쉽고 빠른 SQL 핵심요약 정리_4주차

select restaurant_name,
       CASE WHEN sum_quant <= 5 then 0.1
            WHEN sum_quant > 15 and sum_price >= 300000 then 0.005
            ELSE 0.01 END ratio_of_add
from 
(
select restaurant_name,
       sum(quantity) sum_quant,
       sum(price) sum_price
from food_orders
group by 1
) a

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

 

1) 서브쿼리가 필요한 경우

 

* 여러번의 연산을 수행해야 할 때

- 예 : 수수료를 부과할 수 있는 시간을 구하고 -> 구해진 시간에 주문 금액별로 가중치를 주고 -> 가중치를 적용한 결과로 최종 예상 배달비를 계산할 때

 

*조건문에 연산 결과를 사용해야 할 때

- 예 : 음식 타입별 평균 음식 주문금액에 따라 음식비 상/중/하를 나누고 싶을 때

 

*조건에 쿼리 결과를 사용하고 싶을 때

- 예 : 30대 이상이 주문한 결과만 조회하고 싶을 때

 

2) 서브쿼리 문의 기본 구조

쿼리 안에 서브로 들어간 구문

select column1, special_column
from
    ( /* subquery */
    select column1, column2 special column
    from table 1
    ) a

select column1, column 2
from table 1
Where column1 = (select col1 from table2)

 

3) 실습 : 서브 쿼리 문을 이용해서 연산문 적어보기

음식 주문시간이 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 ;

 

⊙ 여러 번의 연산을 한 번의 SQL 문으로 수행하기 (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'
            else '>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

 

⊙ 복잡한 연산을 한 번의 SQL 문으로 수행하기

 

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

 

서브쿼리 카운트 함수 안 distinct 넣는 이유 : 없이 하면 행의 숫자가 출력되므로 각각의 수를 구하려면 붙여야 한다.

 

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

select restaurant_name,
       CASE WHEN sum_quant <= 5 then 0.1
            WHEN sum_quant > 15 and sum_price >= 300000 then 0.005
            ELSE 0.01 END ratio_of_add
from 
(
select restaurant_name,
       sum(quantity) sum_quant,
       sum(price) sum_price
from food_orders
group by 1
) a

 

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

 

1) join이 필요한 경우

주문 가격은 주문 테이블에 있지만 어떤 수단으로 결제를 했는지는 결제 테이블에 있을 때

주문한 사람을 확인하려면 주문 테이블과 고객 테이블에서 각각 정보를 가져와서 엑셀에서 합쳐줘야 할 때

 

2) join의 기본 원리와 종류

join은 기본적으로 엑셀의 Vlookup과 유사하다

- LEFT JOIN : 공통 컬럼 (키값) 을 기준으로, 하나의 테이블에 값이 없더라도 모두 조회되는 경우를 의미한다. 

- INNER 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.공통칼럼명

 

tip : 공통칼럼은 묶어주기 위한 '공통 값'이기 때문에 두 테이블의 컬럼 명은 달라도 괜찮다. 예를 들어 주문정보에는 '고객id', 고객정보에는 '고객아이디'라고 컬럼명이 되어있다면, 테이블1.고객id=테이블2.고객아이디 와 같이 묶어줄 수 있다.

 

3) 실습 : 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

 

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

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 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
WHERE c.name is not null
ORDER BY 1

 

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

SELECT f.order_id,
       f.restaurant_name,
       f.price,
       p.vat,
       f.price*p.vat "수수료"
FROM food_orders f left join payments p on f.order_id=p.order_id

 

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

내가 쓴 답

SELECT cuisine_type,
       sum(price) "원래 가격",
       sum(price*discount) "할인 합"
FROM 
(
select f.cuisine_type,
       f.price,
       c.age,
       CASE WHEN c.age >= 50 THEN 0.005
       Else 1 end discount
FROM food_orders f left join customers c on f.customer_id = c.customer_id 
) a
GROUP BY 1
ORDER BY 3 DESC

- 서브쿼리에서 case 문을 통해 discount 값을 계산하는 방식에 문제가 있다. 할인율을 정의해야 하는데, 

 

정답

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

 

고객 정보가 없어도 포함해야 하기 때문에 left join 사용

서브쿼리 밖 셀렉트 문에 f. c. 안써줘도 된다 이미 a라는 하나 데이터 결과가 되었기 때문이고 특정할 필요가 없음.