스파르타 코딩클럽(부트캠프) SQL 입문 4장

Subquery란

여러번의 연산을 한 번의 SQL문으로 수행하는것.

Subquery가 필요한 경우

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

예시
select column1, special_column
from
       (/* subquery */)
       select column1, cloumn2 special_column
       from table1
     )a

SELECT price/quantity

from

(

SELECT price, quantity

from food_orders fo

) a

---

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

---

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

 

---

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

• 음식점수 5개 이상, 주문수 30개 이상 -> 수수료 0.05%
• 음식점수 5개 이상, 주문수 30개 미만 -> 수수료 0.08%
• 음식점수 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, sum_price, sum_quantity,

 

case when sum_quantity<=5 then 0.1

when sum_quantity>15 and sum_price>=300000 then 0.005

else 0.01 end discount_rate

from

(

SELECT restaurant_name,

sum(price) sum_price,

sum(quantity) sum_quantity

from food_orders

group by 1

)a

 

---

JOIN이란

필요한 데이터가 하나의 테이블에 모여있지 않을 경우 여러 테이블에서 데이터를 불러오는 방법

 

JOIN의 기본 원리와 종류는 엑셀의 Vlookup과 유사하다.

출처 : 스파르타 코딩클럽

1. 주문 정보에서 고객이메일을 알기위해 고객 정보에서 동일한 고객 ID의 이메일을 가져온다.
2. 각각 주문 정보와 고객 정보가 테이블이라고 할 때 고객 ID를 기준으로 필요한 값을 가져온다.
3. 두 테이블이 공통으로 갖고 있는 컬럼이 된다.

출처 : 스파르타 코딩클럽

공통 컬럼을 기준으로 묶은형태는 위와 같다. 각각 테이블에 필요한 데이터를 조회할 수 있도록 만들어주는것

LEFT JOIN 

• 공통 컬럼 (키 값) 을 기준으로 하나의 테이블에 값이 없더라도 모두 조회하는 경우를 의미

출처 : 스파르타 코딩클럽

INNER JOIN

• 공통 컬럼 (키값)을 기준으로 두 테이블 모두에 있는 값만 조회한다.

공통컬럼은 묶어주기 위한 공통 값이기 때문에 두 테이블의 컬럼명은 달라도 괜찮다.

---

food_orders과 payments을 order_id 기준으로 lift join 묶어보기

select *

from food_orders left join payments on food_orders.order_id =payments.order_id

---

주문 테이블과 고객 테이블을 customer_id 기준으로 left join을 사용하여 묶기.

SELECT f.order_id, f.customer_id, f.restaurant_name, f.price,

               c.name , c.age, c.gender

from food_orders f left join customers c on f.customer_id = c.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

order by 1

---

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

• 조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 수수료율, 수수료
• 수수료율이 있는 경우만 조회

SELECT f.order_id, f.restaurant_name, f.price,

p.vat, f.price*p.vat "수수료율"

FROM food_orders f inner join payments p on f.order_id = p.order_id

---

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

• 조회컬럼 : 음식 타입, 원래 가격, 할인 적용, 할인 가격
• 할인 : 나이 - 50*0.005
• 고객정보가 없는 경우도 포함하여 조회, 할인 금액이 큰 순서대로 정렬

SELECT cuisine_type,

sum(price) sum_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)