ファン・トラップ

3 min読了の目安(約2800字TECH技術記事

ファン・トラップ

データウェアハウス、データマートに潜む危険な罠、ファン・トラップについて解説する。この罠は容易に再現する上に、発生すると致命的である特徴がある。

この記事は、スタースキーマ をある程度理解していることを前提としています。

前提となるテーブル

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 を参考にまとめました。興味あれば是非読んでみてください。
この書籍の手法を使えば、いい感じに解決できないこともないが、筆者の実務には合わなかった。