테이블 조인 3

📌 의미 있는 데이터 추출

이전에 테이블을 조인해서 만든 결과 테이블을 이용해 의미 있는 데이터를 추출해보자.

✅ 상품들 중에서 별점의 평균을 기준으로 여성 회원들이 가장 좋은 평가를 한 상품은 무엇인가?

1. 성별이 여자인 회원 조건
2. item 테이블의 id 컬럼을 기준으로 그루핑
   ❓ 하나의 상품에 여러 개의 리뷰가 달릴 수 있기 때문에 한 상품에 달린 모든 별점의 평균을 구하기 위해 각 상품별로 row들을 그루핑 해주어야 한다. 하나의 상품을 식별할 수 있는 건 item 테이블의 id 컬럼이다.
3. 각 상품별로 별점 평균
4. 별점 평균 내림차순 정렬

SELECT i.id, i.name, AVG(star)
FROM
	copang_main.item AS i LEFT OUTER JOIN copang_main.review AS r
		ON r.item_id = i.id
	LEFT OUTER JOIN copang_main.member AS m
		ON r.mem_id = m.id
WHERE m.gender = 'f'
GROUP BY i.id, i.name
ORDER BY AVG(star) DESC;

✅ 상품들의 리뷰 개수

SELECT i.id, i.name, AVG(star), COUNT(*)
FROM
	copang_main.item AS i LEFT OUTER JOIN copang_main.review AS r
		ON r.item_id = i.id
	LEFT OUTER JOIN copang_main.member AS m
		ON r.mem_id = m.id
WHERE m.gender = 'f'
GROUP BY i.id, i.name
ORDER BY AVG(star) DESC;

별점 평균이 5점인 상품들은 리뷰가 1개 밖에 없다.
단 하나의 리뷰만으로 인기가 많은 상품이라고 보기엔 어렵다.
이러한 문제를 해결하기 위해 row의 개수를 살펴보는 게 좋다.

리뷰 수가 최소 2개 이상인 그룹만 추출해보자.

SELECT i.id, i.name, AVG(star), COUNT(*)
FROM
	copang_main.item AS i LEFT OUTER JOIN copang_main.review AS r
		ON r.item_id = i.id
	LEFT OUTER JOIN copang_main.member AS m
		ON r.mem_id = m.id
WHERE m.gender = 'f'
GROUP BY i.id, i.name
HAVING COUNT(*) > 1 
ORDER BY AVG(star) DESC, COUNT(*) DESC;

남녀 공용 오버핏 반팔 라운드 티셔츠가 여성 회원들이 가장 좋게 평가한 상품이다. 반대로 남성용 밴딩 린넨 와이드 팬츠가 가장 낮은 평가를 받은 상품이다.

✅ 상품들 중에서 별점의 평균을 기준으로 남성 회원들이 가장 좋은 평가를 한 상품은 무엇인가?

SELECT i.id, i.name, AVG(star), COUNT(*)
FROM
	copang_main.item AS i LEFT OUTER JOIN copang_main.review AS r
		ON r.item_id = i.id
	LEFT OUTER JOIN copang_main.member AS m
		ON r.mem_id = m.id
WHERE m.gender = 'm'
GROUP BY i.id, i.name
HAVING COUNT(*) > 1 
ORDER BY AVG(star) DESC, COUNT(*) DESC;

냉감 반팔 티셔츠가 남성 회원이 가장 좋게 평가한 상품이다.
남성용 밴딩 린넨 와이드 팬츠가 가장 안 좋은 평가를 받은 상품이다.

남녀 회원들 모두 남성용 밴딩 린넨 와이드 팬츠가 가장 안 좋게 평가했다.
이 상품에 달린 리뷰들을 살펴보자.

SELECT * FROM copang_main.review WHERE item_id = 2;

---

✅ 과제 : 여러 테이블 조인

member, item, review 테이블이 있다.

세 테이블을 조인해서 '남녀 공용 상품의 등록 연도별 평균 별점'을 보려고 한다. 다음 조건을 만족하는 알맞은 SQL 문을 작성하라.

👉 조인 기준(INNER JOIN)

• review 테이블의 item_id 컬럼 - item 테이블의 id 컬럼
• review 테이블의 mem_id 컬럼 - member 테이블의 id 컬럼

1. item 테이블의 gender 컬럼의 값이 u인 row들만 선별

2. item 테이블의 registration_date 컬럼에서 연도를 추출해서 이것(상품 등록 연도)을 기준으로 row들을 그루핑

3. 각 그룹 내 row 개수가 10개 이상인 그룹들만 추출

4. 결과를 별점 평균값을 기준으로 내림차순 정렬

5. 컬럼은 총 세 개를 조회

• 상품 등록 연도 컬럼('등록 연도'라는 alias)
• 각 그룹 내 row의 개수('리뷰 개수'라는 alias)
• 각 그룹별 별점 평균값('별점 평균값'이라는 alias)

SELECT
	YEAR(i.registration_date) AS '등록 연도', 
    COUNT(*) AS '리뷰 개수', 
    AVG(r.star) AS '별점 평균값'
FROM review AS r INNER JOIN item AS i
		ON r.item_id = i.id
    INNER JOIN member AS m
		ON r.mem_id = m.id
WHERE i.gender = 'u'
GROUP BY YEAR(i.registration_date)
HAVING COUNT(*) >= 10
ORDER BY AVG(r.star);

---

📝 다른 종류의 조인들

두 테이블을 서로 합치는 연산에는 크게 두 가지 종류가 있다.

• 두 테이블을 가로 방향으로 합치는 결합 연산
• 두 테이블을 세로 방향으로 합치는 집합 연산

• 결합 연산 : LEFT OUTER JOIN, RIGHT OUTER JOIN, INNER JOIN
• 집합 연산 : INTERSECT, MINUS, UNION, UNION ALL

이 때 집합 연산 중 INTERSECT, MINUS 연산자는 MySQL에서 지원하지 않기 때문에 JOIN을 통해 간접적으로 원하는 결과를 얻었다.

이 외에도 여러가지 조인 종류가 존재한다.

1. NATURAL JOIN
   두 테이블에서 같은 이름의 컬럼을 찾아서 자동으로 조인 조건을 설정하고 INNER JOIN 해주는 조인이다. 자연 조인이라고도 한다.

SELECT
	p.id,
    p.player_name,
    p.team_name,
    t.team_name,
    t.region
FROM player AS p NATURAL JOIN team AS t;

자동으로 조인 조건을 찾아서 조인하기 때문에 조인 기준을 설정하는 ON절을 입력하지 않아도 된다.

2. CROSS JOIN
   한 테이블의 하나의 row에 다른 테이블의 모든 row들을 매칭하고, 그 다음 row에서도 또 다른 테이블의 모든 row들을 매칭하는 것을 반복함으로써 두 테이블의 row들을 모든 조합으로 보여주는 조인이다.

SELECT * FROM member CROSS JOIN stock

3. SELF JOIN
   셀프라는 단어 뜻 그대로 테이블이 자기 자신과 조인하는 경우를 말한다.

SELECT * FROM member AS m1 LEFT OUTER JOIN member AS m2
ON m1.age = m2.age;

예시를 살펴보자.

SELECT * FROM FOR_TEST.employee;

SELECT *
FROM employee AS e1 LEFT OUTER JOIN employy AS e2
ON e1.boss = e2.id;

각 직원 옆에 직속 상사 정보가 함께 뜬다.

SELECT *
FROM employee AS e1 LEFT OUTER JOIN employy AS e2
ON e1.boss = e2.id
LEFT OUTER JOIN employee AS e3
ON e2.boss = e3.id;

SELF JOIN을 한번 더 하니까, 한 직원의, 직속 상사의, 직속 상사까지도 볼 수가 있다. 결과를 보니 경영관리부의 '서종민'이라는 분이 CEO라는 걸 알 수 있다.

4. FULL OUTER JOIN
   두 테이블의 LEFT OUTER JOIN 결과와 RIGHT OUTER JOIN 결과를 합치는 조인이다. 대신 두 결과에 존재하는 공통 row들은 한 번만 표현해준다.

• LEFT OUTER JOIN과 RIGHT OUTER JOIN을 UNION으로 중복 제거해준다.
• Oracle이라는 DBMS에는 FULL OUTER JOIN 연산자가 내장되어 있다.

5. Non-Equi 조인

• 조인 조건에 등호(=)를 사용하는 조인을 Equi 조인이라고 한다.
• Non-Equi 조인은 동등 조건이 아닌 다른 종류의 조건을 사용해서 조인한다.

SELECT
	m.email,
    m.sign_up_day,
    i.name,
    i.registration_date
FROM copang_main.member AS m LEFT OUTER JOIN copang_main.item AS i
ON m.sign_up_day < i.registration_Date
ORDER BY m.sign_up_day ASC;

---