同じ3行から、平均150msと400msが出る理由。

説明用の記録を三行だけ並べてみます。応答時間は100ms、200ms、900ms。最初の二件は成功、最後の一件は失敗でした。全部なら平均400ms、成功だけなら150ms。どちらも計算は合っているのに、答えは違う。「平均を教えて」だけでは、ここが決まらないんですね。

AIが150msと答えたら、足し算より先に「どの行を使った？」と聞きたいところ。SQLは、その選び方を条件として書く言葉です。三行の記録で、平均が変わる場所を追ってみましょう。^[1][2]

SQLのSELECTはデータを取り出す指定、WHEREは条件に合う行へ絞る指定です。この例でWHERE status=200を付けると、成功の二行が対象に。平均を求めるavg(duration_ms)を使えば150、全三行なら400。計算へ入る前に、使う行を選んでいるわけです。^[1][2]

count(*)も一緒に出せば、使った行数が見えます。150だけでは気づきにくい違いも、「2件の平均150」と「3件の平均400」なら話しやすくなります。実データの成功条件は業務によって決める必要がありますが、その条件を確認できる形にするのが最初の一歩です。^[2]

FILTERなら、同じ行から別の集計を並べられる

PostgreSQLのFILTERは、指定した集計へ入れる行を絞ります。たとえばcount(*)は全行、avg(duration_ms) FILTER (WHERE status=200)は成功だけ、というように一つの結果に並べられます。WHEREをクエリ全体へ付けた場合と違い、ほかの集計は全行を受け取れるのがポイントです。^[2]

下の実践では、全件のcountとavg、成功だけのcountとavgを横に並べます。期待する値は3、400、2、150。どの数字がどの条件に対応するかを追ってみてください。SQLを速く書くことより、条件を読んで「この平均の対象はここだ」と指せることが、今回の発見です。^[2]

要は、WHEREは集計する前の行を絞り、FILTERはその集計だけに条件を掛けるという違いです。全件の平均と成功だけの平均を並べたいときは、この掛ける場所を読めると、なぜ400と150が同時に出るかが分かります。

では、記録できなかった時間があったらどうでしょう。値が入っていないことを表すNULLが出てきます。行の数と、時間を記録できた数が同じとは限らなくなるので、先ほどの三行を少し変えてみましょう。^[3]

ここからの式はPostgreSQL向けです。SQLには製品ごとの違いもあるので、手元で試すときは同じ製品の案内を使ってください。まずは値がない一行を、平均がどう扱うかに注目します。^[2]

説明用に、応答時間が100、200、NULLの三行を考えます。count(*)は三行、count(duration_ms)は値のある二行。avg(duration_ms)はNULL以外の値を使うので150です。100と200にゼロを足して三で割る計算とは違います。^[3]

つまり、平均へ添える件数にも、何を数えたかが必要なんです。「全行は三件、時間を記録できたのは二件」と並べれば、数字が食い違う理由を話せます。NULLを本当のゼロへ置き換えてよいかは、データの意味から別に決めます。

AIへ式を頼むとき、白紙から動く答えが出てくるだけで、少しうれしくなりますよね。そこで「実行できたから合っている」と判断する前に、三行なら手で計算できることを使いましょう。全件なら400、成功だけなら150。この二つを、AIの式を見る前にメモしておきます。

依頼も、平均だけから一段具体的にします。「全件の件数と平均、成功した件数と平均を同じ結果へ出したい。成功はstatus=200。この三行なら3件・400、2件・150になる」。どの数字が欲しいかに加え、どの行を使うかまで渡せました。

もし返った式にWHERE status=200があり、全件のcount(*)まで2になったら。構文は動いていても、「全件」を集計する前に一行が消えています。先ほどのFILTERなら、成功だけの集計へ条件を付け、ほかの集計へは全行を渡せます。間違いを見つけた場所と、直す場所が対応するんですね。^[1][2]

ここで「FILTERが正しい呪文」と覚えるより、ほしい結果を先に言えることが大事です。最初から成功行だけを調べたいなら、WHEREで絞る目的もあります。何を比較したいかが変われば、同じ条件を掛ける場所も変わるわけです。^[1][2]

一度期待値と一致したら、今度は成功した200msを300msへ変える。成功だけの平均は200、全件は約433.33になるはずです。一つ値を変えて予想した方向へ動くかを見ると、たまたま元の三行だけで合った式も、もう一度問い直せます。

いま200msを300msへ変えた後のデータは、100・300・900の三行です。ここから900msをNULLへ変えると、100・300・NULLになり、平均は200。前の節の図は、変更前の100・200・NULLで平均150でした。三行目がNULLでも、二行目の値が違うので平均も変わるんですね。^[3]

同じ100・300・NULLで、今度は件数も比べます。count(*)は三行、count(duration_ms)は値がある二件。avg(duration_ms)は後者の二つの値を使います。NULLをゼロへ変えて三で割ると約133.33になるので、記録がないことと、本当のゼロは分けて扱います。^[3]

要は、AIより速くSQLを書く競争をしなくても、式が答える問いを確かめる仕事はできるんです。小さなデータ、先に書いた期待値、条件を一つ変えた結果。この三つを持っておくと、「数字が出た」から「この数字になる理由を説明できる」へ進めます。

自分が使ってよいPostgreSQL環境で行います。手元になければ記事末尾の「環境の準備に使う公式ガイド」から、OSに合った導入方法を確認してください。この記事では新しいDBの契約や構築は不要です。まず架空の3行を紙に書き、全件と成功だけを分ければ、同じ違いを確かめられます。

WITH sample(status, duration_ms) AS (VALUES (200,100),(200,200),(500,900))
SELECT count(*) AS all_count, avg(duration_ms) AS all_avg,
       count(*) FILTER (WHERE status=200) AS success_count,
       avg(duration_ms) FILTER (WHERE status=200) AS success_avg
FROM sample;

準備と、確かめる順番

全件は3件で400、成功だけは2件で150。これは式から求めた期待値です。手元で実行した結果と比べてみてください。表示する小数点の桁は環境で異なります。

FILTERが付いた二つの集計と、付いていない二つを指で追います。なぜ同じSELECTの中で対象が変わるのか、「FILTERなら、同じ行から別の集計を並べられる」の説明と照合します。

成功行の200msを300msへ変えたらどうなるか、実行前に予想します。全件の平均は約433.33、成功だけは200です。

次に「平均」を見たら、対象と件数も確かめる。そのために読めるSQLを増やす続きが、DBの学習です。