다대다 맵핑(Many to Many)
다대다 맵핑은 일대다 처럼 많이 사용되는 관계이다. 다대다는 일대다에서 더 확장된 사고라고 생각하면 된다.
책과 작가의 관계를 생각해보자, 책은 여러 작가들에 의해서 집필 될 수 있다. 반대로 작가도 여러 책들을 쓸 수 있다. 학생과 수업의 관계를 생각해보면, 학생은 여러 수업을 들을 수 있고, 수업도 여러 학생들로 구성되어 있다. 이것이 바로 many to many 맵핑이다. tv쇼와 리뷰어의 관계도 마찬가지이다. tv쇼 하나에 여러 리뷰어들이 리뷰를 쓸 수 있으며, 리뷰어도 여러 tv쇼에 리뷰를 쓸 수 있다.
그림으로 표현하면 다음과 같다.
--------- -----------
|tv show| <--(n)-------(m)--> |reviewers|
--------- -----------다대다 관계 자체를 그대로 SQL로 풀어내는 방법은 쉽지 않다. 다대다 관계를 풀어내는 핵심은 제 3자 table을 만들어 다대다 관계를 일대다, 다대일로 바꿔내어 문제를 해결하는 것이다. 가령 tv show가 있고 reviewer가 있다면 이들의 review 관계를 tv show - review - reviewers 로 바꾸는 것이다. tv show에 review는 여러 개 있을 수 있다. 각 review는 하나의 tv show에 귀속된다. reviewers는 여러 review를 만들 수 있다. 각 review는 하나의 reviewers에 귀속된다. 이렇게 다대다 관계를 제 3의 table을 만들어 일대다 관계로 풀어내어 해결하는 것이다.
--------- -------- -----------
|tv show| <--(n)-------(1)--> |review| <--(n)-------(1)--> |reviewers|
--------- -------- -----------우리는 TV show를 의미하는 series table, reviewer를 의미하는 reviewers table, review를 의미하는 reviews table을 만들 것이다. review는 series와 reviewers와 Foregin key 관계를 갖게 된다. 왜냐하면 review 한개 에대해서 정확히 하나의 serise, reviewer에게 귀속되기 때문이다.
다음과 같이 만들 수 있다.
CREATE TABLE series (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(100),
released_year YEAR,
genre VARCHAR(100)
);
CREATE TABLE reviews (
id INT PRIMARY KEY AUTO_INCREMENT,
rating DECIMAL(2 , 1 ),
series_id INT,
reviewer_id INT,
FOREIGN KEY (series_id) REFERENCES series (id),
FOREIGN KEY (reviewer_id) REFERENCES reviewers (id)
);
CREATE TABLE reviewers (
id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL
);
table의 관계를 그리면 다음과 같다.
-------- -------- -----------
|series| <--(n)-------(1)--> |review| <--(n)-------(1)--> |reviewers|
-------- -------- -----------table을 만들었으니 이제 data를 추가해보도록 하자.
INSERT INTO series (title, released_year, genre) VALUES
('Archer', 2009, 'Animation'),
('Arrested Development', 2003, 'Comedy'),
("Bob's Burgers", 2011, 'Animation'),
('Bojack Horseman', 2014, 'Animation'),
("Breaking Bad", 2008, 'Drama'),
('Curb Your Enthusiasm', 2000, 'Comedy'),
("Fargo", 2014, 'Drama'),
('Freaks and Geeks', 1999, 'Comedy'),
('General Hospital', 1963, 'Drama'),
('Halt and Catch Fire', 2014, 'Drama'),
('Malcolm In The Middle', 2000, 'Comedy'),
('Pushing Daisies', 2007, 'Comedy'),
('Seinfeld', 1989, 'Comedy'),
('Stranger Things', 2016, 'Drama');
INSERT INTO reviewers (first_name, last_name) VALUES
('Thomas', 'Stoneman'),
('Wyatt', 'Skaggs'),
('Kimbra', 'Masters'),
('Domingo', 'Cortes'),
('Colt', 'Steele'),
('Pinkie', 'Petit'),
('Marlon', 'Crafford');
INSERT INTO reviews(series_id, reviewer_id, rating) VALUES
(1,1,8.0),(1,2,7.5),(1,3,8.5),(1,4,7.7),(1,5,8.9),
(2,1,8.1),(2,4,6.0),(2,3,8.0),(2,6,8.4),(2,5,9.9),
(3,1,7.0),(3,6,7.5),(3,4,8.0),(3,3,7.1),(3,5,8.0),
(4,1,7.5),(4,3,7.8),(4,4,8.3),(4,2,7.6),(4,5,8.5),
(5,1,9.5),(5,3,9.0),(5,4,9.1),(5,2,9.3),(5,5,9.9),
(6,2,6.5),(6,3,7.8),(6,4,8.8),(6,2,8.4),(6,5,9.1),
(7,2,9.1),(7,5,9.7),
(8,4,8.5),(8,2,7.8),(8,6,8.8),(8,5,9.3),
(9,2,5.5),(9,3,6.8),(9,4,5.8),(9,6,4.3),(9,5,4.5),
(10,5,9.9),
(13,3,8.0),(13,4,7.2),
(14,2,8.5),(14,3,8.9),(14,4,8.9);이제 다대다 관계를 다대일 관계로 바꿔놓았으니 join을 통해 원하는 정보들을 얻어보도록 하자. tv show에 있는 review들의 점수를 뽑아내보도록 하자.
SELECT title, rating FROM series JOIN reviews ON series.id = reviews.series_id;
+----------------------+--------+
| title | rating |
+----------------------+--------+
| Archer | 8.0 |
| Archer | 7.5 |
| Archer | 8.5 |
| Archer | 7.7 |
| Archer | 8.9 |
| Arrested Development | 8.1 |
| Arrested Development | 6.0 |
| Arrested Development | 8.0 |
| Arrested Development | 8.4 |
| Arrested Development | 9.9 |
| Bob's Burgers | 7.0 |
| Bob's Burgers | 7.5 |
| Bob's Burgers | 8.0 |
| Bob's Burgers | 7.1 |
| Bob's Burgers | 8.0 |
| Bojack Horseman | 7.5 |
| Bojack Horseman | 7.8 |
| Bojack Horseman | 8.3 |
| Bojack Horseman | 7.6 |
| Bojack Horseman | 8.5 |
| Breaking Bad | 9.5 |
| Breaking Bad | 9.0 |
| Breaking Bad | 9.1 |
| Breaking Bad | 9.3 |
| Breaking Bad | 9.9 |
| Curb Your Enthusiasm | 6.5 |
| Curb Your Enthusiasm | 7.8 |
| Curb Your Enthusiasm | 8.8 |
| Curb Your Enthusiasm | 8.4 |
| Curb Your Enthusiasm | 9.1 |
| Fargo | 9.1 |
| Fargo | 9.7 |
| Freaks and Geeks | 8.5 |
| Freaks and Geeks | 7.8 |
| Freaks and Geeks | 8.8 |
| Freaks and Geeks | 9.3 |
| General Hospital | 5.5 |
| General Hospital | 6.8 |
| General Hospital | 5.8 |
| General Hospital | 4.3 |
| General Hospital | 4.5 |
| Halt and Catch Fire | 9.9 |
| Seinfeld | 8.0 |
| Seinfeld | 7.2 |
| Stranger Things | 8.5 |
| Stranger Things | 8.9 |
| Stranger Things | 8.9 |
+----------------------+--------+점수의 평균을 구해보도록 하자.
SELECT title, ROUND(AVG(rating), 2) AS avg_rating FROM series
JOIN reviews ON series.id = reviews.series_id
GROUP BY title
ORDER BY avg_rating;
+----------------------+------------+
| title | avg_rating |
+----------------------+------------+
| General Hospital | 5.38 |
| Bob's Burgers | 7.52 |
| Seinfeld | 7.60 |
| Bojack Horseman | 7.94 |
| Arrested Development | 8.08 |
| Archer | 8.12 |
| Curb Your Enthusiasm | 8.12 |
| Freaks and Geeks | 8.60 |
| Stranger Things | 8.77 |
| Breaking Bad | 9.36 |
| Fargo | 9.40 |
| Halt and Catch Fire | 9.90 |
+----------------------+------------+다음으로 review와 reivewers table을 join하여 어떤 reviewer가 몇 점 짜리 review들을 만들었는 지 보도록 하는 것이다.
SELECT first_name, last_name FROM reviewers
JOIN reviews ON reviews.reviewer_id = reviewers.id;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Thomas | Stoneman |
| Thomas | Stoneman |
...
| Pinkie | Petit |
| Pinkie | Petit |
| Pinkie | Petit |
| Pinkie | Petit |
+------------+-----------+이번에는 series 중에 review가 없는 series를 찾아내도록 하자. 이는 left join을 사용하면 좋다.
SELECT title, rating FROM series
LEFT JOIN reviews ON reviews.series_id = series.id
WHERE rating IS NULL;
+-----------------------+--------+
| title | rating |
+-----------------------+--------+
| Malcolm In The Middle | NULL |
| Pushing Daisies | NULL |
+-----------------------+--------+Malcolm In The Middle와 Pushing Daisies은 review가 없는 것을 볼 수 있다.
이제 가장 궁극적으로 다대다 맵핑으로 얻고자 했던 series와 reviewers의 join을 해보도록 하자. series마다 어떤 reviewer가 몇 점의 점수를 주었는 지 정리하는 것이다. 우리가 원하는 그림은 다음과 같다.
+-------+--------+----------+
| title | rating | reviewer |
+-------+--------+----------+이를 위해서는 JOIN을 두 번 써야하는 수 밖에 없다. 먼저 series와 reviews를 join하여 title과 rating을 얻어낸다. 다음으로 reviews와 reviewers를 join하여 reviewer를 얻어내면 된다.
차근차근 하나씩하면 비교적 간단하고 쉽다. 먼저 series와 reviews를 join하도록 하자.
SELECT title, rating FROM reviews JOIN series On reviews.series_id = series.id;
+----------------------+--------+
| title | rating |
+----------------------+--------+
| Archer | 8.0 |
| Archer | 7.5 |
...
| Seinfeld | 8.0 |
| Seinfeld | 7.2 |
| Stranger Things | 8.5 |
| Stranger Things | 8.9 |
| Stranger Things | 8.9 |
+----------------------+--------+title과 rating을 얻어내었다. FROM절에 reviews를 썼는데, 이는 reviews랑 series랑 join을 하고 reviews랑 reviewers를 join해야하기 때문이다. 사실 순서는 상관없지만, 다대다 join에서는 이렇게 한다고 생각하면 편하다.
다음으로 reviewer의 이름을 알아내기 위해서 reviews와 reviewers를 join하도록 한다.
SELECT title, rating, first_name, last_name FROM reviews
JOIN series ON reviews.series_id = series.id
JOIN reviewers ON reviews.reviewer_id = reviewers.id;
+----------------------+--------+------------+-----------+
| title | rating | first_name | last_name |
+----------------------+--------+------------+-----------+
| Archer | 8.0 | Thomas | Stoneman |
| Arrested Development | 8.1 | Thomas | Stoneman |
| Bob's Burgers | 7.0 | Thomas | Stoneman |
...
| Freaks and Geeks | 8.8 | Pinkie | Petit |
| General Hospital | 4.3 | Pinkie | Petit |
+----------------------+--------+------------+-----------+어떤 series에 누가 몇 점을 주었는 지 알게되었다.
이렇게 다대다 관계에 있는 두 개의 table 사이에 일대다 관계를 만들어주는 제 3의 table을 만들어주고 제 3의 table을 통해서 이 두 테이블을 간접적으로 join하도록 하는 것이다.
