小売チェーンの売上集計SQLにおけるNULLと最新行抽出の誤り
全国120店舗を展開する小売チェーンCは、売上データ基盤を刷新した。売上明細テーブル sales_detail は月間約900万行、2年分で約2億行を保持し、列は order_id、line_no、product_id、qty、amount、status、updated_at である。status は「S」（確定）、「C」（取消）、NULL（未確定）の3値で、NULLは全行の約2%を占める。商品マスタ product は約8万件、廃番商品テーブル discontinued は約4,000件で、product_id にNULLを許す列がある。商品別の売上集計を status <> 'C' の条件で作成したところ、経理部門の売上高より約2%少なくなった。廃番商品を除外するため NOT IN (SELECT product_id FROM discontinued) と書いたレポートは、除外対象がないはずの商品まで含めて0件を返した。さらに、商品ごとに最後に更新された明細だけを抽出する処理を相関サブクエリで実装したが、実行に40分かかり、同じ updated_at の行が複数ある商品で結果が実行ごとに変わることがある。アプリケーション側ではNULLを空文字に変換してから登録する改修が一部の画面でしか行われていない。
1. 商品別集計が経理の売上高より約2%少ない原因を特定し、修正SQLと検証方法を示せ。
Read the worked answer
原因： SQLの3値論理。status <> 'C' は status がNULLの行に対して UNKNOWN になり、WHERE句は TRUE の行だけを通すため、未確定（NULL）の約2%が集計から落ちる。差の2%はNULLの比率と一致しており、これで説明がつく。
修正SQL（未確定を含める場合）：
SELECT product_id, SUM(amount) AS total
FROM sales_detail
WHERE status IS NULL OR status <> 'C'
GROUP BY product_id;
COALESCE(status,'') <> 'C' でも結果は同じだが、列に関数演算をかけると索引が使われにくくなるため、OR条件か status IN ('S') と業務定義で決めるのがよい。
まず業務定義の確認： 未確定を売上に含めるのか、確定（S）だけを売上とするのか。経理の数字が「確定ベース」なら、WHERE status = 'S' が正解であり、NULLを足すのは誤り。差が出た原因と、あるべき定義は別に切り分ける。
検証： 全体件数、status別の件数と金額、除外された件数をカウントし、経理の売上高と一致することを確認する。リグレッション防止に、NULLを含むテストデータを用意する。
2. NOT IN を使ったレポートが0件を返した原因を説明し、安全な書き換えを示せ。
Read the worked answer
原因： NOT IN のサブクエリ結果にNULLが1件でも含まれると、比較はすべて UNKNOWN になる。x NOT IN (1, NULL) は「x <> 1 かつ x <> NULL」であり、x <> NULL が UNKNOWN のため行全体が UNKNOWN となり、WHEREで除外される。サブクエリ側の product_id にNULL許容列があるため、全行が落ちて0件になる。
書き換え（推奨）：
SELECT p.product_id
FROM product p
WHERE NOT EXISTS (
SELECT 1 FROM discontinued d
WHERE d.product_id = p.product_id
);
NOT EXISTS は相関する行の存在だけを見るため、NULLの影響を受けない。
次善の策： NOT IN を残すなら、サブクエリに WHERE product_id IS NOT NULL を付ける。ただし将来NULLが入ると再発する。
根本対策： discontinued.product_id にNOT NULL制約を付け、「該当なし」をNULLで表す設計をやめる。LEFT JOIN ... WHERE d.product_id IS NULL でも書けるが、結合キーがNULLだと一致しないため、同じくキーのNOT NULL化が前提になる。
3. 商品別の最新明細を抽出する処理が遅く、結果が実行ごとに変わる原因を説明し、書き換えを示せ。
Read the worked answer
遅い原因： 相関サブクエリは商品ごとに行を走査するため、2億行規模では事実上全表走査に近い負荷になる。
結果が変わる原因： ORDER BY updated_at DESC だけでは、同じ updated_at の行の順序が一意に決まらない。SQLは同着時の順序を保証しないため、実行計画が変わると結果が変わる。タイブレーカ（line_no など）を必ず入れる。
書き換え：
SELECT product_id, line_no, qty, amount, updated_at
FROM (
SELECT s.*, ROW_NUMBER() OVER (
PARTITION BY product_id
ORDER BY updated_at DESC, line_no DESC) AS rn
FROM sales_detail s
) t
WHERE rn = 1;
- 索引（product_id, updated_at DESC, line_no DESC）があると、パーティション内のソートを避けられる。
- 同着行を全部欲しいなら ROW_NUMBER ではなく RANK を使う。1行に決めたいなら ROW_NUMBER とタイブレーカである。
- PostgreSQLなら DISTINCT ON (product_id) ... ORDER BY product_id, updated_at DESC, line_no DESC でも同じ結果が得られる。
- 実行計画を取り、ウィンドウ関数のソートがメモリに収まっているかを確認する。収まらない場合は work_mem の調整か、商品ごとの分割処理を検討する。