JOIN 활용 실제 예제3

이해하기 쉽고, 장황하지 않은 자료를 기반으로 강의를 진행합니다.

잔재미코딩 소식 공유

좀더 제약없이, IT 컨텐츠를 공유하고자, 자체 온라인 강의 사이트와 유투브 채널을 오픈하였습니다
응원해주시면, 곧 좋은 컨텐츠를 만들어서 공유하겠습니다

● 직운영 온라인 강의 서비스 오픈

● 잔재미코딩 유투브 오픈 [구독해보기]

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)

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

체계적으로 전문가 레벨까지 익힐 수 있도록 온라인 강의 로드맵을 제공합니다

데이터 분석/과학 로드맵

데이터 분석가와 데이터 과학자 직군의 기본기를 차근차근 쌓을 수 있도록 꾸몄습니다

자세히 알아보기 모든 강좌 보기

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 테이블 (각 영화별 상세 정보를 다 모아놓았군)¶

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

가장 빠르게 풀스택 개발자가 될 수 있도록, 최적화된 로드맵을 제공합니다

웹/앱서비스 풀스택 로드맵

자세히 알아보기 모든 강좌 보기

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

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

체계적으로 전문가 레벨까지 익힐 수 있도록 온라인 강의 로드맵을 제공합니다

데이터 분석/과학 로드맵

데이터 분석가와 데이터 과학자 직군의 기본기를 차근차근 쌓을 수 있도록 꾸몄습니다

자세히 알아보기 모든 강좌 보기

일단 각 테이블 현황을 파악하자. - 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 를 가지고 카테고리를 알 수 있는 방법은 무엇일까?

rental 테이블에는 물품 정보(inventory_id)밖에 없음
inventory 테이블은 inventory_id, film_id 가 있고,
film_category 테이블은 film_id, category_id 가 있고
category 테이블은 category_id, name(카테고리 이름) 가 있음

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

가장 빠르게 풀스택 개발자가 될 수 있도록, 최적화된 로드맵을 제공합니다

웹/앱서비스 풀스택 로드맵

자세히 알아보기 모든 강좌 보기

생각해본 전략: rental 테이블과 category 테이블을 엮은 후, category.name 카테고리가 "Comedy", "Sports", "Family" 인것으로 WHERE를 하고, 이를 다시 GROUP BY category.name 로 하면!
- 코드로 바꾸려면 작은 단위로 단계별로 만들어가자

rental 테이블에는 물품 정보(inventory_id)밖에 없음
inventory 테이블은 inventory_id, film_id 가 있고, (JOIN)
film_category 테이블은 film_id, category_id 가 있고 (JOIN)
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 으로 쓰기로 함

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

체계적으로 전문가 레벨까지 익힐 수 있도록 온라인 강의 로드맵을 제공합니다

데이터 분석/과학 로드맵

데이터 분석가와 데이터 과학자 직군의 기본기를 차근차근 쌓을 수 있도록 꾸몄습니다

자세히 알아보기 모든 강좌 보기

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)

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

가장 빠르게 풀스택 개발자가 될 수 있도록, 최적화된 로드맵을 제공합니다

웹/앱서비스 풀스택 로드맵

자세히 알아보기 모든 강좌 보기

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)

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

체계적으로 전문가 레벨까지 익힐 수 있도록 온라인 강의 로드맵을 제공합니다

데이터 분석/과학 로드맵

데이터 분석가와 데이터 과학자 직군의 기본기를 차근차근 쌓을 수 있도록 꾸몄습니다

자세히 알아보기 모든 강좌 보기

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