[TIL] 엑셀보다 쉽고 빠른 SQL _4주차_SUBQUERY, JOIN

bmn.kim·2024년 8월 30일
0

데이터 배우기_SQL

목록 보기
4/24

SUBQUERY

**서브쿼리를 사용하는 이유
- 여러번의 연산을 수행해야할 때
- 조건문에 연산결과를 사용해야할 때
- 조건에 쿼리를 사용하고 싶을 때

기본 형식

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

<실습 01 >

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

<실습 02 >

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

<실습 03 >

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

<실습 04 >

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 

<실습 05 >

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 

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

<실습 01>

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 

<실습 02>

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
profile
문과생의 sql 배우기 많은 관심 부탁드립니다

0개의 댓글