複数の表をJOINした後に合計金額が増えたときは、SQLの構文より先に、結果の一行が何を表しているかを確認します。結合先が複数件あると、元の行が複数の組合せとして現れます。
この記事では、三つの表を使う仮例で行の増殖と集約の考え方を整理します。コードはPostgreSQLの構文に基づく未実行の説明例です。
一対多が重なると組合せが増える
仮に顧客Aに注文が2件、問い合わせが3件あるとします。顧客IDだけで顧客・注文・問い合わせを結ぶと、顧客Aについて注文と問い合わせの組合せが6行になります。
これは「重複したデータが勝手に追加された」のではなく、指定条件に一致する組合せが結果へ出たものです。PostgreSQLのJOINの説明
| 表 | 顧客Aの件数 | 表の一行の意味 |
|---|---|---|
| 顧客 | 1 | 一人の顧客 |
| 注文 | 2 | 一件の注文 |
| 問い合わせ | 3 | 一件の問い合わせ |
| 単純な結合結果 | 6 | 注文と問い合わせの組合せ |
この結果で注文金額を足すと、各注文が問い合わせの件数分だけ繰り返されます。
欲しい結果の単位を先に決める
求めたいのが「顧客ごとの注文合計と問い合わせ件数」なら、最終結果は顧客一人につき一行です。注文と問い合わせをそれぞれ顧客単位へ集約してから結ぶ方法を検討できます。
WITH order_totals AS (
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
), inquiry_counts AS (
SELECT customer_id, COUNT(*) AS inquiry_count
FROM inquiries
GROUP BY customer_id
)
SELECT c.customer_id,
o.total_amount,
i.inquiry_count
FROM customers AS c
LEFT JOIN order_totals AS o
ON o.customer_id = c.customer_id
LEFT JOIN inquiry_counts AS i
ON i.customer_id = c.customer_id;
GROUP BYで顧客単位の結果を作ってからJOINする構成です。PostgreSQLのGROUP BY
ここでは顧客IDの一意性などを前提にしています。注文や問い合わせがない場合のNULLを、業務上0として表示してよいかも別に決めます。
DISTINCTで合計のずれを隠さない
行が増えたためにDISTINCTを足すだけでは、集計単位の誤りを解消できるとは限りません。同額の別注文を一つにしてしまうような処理は、求める業務結果と異なります。
まず「同じ注文を識別するキー」と「別注文だが値が同じ行」を区別します。結果の重なりを消すことより、なぜ複数の組合せができるかを確認します。
表を一つずつ加えて件数を照合する
検証では、顧客だけ、顧客と注文、三表の結合という順に結果を見ます。各段階で行数、顧客IDの種類数、特定顧客の注文IDと金額を照合すると、増えた段階を絞れます。
本番データの全件出力ではなく、権限のある検証用データや対象を絞った読取で確認してください。性能やデータの機密性も実際の環境に合わせて扱います。
合計だけでなくキーの対応を残す
データ移行の検証と同様に、件数が合うことだけで正しい対応とは限りません。業務キーと期待結果を揃えて確認します。
結合前の粒度、結合後の粒度、集約する単位を記録すると、複数表のSQLを別の担当者へ説明しやすくなります。