[사전캠프/SQL] JOIN이란? / 4주차 숙제

1. JOIN 이란?

• JOIN이 필요한 경우
  • 필요한 데이터가 하나의 테이블에 모여있지 않을때(여러 테이블에서 데이터를 불러와야 할 때)
• 기본 원리

내가 해석한 예시 :

만약 주문 정보에서 고객 이메일을 알기 위해서는 고객 정보에서 동일한 고객 ID의 이메일을 가져와야함, 그럴때 JOIN을 사용하는 것

여기서 JOIN이 하는 일은 주문 정보와 고객 정보가 테이블이라고 할 때, 고객 ID를 기준으로 필요한 값을 가져와 주는 것

그럼 결국 JOIN을 사용할려면 두 테이블에 공통으로 가지고 있는 컬럼이 있어야함! ( 위의 예시에서는 공통적인 컬럼은 고객 ID)

JOIN을 하면 이런식으로 합쳐진다

---

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

---

• 유의 사항
  • 공통 컬럼은 묶어주기 위한 '공통 값'이기 때문에 두 테이블의 컬럼명은 달라도 괜찮음
    • 예를 들어 주문 정보에는 '고객ID', 고객정보에는 '고객아이디'라고 컬럼명이 되어있어도, 묶을 수 있음

---

• 예시 : 주문 테이블과 고객 테이블을 customer_id를 기준으로 left 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

여기서 나는 a가 무엇을 뜻하는건지 몰랐는데, 2개의 테이블이니까 저기 FROM절에 있는 a는 결국

food_orders라는 테이블은 a이고, customers라는 테이블은 b이다. 

a.customer_id와 b.customer_id는 a테이블과 b테이블의 공통컬럼명을 나타낸다.

LEFT JOIN은 공통 컬럼(키값)을 기준으로, 하나의 테이블에 값이 없더라도 모두 조회되는 경우라고 했으니,

a 테이블(food_orders)의 모든 데이터와 b테이블(customers)의 일치하는 코드를 가져옴, 만약 없는 경우는 NULL값을 반환한다.

---

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

2-1. 한국 음식의 주문별 결제수단과 수수료율을 조회하기

(조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 결제 수단, 수수료율) *결제 정보가 없는 경우도 포함하여 조회

1. 어떤 테이블에서 데이터를 뽑을 것인가 - food_orders,payments
2. 어떤 컬럼을 이용할 것인가 - pay_type,vat,order_id,cuisine_type
3. 어떤 조건을 지정해야 하는가 
4. 어떤 함수 (수식) 을 이용해야 하는가 - GROUP BY,LEFT 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'
GROUP BY order_ID

처음에는 select에 cuisine_type도 넣어야하는거 아닌가? 라고 생각했는데 ㅁ문제 조회 컬럼에 포함되지 않는 걸 보고 뺐다!!

이 정도 난이도까지는 무난하게 하는 것 같다 :ㅁ 조금 더어려워지고 서브쿼리 나와버리면 머리아플 거 같네 .....ㅠㅠ 

---

2-2. 고객의 주문 식당 조회하기

(조회 컬럼 : 고객 이름, 연령, 성별, 주문 식당) *고객명으로 정렬, 중복 없도록 조회

1. 어떤 테이블에서 데이터를 뽑을 것인가 - customers, food_orders
2. 어떤 컬럼을 이용할 것인가 - name,age,gender,customer_id,restarant_name
3. 어떤 조건을 지정해야 하는가 
4. 어떤 함수 (수식) 을 이용해야 하는가 - LEFT JOIN

• 전체 구조로 합치기

SELECT DISTINCT 
	a.name,
	a.age,
	a.gender,
	b.restaurant_name
FROM customers a LEFT JOIN food_orders b ON a.customer_id=b.customer_id
ORDER BY a.name

우선 나는 이렇게했다, 하지만 중복값은 계속 나왔고 DISTINCT를 넣어도 중복값이 나온것이다.

내가 생각한건 사람들 기준으로 주문 식당을 조회하는거니까, 테이블을 저렇게했는데,

문제를 찾아보니 테이블 순서가 바뀌었다고한다,

테이블의 순서를 바꿔주면 food_orders 테이블을 기준으로 조인하기 때문에, 고객이 여러 식당을 주문한 경우

각 식당 이름에 대해 고객 정보를 가져온다, 그리고 DISTINCT가 이 조합에서 중복을 제거한다.

근데 .. 문제는 고객의 주문식당 조회니까 .. 당연히 고객인 줄 알았는데 .. 내가 문제 이해를 제대로 못하는걸까? ㅠㅠ.. 아직 문제랑 쿼리랑 조합이 잘 되지 않는 것 같아서 아쉽다. . 그리고 수정한 쿼리!

SELECT DISTINCT 
	b.name,
	b.age,
	b.gender,
	a.restaurant_name
FROM food_orders a LEFT JOIN customers b ON a.customer_id=b.customer_id
ORDER BY b.name

---

3. [실습] JOIN으로 두 테이블의 값을 연산하기

3-1. 주문 가격과 수수료를 곱하여 주문별 수수료 구하기

(조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 수수료율, 수수료) *수수료율이 있는 경우만 조회

1. 어떤 테이블에서 데이터를 뽑을 것인가 - order_id,restaurant_name,price,vat
2. 어떤 컬럼을 이용할 것인가 - food_orders, payments
3. 어떤 조건을 지정해야 하는가 - price * vat
4. 어떤 함수 (수식) 을 이용해야 하는가 -INNER JOIN

• 전체 구조로 합치기

SELECT
	a.order_id,
	a.restaurant_name,
	a.price,
	b.vat,
	price * vat '수수료율'
FROM food_orders a INNER JOIN payments b ON a.order_id=b.order_id

이거는 문제가 ... 이상하게 되어있어서 살짝 수정해서 풀었다!

문제를 수정하니까 오히려 더 잘 이해되고 잘 풀린듯 ?! 

---

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

(조회 컬럼 : 음식 타입, 원래 가격, 할인 적용 가격, 할인 가격)

*할인 : 나이-50*0.005 * 고객 정보가 없는 경우도 포함하여 조회, 할인 금액이 큰 순서대로 정렬

1. 어떤 테이블에서 데이터를 뽑을 것인가 - food_orders, customers
2. 어떤 컬럼을 이용할 것인가 - cuisine_type,price,할인 적용 가격,할인 가격
3. 어떤 조건을 지정해야 하는가 - age >= 50
4. 어떤 함수 (수식) 을 이용해야 하는가 - LEFT JOIN,ORDER BY

• 전체 구조로 합치기

우선, 할인율을 적용하기전에 할인 가격을 계산해야한다.

그리고 할인가를 알려면 나이-50*0.005라고 했으니 나이가 필요하고(50세 이상이니까 50세 이상이라는 조건도 정해줘야함),

이 할인가 * 원래 가격을 하면 할인 적용 가격이 나온다.

그러므로 price와 age를 써야하는데 두개 테이블이 다르니까 JOIN을 해준다.

(고객 정보가 없는 경우도 포함하여 조회해야 하니, LEFT JOIN을 사용했다.) 정답에서는 INNER JOIN인데 왜그런지 아직 잘 모르겠음

SELECT a.cuisine_type,
       price,
       price*((b.age-50)*0.005) discount_price
FROM food_orders a LEFT JOIN customers b ON a.customer_id=b.customer_id
WHERE b.age>=50

그리고 나서 원래 가격과 할인 적용 가격 합을 구해야한다. 

원래 가격의 합은 SUM(price), 할인 적용 가격 합은 원래 가격에서 할인가를 빼주면 된다.

이거를 적용해주면

select cuisine_type,
       sum(price) "원래 가격",
       sum(price)-sum(discount_price) "할인 적용가"

이렇게 된다. 근데 여기서 중요한 점은 이 쿼리가 실행 될려면 discount_price의 값을 받아올 수 있어야한다.

그러니까 위의 JOIN한 값을 가지고 이 쿼리를 실행 할 수 있는 거니까, 위의 JOIN문이 FROM 자리에 와야하는 것.

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 LEFT JOIN customers b ON a.customer_id=b.customer_id
WHERE b.age>=50
) c

합치게 되면 이렇게 된다. 

문제에서 할인 가격도 조회 컬럼에 포함되어 있어서, 추가했다

또한 할인 금액이 큰 순서대로 정렬이니까 내림차순으로 정렬 해주었고( 위에서 부터 4개의 컬럼 정렬)

음식 타입별로 <<니까 그룹으로 묶어줬다

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 LEFT JOIN customers b ON a.customer_id=b.customer_id
WHERE b.age>=50
) c
GROUP BY cuisine_type
ORDER BY 4 DESC

---

4. 4주차 숙제

[문제] 식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation하기

- 평균 음식 주문 금액 기준 : 5,000 / 10,000 / 30,000 / 30,000 초과

- 평균 연령 : ~20대 / 30대 / 40대 / 50대 이상

1. 어떤 테이블에서 데이터를 뽑을 것인가 - food_orders,customers
2. 어떤 컬럼을 이용할 것인가 - restaurant_name,price,age,customer_id
3. 어떤 조건을 지정해야 하는가 - 위의 기준들
4. 어떤 함수 (수식) 을 이용해야 하는가 - AVG,CASE WHEN,GROUP BY,ORDER BY, INNER JOIN

• 전체 구조로 합치기

SELECT restaurant_name,
	CASE WHEN price <= 5000 THEN '5000원 이하'
		WHEN price 5000 BETWEEN 10000 THEN '10,000원'
		WHEN price 10000 BETWEEN 30000 THEN '30,000원'
		WHEN price >= 30000 THEN '30000원 초과' END '평균 음식 주문 금액 기준'
	CASE WHEN age < 30 THEN '~20대'
		WHEN age BETWEEN 30 AND 39 THEN '30대'
		WHEN age BETWEEN 40 AND 49 THEN '40대'
		ELSE '50대 이상' END '평균 연령'
from
(
SELECT a.restaurant_name,
	AVG(a.price) price,
	AVG(b.age) age
FROM food_orders a INNER JOIN customers b ON a.customer_id=b.customer_id
GROUP BY a.restaurant_name
) m
ORDER BY restaurant name

처음에 이렇게 풀었는데, 저기 저 가격에서 오류가 떴다.

이유를 찾아보니 5000원과 10000원 사이라고하면 10000원도 포함된다고한다 .. 그래서

다음 쿼리 10000원에서 30000원 사이가 되는거니까 중복으로 겹치게되어서 오류가 뜬거라고한다.

그럼 결국 조건을 나눠줄 수 밖에없어서 조건을 나눠주었다.

SELECT restaurant_name,
	CASE WHEN price <= 5000 THEN '5000원 이하'
		WHEN price >5000 AND price <=10000 THEN '10,000원'
		WHEN price >10000 and price <=30000 THEN '30,000원'
		WHEN price >= 30000 THEN '30000원 초과' END '평균 음식 주문 금액 기준',
	CASE WHEN age < 30 THEN '10대20대'
		WHEN age BETWEEN 30 AND 39 THEN '30대'
		WHEN age BETWEEN 40 AND 49 THEN '40대'
		ELSE '50대 이상' END '평균 연령'
from
(
SELECT a.restaurant_name,
	AVG(a.price) price,
	AVG(b.age) age
FROM food_orders a INNER JOIN customers b ON a.customer_id=b.customer_id
GROUP BY a.restaurant_name
) m
ORDER BY 1

나눠주면서 쉼표 빠진거 추가해주고 ORDER BY를 restaurant_name으로 적어놓으니 서브쿼리랑 충돌이 나는지, 오류가 떠서

그냥 첫번째 쿼리를 뜻하는 1로 바꿔주었다

문제 짱어렵다 ^^ ,, 문제가 조금 상세했으면 차근차근 문제 하나하나 분해해서 풀었을지도 모르겠다

내일은 1~4주차 복습할 예정!