第４章：結合

2020.06.06 05:36

4-1 マスタテーブルの結合

マスタデータ：ある要素に対する共通のデータをまとめたデータ。顧客マスタなら、顧客の氏名、年齢、住所など。
レコードテーブルとマスタテーブル結合処理(join)の注意点：結合するテーブルの大きさをなるべく小さくして、利用するメモリ量を少なくすること。
言語仕様：R / Pythonは、一度抽出前のデータをメモリ上にすべて乗せねばならないので、データサイズが大きい場合はSQLを使う。

例）予約テーブルとホテルテーブルを結合して、宿泊人数が１人のビジネスホテルの予約レコードのみ取り出す。

SELECT
　-- 必要な列の抽出
　rsv.reserve_id, rsv.hotel_id, rsv.customer_id, rsv.reserve_datetime,
　rsv.checkin_date, rsv_checkin_time, rsv.checkout_date, rsv.people_num,
　rsv.total_price,
　hotel.base_price, hotel.big_area_name, hotel.small_area_name,
　hotel.hotel_latitude, hotel.hotel_longitude, hotel.is_business
FROM
　work.reserve_table rsv
JOIN
　work.hotel_tb hotel
　ON rsv.hotel_id = hotel.hotel_id
　　　AND
　　　-- ホテルテーブルからビジネスホテルのデータのみ抽出
　　　hotel.is_business is True
　　　AND
　　　-- 予約テーブルからビジネスホテルのデータのみ抽出
　　　AND rsv.people_num = 1;

Python

pd.merge(reserve_tb.query('people_num == 1'),
　　　　　hotel_tb.query('is_business'),
　　　　　on = 'hotel_id', how = 'inner')

4-2 条件に応じた結合テーブルの切り替え

方法：結合元のテーブルに結合キー用の列として、条件式で参照するためのそれぞれ値が異なる新たな列を生成する。次に、結合するマスタテーブル２つの必要な共通項を取り出し、１つのテーブルにする。最後に、テーブル同士を結合する。

例）ホテルの予約レコード。ホテルテーブルのすべてのホテルに対して、レコメンド候補のホテルを紐付けたデータを作成する。レコメンド候補のホテルは、同じ小地域（small_area_nameが等しい）のホテルが２０件以上ある場合は、同じ小地域のホテルをレコメンド候補とする。同じ小地域のホテルが２０件に満たない場合は、同じ大地域（big_area_nameが等しい）のホテルをレコメンド候補とする。

-- small_area_nameごとにホテル数をカウント、結合キーを判定するためのテーブル
WITH small_area_mst AS(
　SELECT
　　small_area_name,
　　-- 20件以上であればjoin_area_idをsmall_area_nameとして設定
　　-- 20件未満であればjoin_area_idをbig_area_nameとして設定
　　-- -1は、自ホテルを引いている
　　CASE
　　　WHEN COUNT(hotel_id) -1 >= 20 THEN small_area_name
　　　ELSE big_area_name
　　END AS join_area_id
　FROM
　　work.hotel_tb
　GROUP BY
　　big_area_name, small_area_name
),
-- recommend_hotel_mstはレコメンド候補のためのテーブル
recommend_hotel_mst AS(
　-- join_area_idをbig_area_nameとしてレコメンド候補マスタ
　SELECT
　　big_area_name AS join_area_id,
　　hotel_id AS rec_hotel_id
　FROM
　　work.hotel_tb
　-- unionで、テーブル同士を連結
　UNION
　-- join_area_idをsmall_area_nameとしたレコメンド候補マスタ
　SELECT
　　small_area_name AS join_area_id,
　　hotel_id AS rec_hotel_id
　FROM
　　work.hotel_tb
)
SELECT
　hotels.hotel_id,
　r_hotel_mst.rec_hotel_id
-- レコメンド元のhotel_tbを読み込み
FROM
　work.hotel_tb hotels
-- 各ホテルのレコメンド候補の対象エリアを判断するためにsmall_area_mstを統合
INNER JOIN small_area_mst s_area_mst
　ON hotels.small_area_name = s_area_mst.small_area_name
-- 対象エリアのレコメンド候補を統合する
INNER JOIN recommend_hotel_mst r_hotel_mst
　ON s_area_mst.join_area_id = r_hotel_mst.join_area_id
-- レコメンド候補から自分ホテルを除く
AND hotels.hotel_id != r_hotel_mst.rec_hotel_id;

Python

# ガベージコレクションライブラリ
import gc

# small_area_nameごとにホテル数をカウント
small_area_mst = hotel_tb \
　　.groupby(['big_area_name', 'small_area_name'], as_index=False) \
　　.size().reset_index()
small_area_mst.columns = ['big_area_name', 'small_area_name', 'hotel_cnt']

# 20件以上であればjoin_area_idをsmall_area_nameとして設定
# 20件未満であればjoin_area_idをbig_area_nameとして設定
# -1は自ホテルを引いている
small_area_mst['join_area_id'] = \
　　np.where(small_area_mst['hotel_cnt'] - 1 >= 20,
　　　　　　　　small_area_mst['small_area_name'],
　　　　　　　　　　small_area_mst['big_area_name'])

# 必要無くなった列を削除
small_area_mst.drop(['hotel_cnt', 'big_area_name'], axis=1, inplace=True)

# レコメンド元になるホテルにsmall_area_mstを結合することで、join_area_idを設定
base_hotel_mst = pd.merge(hotel_tb, small_area_mst, on='small_area_name') \
　　　　　　　　　　　　　.loc[:, ['hotel_id', 'join_area_id']

# メモリ解放
del small_area_mst
gc.collect()

# recommend_hotel_mstはレコメンド候補のためのテーブル
recommend_hote_mst = pd.concat([
　　# join_area_idをbig_are_nameとしたレコメンド候補マスタ
　　hotel_tb[['small_area_name', 'hotel_id']] \
　　　　　　.rename(columns={'small_area_name': 'join_area_id'}, inplace=False),
　　# join_area_idをsmall_area_nameとしたレコメンド候補マスタ
　　hotel_tb[['big_area_name', 'hoteo_id']] \
　　　　　　.rename(columns={'big_area_name': 'join_area_id'}, inplace=False)
])

# hotel_idの列名が結合すると重複するので変更
recommend_hotel_mst.rename(columns={'hotel_id': 'rec_hotel_id', inplace=True)

# base_hotel_mstとrecommend_hotel_mstを結合し、レコメンド候補の情報を付与
# query関数によりレコメンド候補から自分を除く
pd.merge(base_hotel_mst, recommend_hotel_mst, on='join_area_id') \
　　　　　.loc[:, ['hotel_id', 'rec_hotel_id']] _
　　　　　.query('hotel_id != rec_hotel_id')

4-3 過去データの結合

注意点：やみくもに過去データを取り出すと膨大な数になる。
対策：

結合対象とする過去の期間を絞る
結合した過去データに集約関数を利用して、データ数を増やさないようにする

例１）n件前のデータ取得

ホテルの予約テーブルの全ての行に、同じ顧客の２回前の予約金額の情報を付与する。予約無い場合は値なしとする。

SELECT
　*,
　-- LAG関数で、２件前のtotal_priceをbefore_priceとして取得
　-- LAG関数により参照する際のグループをcustomer_idに指定
　-- LAG関数により参照する際のグループ内のデータをreserve_datetimeの古い順に指定
　LAG(total_price, 2) OVER
　　(PARTITION BY customer_id ORDER BY reserve_datetime) AS before_price
FROM
　work.reserve_tb;

LAG(対象列, n件前) OVER( ) : n件前のデータを取得するウィンドウ関数

Python

# customerごとにreserve_datetimeで並べ替え
# groupby関数のあとにapply関数を適用し、groupごとに並べ替え
# sort_values関数によりデータを並び替え
result = reserve_tb.groupby('customer_id') \
　　　　　　　　 .apply(lambda group:
　　　　　　　　　group.sort_values(by='reserve_datetime', axis=0, inplace=True)

# customerごとに２つ前のtotal_priceをbefore_priceとして保存
# shift関数は、periodsの引数の数だけデータ行を下にずらす関数
result['before_price'] = \
　result['totla_price'].groupby('customer_id').shift(period=2)

例２）過去n件の合計値

予約テーブルのすべての行に、自身の行から２件前までの３回の合計金額の情報を付与する。過去の予約が３回未満の場合は、値なしとする。

SELECT
　　*,
　　CASE
　　　-- COUNT関数で何件の合計を計算したかをカウントし、３件あるか判定
　　　COUNT(total_price) OVER
　　　(PARTITION BY customer_id ORDER BY reserve_datetime
　　　ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) = 3
　　THEN
　　　-- 自身を含めた３件の合計金額を計算
　　　SUM(total_price) OVER
　　　(PARTITION BY customer_id ORDER BY reserve_datetime
　　　ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
　　ELSE
　　　NULL
　　END AS price_sum
FROM
　work.reserve_tb;

Python

複数のデータ集合に区切るrolling関数の利用

# customer_idごとにreserve_datetimeでデータを並び替え
result = reserve_tb.groupby('customer_id') \
　　　　.apply(lambda x: x.sort_values(by='reserve_datetime', ascending=True)) \
　　　 .reset_index(drop=True)

# 新たな列としてprice_avgを追加
result['price_avg'] = pd.Series(
　　result
　　　# customer_idごとにtotal_priceのwindow3件にまとめ、その平均値を計算
　　　# min_periodsを1に設定し、１件以上あった場合には計算するように設定
　　　.groupby('customer_id')
　　　['total_price'].rolling(center=False, window=3, min_periods=1).mean()
　　　# gourp化を解除すると同時に、customer_idの列を削除
　　　.reset_index(drop=True)
)
# customer_idごとにprice_avgを１行下にずらす
result['price_avg'] = \
　result.groupby('customer_id')['price_avg'].shit(periods=1)

例３）過去n件の平均値

ホテルの予約テーブルの全ての行に、自身の行を含めないで１件前から３件前までの３回の平均予約金額の情報を付与する。過去の予約が３回未満の場合は、満たない回数内で平均予約金額を計算する。予約が１回も無い場合は、値無しとする。

SELECT
　　*,
　　AVG(total_price) OVER
　　(PARTITION BY customer_id ORDER BY checkin_data
　　 ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS price_avg
FROM
　　work.reserve_tb;

例４）過去n日間の合計値

ホテルの予約テーブルのすべてのデータ行に対して、自身の行を含めないで同じ顧客の過去90日間の合計予約金額の情報を付与する。予約が１回も無い場合は0とする。

結合範囲が件数ではなく、結合条件に従う場合には、Window関数は利用できないので、JOIN句を用いた結合を利用する。
SELECT
　-- 結合元のデータ列をすべて取得
　base.*,
　-- 対象の件数が0件の場合は0、1件以上ある場合は合計金額を計算
　COALESCE(SUM(combine.total_price), 0) AS price_sum
-- 結合元の予約テーブルの指定
From
　work.reserve_tb base
-- 過去の情報として結合する予約テーブルの指定
LEFT JOIN
　work.reserve_tb combine
-- 同じcustomer_id同士で結合
　ON
　　base.customer_id = combine.customer_id
　-- 過去のデータのみを結合対象として指定
　AND
　　base.reserve_datetime > combine.reserve_datetime
　-- 90日前までの過去のデータのみを結合対象として指定
　AND
　　DATEADD(day, -90, base.reserve_datetime) <= combine.reserve_datetime
-- 結合元の予約テーブルのすべてのデータ列で集約
GROUP BY
　base.reserve_id, base.hotel_id, base.customer_id, base.reserve_datetime,
　base.checkin_date, base.checkin_time, base.checkout_date,
　base.people_num, base.total_price;

4-4 全結合

全結合：結合するテーブル同士の全ての組み合わせを掛け合わせて生成する結合。
たとえば、顧客の月ごとの利用金額を集計する。予約テーブルがあれば、顧客と月ごとに利用金額の値を集約して、合計利用金額を計算できる。しかし、この計算方法では、ある顧客のある月の利用が無い場合は予約レコードが存在しないので、期待する利用料金が0円という結果は生成されない。このような問題を全結合により回避できる。事前に顧客IDと集計対象期間の月を全結合してから、予約レコードを結合することにより、利用料金が0円という結果が生成される。

例）ホテルの予約レコードにおいて、顧客ごとに2017年1月〜2017年3月の月間合計利用料金を計算する。利用がない月は0とする。日付はチェックイン日付を用いる。

全結合を行う場合は、CROSS JOIN句を利用する。CROSS JOINでは、ON句で結合条件を指定しない。また、SQLで一時的な年月マスタを作成するのは難しいので、事前に汎用的なテーブルとしてデータベースに常に年月マスタを準備するほうが良い。
年月マスタ：年、月、月開始日、月終了日が一覧になっているテーブル

年月マスタテーブル : work.month_mst
year_num | moth_mun | month_first_day | month_last_day
2016 | 1 | 2016-01-01 | 2016-01-31
2016 | 2 | 2016-02-01 | 2016-02-29
2016 | 3 | 2016-03-01 | 2016-03-31
~ 省略 ~
SELECT
　cus.customer_id,
　-- 年月マスタから年を取得
　mst.year_num,
　-- 年月マスタから月を取得
　mst.month_num
　-- 該当のtotal_priceがある場合は足し合わせ、無い場合は0を足し合わせる
　SUM(COALESCE(rsv.total_price, 0)) AS total_price_month
FROM
　work.customer_tb cus
-- 顧客テーブルと年月マスタを全結合
CROSS JOIN
　work.month_mst mst
-- 顧客テーブルと年月マスタと予約テーブルを結合
-- ( work.month_mst１行に対してwork.reserve_tbの該当行(その月のレコード)全部
LEFT JOIN work.reserve_tb rsv
　ON
　　cus.customer_id = rsv.customer_id
　AND
　　mst.month_first_day <= rsv.chekin_date
　AND
　　mst.month_last_day >= rev.chekin_date
-- 年月マスタの対象期間を絞り込み
WHERE
　mst.month_first_day >= '2017-01-01'
　AND
　mst.month_first_day <= '2017-04-01'
GROUP BY
　cus.customer_id, mst.year_num, mst.month_num;

Python

Pythonでは、全結合用の関数がない。なので、すべてが同じ値である結合キーを準備する必要がある。

# 日付型用のライブラリ
import datetime
# 日付の計算用ライブラリ
from dateutil.relativedelta import relativedelta

# 年月マスタの生成
month_mst = pd.DataFrame({
　　'year_month':
　　　　# relativedeltaで2017-01-01をx月間進める。x=0,1,2
　　　　# 2017-01-01, 2017-02-01, 2017-03-01のリストを生成
　　　　[(datetime.date(2017, 1, 1) + relativedelta(months=x)).strftime('%Y%m')
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　for x in range(0, 3)]
　　})

# cross join のためにすべて同じ値の結合キーを準備
customer_tb['join_key'] = 0
month_mst['join_key'] = 0

# customer_tbとmonth_mstを準備した結合キーで内部結合し、全結合を実現
custmer_mst = pd.merge(
　　customer_bt[['customer_id', 'join_key']], month_mst, on='join_key'
)

# 年月の結合キーを予約テーブルで準備
reserve_tb['year_month'] = reserve_tb['checkin_date'] \
　　　　.apply(lambda x: pd.to_datetime(x, format='%Y-%m-%d').strftime('%Y%m'))

# 予約レコードと結合し、合計利用金額を計算
summary_result = pd.merge(
　　customer_mst,
　　reserve_tb[['customer_id', 'year_month', 'total_price']],
　　on=['customer_id', 'year_month'], how='left
　　).groupby((['customer_id', 'year_month'])['total_price'].sum().reset_index()

# 予約レコードが無かった場合の合計金額を値なしから０に変換
summary_result.fillna(0, inplace=True)

機械学習Tips保管庫

データ解析、機械学習のための学習内容の保管庫。復習用。

0コメント

1000 / 1000