4주차 SQL
키워드 정리
subquery 쿼리의 결과물을 mainquery 에서 쓰는것 (2+1) * 6 사칙연산에서 괄호와 같은 개념 select from where having 등 다양한곳에서 응용 가능.
join 테이블을 같은 컬럼을 기준으로 합치는것 A a join B b on a.common_colmn = b.common_column
4-1
수업목표
Subquery 를 활용하여 복잡한 연산을 수행한다
Join 을 활용하여 여러개의 테이블에 있는 데이터를 한 번에 조회하고 연산한다
복습
문자변경 replace
문자추출 substring == substr
문자합체 concat
조건문
if(조건, '조건충족할때', '조건미충족할때')
case when end
case when 조건1 then 값1
when 조건2 then 값2
else 값
end alias
4-2 여러번의 연산을 한번의 SQL문으로 수행하기 Subquery문 = ( ) 먼저 계산하는 쿼리를 짠다
조건문에 연산결과를 사용하고싶을 때
where에 Query 결과를 사용하고싶을 때
> 쿼리한 결과물을 쿼리에 사용하고 싶을 때 쓰는 구문
select column1, special_column
from
( /* subquery */
select column1, column2 special_column
from table1 ) a
예시) 준비시간이 25분이 넘는경우 overtime 추출하기
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
의문) alias를 서브쿼리문과 본쿼리문에서 둘 다 over_time을 설정했는데 오류가 생기지않는다?
서브쿼리를 from에 쓰면 메인쿼리의 테이블이 된다. 아무문제없다~
서브쿼리는 select from where having 다양한곳에서 응용가능하다.
4-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, avg_delivery_time,
case when avg_delivery_time<=20 then '<=20'
when avg_delivery_time>20 and avg_delivery_time<=30 then '20<x<=30'
else '>30' end delivery_time_segment
from
(
select restaurant_name,
substr(addr, 1, 2) sido,
avg(delivery_time) avg_delivery_time
from food_orders
group by 1, 2
) a
4-4실습
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
select restaurant_name,
sum_price, sum_quantity,
case when sum_quantity <=5 then 0.1
when sum_quantity >15 and sum_price >=30000 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
4-5 서로 다른 테이블의 데이터를 조회하기 join
join 엑셀의 Vlookup과 유사하다
서로다른 테이블의 공통 컬럼을 기준으로 두 테이블을 합쳐서, 각각 테이블에서 필요한 데이터를 조회한다.
inner join, left join, right join, full join, cross join, self join이 있다.
left join : 공통컬럼 (키값 key column, common column) 기준으로, 왼쪽컬럼은 모두조회되고 오른쪽컬럼은 null로 채워진다.
a 테이블과 b 테이블중 키값 기준으로 a테이블은 전부 불러오고 b테이블은 키값이 곂치는 행만 나온다.
inner join : 공통 컬럼(키값) 을 기준으로, 두테이블 양쪽에 있는 행만 조회한다.
a테이블과 b테이블에 곂치는 키값이 있는 행만 나온다.
join의 기본구조
from A_table left join B_table on A_table.common_column=B_table.common_colomn
from A_table a left join B_table b on a.common_column=b.common_colomn
food_orders 와 payments 테이블 order_id기준으로 묶기.
select*
from food_orders left join payments on food_orders.order_id=payments.order_id
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
서브커리, 컬럼, 테이블 전부 alias 지정 가능.
4-6 join 실습
실습) 한국 음식의 주문별 결제수단과 수수료율 조회하기
select f.order_id, f.restaurant_name, f.price,
p.pay_type, p.vat
from food_orders f join payments p on f.order_id=p.order_id
where cuisine_type = 'Korean'
실습)고객의 주문 식당 조회하기
(조회 컬럼 : 고객 이름, 연령, 성별, 주문 식당) *고객명으로 정렬, 중복 없도록 조회
order by 도 group by 처럼 1 사용가능.
select distinct c.name, c.age, c.gender,
f.restaurant_name
from food_orders f join customers c on f.customer_id=c.customer_id
where cuisine_type = 'Korean'
order by c.name
4-7 join 실습
두테이블의 값을 가져와 연산실습
select f.order_id, f.restaurant_name, f.price,
p.vat, f.price*p.vat vat2
from food_orders f inner join payments p on f.order_id=p.order_id
실습) 50세 이상 고객의 연령에 따라 경로 할인율을 적용하고, 음식 타입별로 원래 가격과 할인 적용 가격 합을 구하기
(조회 컬럼 : 음식 타입, 원래 가격, 할인 적용 가격) *할인 : (나이-50)*0.005
고객 정보가 없는 경우도 포함하여 조회, 할인 금액이 큰 순서대로 정렬
할인적용까지 서브쿼리 가격합은 본쿼리
select a.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 3 desc
숙제 식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기
평균 음식 주문 금액 기준 : 5,000 이하 / ~10,000 / ~30,000 / 30,000 초과
평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상
두 테이블 모두에 데이터가 있는 경우만 조회, 식당 이름 순으로 오름차순 정렬
group by 하면 무조건 select에 선언하기. alias 습관적으로 해주기 case문은 end 뒤 end as price_group
내쿼리
select restaurant_name ,
case when avg(price)<=5000 then 'price_group1'
when avg(price)<=10000 then 'price_group2'
when avg(price)<=30000 then 'price_group3'
else 'price_group4' end as price_group,
case when avg(age)<=29 then 'age_group1'
when avg(age)<=39 then 'age_group2'
when avg(age)<=49 then 'age_group3'
else 'age_group4' end age_group
from food_orders f inner join customers c on f.customer_id=c.customer_id
group by restaurant_name
order by 1
정답 쿼리
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 31 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
내 답. 조인후 바로 분류안에 계산
정답. 서브쿼리로 계산값 구하고 메인쿼리에서 분류
세그멘테이션하는 습관 들이기
추가학습하기 join on