- 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!
서브쿼리와 조인을 함께 사용하면
복잡한 연산이 필요한 쿼리도 작성이 가능하다!
길~게 작성된 쿼리가 부담스러웠는데,
괄호(서브쿼리)를 찾고 구문별로 끊어서 읽다보니 어느새 읽히기 시작했다.
쿼리문을 작성할 때도 비슷한 구조로 생각하는 연습 해보기.
