In [1]:
# 특정 카테고리에 포함된 영화들의 렌탈 횟수
# "comedy", "sports", "family" 카테고리에 포함되는 영화들의 렌탈 횟수
# rental, inventory, film, film_category, category table 이용
import pandas as pd
import MySQLdb
In [2]:
db = MySQLdb.connect(
"db.fastcamp.us",
"root",
"dkstncks",
"sakila",
charset="utf8",
)
In [3]:
SQL_QUERY = """
SELECT
c.category_id category_id,
c.name category_name,
COUNT(*) rentals_per_category
FROM
rental r
JOIN inventory i ON r.inventory_id = i.inventory_id
JOIN film_category fc ON fc.film_id = i.film_id
JOIN category c ON fc.category_id = c.category_id
WHERE
c.name IN ("Family", "Comedy", "Sports")
GROUP BY
category_id
ORDER BY rentals_per_category DESC
;
"""
pd.read_sql(SQL_QUERY, db)
Out[3]:
In [4]:
# Store 1의 등급별 매출 중 "G", "PG-13"의 렌탈매출 구하기
# table :
# 1. film : rental_rate, film_id
# 2. inventory : film_id, inventory_id
# 3. rental : inventory_id
In [5]:
SQL_QUERY = """
SELECT
i.store_id store_id,
f.rating rating,
SUM(p.amount) total_revenue
FROM
payment p
JOIN rental r ON p.rental_id = r.rental_id
JOIN inventory i ON r.inventory_id = i.inventory_id
JOIN film f ON i.film_id = f.film_id
GROUP BY
store_id,
f.rating
HAVING
store_id = 1
AND rating IN ("G", "PG-13")
;
"""
pd.read_sql(SQL_QUERY, db)
Out[5]:
In [6]:
# 배우별 매출
# 1. 영화별 매출을 구하고
## 1-1. payment, rental, inventory, film table 이용
# 2. 이후 배우별 매출 구하기
In [11]:
# 영화별 매출
SQL_QUERY = """
SELECT
f.film_id film_id,
COUNT(p.amount) revenue
FROM
payment p
JOIN rental r ON p.rental_id = r.rental_id
JOIN inventory i ON r.inventory_id = i.inventory_id
JOIN film f ON i.film_id = f.film_id
GROUP BY
film_id
;
"""
pd.read_sql(SQL_QUERY, db)
# 배우별 매출
RESULT_SQL_QUERY = """
SELECT
a.actor_id actor_id,
a.last_name last_name,
a.first_name first_name,
SUM(sq.revenue) revenue_per_actor
FROM
({SQL_QUERY}) AS sq
JOIN film_actor fa ON sq.film_id = fa.film_id
JOIN actor a ON fa.actor_id = a.actor_id
GROUP BY
actor_id
;
""".format(
SQL_QUERY=SQL_QUERY.replace(";", ""),
)
pd.read_sql(RESULT_SQL_QUERY, db)
Out[11]:
In [12]:
print(RESULT_SQL_QUERY)
In [ ]: