売上と入金を結合したら金額が倍に？ 集計する粒度からSQLを直す

1. まずSQLを使わず、期待する答えを手で作る

受注101の売上は1,000×2＋500×1＝2,500円、入金は1,500＋1,000＝2,500円です。102の売上と入金はともに1,200円、103の売上は2,400円、入金は1,000円です。104はどちらも明細がないので、この教材の規則では0円とします。

C1は101と102を持つので売上3,700円、入金3,700円、未収0円です。C2は2,400円、1,000円、1,400円、C3は全て0円になります。この小さな正解表を持っておくと、複雑なSQLの結果がもっともらしい数字でも誤りを発見できます。実際の業務では、明細のない受注を異常として別途通知する設計も考えられます。

2. 二つの「多」を同時に結合すると何行になるかを見る

受注101に商品明細が2件、入金明細が2件あるため、両方を受注IDで結合すると2×2＝4行になります。一行は商品明細そのものではなく、「ある商品明細とある入金明細の組合せ」です。単価×数量の2,000円は入金2件に対応して2回、500円も2回現れます。

結果として売上は2,000＋2,000＋500＋500＝5,000円、入金も1,500＋1,000＋1,500＋1,000＝5,000円になります。GROUP BYを顧客IDにしても、重複した入力行の合計を計算する点は変わりません。行が増えた後で集計するだけでは、取引の単位へ戻れないのが原因です。

3. 商品明細と入金明細を、それぞれ受注単位へ集約する

二つの明細表を直接つなぐ前に、商品明細を受注IDごとにSUM(unit_price * quantity)、入金明細を受注IDごとにSUM(amount)で集約します。こうして各表を「受注IDごとに最大一行」にすると、一つの受注について金額同士が掛け合わされなくなります。

SQLの共通表式を使うなら、集約部分は次のように表せます。列名・表名はこの演習用です。売上は金額の列を集計するのであり、商品明細の件数や入金の件数を掛ける処理はありません。

WITH line_totals AS ( SELECT order_id, SUM(unit_price * quantity) AS sales FROM order_lines GROUP BY order_id ), payment_totals AS ( SELECT order_id, SUM(amount) AS paid FROM payments GROUP BY order_id )

4. 顧客を起点にLEFT JOINし、0円の表示条件を明示する

全顧客を残すため、customersを起点にorders、line_totals、payment_totalsをLEFT JOINします。受注104には対応する集約行がないので、結合結果の金額はNULLになります。SUMの結果がNULLになる場合をCOALESCEで0へ置き換えると、明細のない顧客も指定どおり表示できます。

SELECT c.customer_id, COALESCE(SUM(l.sales), 0) AS sales, COALESCE(SUM(p.paid), 0) AS paid, COALESCE(SUM(l.sales), 0) - COALESCE(SUM(p.paid), 0) AS unpaid FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id LEFT JOIN line_totals l ON l.order_id = o.order_id LEFT JOIN payment_totals p ON p.order_id = o.order_id GROUP BY c.customer_id ORDER BY c.customer_id;

このSELECTは、前の手順のWITH句に続けて実行します。結合キーの一意性も前提です。例えば顧客表に同じIDを重複登録できるなら、集約を直しても再び行が増えます。主キー・外部キーの設計とデータの状態を確認することも、SQLの正しさを支えます。

5. 全体の総額と、誤った修正が通らないデータで検算する

元の商品明細から求めた総売上は2,500＋1,200＋2,400＝6,100円、元の入金総額は2,500＋1,200＋1,000＝4,700円です。レポートの列合計も同じで、未収合計は1,400円になります。顧客が3行表示されることと合わせて確認すると、金額と行の欠落を別々に検査できます。

SUM(DISTINCT 金額)で重複を消す修正は不適切です。1,000円の正当な入金が別々に2件あれば、同額でも2,000円と数える必要があります。重複を判断する単位は金額の値ではなく取引です。元表の合計との照合、同額の別取引、明細0件、明細が2件以上というテストを組み合わせましょう。