環境変数から接続情報を取得します。
In [1]:
import os
pgconfig = {
'host': os.environ['PGHOST'],
'port': os.environ['PGPORT'],
'database': os.environ['PGDATABASE'],
'user': os.environ['PGUSER'],
'password': os.environ['PGPASSWORD'],
}
拡張を読み込み、データベースに接続します。
In [2]:
%load_ext sql
dsl = 'postgres://{user}:{password}@{host}:{port}/{database}'.format(**pgconfig)
%sql $dsl
Out[2]:
SQL を実行します。
In [3]:
%%sql
select * from actor order by actor_id limit 10
Out[3]:
実行結果を pandas のデータフレームに変換します。
SqlMagic.autopandas パラメータを True に設定しておくと、結果セットを pandas の DataFrame として処理してくれます。
In [4]:
%config SqlMagic.autopandas = True
df = %sql select * from actor order by actor_id
df.describe()
Out[4]:
少し複雑な SQL を実行するため、店舗ごとの所在地、従業員数、顧客数を集計します。
In [5]:
%%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
Out[5]:
描画用にモジュールを読み込みます。
In [6]:
import seaborn as sns
%matplotlib inline
rental テーブルに商品の貸し出し情報が入っており、貸出日が rental_date 、返却日が return_date に記録されています。商品のIDは inventory_id で、顧客IDは customer_id です。これらを使い、未返却DVDのある顧客数の分布を計算して棒グラフを描画してみます。 SQL でビデオレンタルに関するデータを取得し、データフレームとして変数に保存します。
In [7]:
df = %sql select * from rental
df.head()
Out[7]:
属性ごとの項目数を確認します。
In [8]:
dt = []
for k in df.keys():
print('{:20s} : {:,} ({:,})'.format(k, df[k].count(), df[k].nunique()))
16,044件の貸し出し情報があり、商品数は4,580件、顧客数は599人であることが分かります。また、返却日は15,861件ありますので、差し引きすると183件が未返却であると言えます。
返却日が未登録のレコードを顧客IDで集計して顧客ごとの未返却DVDの件数を計算し、さらに件数を集計することで、未返却DVDのある顧客数の分布を計算します。
In [9]:
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'})
Out[9]:
この分布を棒グラフで表現してみます。（これくらいなら表の数値を見れば理解できる内容ではありますが）
In [10]:
d = _
sns.barplot(x='rental_cnt', y='customer_cnt', data=d)
Out[10]:
上記のグラフを見ると、３件未返却である顧客は特殊と言えるかもしれませんので、具体的に個人を特定してみます。
In [11]:
dd[dd['rental_id'] == 3]
Out[11]:
顧客IDが75の人が対象であることが分かりますので、 customer テーブルで具体的に確認します。このとき、SQL に数値をハードコードするのではなく、 ipython-sql の変数置換を利用できます。
変数置換は、Notebook で普通の変数を定義して、SQL 文で ":" を付けることで参照できます。
In [12]:
customer_id = 75
c = %sql select * from customer where customer_id = :customer_id
c.T
Out[12]:
変数はリストでも受け付けることができますので、特定のIDをハードコードする必要もありません。SQL の WHERE 句では IN を使います。
In [13]:
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
Out[13]:
In [ ]: