<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
Step7:  4T_Pandas로 배우는 SQL 시작하기 (4) - HAVING, SUB QUERY
SQL => 연산의 결과로 나온 데이터를 다시 Filtering ( HAVING )
SUB QUERY + TEMPORARY TABLE ( 임시 테이블 )
실습)
"5월 달에" / "지금까지" 렌탈 횟수가 30회 이상인 유저
유저이름과 유저 이메일 => 마케팅
customer, rental
Pandas
Step9:  HAVING
Step10:  실습)
제일 많이 매출을 발생시킨 "영화 제목"
payment, film, inventory, rental
영화별 ( GROUP BY )
매출 ( SUM )을 뽑아서
정렬하자 ( ORDER BY )
데이터가 어디에 들어있는가?
Step13:  film_df => film_id, title
inventory_df => inventory_id, film_id
rental_df => rental_id, inventory_id
payment_df => rental_id, amount
Step15:  실습 추가)
결제 누적액이 많은 유저 상위 10명 ( customer, payment )
Step17:  영화를 흥행시킨 ( 매출이 많이 발생한 ) 배우 상위 10명
rental, payment ...
actor_df => actor_id, first_name, last_name
film_actor_df => actor_id, film_id
inventory_df => inventory_id, film_id
rental_df => rental_id, inventory_id
payment_df => rental_id, amount
<ASSISTANT_TASK:>
Python Code:
import pymysql
import curl
db = pymysql.connect(
    "db.fastcamp.us",
    "root",
    "dkstncks",
    "sakila",
    charset = "utf8",
)
customer_df = pd.read_sql("SELECT * FROM customer;", db)
rental_df = pd.read_sql("SELECT * FROM rental;", db)
df = rental_df.merge(customer_df, on="customer_id")
df.head(1)
rental_per_customer_groups = df.groupby("customer_id")
rental_per_customer_groups.get_group(1)
rental_per_customer_groups.agg({"customer_id": np.size})["customer_id"]
# rental_per_customer_groups.agg({"customer_id": np.size})["customer_id"] > 30
is_many_rentals_user = rental_per_customer_groups.size() > 30
is_many_rentals_user
#여기서는 어떤 문제로 되지 않는다. 다음에 알려주겠다.
# SQL로 하겠다
# 1. Sub Query - Query 안에 Query 가 들어있다.
SQL_QUERY = 
    SELECT
        r.customer_id,
        COUNT(*) rentals
    FROM rental r
        JOIN customer c
        ON r.customer_id = c.customer_id
    GROUP BY r.customer_id
    WHERE rentals > 30
pd.read_sql(SQL_QUERY, db)
#순서가 FROM -> group by -> where(=>rental이 없다.) -> select에서 카운트가 마지막이라서
# 1. Sub Query = Query 안에 Query가 들어있다.
SQL_QUERY = 
    SELECT rentals_per_customer.customer_id "Customer ID", rentals_per_customer.rentals
    FROM (
        SELECT
            r.customer_id,
            COUNT(*) rentals
        FROM rental r
            JOIN customer c
            ON r.customer_id = c.customer_id
        GROUP BY r.customer_id
    ) AS rentals_per_customer
    WHERE rentals > 30
    ;
pd.read_sql(SQL_QUERY, db)
# sub query스럽지 않아서 나눠 쓰면 보기가 좋다.
RENTALS_PER_CUSTOMER_SQL_QUERY = 
    SELECT
        r.customer_id,
        COUNT(*) rentals
    FROM rental r
        JOIN customer c
        ON r.customer_id = c.customer_id
    GROUP BY r.customer_id
    ;
SQL_QUERY = 
    SELECT *
    FROM (
        {rentals_per_customer_sql_query}
    ) AS rentals_per_customer
    WHERE rentals > 30
    ;
.format(
    rentals_per_customer_sql_query=RENTALS_PER_CUSTOMER_SQL_QUERY.replace(";", "")
)
pd.read_sql(SQL_QUERY, db)
print(SQL_QUERY)
# 30번 이상인 애들의 => 이름, 이메일 
RESULT_SQL_QUERY = 
    SELECT customer.last_name, customer.first_name, customer.email
    FROM ({SQL_QUERY}) many_rental_user 
        JOIN customer ON many_rental_user.customer_id = customer.customer_id
    ;
.format(
    SQL_QUERY=SQL_QUERY.replace(";", "")
)
pd.read_sql(RESULT_SQL_QUERY, db)
# Temporary Table ( 임시 테이블 )
SQL_QUERY = 
    DROP TEMPORARY TABLE IF EXISTS rentals_per_customer;
    CREATE TEMPORARY TABLE rentals_per_customer
    
    SELECT
        r.customer_id,
        COUNT(*) rentals
    FROM rental r
        JOIN customer c
        ON r.customer_id = c.customer_id
    GROUP BY r.customer_id
    ;
# pd.read_sql() => 이걸로 실행시키면 오류가 난다. 그래서 cursor로 실행
cursor = db.cursor()
cursor.execute(SQL_QUERY)
SQL_QUERY = 
    SELECT rpc.customer_id, rpc.rentals
    FROM rentals_per_customer rpc
    WHERE rentals > 30
    ;
pd.read_sql(SQL_QUERY, db)
Explanation: 4T_Pandas로 배우는 SQL 시작하기 (4) - HAVING, SUB QUERY
SQL => 연산의 결과로 나온 데이터를 다시 Filtering ( HAVING )
SUB QUERY + TEMPORARY TABLE ( 임시 테이블 )
실습)
"5월 달에" / "지금까지" 렌탈 횟수가 30회 이상인 유저
유저이름과 유저 이메일 => 마케팅
customer, rental
Pandas
End of explanation
SQL_QUERY = 
    SELECT r.customer_id, COUNT(*) rentals
    FROM rental r
        JOIN customer c
        ON r.customer_id = c.customer_id
    GROUP BY r.customer_id
    # WHERE rentals > 30  #연산에 대한 결과로 Filtering을 할 수 없다.
    # 연산에 대한 결과로 Filtering을 할 수 있는 기능
    HAVING rentals > 30
pd.read_sql(SQL_QUERY, db)
Explanation: HAVING
End of explanation
db = pymysql.connect(
    "db.fastcamp.us",
    "root",
    "dkstncks",
    "sakila",
    charset="utf8"
)
film_df = pd.read_sql("SELECT * FROM film;", db)
rental_df = pd.read_sql("SELECT * FROM rental;", db)
payment_df = pd.read_sql("SELECT * FROM payment;", db)
inventory_df = pd.read_sql("SELECT * FROM inventory;", db)
Explanation: 실습)
제일 많이 매출을 발생시킨 "영화 제목"
payment, film, inventory, rental
영화별 ( GROUP BY )
매출 ( SUM )을 뽑아서
정렬하자 ( ORDER BY )
데이터가 어디에 들어있는가?
End of explanation
SQL_QUERY = 
    SELECT f.film_id, f.title, SUM(p.amount) "revenue"
    FROM 
        film f,
        rental r,
        payment p,
        inventory i
    WHERE
        f.film_id = i.film_id
        AND i.inventory_id = r.inventory_id
        AND r.rental_id = p.rental_id
    GROUP BY f.film_id
    ORDER BY revenue DESC
    ;
pd.read_sql(SQL_QUERY, db)
SQL_QUERY = 
    SELECT f.film_id, f.title, SUM(p.amount) "revenue"
    FROM payment p
        JOIN rental r ON p.rental_id = r.rental_id
        JOIN inventory i ON i.inventory_id = r.inventory_id
        JOIN film f ON f.film_id = i.film_id
    GROUP BY f.film_id
    ORDER BY revenue DESC
    ;
pd.read_sql(SQL_QUERY, db)
Explanation: film_df => film_id, title
inventory_df => inventory_id, film_id
rental_df => rental_id, inventory_id
payment_df => rental_id, amount
End of explanation
customer_df = pd.read_sql("SELECT * FROM customer;", db)
customer_df.head(1)
payment_df.head(1)
SQL_QUERY = 
    SELECT c.first_name, c.last_name, SUM(p.amount) "revenue"
    FROM customer c
        JOIN payment p
        ON c.customer_id = p.customer_id
    GROUP BY c.customer_id
    ORDER BY revenue DESC
    ;
pd.read_sql(SQL_QUERY, db)
Explanation: 실습 추가)
결제 누적액이 많은 유저 상위 10명 ( customer, payment )
End of explanation
SQL_QUERY = 
    SELECT a.first_name, a.last_name, SUM(p.amount) "revenue"
    FROM
        actor a,
        film_actor fa,
        inventory i,
        rental r,
        payment p
    WHERE
        a.actor_id = fa.actor_id
        AND fa.film_id = i.film_id
        AND i.inventory_id = r.inventory_id
        AND r.rental_id = p.rental_id
    GROUP BY a.actor_id
    ORDER BY revenue DESC
    ;
pd.read_sql(SQL_QUERY, db)
Explanation: 영화를 흥행시킨 ( 매출이 많이 발생한 ) 배우 상위 10명
rental, payment ...
actor_df => actor_id, first_name, last_name
film_actor_df => actor_id, film_id
inventory_df => inventory_id, film_id
rental_df => rental_id, inventory_id
payment_df => rental_id, amount
End of explanation
<END_TASK>