最も危険なSQLはエラーを出さない

一言でいうと

SQLで本当に怖いのは文法エラーではなく、エラーなしにもっともらしく間違った結果を返す、3つの落とし穴です。

なぜ必要なのか

文法が間違ったクエリは、すぐに知らせてくれます。危険なのは、実行されるクエリです。顧客の前で数字を読み上げ、その数字が間違っていたなら、そのプロジェクトでその後に話すすべての数字が疑われます。

FDEが特にこの危険にさらされる理由があります。他人のスキーマだからです。どの列にNULLが入りうるのか、どの関係が1:Nなのか、どの値が論理削除のマークなのかを知らないまま、クエリを書きます。

どう動くのか

エラーなしに間違う代表的な3つを知っておけば、大部分を避けられます。

1つ目は、NULLとNOT INです。注文が一度もない顧客を探すとします。

SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);

orders.customer_idにNULLが1件でもあると、このクエリは0件を返します。エラーも警告もありません。そして、レポートを書く人は、「注文のない顧客は1人もいない」という、ビジネス的にもっともらしい結論をそのまま書きます。

原因は3値論理です。id NOT IN (1, 2, NULL)は、真ではなく不明になり、不明はWHEREを通過できません。解決策はNOT EXISTSです。行が返されるかどうかだけを見るので、NULLにつまずきません。習慣的にNOT EXISTSを使うほうが安全です。

2つ目は、COUNT(*)とCOUNT(열)です(プレースホルダーは列名です)。この2つは違います。COUNT(*)は行を数え、COUNT(열)は、その列がNULLでない行だけを数えます。

この違いが最もよく表れるのが、LEFT JOINです。チケットが1つもない顧客は、結合結果で、チケット側の列がすべてNULLの行1つとして現れます。このときCOUNT(t.id)は0を返し、COUNT(*)は1を返します。後者を使うと、チケットがない顧客が、チケット1件を持っているものとして集計されます。

同じ理由で、COUNT(closed_at)は、閉じたチケットだけを数えます。全体のチケットが80件なのにこの値が60なら、20件はまだ開いているという意味です。これを知って使えば便利なイディオムですが、知らずに使えば静かな誤答です。

3つ目は、LEFT JOINに付けたWHEREです。左のテーブルをすべて残すためにLEFT JOINを使っておき、WHERE句に右のテーブルの列の条件を付けると、その瞬間、マッチしない行がすべて脱落します。NULLは、どんな比較とも真にならないからです。結果としてINNER JOINと同じになります。右のテーブルに対する条件は、WHEREではなくON句に入れなければなりません。

現場での姿

ここに、sqlite特有の落とし穴をもう1つ加えます。CSVを.importでロードすると、すべての値がTEXTとして入ります。すると、WHERE id = 1001が0件を返します。保存されているのは、文字列'1001'だからです。結合も同じように、静かに0件になります。

CSVをロードしたのに結合結果が空だったなら、ほとんどいつもこの話です。先にCREATE TABLEで型を定義してからロードするか、照会時にCASTを使います。

最後に、実務の習慣を1つ。集計クエリを顧客に見せる前に、分母を確認します。全体が何件で、そのうち何件が条件に当てはまったか。割合だけを報告すると、5件中1件なのか5万件中1万件なのかがわからず、2つの状況の意味はまったく違います。

数字を出す前にする確認

前の3つの落とし穴を避けても、顧客の前で読み上げる数字なら、もう1段階が残ります。その数字が正しいことを、どう示すのかです。

別の方法でもう一度数えます。結合で出した合計を、結合なしでサブクエリでも出してみて、2つの値が同じかを見ます。2つの方法が同じ答えを返せば、間違えた可能性が大きく下がり、違えば、その差そのものが何を見逃したかを教えてくれます。1:Nの関係を結合したあとに合計を出すと、片方が膨らむ事故が、ここで見つかります。

分母と分子を一緒に書きます。前に述べたことと同じ話ですが、レポートに書くときは、さらに一歩進めます。「コンバージョン率20%」ではなく、「5件中1件(20%)」と書けば、読む人がその数字をどれだけ信じるかを、自分で判断できます。

期間と基準時刻を書きます。「先月の売上」は、人によって読み方が違います。どの列を基準に切ったのか(注文時刻か決済時刻か)、タイムゾーンは何か、境界を含むかまで書いてこそ、次の人が同じ数字をもう一度出せます。

除外したものを書きます。テストアカウント、キャンセルされた注文、社内の従業員によるリクエストのようなものを除いたなら、その事実と件数を一緒に書きます。除くこと自体はたいてい正しいですが、書かなければ、他の人が出した数字と食い違ったときに、原因を探すのに何時間もかかります。

最後に、クエリ自体を残します。結果だけを渡すと、その数字は、数週間後には再現できない値になります。クエリと実行時刻を一緒に残しておけば、あとで誰かが「この数値はどう出したのですか」と尋ねたときに数秒で答えられ、何より、自分がもう一度見るときに、何を仮定したかがわかります。

次のラボですること

カスタマーサポートシステムのスナップショットをsqliteにロードし、顧客数・確定売上・最大売上の顧客のような平凡な質問から始めて、NULLの集計とLEFT JOINが必要な質問まで答えを出します。最後の質問の答えは、たぶん予想と違うでしょう。