<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 ipython-sql を使うサンプル
環境変数から接続情報を取得します。
Step1:  拡張を読み込み、データベースに接続します。
Step2:  SQL を実行してその結果を確認
SQL を実行します。
Step3:  実行結果を pandas のデータフレームに変換します。
SqlMagic.autopandas パラメータを True に設定しておくと、結果セットを pandas の DataFrame として処理してくれます。
Step4:  少し複雑な SQL を実行するため、店舗ごとの所在地、従業員数、顧客数を集計します。
Step5:  結果を集計してグラフ描画
描画用にモジュールを読み込みます。
Step6:  rental テーブルに商品の貸し出し情報が入っており、貸出日が rental_date 、返却日が return_date に記録されています。商品のIDは inventory_id で、顧客IDは customer_id です。これらを使い、未返却DVDのある顧客数の分布を計算して棒グラフを描画してみます。
SQL でビデオレンタルに関するデータを取得し、データフレームとして変数に保存します。
Step7:  属性ごとの項目数を確認します。
Step8:  16,044件の貸し出し情報があり、商品数は4,580件、顧客数は599人であることが分かります。また、返却日は15,861件ありますので、差し引きすると183件が未返却であると言えます。
返却日が未登録のレコードを顧客IDで集計して顧客ごとの未返却DVDの件数を計算し、さらに件数を集計することで、未返却DVDのある顧客数の分布を計算します。
Step9:  この分布を棒グラフで表現してみます。（これくらいなら表の数値を見れば理解できる内容ではありますが）
Step10:  変数置換を利用したクエリの実行
上記のグラフを見ると、３件未返却である顧客は特殊と言えるかもしれませんので、具体的に個人を特定してみます。
Step11:  顧客IDが75の人が対象であることが分かりますので、 customer テーブルで具体的に確認します。このとき、SQL に数値をハードコードするのではなく、 ipython-sql の変数置換を利用できます。
変数置換は、Notebook で普通の変数を定義して、SQL 文で "
Step12:  変数はリストでも受け付けることができますので、特定のIDをハードコードする必要もありません。SQL の WHERE 句では IN を使います。
<ASSISTANT_TASK:>
Python Code:
import os
pgconfig = {
    'host': os.environ['PGHOST'],
    'port': os.environ['PGPORT'],
    'database': os.environ['PGDATABASE'],
    'user': os.environ['PGUSER'],
    'password': os.environ['PGPASSWORD'],
}
Explanation: ipython-sql を使うサンプル
環境変数から接続情報を取得します。
End of explanation
%load_ext sql
dsl = 'postgres://{user}:{password}@{host}:{port}/{database}'.format(**pgconfig)
%sql $dsl
Explanation: 拡張を読み込み、データベースに接続します。
End of explanation
%%sql
select * from actor order by actor_id limit 10
Explanation: SQL を実行してその結果を確認
SQL を実行します。
End of explanation
%config SqlMagic.autopandas = True
df = %sql select * from actor order by actor_id
df.describe()
Explanation: 実行結果を pandas のデータフレームに変換します。
SqlMagic.autopandas パラメータを True に設定しておくと、結果セットを pandas の DataFrame として処理してくれます。
End of explanation
%%sql
WITH store_inv AS (
    SELECT store_id, COUNT(*) AS inventory_cnt, COUNT(DISTINCT film_id) AS film_cnt
    FROM inventory GROUP BY 1
), store_staff AS (
    SELECT store_id, COUNT(*) AS staff_cnt
    FROM staff GROUP BY 1
), store_customer AS (
    SELECT store_id, COUNT(*) AS customer_cnt, SUM(active) AS active_customer_cnt
    FROM customer GROUP BY 1
)
SELECT store.store_id, addr.address, addr.district, city.city, country.country,
       store_inv.inventory_cnt, store_inv.film_cnt,
       store_staff.staff_cnt,
       store_customer.customer_cnt, store_customer.active_customer_cnt
  FROM store
LEFT JOIN address addr USING (address_id)
LEFT JOIN city USING (city_id)
LEFT JOIN country USING (country_id)
LEFT JOIN store_inv USING (store_id)
LEFT JOIN store_staff USING (store_id)
LEFT JOIN store_customer USING (store_id)
ORDER BY 1
Explanation: 少し複雑な SQL を実行するため、店舗ごとの所在地、従業員数、顧客数を集計します。
End of explanation
import seaborn as sns
%matplotlib inline
Explanation: 結果を集計してグラフ描画
描画用にモジュールを読み込みます。
End of explanation
df = %sql select * from rental
df.head()
Explanation: rental テーブルに商品の貸し出し情報が入っており、貸出日が rental_date 、返却日が return_date に記録されています。商品のIDは inventory_id で、顧客IDは customer_id です。これらを使い、未返却DVDのある顧客数の分布を計算して棒グラフを描画してみます。
SQL でビデオレンタルに関するデータを取得し、データフレームとして変数に保存します。
End of explanation
dt = []
for k in df.keys():
    print('{:20s} : {:,} ({:,})'.format(k, df[k].count(), df[k].nunique()))
Explanation: 属性ごとの項目数を確認します。
End of explanation
dd = df[df['return_date'].isnull()].groupby('customer_id').count()[['rental_id',]].reset_index()
dd.groupby('rental_id').count().reset_index().rename(
    columns={'rental_id': 'rental_cnt', 'customer_id': 'customer_cnt'})
Explanation: 16,044件の貸し出し情報があり、商品数は4,580件、顧客数は599人であることが分かります。また、返却日は15,861件ありますので、差し引きすると183件が未返却であると言えます。
返却日が未登録のレコードを顧客IDで集計して顧客ごとの未返却DVDの件数を計算し、さらに件数を集計することで、未返却DVDのある顧客数の分布を計算します。
End of explanation
d = _
sns.barplot(x='rental_cnt', y='customer_cnt', data=d)
Explanation: この分布を棒グラフで表現してみます。（これくらいなら表の数値を見れば理解できる内容ではありますが）
End of explanation
dd[dd['rental_id'] == 3]
Explanation: 変数置換を利用したクエリの実行
上記のグラフを見ると、３件未返却である顧客は特殊と言えるかもしれませんので、具体的に個人を特定してみます。
End of explanation
customer_id = 75
c = %sql select * from customer where customer_id = :customer_id
c.T
Explanation: 顧客IDが75の人が対象であることが分かりますので、 customer テーブルで具体的に確認します。このとき、SQL に数値をハードコードするのではなく、 ipython-sql の変数置換を利用できます。
変数置換は、Notebook で普通の変数を定義して、SQL 文で ":" を付けることで参照できます。
End of explanation
customers = tuple([int(i) for i in dd[dd['rental_id'] == 3]['customer_id'].tolist()])
c = %sql select * from customer where customer_id in :customers
c.T
Explanation: 変数はリストでも受け付けることができますので、特定のIDをハードコードする必要もありません。SQL の WHERE 句では IN を使います。
End of explanation
<END_TASK>