SQL#4-2

---

- 4주차 (2/2)

1. 필요한 데이터가 서로 다른 테이블에 있을 때 조회하기

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

• LEFT JOIN: 공통 컬럼(키값)을 기준으로, 하나의 테이블에 값이 없더라도 모두 조회.

  

• INNER JOIN: 공통 컬럼(키값)을 기준으로, 두 테이블 모두에 있는 값만 조회.

  

• 💡 공통컬럼은 묶어주기 위한 ‘공통 값’ 이기 때문에 두 테이블의 컬럼명은 달라도 됨.
  EX)
  주문정보에는 ‘고객ID’, 고객정보에는 ‘고객아이디’ 라고 컬럼명이 되어있다면,
  테이블1.고객ID = 테이블2.고객아이디 와 같이 묶어줄 수 있다.

---

2. JOIN 으로 두 테이블의 데이터 조회 (실습)

가. 한국 음식의 주문별 결제 수단과 수수료율을 조회
+) 조회 컬럼: 주문 번호, 식당 이름, 주문 가격, 결제 수단, 수수료율
/ 결제 정보가 없는 경우도 포함하여 조회

가-1. QUERY 적기 전 흐름 정리하기

#어떤 테이블에서 데이터를 뽑을 것인가 > 주문테이블
#어떤 컬럼을 이용할 것인가 > 주문 번호, 식당 이름, 주문 가격, 결제 수단, 수수료율, 음식 타입
#어떤 조건을 지정해야 하는가 > 한국 음식
#어떤 함수(수식)을 이용해야 하는가 > 결합

가-2. 구문으로 만들기

#어떤 테이블에서 데이터를 뽑을 것인가 > FROM food_orders, payments
#어떤 컬럼을 이용할 것인가 > order_id, restaurant_name, price, pay_type, vat
#어떤 조건을 지정해야 하는가 > WHERE cuisine_type = 'Korean'
#어떤 함수(수식)을 이용해야 하는가 > LEFT JOIN payments (ON order_id)

가-3. 전체 구조로 합치기

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'

나. 고객의 주문 식당 조회
+) 조회 컬럼: 고객 이름, 연령, 성별, 주문 식당
/ 고객명으로 정렬, 중복 없이 조회

나-1. QUERY 적기 전 흐름 정리하기

#어떤 테이블에서 데이터를 뽑을 것인가 > 고객테이블, 주문테이블
#어떤 컬럼을 이용할 것인가 > 고객 이름, 연령, 성별, 주문 식당
#어떤 조건을 지정해야 하는가 > 없음
#어떤 함수(수식)을 이용해야 하는가 > 결합, 정렬, 중복 제거

나-2. 구문으로 만들기

#어떤 테이블에서 데이터를 뽑을 것인가 > FROM customers, food_orders
#어떤 컬럼을 이용할 것인가 > name, age, gender, restaurant_name
#어떤 조건을 지정해야 하는가 > 없음
#어떤 함수(수식)을 이용해야 하는가 > LEFT JOIN payments (ON order_id), ORDER BY, DISTINCT

나-3. 전체 구조로 합치기

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

---

3. JOIN 으로 두 테이블의 값 연산 (실습)

가. 주문 가격과 수수료율을 곱하여 주문별 수수료 구하기
+) 조회 컬럼: 주문 번호, 식당 이름, 주문 가격, 수수료율, 수수료
/ 수수료율이 있는 경우만 조회

가-1. QUERY 적기 전 흐름 정리하기

#어떤 테이블에서 데이터를 뽑을 것인가 > 주문테이블, 결제테이블
#어떤 컬럼을 이용할 것인가 > 주문 번호, 식당 이름, 주문 가격, 수수료율
#어떤 조건을 지정해야 하는가 > 없음
#어떤 함수(수식)을 이용해야 하는가 > 결합, 곱하기

가-2. 구문으로 만들기

#어떤 테이블에서 데이터를 뽑을 것인가 > FROM food_orders, payments
#어떤 컬럼을 이용할 것인가 > order_id, restaurant_name, price, vat
#어떤 조건을 지정해야 하는가 > 없음
#어떤 함수(수식)을 이용해야 하는가 > INNER JOIN, price * vat

가-3. 전체 구조로 합치기

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

나. 50세 이상 고객의 연령에 따라 경로 할인율 적용, 음식 타입별 원래 가격과 적용 가격 합 구하기
+) 조회 컬럼: 음식 타입, 원래 가격, 할인 적용 가격, 할인 가격
/ 할인: 나이 - 50*0.005, 고객 정보가 없는 경우 포함 조회, 할인 금액 큰 순서대로 정렬

나-1. QUERY 적기 전 흐름 정리하기

#어떤 테이블에서 데이터를 뽑을 것인가 > 주문테이블, 고객테이블
#어떤 컬럼을 이용할 것인가 > 음식 타입, 주문 금액, 연령
#어떤 조건을 지정해야 하는가 > 50세 이상
#어떤 함수(수식)을 이용해야 하는가 > 결합, 곱하기, 합계

나-2. 구문으로 만들기

#어떤 테이블에서 데이터를 뽑을 것인가 > FROM food_orders, customers
#어떤 컬럼을 이용할 것인가 > cuisine_type, price, age
#어떤 조건을 지정해야 하는가 > WHERE age >= 50
#어떤 함수(수식)을 이용해야 하는가 > AVG(price / quantity), CASE, GROUP BY

나-3. 전체 구조로 합치기

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

---

- 과제풀이

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

☑️ POINT!
서브쿼리와 조인을 함께 사용하면
복잡한 연산이 필요한 쿼리도 작성이 가능하다!

---

길~게 작성된 쿼리가 부담스러웠는데,
괄호(서브쿼리)를 찾고 구문별로 끊어서 읽다보니 어느새 읽히기 시작했다.
쿼리문을 작성할 때도 비슷한 구조로 생각하는 연습 해보기.