注文を明細とJOINしてから注文金額を足すと、明細の数だけ重複する
1対多のJOINは、親1行に対して一致した子の数だけ行を作る。SUMやCOUNTの前に、1行が注文・明細のどちらを表すかという粒度を確認する。
用語
- 粒度
- 表や集計結果の1行が何を表すか。1注文と1明細では、同じ注文金額を足しても意味が変わる。
- INNER JOIN
- 結合条件を満たす左右の行の組ごとに結果を作る内部結合。一致する相手が複数なら、元の行も複数回現れる。
- GROUP BY
- 指定列の値が同じ行をグループにまとめる指定。グループ内の重複した金額を自動で除くものではない。
- EXISTS
- 条件に合う行が少なくとも1件あるかを判定するSQLの式。存在だけを知りたいとき、親を子の数だけ増やさずに絞り込める。
図と例
- 注文表で合計: 2,000円
- JOIN後に金額を合計: 5,000円
- 金額をDISTINCTにして合計: 1,000円
計算例：注文Aは1,000円で明細2行、注文Bも1,000円で明細3行。INNER JOINの結果は5行になり、SUM(注文金額)は5,000円。SUM(DISTINCT 注文金額)は1,000円になる。注文表の2行を合計した2,000円が、今回求めたい注文総額である。
実際のサービス・製品では
PostgreSQL
PostgreSQLの表式の説明は、INNER JOINが一致した行の組ごとに結果を作り、その結果へGROUP BYと集約を適用する流れを示す。
資料を読む
解説
売上レポート、注文数、会員ごとの利用額をSQLで求めるときに使う。各表と結合後の結果について「1行は何か」を書き、子が0件・1件・複数件の例と、同額の別注文で確認する。LEFT JOINでは子がない親にも1行ができるため、COUNT(*)とCOUNT(子の非NULLキー)の違いにも注意する。
注文表の主キーを注文ID、明細表の各行を1つの商品明細とする。INNER JOINで注文IDを結び付けると、明細が3行ある注文は結合結果にも3行現れ、注文金額も3回並ぶ。GROUP BYで1注文にまとめても、SUMはグループ内の3つの金額を足すため重複は消えない。注文総額を知りたいだけなら注文表で集計する。明細条件に合う注文だけを数えるならEXISTSを使って存在を確認するか、対象の注文IDを一意にしてから結ぶ。注文と明細集計を並べるなら、先に明細を注文IDごとに集計して1行にそろえる。SUM(DISTINCT 注文金額)は金額が等しい別注文まで1つにするので、注文単位の重複除去にはならない。
トレードオフ
- 明細を先に注文ごとに集計してからJOINする
- 得るもの：親と子の集計を同じ注文単位で組み合わせられる
- 払うもの：集計後に必要な明細の条件や内訳を、先に決める必要がある
- EXISTSで条件に合う注文を絞る
- 得るもの：明細数で注文行を増やさず、条件に合う注文を選べる
- 払うもの：明細の個別項目や合計も表示するなら、別途読み取りや集計が要る
さらに学ぶ
チェック
注文Aは1,000円・明細2行、注文Bも1,000円・明細3行。全注文の金額を各注文1回ずつ合計したい。正しい方法と結果は？
- 明細とINNER JOINしてからSUMし、5,000円
- SUM(DISTINCT 注文金額)で、1,000円
- 注文表の注文金額をSUMし、2,000円
解答と解説
注文表の注文金額をSUMし、2,000円
今回の対象は全注文なので、注文表の1注文1行の粒度で1,000円＋1,000円＝2,000円と足す。JOIN後のSUMは明細数で水増しされる。DISTINCTを金額に付けても、別注文の同じ1,000円まで1つにまとめてしまう。