SQL Interview Prep · レッスン

NULLを含むSUMとAVG

Q: 「NULLを含むSUMとAVG」で何を学びますか？

AVGがNULLを無視する理由と、それによって面接で期待される答えがどう変わるかを学びます。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

AVGがNULLを無視する理由と、それによって面接で期待される答えがどう変わるかを学びます。

「NULLを含むSUMとAVG」はCoddyKit上の無料SQL Interview Prepレッスンです。これはレッスン2/4です。下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。これはSQL Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Interview Prepコースには全4レッスンが含まれています。

AVG に潜む落とし穴

これは、注意を怠った候補者がつまずく面接の定番問題です: 「NULL がいくつか含まれる salary 列があります。AVG(salary) は何を計算しますか。それはビジネスが求める値ですか?」

正しく答えるには、集約関数は NULL を無視するため、平均の分母が変わることを理解している必要があります。これを本番環境で間違えると、報告する平均値が気づかないうちに大きくなってしまいます。

その動作を明確に確認しましょう。

サンプルデータ

このレッスンでは、NULL を許容する bonus 列を持つ employees テーブルを使います:

Alice、ボーナス100
Bob、ボーナス200
Carol、ボーナス NULL
Dan、ボーナス300

4行のうち、NULL ではないボーナスは3つ、NULL は1つです。このデータに対して SUM と AVG を実行し、NULL がどのように扱われるかを確認します。

SUM は NULL を無視する

SUM(bonus) は NULL ではない値だけを合計します: 100 + 200 + 300 = 600。NULL の行は何も寄与せず、単にスキップされます。算術上の意味で件数を変えるように0として扱われるわけではありません。

実質的には、NULL が存在しないものとして扱われます。SUM は NULL があってもエラーにならず、入力がすべて NULL の場合を除き NULL を返しません。

SELECT SUM(bonus) AS total_bonus
FROM employees;
-- returns 600

AVG も NULL を無視する

AVG(bonus) が重要なポイントです。これはNULL ではない値の合計を、NULL ではない値の個数で割ったものを計算します: 600 / 3 = 200。

分母は4ではなく3です。NULL の行は分子と除数の両方から除外されます。AVG が人を驚かせる理由はここにあります。平均はすべての行ではなく、存在する値を対象に計算されます。

SELECT AVG(bonus) AS avg_bonus
FROM employees;
-- 600 / 3 = 200, NOT 600 / 4 = 150

分母が重要な理由

ビジネス上、NULL のボーナスが「ボーナスを受け取っていない」= 0 を意味するとします。その場合、真の平均は 600 / 4 = 150 であるべきですが、AVG(bonus) は 200 を返します。

面接での適切な答えは次のとおりです。「AVG は NULL を無視するため、ボーナスがある従業員を対象に平均を計算します。NULL が0を意味するなら、事前に NULL を0へ変換する必要があります。」この違いを明確に述べることが評価されます。

COALESCE で NULL を 0 に変換する

NULL を0としてすべての行の平均を求めるには、列を COALESCE(bonus, 0) で包みます。これで各行に数値が入るため、分母は4になります。

結果は 600 / 4 = 150 です。ここでの教訓は、AVG(col) と AVG(COALESCE(col, 0)) は異なるビジネス上の問いに答えるということです。意図を持って使い分けてください。

SELECT AVG(COALESCE(bonus, 0)) AS avg_over_all
FROM employees;
-- 600 / 4 = 150

AVG = SUM / COUNT（要注意）

便利な恒等式として、AVG(col) は SUM(col) / COUNT(col) と等しくなります — COUNT(*) ではなく COUNT(col) である点に注意してください。AVG とこの COUNT はどちらも NULL を除外するためです。

誤って SUM(col) / COUNT(*) と書くと、全行を対象にした平均（ここでは150）になり、AVG（200）とは異なります。面接官が AVG を手作業で再現するよう求め、適切な COUNT を選べるか確認することもあります。

SELECT
  AVG(bonus)                       AS builtin_avg,   -- 200
  SUM(bonus) * 1.0 / COUNT(bonus)  AS manual_avg,    -- 200
  SUM(bonus) * 1.0 / COUNT(*)      AS over_all_rows  -- 150
FROM employees;

整数除算の落とし穴

平均を手作業で計算するときに起こる微妙なバグがあります。多くのデータベースでは、2つの整数を割ると整数除算になり、小数部分が切り捨てられます。7 / 2 の結果が3.5ではなく3になることがあります。

AVG 自体は通常小数を返しますが、整数型の列に対して SUM / COUNT で再構築すると、精度が失われる場合があります。最初に 1.0 を掛けるか、小数型にキャストしてください。

SELECT
  SUM(bonus) / COUNT(bonus)        AS maybe_truncated,
  SUM(bonus) * 1.0 / COUNT(bonus)  AS precise
FROM employees;

すべてが NULL の場合

面接官が好んで出すエッジケースです。すべての値が NULL の場合、またはフィルターに一致する行がない場合はどうなるでしょうか?

SUM は NULL ではない入力がない場合、0ではなくNULLを返します。
AVG もNULLを返します。0個の値で割ることは未定義だからです。
一方、COUNT は0を返します。

数値のデフォルト値が必要な場合は、結果を COALESCE(SUM(col), 0) で包みます。

SELECT COALESCE(SUM(bonus), 0) AS safe_total
FROM employees
WHERE 1 = 0;  -- no rows: returns 0, not NULL

グループごとの平均

同じ NULL のルールが GROUP BY 内でも適用されます。各グループの AVG は、そのグループ内の NULL ではない値の個数で割られます。ボーナスがすべて NULL のグループでは、そのグループの AVG は NULL になります。

そのため、部署ごとの平均値が予想外の場合は、JOIN のバグを疑う前に、NULL によって各分母が小さくなっていないかを疑ってください。

SELECT department, AVG(bonus) AS avg_bonus
FROM employees
GROUP BY department;

回答の伝え方

洗練された面接での回答は、次のようになります: 「SUM と AVG はどちらも NULL を無視します。AVG は NULL ではない値の個数で割るため、NULL によって実質的に分母が小さくなります。NULL を0として扱うべき場合は、集約する前に COALESCE で変換します。そうでなければ、平均は値を持つ行だけを反映します。」

この一文で、正確さ、ビジネスへの理解、そして解決方法を示せます。

クイックチェック

サンプルデータにこのルールを適用してください。

まとめ

NULL を含む SUM と AVG の要点:

どちらもNULL を完全に無視します。
AVG(col) = SUM(col) / COUNT(col) — 分母から NULL が除外されます。
NULL が0を意味し、計算に含める必要がある場合は、COALESCE(col, 0) を使用します。
すべての値が NULL の場合、または行がない場合、SUM と AVG はNULLを返します（COUNT は0を返します）。
AVG を手作業で再構築するときは、整数除算に注意してください。

次は、MIN、MAX、および数値以外のデータの集約です。

よくある質問

「NULLを含むSUMとAVG」レッスンは無料ですか？

はい。「NULLを含むSUMとAVG」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Interview Prepコースには全4レッスンが含まれています。

「NULLを含むSUMとAVG」で何を学びますか？

AVGがNULLを無視する理由と、それによって面接で期待される答えがどう変わるかを学びます。ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Interview Prepを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。これはレッスン2/4です。

「NULLを含むSUMとAVG」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Interview Prepレッスンでコードを書いて実行できますか？

はい。すべてのSQL Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

← SQL Interview Prepに戻る