**서브쿼리를 사용하는 이유
- 여러번의 연산을 수행해야할 때
- 조건문에 연산결과를 사용해야할 때
- 조건에 쿼리를 사용하고 싶을 때
기본 형식
select column1, special_column from ( /* subquery */ select column1, column2 special_column from table1 ) a
Query 를 적기 전에 흐름을 정리해보기
1. 어떤 테이블에서 데이터를 뽑을 것인가
2. 어떤 컬럼을 이용할 것인가
3. 어떤 조건을 지정해야 하는가
4. 어떤 함수 (수식) 을 이용해야 하는가
select price/quantity
from
(
select price, quantity
from food_orders
) a
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
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
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
select restaurant_name,
sido,
avg_delivery_time,
case when avg_delivery_time then '<=20'
when avg_delivery_time>20 and avg_delivery_time <=30 then '20<x<=30'
when avg_delivery_time>30 then '>30' end 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
select cuisine_type,
total_quantity,
count_res,
case when total_quantity >= 30 and count_res >= 5 then 0.005
when total_quantity < 30 and count_res >= 5 then 0.008
when total_quantity >= 30 and count_res < 5 then 0.01
when total_quantity < 30 and count_res < 5 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,
case when "총 주문수량"<= 5 then 0.1
when "총 주문수량"> 15 and "총 주문금액">= 300000 then 0.005
else 0.001 end "할인율"
FROM
(
select restaurant_name,
sum(quantity) "총 주문수량",
sum(price) "총 주문금액"
from food_orders
group by 1
) a
조인의 원리는 엑셀의 브이룩업과 유사하다
필요한 데이터가 여러 테이블에 각각 산재되어 있을때 한 테이블로 합춰주는 기능
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.공통컬럼명
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.vat,
f.price * p.vat "수수료"
from food_orders f inner join payments p on f.order_id = p.order_id
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 3 DESC
1) 서브쿼리
(
select f.restaurant_name,
avg(f.price) avg_price,
avg(c.age) avg_age
from food_orders f inner join customers c on f.customer_id = c.customer_id
group by 1
) a
2) 메인 쿼리 (서브쿼리)
select restaurant_name,
case when avg_price <=5000 then 'price_group1'
when avg_price between 5001 and 10000 then 'price_group2'
when avg_price between 10001 and 30000 then 'price_group3'
when avg_price >30000 then 'price_group4' end price_group,
case when avg_age <=29 then 'age_group1'
when avg_age between 30 and 39 then 'age_group2'
when avg_age between 40 and 49 then 'age_group3'
when avg_age >=50 then 'age_group4' end age_group
from
(
select f.restaurant_name,
avg(f.price) avg_price,
avg(c.age) avg_age
from food_orders f inner join customers c on f.customer_id = c.customer_id
group by 1
) a
order by 1