이번 주차의 목표
1. Subquery 를 활용하여 복잡한 연산을 수행한다
2. Join 을 활용하여 여러개의 테이블에 있는 데이터를 한 번에 조회하고 연산한다
포맷 변경과 조건문 복습
문자 변경
1. REPLACE : 지정한 문자를 다른 문자로 변경
2. SUBSTRING : 특정 문자만 추출
3. CONCAT : 여러 문자를 합하여 포맷팅
조건문
1. IF
if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)
2. CASE WHEN END
case when 조건1 then 값(수식)1
when 조건2 then 값(수식)2
else 값(수식)3
end
Subquery 가 필요한 경우
1. 여러 번의 연산을 수행해야 할 때
2. 조건문에 연산 결과를 사용해야 할 때
3. 조건에 Query 결과를 사용하고 싶을 때
Subquery 문의 기본 구조
Query 안에 sub 로 들어간 구문이라 생각하면 됨
select column1, special_column
from
( /* subquery */
select column1, column2 special_column
from table1
) aselect column1, column2
from table1
where column1 = (select col1 from table2)
EX)
select price/quantity
from
(
select price, quantity
from food_orders
) a
EX) Subquery 문 안을 수정해서, 음식 주문시간이 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
EX) 음식점의 평균 단가별 segmentation 을 진행하고, 그룹에 따라 수수료 연산하기
(수수료 구간 : ~5000원 미만 0.05% / ~20000원 미만 1% / ~30000원 미만 2% / 30000원 초과 3%)
1. Query 를 적기 전에 흐름을 정리해보기
- 어떤 테이블에서 데이터를 뽑을 것인가 → 주문 테이블
- 어떤 컬럼을 이용할 것인가 → 식당 이름, 주문 금액, 주문 수량
- 어떤 조건을 지정해야 하는가 → X
- 어떤 함수 (수식) 을 이용해야 하는가 → 평균 구하는 식, 카테고리에 따라 연산, 조건
2. 구문으로 만들
- 어떤 테이블에서 데이터를 뽑을 것인가 → from food_orders
- 어떤 컬럼을 이용할 것인가 → restaurant_name, price, quantity
- 어떤 조건을 지정해야 하는가 → X
- 어떤 함수 (수식) 을 이용해야 하는가 → avg(price/quantity), case when, group by
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
EX) 음식점의 지역과 평균 배달시간으로 segmentation 하기
1. Query 를 적기 전에 흐름을 정리해보기
- 어떤 테이블에서 데이터를 뽑을 것인가 → 주문 테이블
- 어떤 컬럼을 이용할 것인가 → 식당 이름, 주소, 배달 시간
- 어떤 조건을 지정해야 하는가 → X
- 어떤 함수 (수식) 을 이용해야 하는가 → 평균 구사는 수식, 조건문, 카테고리별 연산, 문자의 특정 부분만 추출
2. 구문으로 만들기
- 어떤 테이블에서 데이터를 뽑을 것인가 → from food_orders
- 어떤 컬럼을 이용할 것인가 → restaurant_name, addr, delivery_time
- 어떤 조건을 지정해야 하는가 → X
- 어떤 함수 (수식) 을 이용해야 하는가 → avg(delivery_time), substring(addr, 1, 2), case when, group by
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
복잡한 연산을 Subquery 로 수행하기
음식 타입별 지역별 총 주문수량과 음식점 수를 연산하고, 주문수량과 음식점수 별 수수료율을 산정하기
(음식점수 5개 이상, 주문수 30개 이상 → 수수료 0.5% / 음식점수 5개 이상, 주문수 30개 미만 → 수수료 0.8% / 음식점수 5개 미만, 주문수 30개 이상 → 수수료 1% / 음식점수 5개 미만, 주문수 30개 미만 → 수수로 2%)
1. Query 를 적기 전에 흐름을 정리해보기
- 어떤 테이블에서 데이터를 뽑을 것인가 → 주문 테이블
- 어떤 컬럼을 이용할 것인가 → 음식 타입, 주소, 주문 수량, 식당 이름
- 어떤 조건을 지정해야 하는가 → X
- 어떤 함수 (수식) 을 이용해야 하는가 → 합계 구하는 기능, 갯수 수하는 기능
2. 구문으로 만들기
- 어떤 테이블에서 데이터를 뽑을 것인가 → from orders
- 어떤 컬럼을 이용할 것인가 → price, cuisine_type, addr
- 어떤 함수 (수식) 을 이용해야 하는가 → sum(quantity), count(distinct restaurant_name)
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
음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기
(할인조건 수량이 5개 이하 → 10% 수량이 15개 초과, 총 주문금액이 300000 이상 → 0.5% 이 외에는 일괄 1%)
1. Query 를 적기 전에 흐름을 정리해보기
- 어떤 테이블에서 데이터를 뽑을 것인가 → 주문 테이블
- 어떤 컬럼을 이용할 것인가 → 음식점 이름, 주문 수량, 주문 금액
- 어떤 조건을 지정해야 하는가 → X
- 어떤 함수 (수식) 을 이용해야 하는가 → 합계를 구하는 기능, 조건문
2. 구문으로 만들기
- 어떤 테이블에서 데이터를 뽑을 것인가 → from food_orders
- 어떤 컬럼을 이용할 것인가 → restaurant_name, quantity, price
- 어떤 조건을 지정해야 하는가 → X
- 어떤 함수 (수식) 을 이용해야 하는가 → sum(quantity), sum(price), case when
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
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.공통컬럼명
cf) 공통컬럼은 묶어주기 위한 ‘공통 값’ 이기 때문에 두 테이블의 컬럼명은 달라도 괜찮음
EX) 주문 테이블과 고객 테이블을 cusomer_id 를 기준으로 left 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
left join을 사용했으므로 오른쪽 테이블에는 null 값이 존재함
JOIN 으로 두 테이블의 데이터 조회하기
EX) 한국 음식의 주문별 결제 수단과 수수료율을 조회하기
(조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 결제 수단, 수수료율, 결제 정보가 없는 경우도 포함하여 조회)
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'
EX) 고객의 주문 식당 조회하기
(조회 컬럼 : 고객 이름, 연령, 성별, 주문 식당, 고객명으로 정렬, 중복 없도록 조회)
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
distinct : 중복 값 제거
null 값 제거
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
JOIN 으로 두 테이블의 값을 연산하기
EX) 주문 가격과 수수료율을 곱하여 주문별 수수료 구하기
(조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 수수료율, 수수료, 수수료율이 있는 경우만 조회)
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
EX) 50세 이상 고객의 연령에 따라 경로 할인율을 적용하고, 음식 타입별로 원래 가격과 할인 적용 가격 합을 구하기
조회 컬럼 : 음식 타입, 원래 가격, 할인 적용 가격, 할인 가격
할인 : (나이-50)*0.005
고객 정보가 없는 경우도 포함하여 조회, 할인 금액이 큰 순서대로 정렬
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
숙제
식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기
-평균 음식 주문 금액 기준 : 5,000 이하 / ~10,000 / ~30,000 / 30,000 초과
- 평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상
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 between 30 and 39 then 'age_group2'
when age between 40 and 49 then 'age_group3'
else 'age_group4' end age_group
from
(
select a.restaurant_name,
avg(price) price,
avg(age) age
from food_orders a inner join customers b on a.customer_id=b.customer_id
group by 1
) t
order by 1