잔재미코딩

데이터베이스 예제코드 (MySQL+AWS) - . JOIN 활용 실제 예제3

11. JOIN 활용 실제 예제3

예제3: 특정 카테고리에 포함된 영화들의 렌탈 횟수 구해보기

  • "Comedy", "Sports", "Family" 카테고리에 포함되는 영화들의 렌탈 횟수
    • rental, inventory, film, film_category, category 테이블 활용하기

일단 각 테이블 현황을 파악하자. - rental 테이블 (렌탈 기록은 다 모아놓았군)

In [131]:
rental_df = pd.read_sql("SELECT * FROM rental;", db)
In [132]:
rental_df.head()
Out[132]:
rental_id rental_date inventory_id customer_id return_date staff_id last_update
0 1 2005-05-24 22:53:30 367 130 2005-05-26 22:04:30 1 2006-02-15 21:30:53
1 2 2005-05-24 22:54:33 1525 459 2005-05-28 19:40:33 1 2006-02-15 21:30:53
2 3 2005-05-24 23:03:39 1711 408 2005-06-01 22:12:39 1 2006-02-15 21:30:53
3 4 2005-05-24 23:04:41 2452 333 2005-06-03 01:43:41 2 2006-02-15 21:30:53
4 5 2005-05-24 23:05:21 2079 222 2005-06-02 04:33:21 1 2006-02-15 21:30:53

일단 각 테이블 현황을 파악하자. - inventory 테이블 (각 영화별 실제 물건 현황은 다 모아놓았군)

In [133]:
inventory_df = pd.read_sql("SELECT * FROM inventory;", db)
In [134]:
inventory_df.head()
Out[134]:
inventory_id film_id store_id last_update
0 1 1 1 2006-02-15 05:09:17
1 2 1 1 2006-02-15 05:09:17
2 3 1 1 2006-02-15 05:09:17
3 4 1 1 2006-02-15 05:09:17
4 5 1 2 2006-02-15 05:09:17
  • 각 영화별 물건도 알 수는 있겠군
In [135]:
inventory_df_groupby = pd.read_sql("SELECT film_id, COUNT(*) FROM inventory GROUP BY film_id", db)
In [136]:
inventory_df_groupby.head()
Out[136]:
film_id COUNT(*)
0 1 8
1 2 3
2 3 4
3 4 7
4 5 3

일단 각 테이블 현황을 파악하자. - film 테이블 (각 영화별 상세 정보를 다 모아놓았군)

In [137]:
film_df = pd.read_sql("SELECT * FROM film;", db)
In [138]:
film_df.head()
Out[138]:
film_id title description release_year language_id original_language_id rental_duration rental_rate length replacement_cost rating special_features last_update
0 1 ACADEMY DINOSAUR A Epic Drama of a Feminist And a Mad Scientist... 2006 1 None 6 0.99 86 20.99 PG Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42
1 2 ACE GOLDFINGER A Astounding Epistle of a Database Administrat... 2006 1 None 3 4.99 48 12.99 G Trailers,Deleted Scenes 2006-02-15 05:03:42
2 3 ADAPTATION HOLES A Astounding Reflection of a Lumberjack And a ... 2006 1 None 7 2.99 50 18.99 NC-17 Trailers,Deleted Scenes 2006-02-15 05:03:42
3 4 AFFAIR PREJUDICE A Fanciful Documentary of a Frisbee And a Lumb... 2006 1 None 5 2.99 117 26.99 G Commentaries,Behind the Scenes 2006-02-15 05:03:42
4 5 AFRICAN EGG A Fast-Paced Documentary of a Pastry Chef And ... 2006 1 None 6 2.99 130 22.99 G Deleted Scenes 2006-02-15 05:03:42

일단 각 테이블 현황을 파악하자. - film_category 테이블 (각 영화별 카테고리 ID는 알 수 있는데, 카테고리 이름은 안들어 있음)

In [139]:
film_category_df = pd.read_sql("SELECT * FROM film_category;", db)
In [140]:
film_category_df.head()
Out[140]:
film_id category_id last_update
0 1 6 2006-02-15 05:07:09
1 2 11 2006-02-15 05:07:09
2 3 6 2006-02-15 05:07:09
3 4 11 2006-02-15 05:07:09
4 5 8 2006-02-15 05:07:09

일단 각 테이블 현황을 파악하자. - category 테이블 (카테고리 ID별 이름은 이 테이블에서야 알 수 있군)

In [141]:
category_df = pd.read_sql("SELECT * FROM category;", db)
In [142]:
category_df.head()
Out[142]:
category_id name last_update
0 1 Action 2006-02-15 04:46:27
1 2 Animation 2006-02-15 04:46:27
2 3 Children 2006-02-15 04:46:27
3 4 Classics 2006-02-15 04:46:27
4 5 Comedy 2006-02-15 04:46:27

핵심 정보는 결국 렌탈 정보인 rental 테이블일 수 밖에 없음

  • rental 테이블에 inventory_id 를 가지고 카테고리를 알 수 있는 방법은 무엇일까?
  1. rental 테이블에는 물품 정보(inventory_id)밖에 없음
  2. inventory 테이블은 inventory_id, film_id 가 있고,
  3. film_category 테이블은 film_id, category_id 가 있고
  4. category 테이블은 category_id, name(카테고리 이름) 가 있음
  • 생각해본 전략: rental 테이블과 category 테이블을 엮은 후, category.name 카테고리가 "Comedy", "Sports", "Family" 인것으로 WHERE를 하고, 이를 다시 GROUP BY category.name 로 하면!
    • 코드로 바꾸려면 작은 단위로 단계별로 만들어가자
  1. rental 테이블에는 물품 정보(inventory_id)밖에 없음
  2. inventory 테이블은 inventory_id, film_id 가 있고, (JOIN)
  3. film_category 테이블은 film_id, category_id 가 있고 (JOIN)
  4. category 테이블은 category_id, name(카테고리 이름) 가 있음 (JOIN)
  • 두 테이블 연결
    1. rental 테이블에는 물품 정보(inventory_id)밖에 없음
    2. inventory 테이블은 inventory_id, film_id 가 있고,
In [143]:
SQL_QUERY = """
    SELECT inventory.film_id, rental.rental_id
    FROM rental
    JOIN inventory
    ON inventory.inventory_id = rental.inventory_id
"""
  • JOIN 이 INNER JOIN 이니 JOIN 으로 쓰기로 함
In [144]:
category_rental_df = pd.read_sql(SQL_QUERY, db)
In [145]:
category_rental_df.head()
Out[145]:
film_id rental_id
0 1 4863
1 1 11433
2 1 14714
3 1 972
4 1 2117
  • 네 테이블 연결
    1. rental 테이블에는 물품 정보(inventory_id)밖에 없음
    2. inventory 테이블은 inventory_id, film_id 가 있고,
    3. film_category 테이블은 film_id, category_id 가 있고
    4. category 테이블은 category_id, name(카테고리 이름) 가 있음
In [162]:
SQL_QUERY = """
    SELECT category.name, inventory.film_id, rental.rental_id
    FROM rental
    JOIN inventory ON inventory.inventory_id = rental.inventory_id
    JOIN film_category ON film_category.film_id = inventory.film_id
    JOIN category ON category.category_id = film_category.category_id
    WHERE category.name = 'Comedy'
"""
In [163]:
category_rental_df = pd.read_sql(SQL_QUERY, db)
In [164]:
category_rental_df.head()
Out[164]:
name film_id rental_id
0 Comedy 7 11084
1 Comedy 7 14942
2 Comedy 7 3159
3 Comedy 7 4937
4 Comedy 7 10205
  • 네 테이블 연결하고, 카테고리가 "Comedy", "Sports", "Family" 인것으로 WHERE를 하기
    1. rental 테이블에는 물품 정보(inventory_id)밖에 없음
    2. inventory 테이블은 inventory_id, film_id 가 있고,
    3. film_category 테이블은 film_id, category_id 가 있고
    4. category 테이블은 category_id, name(카테고리 이름) 가 있음

직접 작성해보기

In [219]:
SQL_QUERY = """

"""
In [220]:
category_rental_df = pd.read_sql(SQL_QUERY, db)
In [221]:
category_rental_df.head()
Out[221]:
name category_id film_id rental_id
0 Comedy 5 7 11084
1 Comedy 5 7 14942
2 Comedy 5 7 3159
3 Comedy 5 7 4937
4 Comedy 5 7 10205
  • rental 테이블과 category 테이블을 엮은 후, category.name 카테고리가 "Comedy", "Sports", "Family" 인것으로 WHERE를 하고, 이를 다시 GROUP BY category.name 로 한 후에, 로우수를 COUNT(*) 로 하면
In [165]:
SQL_QUERY = """
    SELECT COUNT(*)
    FROM rental
    JOIN inventory ON inventory.inventory_id = rental.inventory_id
    JOIN film_category ON film_category.film_id = inventory.film_id
    JOIN category ON category.category_id = film_category.category_id
    WHERE category.name = 'Comedy'
"""
In [166]:
category_rental_df = pd.read_sql(SQL_QUERY, db)
In [167]:
category_rental_df.head()
Out[167]:
COUNT(*)
0 941