ファン・トラップ
ファン・トラップ
データウェアハウス、データマートに潜む危険な罠、ファン・トラップについて解説する。この罠は容易に再現する上に、発生すると致命的である特徴がある。
この記事は、スタースキーマ をある程度理解していることを前提としています。
前提となるテーブル
sales(売上)とshipment(出荷)の2つのテーブルがあったとする。
salesテーブル
| sale_id | date | client | product | quantity | amount |
|---|---|---|---|---|---|
| 1 | 2020/01/01 | tarou | PR01 | 1 | 100 |
| 2 | 2020/01/02 | tarou | PR02 | 1 | 70 |
| 3 | 2020/01/03 | francesco | PR02 | 2 | 140 |
| 4 | 2020/01/04 | francesco | PR03 | 1 | 300 |
| 5 | 2020/01/05 | francesco | PR01 | 40 | 4000 |
shipmentsテーブル
| shipment_id | sale_id | shipment_date | shipment_quantity | shipment_amount |
|---|---|---|---|---|
| 1 | 1 | 2020/01/01 | 1 | 100 |
| 2 | 2 | 2020/01/02 | 1 | 70 |
| 3 | 3 | 2020/01/02 | 2 | 140 |
| 4 | 4 | 2020/01/03 | 1 | 300 |
| 5 | 5 | 2020/01/04 | 10 | 1000 |
| 6 | 5 | 2020/01/31 | 30 | 3000 |
ファン・トラップの発生条件
ファントラップは、右側に来るテーブルが少なくとも1つ以上のメジャーを含む2つのテーブルの特定の組み合わせで発生する。
メジャーとは、売上金額、売上数量、出荷金額などである。
salesテーブルとshipmentsテーブルの関係を見ると、salesテーブルの1行が、shipmentsの複数行を指すケースがある。1(sales)対多(shipments)の関係であることがわかる。つまり、shipmentsはsalesによって爆発的に増える可能性がある状態と言える。
出荷の合計と売上の合計は同じである。これは全ての注文が正しく全て処理されたからである。salesのsale_idの5は、注文した内の一部が在庫の関係上2回に分けて出荷された。ここで重要なのがsalesとshipmentsの粒度が違うことである。見ての通りだが、salesは5行で、shipmentsは6行になっている。素直にJOINするとファン・トラップが発生する。
SELECT shipment_id, sale_id,... -- カラム名は適当に
FROM sales
INNER JOIN shipments ON shipments.sale_id = sales.sale_id
| shipment_id | sale_id | shipment_date | shipment_quantity | shipment_amount | sales_date | client | product | sales_quantity | sales_amount |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 2020/01/01 | 1 | 100 | 2020/01/01 | tarou | PR01 | 1 | 100 |
| 2 | 2 | 2020/01/02 | 1 | 70 | 2020/01/02 | tarou | PR02 | 1 | 70 |
| 3 | 3 | 2020/01/02 | 2 | 140 | 2020/01/02 | francesco | PR02 | 2 | 140 |
| 4 | 4 | 2020/01/03 | 1 | 300 | 2020/01/03 | francesco | PR03 | 1 | 300 |
| 5 | 5 | 2020/01/04 | 10 | 1000 | 2020/01/04 | francesco | PR01 | 40 | 4000 |
| 6 | 5 | 2020/01/31 | 30 | 3000 | 2020/01/04 | francesco | PR01 | 40 | 4000 |
shipmentsテーブルのshipment_idの5、6が繰り返されることで、結果の一部が重複していることがわかる。
sales_quantityの合計が85(本来は45)。sales_amountの合計が8610(本来は4610)になり、本来の実績より大きくなっていることがわかる。これがファン・トラップである。
解決策
結論から話すと、いい感じの解決策はないが、回避する方法は存在する。
もし、分析の要件として、販売数量と出荷数量の比較である場合は、出荷と販売を同じレベルの粒度に集約する必要がある。例えば、shipmentsテーブルをsale_idごとに先にまとめておくなどである。しかし、これをやってしまうと、他のカラムが使えなくなってしまう。別の分析要件が出てきた場合はまた新しいクエリを用意する必要が出てくる。このようなことを繰り返し、アドホックなクエリを大量に作れば分析は出来るが、非常にコストがかかるが、良い解決方法はなくはなく、粒度調整を頑張る必要がある。
参考文献
Unified Star Schema を参考にまとめました。興味あれば是非読んでみてください。
この書籍の手法を使えば、いい感じに解決できないこともないが、筆者の実務には合わなかった。
Discussion