2024.06.26 TIL - SQL 강의 (Subquery, Join)

Innes·2024년 6월 26일

JOIN sql subquery

TIL(Today I Learned)

목록 보기

138/147

Subquery

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

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)

예시 1

음식 주문시간이 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

// 음식 주문시간 - 25 했을 때, 25분보다 작은 결과가 있을 수도 있음
// -> 더 작은 경우는 0으로 출력하기 위해 if문 적용

예시 2

음식점의 평균 단가별 segmentation 을 진행하고, 그룹에 따라 수수료 연산하기(수수료 구간 -
~5000원 미만 0.05%
~20000원 미만 1%
~30000원 미만 2%
30000원 초과 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

실습 1

오답

SELECT addr,
	   case when delivery_time > 30 then delivery_time/QUALIFY "30분 초과" 
	   		when delivery > 20 then delivery_time/QUALIFY "20분에서 30분"
	   		else delivery_time/QUALIFY end "10분에서 20분"
from (select addr, delivery_time, quantity
from food_orders
group by 1) a

// ❌ subquery 안에 delivery_tiem, quantity를 따로 놓고 거기서 갖다 쓰려하니까 에러가 났다.
// 이 둘을 미리 avgerage 구해놓고, 그걸 가지고 이름 붙여줘야 하는 거였다.

해답

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

실습 2

음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기
(할인조건
수량이 5개 이하 → 10%
수량이 15개 초과, 총 주문금액이 300000 이상 → 0.5%
이 외에는 일괄 1%)

오답 1)

SELECT restaurant_name,
		total_quantity,
		total_price,
		case when total_quantity <= 5 then 0.1 
			 when total_quantity > 15 and total_price >= 300000 then 0.005
			 else 0.01 end rate
from 
(
SELECT restaurant_name ,
		sum(quantity) total_quantity,
		sum(price) total_price
from food_orders 
group by 1
)

에러 메시지 : SQL Error [1248] [42000]: Every derived table must have its own alias

모든 열이 있어야 된다고...? 어떻게 해결하지
-> then 뒤에 '열 이름'이 와야됐는데, 출력되는 값을 지정해주는 걸로 착각했던 것이다.
-> 값은 원래 있던게 출력되는거고,해당하는 열의 이름을 지정해주도록 해보자!

오답 2)

SELECT restaurant_name,
		total_quantity,
		total_price,
		case when total_quantity <= 5 then '10%'
			 when total_quantity > 15 and total_price >= 300000 then '0.5%'
			 else '1%' end rate
from 
(
SELECT restaurant_name ,
		sum(quantity) total_quantity,
		sum(price) total_price
from food_orders 
group by 1
)

또 똑같은 에러 메시지가 떴다...

원인 : subquery에 별칭(alias)를 주지 않아서 생긴 문제였다!!
- 에러메시지 : SQL Error [1248] [42000]: Every derived table must have its own alias
- 뜻 : 파생 테이블(즉, 서브쿼리)에 별칭(alias)을 부여해야 한다!

튜터님 해답

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 : 두 테이블에서 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.공통컬럼명

예시 1

SELECT *
from food_orders left join payments on food_orders.order_id = payments.order_id

food_orders 와 payments 두 테이블을 join할 예정

벤다이어그램의 A 테이블은 food_orders, B 테이블은 payments가 된다고 생각하기

두 테이블에서 겹치는 열 : order_id

payments에는 order_id의 값이 없더라도 food_orders 테이블은 전부 다 보여줄 것 (left join)

겹치는 열 order_id를 지정해주는 구문 on

예시 2

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