[내일배움캠프] 사전캠프 (6일차)

2/19 보충

오늘 한 일

• SQL (4-1 ~ 4-8)

 

4-1 포맷 변경과 조건문 복습, 이번 수업 내용 맛보기

 

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

Subquery가 필요한 경우

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

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

- 조건에 Query 결과를 사용하고 싶을 때

 

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)

 

 

4-3 [실습] User Segmentation와 조건별 수수료를 Subquery로 결합해보기

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

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-4 [실습] 복잡한 연산을 Subquery로 수행하기

[실습] 음식 타입별 지역별 총 주문수량과 음식점 수를 연산하고, 주문수량과 음식점 수 별 수수료율 산정하기

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

 

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

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

 

 

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

JOIN이 필요한 경우

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

 

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

 

 

[실습] 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

 

4-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
where c.name is not null -- null 값 제거
order by c.name

 

 

4-7 [실습] JOIN으로 두 테이블의 데이터 조회하기

[실습] 주문 가격과 수수료율을 곱하여 주문별 수수료 구하기

select f.order_id,
       f.restaurant_name,
       f.price,
       p.vat,
       f.price*b.vat "수수료율"
from food_orders f inner join payments p on f.order_id=p.order_id

 

 

[실습] 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 - (price * discount_rate)) desc

4-8 [실습문제]

식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기

 

select restaurant_name,
	   case when (price<=5000) then 'price_group1'
	   		when (price>5000 and price<=10000) then 'price_group2'
	   		when (price>10000 and price<=30000) then 'price_group3'
	   		when (price>30000) then 'price_group4'
	   	end "price_group" ,
	   case when (age<30) then 'age_group1'
	   		when (age>=30 and age<40) then 'age_group2'
	   		when (age>=40 and age<50) then 'age_group3'
	   		when (age>=50) then 'age_group4' 
	   	end "age_group" 
from
(
select restaurant_name,
	   avg(price) price,
	   avg(age) age
from food_orders f inner join customers c on f.customer_id=c.customer_id
group by restaurant_name 
) t
order by restaurant_name