SQL 서브쿼리(Subquery) 와 조인(Join)

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

연산이 한 번에 끝나면 참 좋은데 아닐 때가 많죠.

문자 변경한 다음에 숫자 연산 하고 그 결과로 또 연산을 해야할 때,

긴긴 쿼리문 보다는 조금 더 효율적이고 알아보기 쉽게 사용해봅시다.

 

Subquery 가 필요한 경우

• 여러번의 연산을 수행해야 할 때
  → 수수료를 부과할 수 있는 시간을 구하고
  → 구해진 시간에 주문 금액별로 가중치를 주고
  → 가중치를 적용한 결과로 최종 예상 배달비를 계산할 때
  
  
• 조건문에 연산 결과를 사용해야 할 때
   → 음식 타입별 평균 음식 주문금액 따라 음식비 상/중/하 를 나누고 싶을 때
  
  
• 조건에 Query 결과를 사용하고 싶을 때
  → 30대 이상이 주문한 결과만 조회하고 싶을 때

Subquery 문의 기본 구조

Sub 라는 명칭에서 알 수 있듯이, Query 안에 sub 로 들어간 구문이라고 생각해주시면 됩니다.

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)

---

[실습] Subquery 문을 이용하여 연산문 적어보기

• 주문 테이블에서 주문 번호, 음식점명, 음식 준비시간을 가져오기
  • select 기본문
  • 가져올 컬럼 적기
  • subquery 문으로 추가

select order_id, restaurant_name, food_preparation_time
from 
(
select order_id, restaurant_name, food_preparation_time
from food_orders
) a

 

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

---

[실습] User Segmentation 와 조건별 수수료를 Subquery 로 결합해보기

조건문과 Subquery 를 결합하여 user segmentation 과 연산을 해봅시다.

 

1) [실습] 음식점의 평균 단가별 segmentation 을 진행하고, 그룹에 따라 수수료 연산하기

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

 

2) [실습] 음식점의 지역과 평균 배달시간으로 segmentation 하기

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 로 수행하기

하나의 쿼리문에서 수행하기 어려운 복잡한 연산을 Subquery 로 실행해봅시다

 

1. [실습] 음식 타입별 총 주문수량과 음식점 수를 연산하고, 주문수량과 음식점수 별 수수료율을 산정하기
(음식점수 5개 이상, 주문수 30개 이상 → 수수료 0.05% ,
음식점수 5개 이상, 주문수 30개 미만 → 수수료 0.08% ,
음식점수 5개 미만, 주문수 30개 이상 → 수수료 1% ,
음식점수 5개 미만, 주문수 30개 미만 → 수수로 2%)

SELECT 
    cuisine_type, 
    total_quantity,
    count_res,
    CASE 
        WHEN count_res >= 5 AND count_orders >= 30 THEN 0.0005
        WHEN count_res >= 5 AND count_orders < 30 THEN 0.008
        WHEN count_res < 5 AND count_orders >= 30 THEN 0.01
        WHEN count_res < 5 AND count_orders < 30 THEN 0.02 
    END AS ratio_of_add
FROM (
    SELECT 
        cuisine_type,
        SUM(quantity) AS total_quantity,
        COUNT(DISTINCT restaurant_name) AS count_res,
        COUNT(*) AS count_orders
    FROM food_orders
    GROUP BY cuisine_type
) a

 

2) [실습] 음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기

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)

필요한 데이터가 꼭 하나의 테이블에 모여있지는 않죠.

이럴 때 여러 테이블에서 데이터를 불러오는 방법을 알아봅시다.

 

JOIN 이 필요한 경우

 

💡 주문 가격은 주문테이블에 있지만, 어떤 수단으로 결제를 했는지는 결제테이블에 있어요

💡 주문을 한 사람을 확인하려면, 주문 테이블과 고객 테이블에서 각각 정보를 가져와서 엑셀에서 합쳐줘야 해요

💡 주문 건별 수수료를 계산하려면 수수료율이 필요한데, 결제 테이블에 있어서 어떻게 연산할 수 있을지 모르겠어요

JOIN 의 기본 원리와 종류

💡 JOIN 은 기본적으로 엑셀의 Vlookup 과 유사합니다 아래와 같은 경우를 생각해보세요

• 주문 정보에서 고객 이메일을 알기 위해서는, 고객 정보에서 동일한 고객 ID 의 이메일을 가져와야 합니다.
  (엑셀에서는 vlookup(고객ID, 고객 정보, 3, False) 라고 적으실 것입니다)
• JOIN 은 동일한 원리를 가집니다. 각각 주문 정보와 고객 정보가 테이블이라고 할 때, 고객 ID 를 기준으로 필요한 값을 가져와 주는 것입니다.
• 이 때 중요한 것은, 두 테이블이 공통으로 갖고 있는 컬럼이 됩니다. 위의 예시에서는 ‘고객 ID’가 되겠죠.

공통 컬럼을 기준으로 묶은 형태는 아래와 같이 됩니다. 즉, 공통 컬럼을 기준으로 두 테이블을 합쳐서, 각각 테이블에서 필요한 데이터를 조회할 수 있도록 만들어주는 것입니다.

 

조인은 하는 방법에 따라 여러가지가 있어요. 갑자기 복잡해지죠? 전혀 걱정하지 마세요. 원리는 동일합니다!

 

LEFT JOIN : 공통 컬럼 (키값) 을 기준으로, 하나의 테이블에 값이 없더라도 모두 조회되는 경우를 의미합니다. 우리의 예시로 보면 아래와 같습니다.

 

INNER JOIN : 공통 컬럼 (키값) 을 기준으로, 두 테이블 모두에 있는 값만 조회합니다. 즉, 위의 예시에서 주문번호 14의 경우, 고객에 대한 정보가 고객 정보에 없으므로 조회 시 제외됩니다.

 

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

 

💡팁!  공통컬럼은 묶어주기 위한 ‘공통 값’ 이기 때문에 두 테이블의 컬럼명은 달라도 괜찮습니다.

예를 들어 주문정보에는 ‘고객ID’, 고객정보에는 ‘고객아이디’ 라고 컬럼명이 되어있다면,

테이블1.고객ID=테이블2.고객아이디 와 같이 묶어줄 수 있습니다.

 

[실습] 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

 

 

[실습] JOIN 으로 두 테이블의 데이터 조회하기

본격적으로 JOIN 으로 두 테이블 데이터를 여러가지 방향으로 조회해봅시다.

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'

 

[실습] 고객의 주문 식당 조회하기

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

 

 

null 값을 제거하려면, distinct

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 으로 두 테이블의 값을 연산하기

1) 주문 가격과 수수료율을 곱하여 주문별 수수료 구하기

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

 

2) 50세 이상 고객의 연령에 따라 경로 할인율을 적용하고, 음식 타입별로 원래 가격과 할인 적용 가격 합을 구하기

select cuisine_type,
       sum(price) "원래 가격",
       sum(price)-sum(discount_price) "할인 적용 가격",
       sum(discount_price) "할인 가격" 
from 
(
select a.cuisine_type,
       price,
       price*((b.age-50)*0.005) discount_price
from food_orders a inner join customers b on a.customer_id=b.customer_id
where b.age>=50
) t
group by 1
order by 4 desc

 

 

3) 식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 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 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

---