[내일배움캠프/사전캠프] SQL 강의 4주차

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

-연산을 여러번하기 위해 사용

-연산한 결과를 다른 연산이나 조건문에 사용하고 싶을 때 사용

 

-쿼리안에 sub으로 들어간 구문

select 컬럼1, 만든 컬럼
*여기서 컬럼1은 서브쿼리문에서 사용한 컬럼1을 다시 계산하고 싶을 때 뽑아와서 사용
from
(select 컬럼1,컬럼2 만든컬럼(별명)
 from 테이블1
)a (서브쿼리에 이름주기)

*괄호 안의 select from 이 서브쿼리

 

※예시

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

----------food_orders 테이블에서 음식주문시간이 25분을 초과한 값을 가져와서 25분이 넘었으면 over_time 넘지 않았으면 0으로 표시

---

User segmentation(사용자 세분화) 와 조건별 수수료를 Subquery로 결합해보기

-음식점의 평균 단가별 segmentation을 진행하고, 그룹에 따라 수수료 연산하기

수수료 구간

~5000원 0.05%

~20000원 1%

~30000원 2%

30000원 초과 3%

 

select restaurant_name,

           price_per_plate*rate_of_add "수수료"

from

(

select restaurant_name,

           case when price_per_plate<5000 then 0.005

                    when price_per_plate>=5000 and price_per_plate<20000 then 0.01

                    when price_per_plate>=20000 and price_per_plate<30000 then 0.02

                    else 0.03

           end rate_of_add,

           price_per_plate

from

(

select restaurant_name

          avg(price/quantity) price_per_plate

from food_orders

group by 1

)a

)b

---

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

-음식점의 총 주문수량과 주문금액을 연산하고, 주문수량을 기반으로 수수료 할인율 구하기

할인조건

수량이 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 "수수료 할인율"

from

(

select restaurant_name,

           sum(quantity) sum_of_quantity,

           sum(price) sum_of_price

from food_orders

group by 1

)a

 

필요한 데이터가 서로 다른 테이블에 있을때 조회
JOIN
LEFT JOIN, INNER JOIN

 

-필요한 데이터가 하나의 테이블에 모여있지 않고 여러 테이블에 있을 경우 불러오고 싶을 때 사용

-엑셀의 Vlookup과 유사

 

 

left join

-공통 컬럼(키값)을 기준으로 하나의 테이블에 값이 없더라도 모두 조회되는 경우 (null 값 있음)

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

*테이블1,2 뒤에 a,b는 쉽게 조회하기 위해 테이블에 별명 지어줌

inner join

-공통컬럼(키값)을 기준으로, 두 테이블에 모두 있는 값만 조회(null 값 없음)

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

---

1. join 으로 두 테이블의 데이터 조회하기

-고객의 주문식당 조회하기

조회컬럼: 고객이름, 연령, 성별, 주문식당

+고객명으로 정렬, 중복 없도록 조회(distinct)

 

select distinct c.name,

           c.age,

           c.gender,

           f.restaurnat_name

from food_orders f left join customers c on f.customer_id=c.customer_id

order by c.name

 

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