In [1]:
# GROUP BY with SUM
# 월별 매출
In [2]:
import numpy as np
import pandas as pd
import MySQLdb
db = MySQLdb.connect(
"db.fastcamp.us", # DATABASE_HOST
"root", # DATABASE_USERNAME
"dkstncks", # DATABASE_PASSWORD
"sakila", # DATABASE_NAME
charset='utf8',
)
cursor = db.cursor()
In [3]:
SQL_QUERY = """
SELECT
LEFT(payment.payment_date, 7) AS "Month",
SUM(payment.amount) AS "Monthly Revenue"
FROM
payment
GROUP BY
1
"""
pd.read_sql(SQL_QUERY, db)
Out[3]:
In [4]:
SQL_QUERY = """
SELECT * FROM payment;
"""
payment_df = pd.read_sql(SQL_QUERY, db)
payment_df.head()
Out[4]:
In [5]:
payment_df["Month"] = payment_df["payment_date"].apply(lambda x: str(x)[:7])
In [6]:
payment_df.head()
Out[6]:
In [7]:
payment_df.groupby("Month").agg({"payment_id": np.size, "amount": [np.sum, np.mean]})
Out[7]: