CS50 Week7: Problem Set, Movies

하버드 CS50 강의 7주차 Problem Set 과제 Movies 의 풀이를 다룹니다.
IMDb 데이터베이스를 기반으로 한 간단한 SQL 연습 문제 입니다.
JOIN을 쓰는 방법과 IN 서브쿼리를 쓰는 방법 등 다양한 방법으로 풀고 비교해보았습니다.

Schema

테이블 구성은 아래와 같다.

CREATE TABLE movies (
                    id INTEGER,
                    title TEXT NOT NULL,
                    year NUMERIC,
                    PRIMARY KEY(id)
                );
CREATE TABLE stars (
                movie_id INTEGER NOT NULL,
                person_id INTEGER NOT NULL,
                FOREIGN KEY(movie_id) REFERENCES movies(id),
                FOREIGN KEY(person_id) REFERENCES people(id)
            );
CREATE TABLE directors (
                movie_id INTEGER NOT NULL,
                person_id INTEGER NOT NULL,
                FOREIGN KEY(movie_id) REFERENCES movies(id),
                FOREIGN KEY(person_id) REFERENCES people(id)
            );
CREATE TABLE ratings (
                movie_id INTEGER NOT NULL,
                rating REAL NOT NULL,
                votes INTEGER NOT NULL,
                FOREIGN KEY(movie_id) REFERENCES movies(id)
            );

Code

1. 2008년에 개봉한 영화 리스트

SELECT title FROM movies 
WHERE year = 2008;

2. 엠마 스톤의 생년

SELECT birth FROM people 
WHERE name = 'Emma Stone';

3. 2018년 이상 개봉 영화 리스트, 알파벳 순

SELECT title FROM movies 
WHERE year >= 2018 
ORDER BY title;

4. IMdb 평점 10.0 작품 목록, 알파벳 순

SELECT title FROM movies 
JOIN ratings ON movies.id = ratings.movie_id 
WHERE rating = 10.0;

5. 해리포터 시리즈의 작품명과 개봉 년도, 연도 순

• 작품명이 '해리 포터' 로 시작하면 시리즈 작품으로 간주한다.

SELECT title, year FROM movies 
WHERE title LIKE 'Harry Potter%' 
ORDER BY year;

6. 2012년 개봉작의 평균 평점

SELECT AVG(rating) FROM movies
JOIN ratings ON movies.id = ratings.movie_id
WHERE year = 2012;

7. 2010년 개봉작의 작품명과 평점, 평점 높은 순, 평점이 같을 경우 알파벳 순

SELECT title, rating FROM movies
JOIN ratings ON movies.id = ratings.movie_id
WHERE year = 2010
ORDER BY rating DESC, title;

8. 토이 스토리 출연자들의 목록

저번 과제에서 배운 JOIN과 IN 서브쿼리의 차이점을 염두하며 여러 방법으로 풀이해봤다.

실행 속도를 측정해서 어떤 방법이 더 빠른지 비교해보았다.

• Join from people table

  SELECT name FROM people
  JOIN stars ON people.id = stars.person_id
  JOIN movies ON stars.movie_id = movies.id
  WHERE title = 'Toy Story';

Runtime: real 5.916 user 1.406250 sys 4.515625

• Join from movies table

  SELECT name FROM movies
  JOIN stars ON movies.id = stars.movie_id
  JOIN people ON stars.person_id = people.id
  WHERE title = 'Toy Story';

Runtime: real 0.319 user 0.265625 sys 0.046875

• IN subquery

  SELECT name FROM people
  WHERE id IN (
    SELECT person_id FROM stars
    WHERE movie_id IN (
        SELECT id FROM movies
        WHERE title = 'Toy Story'));

Runtime: real 0.226 user 0.218750 sys 0.015625

측정 결과 및 해석

측정 결과 IN을 사용한 3번째 쿼리가 처리가 가장 빨랐다.

JOIN을 쓴 1번과 2번 풀이의 속도 차이는 어떤 테이블을 기준으로 JOIN을 했는지가 영향을 미친 것으로 보인다.

people 테이블은 movies 보다 레코드의 수가 많고 (people: 1252264, movies: 407431),
stars 테이블은 중복값이 많은 특성이 있다.

1번 쿼리에서는 사이즈가 큰 people 테이블이 stars와 병합된다. 중복값이 많기에 테이블 크기가 더욱 커진다.

2번 쿼리에서는 상대적으로 사이즈가 작은 movies 테이블을 바탕으로 제목 조건을 통해 필요 없는 데이터를 줄일 수 있다.
좀 더 작은 사이즈로 효율적인 JOIN이 가능했을 것이다.

그렇다면 2번이 3번 방식보다 느린 이유는 뭘까?

이유를 알기 위해 EXPLAIN 명령어로 쿼리가 내부적으로 어떻게 실행되는지 결과를 뽑아보았다.

무슨 소린 지 하나도 몰라서 ChatGPT에 물어봤다.

GPT의 도움을 받아 내 나름대로 이해해 본 2번 코드의 실행 과정은 다음과 같다.

1. 먼저 stars 테이블의 movie_id를 하나씩 순회한다.
2. movies 테이블에서 id에 따른 영화 제목을 비교한다.
3. 제목이 토이스토리가 아니라면 스킵한다.
4. 제목이 토이스토리라면 person_id를 가져온다
5. person_id를 바탕으로 people 테이블에서 name을 가져온다.

stars 테이블의 크기가 작았다면, 이 방식이 더 효율적일 수도 있겠다.

예시로 stars 테이블의 크기가 50이라면, movies 테이블의 크기에 상관없이 stars 테이블 내부의 movie_id만 순회하면 된다.
영화 제목이 토이스토리가 맞는지 50번만 비교하면 되었을테니까..

문제는 stars 테이블의 크기가 1432233으로 movies의 3배가 넘는다는 점이다.
따라서 크기가 더 작은 movies 테이블을 먼저 돌며 제목을 비교한 3번 쿼리가 가장 빨랐던 것으로 보인다.

9. 2004년 개봉 영화 출연자들의 이름, 생년 순 정렬

SELECT name FROM people
where id IN (
    SELECT person_id FROM stars
    WHERE movie_id IN (
        SELECT id FROM movies
        WHERE year = 2004))
ORDER BY birth;

참고로 아래의 쿼리는 틀린 답안이다.

동명이인을 같은 인물로 취급하기 떄문에 실제 레코드 수가 차이가 난다.

SELECT DISTINCT people.name FROM movies
JOIN stars ON movies.id = stars.movie_id
JOIN people ON stars.person_id = people.id
WHERE movies.year = 2004
ORDER BY people.birth;

10. 9.0 이상의 평점을 가진 영화가 있는 감독의 목록

SELECT name FROM people
WHERE id IN (
    SELECT person_id FROM directors
    WHERE movie_id IN(
        SELECT movie_id FROM ratings
        WHERE rating >= 9.0));

11. 채드윅 보스맨 출연작, 평점 높은 순 5개

SELECT title FROM movies
JOIN ratings ON movies.id = ratings.movie_id
WHERE movie.id IN (
    SELECT movie_id FROM stars
    WHERE person_id IN (
        SELECT id FROM people
        WHERE name = 'Chadwick Boseman'))
ORDER BY rating DESC
LIMIT 5;

Run Time: real 0.445 user 0.359375 sys 0.093750

• JOIN만 쓸 경우

  SELECT title FROM movies
  JOIN stars ON movies.id = stars.movie_id
  JOIN people ON stars.person_id = people.id
  JOIN ratings ON movies.id = ratings.movie_id
  WHERE people.name = 'Chadwick Boseman'
  ORDER BY rating DESC
  LIMIT 5;

Run Time: real 6.204 user 1.468750 sys 4.734375

12. 브래들리 쿠퍼와 재니퍼 로렌스가 같이 출연한 영화 목록

• AND 로 두 서브쿼리 연결

  SELECT title FROM movies
  WHERE id IN (
    SELECT movie_id FROM stars
    WHERE person_id IN (
        SELECT id FROM people
        WHERE name = 'Bradley Cooper'))
  AND id IN (
    SELECT movie_id FROM stars
    WHERE person_id IN (
        SELECT id FROM people
        WHERE name = 'Jennifer Lawrence'));

  Run Time: real 0.653 user 0.578125 sys 0.078125

  • 그룹화

    SELECT title FROM movies
    WHERE id IN (
    SELECT movie_id FROM stars
    WHERE person_id IN (
        SELECT id FROM people
        WHERE name IN ('Bradley Cooper', 'Jennifer Lawrence'))
    GROUP BY movie_id
    HAVING COUNT(*) = 2);

    Run Time: real 0.370 user 0.328125 sys 0.046875

그룹화가 더 빨랐다.
두 번 할 작업을 한번에 해서 그런 듯 하다.

13. 케빈 베이컨과 같은 영화에 출연한 인물의 목록

베이컨 지수 가 1인 인물을 찾는 듯 하다.
당연하게도

1. 케빈 베이컨 본인은 포함되면 안되며
2. 할로우맨에 나온 58년생 케빈 베이컨 씨를 찾아야 한다.

SELECT name FROM people
WHERE id IN (
    SELECT person_id FROM stars
    WHERE movie_id IN (
        SELECT movie_id FROM stars
        WHERE person_id IN(
            SELECT id FROM people
            WHERE name = 'Kevin Bacon'
            AND birth = 1958)))
AND (
    name != 'Kevin Bacon'
    And birth != 1958);