SQL NULL — 集計NULL・NULLIF・ゼロ除算の応用

QUESTION 6

NOT IN と NULL サブクエリの罠 — サブクエリに NULL が 1 つあるだけで全行がUNKNOWNで消える

NOT IN / NOT EXISTS相関サブクエリUNKNOWN 全除外NULL安全比較

前提知識 ▼

col NOT IN (a, b, c) は内部的に col <> a AND col <> b AND col <> c と等価です。リストに NULL が 1 つ含まれるだけで、col <> NULL → UNKNOWN となり AND チェーンが UNKNOWN に伝播して、全行が除外されます。

-- リストに NULL があると UNKNOWN が伝播し、全行が除外される
col NOT IN (10, 30, NULL)
-- 内部展開: col <> 10 AND col <> 30 AND col <> NULL (← UNKNOWN)

-- 解決策①: NOT EXISTS (NULL の影響を受けない)
WHERE NOT EXISTS (SELECT 1 FROM sub WHERE sub.col = t.col)

-- 解決策②: サブクエリから NULL を除外
WHERE col NOT IN (SELECT col FROM sub WHERE col IS NOT NULL)

NULL 安全な除外の原則：サブクエリを使う NOT IN では、サブクエリが NULL を返す可能性がないか必ず確認してください。NOT EXISTS は相関サブクエリが「行を返さないかどうか」を判定するため NULL の影響を受けません。データが増えて NULL が混入した瞬間に全件消える「タイムボム」になります。

問題 ▼

employees テーブルから、管理対象外の部署（managed_depts に存在しない dept_id）の従業員を抽出してください。出力列は emp_id, name, dept_id、emp_id 昇順で返してください。

使用テーブル

► employees（6行）

emp_id	name	dept_id
1	田中	10
2	鈴木	20
3	佐藤	30
4	伊藤	40
5	山田	NULL
6	高橋	20

► managed_depts（3行）

dept_id	dept_name
10	営業
30	人事
NULL	未定義

期待出力 ▼

emp_id	name	dept_id
2	鈴木	20
4	伊藤	40
5	山田	NULL
6	高橋	20

模範解答コード ▼

SELECT
  emp_id, name, dept_id
FROM   employees
WHERE  NOT EXISTS (
    SELECT 1
    FROM   managed_depts
    WHERE  managed_depts.dept_id = employees.dept_id  -- 行ごとの相関条件
)
ORDER BY emp_id;

/*
  実行順序:
  1. FROM: employees(6行) を読み込む
  2. WHERE NOT EXISTS: 行ごとに相関サブクエリを評価
  3. ORDER BY: emp_id 昇順に出力
  */

解説（テーブル変化・ポイント） ▼

SELECT
  emp_id, name, dept_id
FROM   employees
WHERE  NOT EXISTS (
    SELECT 1
    FROM   managed_depts
    WHERE  managed_depts.dept_id = employees.dept_id
)
ORDER BY emp_id;

LEGEND ▼

データ取得・読込対象

① FROM employees（6行）/ managed_depts（3行） ▼

FROM employees / managed_deptsemployees テーブルの6行を読み込みます。managed_depts には dept_id=10（営業）・30（人事）・NULL（未定義）の3行があります。managed_depts の dept_id に NULL が含まれていることが NOT IN の罠を引き起こします。

1 / 4

emp_id	name	dept_id	managed_depts.dept_id（サブクエリの値）
1	田中	10	(10, 30, NULL)
2	鈴木	20	(10, 30, NULL)
3	佐藤	30	(10, 30, NULL)
4	伊藤	40	(10, 30, NULL)
5	山田	NULL	(10, 30, NULL)
6	高橋	20	(10, 30, NULL)

employees:6行 / managed_depts: (10, 30, NULL) — NULL 含む 3行

学習ポイント

NOT IN (subquery) にサブクエリが NULL を返すと全行が除外される：col NOT IN (a, b, NULL) は col <> a AND col <> b AND col <> NULL に展開されます。col <> NULL は UNKNOWN → AND チェーン全体 UNKNOWN → WHERE が FALSE として扱い全行除外されます。サブクエリの対象列が NULL になりうる限り、どれだけ正しく見えるクエリでも 0件を返す「タイムボム」になります。

NOT EXISTS は NULL の影響を受けない安全な代替手段：NOT EXISTS は「相関サブクエリが行を返さない場合に TRUE」を返します。WHERE sub.col = t.col で col が NULL の場合、NULL = NULL は UNKNOWN → サブクエリが行を返さない → EXISTS=FALSE → NOT EXISTS=TRUE → 対象行として正しく扱われます。NULL 安全な存在確認には NOT EXISTS が推奨されます。

代替案：NOT IN (SELECT col FROM t WHERE col IS NOT NULL) でサブクエリの NULL を除外する：サブクエリに WHERE col IS NOT NULL を追加してリストから NULL を取り除けば NOT IN も安全に使えます。ただしこの場合、自身の col が NULL の行（emp_id=5 の山田）の扱いも変わる点に注意が必要です。ビジネスロジック次第で NOT EXISTS か NOT IN+IS NOT NULL を使い分けてください。

アンチパターン

NOT IN (SELECT col FROM subquery) を「除外条件」と単純に信じて使う：サブクエリが NULL を返す可能性がある限り、NOT IN は全行 UNKNOWN で除外します。サブクエリが NULL を返さないことが保証できる場合のみ NOT IN を使用し、そうでない場合は NOT EXISTS か NOT IN + IS NOT NULL フィルタを使ってください。

「結果が 0件」を「データが存在しない」と誤解してデバッグに時間を浪費する：NOT IN + NULL のバグは、データが増えた後（NULL 行が混入した瞬間）に突然 0件になるため発見が遅れます。NOT IN を使う際は必ず SELECT COUNT(*) FROM subquery WHERE col IS NULL で NULL 件数を確認するか、NOT EXISTS に書き換えてください。

実務コラム：NOT IN vs NOT EXISTS の使い分けと発生頻度

NOT IN + NULL バグは「静かに壊す」NULL バグの中でも最頻出のものです。よく遭遇するシナリオ：① 除外リスト（blacklist）テーブルに NULL が入りうる設計（例：ban_list.user_id が NULL の行が存在）、② LEFT JOIN で生成した中間テーブルを NOT IN のサブクエリとして使う（LEFT JOIN 結果には NULL が含まれる）。dbt やデータパイプラインでは NOT EXISTS の方がオプティマイザに最適化されやすいケースも多く、パフォーマンス観点でも推奨されます。チームでは「NOT IN を使う際はコードレビューでサブクエリに IS NOT NULL を必ず確認する」ルールを設けると安全です。

QUESTION 7

集計関数と NULL の落とし穴 — COUNT(*) / COUNT(列) / AVG の分母が知らず縮む

COUNT / AVGNULL集計除外分母縮小問題COALESCE補正

前提知識 ▼

集計関数は NULL を自動的に無視します。COUNT(*) だけが全行をカウントし、COUNT(col)・SUM(col)・AVG(col) は NULL の行を除外します。特に AVG の分母縮小は「実績あり担当者だけの平均」が「全員平均」として報告される静かなバグになります。

-- 挙動の違い
COUNT(*)                  -- 全行カウント(NULL含む)
COUNT(amount)             -- 非NULL行のみカウント

-- AVGは NULL が除外され分母が縮む (SUM / COUNT(amount) と等価)
AVG(amount)

-- NULLを0として扱う全員平均
AVG(COALESCE(amount, 0))

AVG の「見えない分母縮小」：amount=NULL（実績なし）を AVG が除外すると、「実績ある担当者だけの平均」が「全担当者の平均」として報告されてしまいます。NULL が「0（実績なし）」を意味するなら COALESCE で補正した true_avg を、「欠損（集計不能）」を意味するなら AVG のまま使うか明示的に件数を表示してください。

問題 ▼

sales_results テーブルから、部署（dept）ごとの担当者数・実績あり担当者数・実績ありの平均売上・0実績を含む全員平均を集計してください。出力列は dept, head_count, active_reps, avg_amount, true_avg、dept 昇順で返してください。

使用テーブル

► sales_results（8行）

rep_id	dept	amount
1	東京	250000
2	東京	300000
3	東京	NULL
4	東京	NULL
5	大阪	150000
6	大阪	200000
7	大阪	180000
8	大阪	NULL

期待出力 ▼

dept	head_count	active_reps	avg_amount	true_avg
大阪	4	3	176667	132500
東京	4	2	275000	137500

模範解答コード ▼

SELECT
  dept,
  COUNT(*)                          AS head_count,   -- 全行(NULL含む)
  COUNT(amount)                     AS active_reps,  -- NULL除外
  ROUND(AVG(amount))               AS avg_amount,   -- NULL除外平均(分母縮小)
  ROUND(AVG(COALESCE(amount, 0)))  AS true_avg      -- 全員平均(0補正)
FROM   sales_results
GROUP BY dept
ORDER BY dept;

/*
  実行順序:
  1. FROM      → sales_results を読み込む
  2. GROUP BY  → 2グループに分割
  3. SELECT    → COUNT・AVG（NULL除外/COALESCE補正）
  4. ORDER BY  → dept 昇順に出力
  */

解説（テーブル変化・ポイント） ▼

SELECT
  dept,
  COUNT(*) AS head_count,
  COUNT(amount) AS active_reps,
  ROUND(AVG(amount)) AS avg_amount,
  ROUND(AVG(COALESCE(amount, 0))) AS true_avg
FROM   sales_results
GROUP BY dept
ORDER BY dept;

LEGEND ▼

データ取得・読込対象

① FROM sales_results（8行） ▼

FROM sales_resultssales_results テーブルの8行を読み込みます。東京の rep_id=3,4 と大阪の rep_id=8 が amount=NULL（当月実績なし）です。これらが COUNT と AVG にどう影響するかがこの問題のポイントです。

1 / 5

rep_id	dept	amount
1	東京	250000
2	東京	300000
3	東京	NULL
4	東京	NULL
5	大阪	150000
6	大阪	200000
7	大阪	180000
8	大阪	NULL

8行読込（NULL: 東京2行, 大阪1行）

学習ポイント

COUNT(*) は全行、COUNT(col) は非 NULL 行のみをカウントする：COUNT(*) は NULL を含む全行をカウントします。COUNT(col) は NULL の行を除外してカウントします。この差が「欠損件数」を示す重要な指標になります。COUNT(*) - COUNT(col) で NULL になっている行数を確認できます。

AVG(col) は NULL 除外の分母で計算される — 分母が縮む：AVG(amount) は SUM(amount) / COUNT(amount) と等価で、NULL を除いた行数が分母になります。NULL が「実績 0」を意味する場合、AVG が高く見える「見せかけの高パフォーマンス」になります。AVG(COALESCE(amount, 0)) で NULL を 0 として全員を分母に含める true_avg と比較して判断してください。

SUM と COUNT の組み合わせで AVG を自分で計算する方法：NULL の扱いを完全にコントロールしたい場合は SUM(amount) / NULLIF(COUNT(*), 0) でゼロ除算を防ぎながら「全員分母の平均」を計算できます。NULLIF(COUNT(*), 0) は0件グループで結果が NULL になる（エラーにならない）ため安全です。

アンチパターン

AVG(amount) を「全担当者の平均」と誤解してダッシュボードに表示する：NULL が「実績なし＝0」を意味するのに AVG(amount) を使うと、実績ある担当者だけの平均が全担当者の平均として報告されます。必ず head_count と active_reps を並べて表示し、レポートを読む人が文脈を理解できるようにしてください。

COUNT(*) と COUNT(col) を混同してレポートを作成する：「担当者8人のうち売上 n 円」と書きたいのに COUNT(amount) を使うと NULL 行が除かれた件数（5人）になります。分母となる「母集団の件数」には COUNT(*) を使い、「有効な値の件数」には COUNT(col) を使うよう意識的に使い分けてください。

実務コラム：KPI 計算で NULL をどう扱うか——「欠損」か「ゼロ」かを定義する

分析 SQL で最も重要な設計上の決断の一つが「NULL は欠損データか、意図的なゼロか」を明確にすることです。欠損（測定不能）なら AVG や SUM で自動除外されることが正しい動作です。意図的なゼロ（実績なし）なら COALESCE で補正してから集計するのが正しい設計です。dbt の staging モデルでは COALESCE(amount, 0) AS amount を施して「下流では NULL を意識しない」早期補正パターンが推奨されます。また COUNT(*) - COUNT(col) をデータ品質モニタリングの指標として定期的に確認することで、NULL 混入を早期発見できます。

QUESTION 8

LEFT JOIN の ON 句フィルタ — 非マッチ行を NULL で保持する挙動

LEFT JOINON 句フィルタ結合時の条件評価非マッチ行の NULL 保持

前提知識 ▼

LEFT JOIN の目的は「左テーブルの全行を保持すること」です。右テーブルの列に対する条件（例：特定の金額以上の注文など）を指定しつつ、条件に合わない左テーブルの行も NULL として保持したい場合は、ON 句にフィルタ条件を記述します。

-- ON 句に書けば、非マッチ行も NULL として保持される
FROM customers c
LEFT JOIN orders o
       ON c.customer_id = o.customer_id
      AND o.amount > 20000

ON 句の評価タイミング：ON 句は結合時に評価され、条件を満たさない行は結合されず、左テーブルの行が NULL と結合されて保持されます。右テーブルをフィルタして「条件に合わない行も NULL で表示したい」場合は ON 句に条件を書いてください。

問題 ▼

customers テーブルの全顧客について、金額が 20,000 円を超える注文（大口注文）の最大金額（large_order）を表示してください。大口注文がない顧客または注文自体がない顧客は large_order を NULL で表示してください。出力列は customer_id, customer_name, large_order、customer_id 昇順で返してください。

使用テーブル

► customers（4行）

customer_id	customer_name
1	田中商事
2	鈴木物産
3	佐藤商会
4	新規顧客

► orders（7行）

order_id	customer_id	amount
1	1	10000
2	1	35000
3	1	40000
4	2	28000
5	2	8000
6	3	5000
7	3	12000

期待出力 ▼

customer_id	customer_name	large_order
1	田中商事	40000
2	鈴木物産	28000
3	佐藤商会	NULL
4	新規顧客	NULL

模範解答コード ▼

SELECT
  c.customer_id,
  c.customer_name,
  MAX(o.amount)  AS large_order
FROM       customers c
LEFT JOIN  orders o
        ON  c.customer_id = o.customer_id
       AND o.amount > 20000          -- ON句でフィルタ(非マッチ顧客はNULLで保持)
GROUP BY   c.customer_id, c.customer_name
ORDER BY   c.customer_id;

/*
  実行順序:
  1. FROM customers                     → 読み込む
  2. LEFT JOIN ... ON ... AND o.amount  → 条件付き結合（未一致はNULL保持）
  3. GROUP BY                           → 顧客でグループ化
  4. SELECT MAX                         → 最大値を集計（未一致はNULL）
  5. ORDER BY                           → customer_id 昇順に出力
  */

解説（テーブル変化・ポイント） ▼

SELECT
  c.customer_id,
  c.customer_name,
  MAX(o.amount) AS large_order
FROM       customers c
LEFT JOIN  orders o
        ON  c.customer_id = o.customer_id
       AND o.amount > 20000GROUP BY   c.customer_id, c.customer_name
ORDER BY   c.customer_id;

LEGEND ▼

データ取得・読込対象

① FROM customers（4行）/ orders（7行） ▼

FROM customers c / orders ocustomers テーブルの4行と orders テーブルの7行を読み込みます。顧客1（田中）は大口注文が2件、顧客3（佐藤）は注文があるものの全て 20,000円以下です。顧客4は注文がありません。

1 / 5

c.customer_id	c.customer_name	o.order_id	o.amount
1	田中商事	1	10000
1	田中商事	2	35000
1	田中商事	3	40000
2	鈴木物産	4	28000
2	鈴木物産	5	8000
3	佐藤商会	6	5000
3	佐藤商会	7	12000
4	新規顧客	（なし）	（なし）

customers:4行 / orders:7行

学習ポイント

ON 句のフィルタは結合時に評価され、非マッチ行は NULL で保持される：ON 句に書いた条件は結合処理の一部として評価されます。条件を満たさない右テーブルの行は結合されず、左テーブルの行は NULL に置き換えられて保持されます。「20,000 円超の注文のある顧客だけでなく全顧客を表示し、条件に合う注文があればその金額を表示したい」という要件に正確に対応します。

複数条件による柔軟な結合：LEFT JOIN の ON 句では、キーの等値結合（c.customer_id = o.customer_id）だけでなく、対象データを絞り込む条件（o.amount > 20000）も同時に記述できます。これにより、事前の中間テーブル作成やサブクエリを使わずに、結合とフィルタリングを1ステップで簡潔に行えます。

アンチパターン

結合前にサブクエリで過剰に絞り込む：ON 句でフィルタできるにもかかわらず、わざわざ LEFT JOIN (SELECT * FROM orders WHERE amount > 20000) o のようにサブクエリを書いてしまうケースがあります。ON 句の条件を活用することで、クエリをよりシンプルで読みやすく保つことができます。

実務コラム：ON 句フィルタの活用とパフォーマンス

ON 句でのフィルタリングは、「マスターテーブルの全件を基準に、特定条件のトランザクションデータだけを結合して集計したい」というダッシュボードやレポート作成のクエリで頻繁に使用されます。例えば、「全商品のリストに対して、当月の売上だけを結合する（ON p.id = s.product_id AND s.month = '2024-10'）」といったユースケースです。ON 句を活用することで、可読性が上がるだけでなく、データベースのオプティマイザが効率的な結合計画を選択しやすくなるメリットもあります。

QUESTION 9

NULLIF でゼロ除算を NULL に変える — NULL を防御的に武器として使う

NULLIFゼロ除算防止NULL を正常値として活用COALESCE との組み合わせ

前提知識 ▼

NULLIF(expr, value) は expr = value のとき NULL を返し、そうでなければ expr をそのまま返す関数です。ゼロ除算防止に使うことで、エラーではなく NULL を安全に返せます。

-- 基本: a = b なら NULL、それ以外は a を返す
NULLIF(a, b)

-- ゼロ除算防止 (units=0 の場合 NULL を返し、結果も NULL になる)
revenue / NULLIF(units, 0)

-- ゼロ除算時に 0 を返したい場合
COALESCE(revenue / NULLIF(units, 0), 0)

NULL を武器として使う：NULL は「不明」を意味しますが、NULLIF でゼロを NULL に変換することで「ゼロ除算を安全にスキップする」という積極的な活用法があります。NULL が伝播して結果も NULL になることを「意図的に利用する」設計パターンです。

問題 ▼

campaign_daily テーブルの各行（日次・キャンペーン単位のレコード）について、クリック数・コンバージョン率（conv_rate = conversions / clicks）・クリック単価（rev_per_click = revenue / clicks）を計算して出力してください。clicks が 0 の行は conv_rate / rev_per_click を NULL で表示してください（ゼロ除算を NULLIF で防ぐこと）。出力列は date, campaign, clicks, conv_rate, rev_per_click、date・campaign 昇順で返してください。

使用テーブル

► campaign_daily（6行）

date	campaign	clicks	conversions	revenue
2024-03-01	CP_A	1000	50	200000
2024-03-01	CP_B	0	0	0
2024-03-02	CP_A	800	32	160000
2024-03-02	CP_B	500	20	100000
2024-03-03	CP_A	0	0	0
2024-03-03	CP_B	600	24	120000

期待出力 ▼

date	campaign	clicks	conv_rate	rev_per_click
2024-03-01	CP_A	1000	0.0500	200.0000
2024-03-01	CP_B	0	NULL	NULL
2024-03-02	CP_A	800	0.0400	200.0000
2024-03-02	CP_B	500	0.0400	200.0000
2024-03-03	CP_A	0	NULL	NULL
2024-03-03	CP_B	600	0.0400	200.0000

模範解答コード ▼

SELECT
  date,
  campaign,
  clicks,
  ROUND(conversions::numeric / NULLIF(clicks, 0), 4) AS conv_rate,    -- clicks=0時はNULL化し除算エラー回避
  ROUND(revenue::numeric    / NULLIF(clicks, 0), 4) AS rev_per_click
FROM   campaign_daily
ORDER BY date, campaign;

/*
  実行順序:
  1. FROM      → campaign_daily を読み込む
  2. SELECT    → NULLIFで0除算回避しROUND
  3. ORDER BY  → date, campaign 昇順に出力
  */

解説（テーブル変化・ポイント） ▼

SELECT
  date, campaign, clicks,
  ROUND(
    conversions::numeric / NULLIF(clicks, 0),
    4
  ) AS conv_rate,
  ROUND(
    revenue::numeric    / NULLIF(clicks, 0),
    4
  ) AS rev_per_click
FROM   campaign_daily
ORDER BY date, campaign;

LEGEND ▼

データ取得・読込対象

① FROM campaign_daily（6行） ▼

FROM campaign_dailycampaign_daily テーブルの6行を読み込みます。CP_B（2024-03-01）と CP_A（2024-03-03）は clicks=0（配信なし日）です。これらの行でそのまま除算するとゼロ除算エラーになります。

1 / 4

date	campaign	clicks	conversions	revenue
2024-03-01	CP_A	1000	50	200000
2024-03-01	CP_B	0	0	0
2024-03-02	CP_A	800	32	160000
2024-03-02	CP_B	500	20	100000
2024-03-03	CP_A	0	0	0
2024-03-03	CP_B	600	24	120000

6行読込（clicks=0 が 2行 → そのまま除算するとエラー）

学習ポイント

NULLIF(expr, value) は expr = value のとき NULL を返す：NULLIF(a, b) は CASE WHEN a = b THEN NULL ELSE a END と等価です。ゼロ除算防止の最頻出ユースケースとして x / NULLIF(y, 0) が使われます。y=0 のとき NULL を返し、その後の除算で NULL 伝播が起きてエラーにならず NULL として出力されます。

COALESCE との組み合わせでデフォルト値を返せる：COALESCE(x / NULLIF(y, 0), 0) とすると y=0 のとき 0 が返ります。NULL のまま出力したい場合は NULLIF のみ、特定のデフォルト値に変換したい場合は COALESCE を組み合わせるのが実務の基本パターンです。NULL か 0 のどちらが意味的に正しいかはビジネスロジックで判断してください。

PostgreSQL の ROUND(x, n) は numeric 型が必要：PostgreSQL では ROUND(float, n) は直接サポートされないため、ROUND(x::numeric, n) と明示的に numeric へキャストします。integer 列を numeric にキャストしてから除算（conversions::numeric / NULLIF(clicks, 0)）することで精度を保った小数計算が可能です。

アンチパターン

ゼロ除算を CASE WHEN で冗長に書く：CASE WHEN clicks = 0 THEN NULL ELSE conversions::numeric / clicks END は動作しますが冗長です。conversions::numeric / NULLIF(clicks, 0) の方が短く意図が明確で、PostgreSQL・MySQL・BigQuery・Snowflake など主要 DBMS で動作します。

ゼロ除算対策として WHERE clicks > 0 でフィルタする：WHERE でゼロクリックの行を除外してしまうと、「配信なし日のデータが消える」という別の問題が発生します。「配信なし日の conv_rate は NULL（計算不能）として表示する」という要件には WHERE 除外ではなく NULLIF が正しい解法です。

実務コラム：NULLIF の活用場面 — ゼロ除算だけではない

NULLIF はゼロ除算防止以外にも活躍します。① 空文字列を NULL として扱う：NULLIF(col, '') で空文字を NULL に変換し、以降の処理で COALESCE や IS NULL で扱えるようにする（CSV インポート後のクリーニングで頻出）。② 特定のセンチネル値を NULL として扱う：NULLIF(error_code, -1) でエラー値 -1 を NULL に変換して正常値のみを集計する。③ 比率計算の変化率：前月比 ROUND((current - prev) / NULLIF(prev, 0), 4) は Q10 で登場します。NULLIF は「ある値を NULL として扱いたい」という設計意図を 1 関数で表現できる強力なツールです。

QUESTION 10

ウィンドウ関数と NULL 伝播 — LAG の境界 NULL と前月比の連鎖 NULL

LAG / ウィンドウ関数NULL 伝播チェーンCTE パターン前月比・時系列分析

前提知識 ▼

LAG(col, offset, default) は前の行の値を返しますが、指定した列自体が NULL の場合と、最初の行で前の行が存在しない場合（境界 NULL）の2つの NULL の発生源があります。これらを区別し、意図しない NULL 伝播を防ぐ設計が必要です。

-- ① 境界 NULL のみ防ぐ（最初の行は 0 になるが、前行が NULL の場合は NULL が返る）
LAG(revenue, 1, 0) OVER (ORDER BY month)

-- ② 境界 NULL とデータ自体の NULL の両方を完全に 0 に防ぐ（実務パターン）
LAG(COALESCE(revenue, 0), 1, 0) OVER (ORDER BY month)

2つの NULL の防御：LAG の第3引数は「前の行が存在しない場合（最初の行など）」にのみ適用される値です。「前の行は存在するが、その値自体が NULL の場合」は第3引数では防げず、NULL がそのまま返ってしまいます。実務では 対象列を COALESCE で保護し、さらに LAG の第3引数も指定する ことで、どんな状況でも確実なデフォルト値（0など）を取得して計算を行うのが鉄則です。

問題 ▼

monthly_revenue テーブルから、月ごとの売上（revenue）・前月売上（prev_revenue）・前月比成長率（mom_growth）を計算してください。
データ未収集で revenue が NULL の月は、売上 0 として扱ってください。また、LAG() を使って前月売上を取得する際も、最初の月や前月が売上 0（NULL を補正したものを含む）の場合は 0 として取得してください。
mom_growth = ROUND((当月 - 前月) / 前月, 4) で計算し、前月が 0 で計算できない場合（ゼロ除算）は NULL で表示してください。出力列は month, revenue, prev_revenue, mom_growth、month 昇順で返してください。

使用テーブル

► monthly_revenue（6行）

month	revenue
2024-01	1000000
2024-02	1200000
2024-03	NULL
2024-04	1100000
2024-05	1400000
2024-06	1350000

期待出力 ▼

month	revenue	prev_revenue	mom_growth
2024-01	1000000	0	NULL
2024-02	1200000	1000000	0.2000
2024-03	0	1200000	-1.0000
2024-04	1100000	0	NULL
2024-05	1400000	1100000	0.2727
2024-06	1350000	1400000	-0.0357

模範解答コード ▼

WITH lagged AS (
  SELECT
    month,
    COALESCE(revenue, 0)  AS revenue,  -- 未収集月を 0 補正
    LAG(COALESCE(revenue, 0), 1, 0) OVER (ORDER BY month) AS prev_revenue  -- 境界/前月NULLも 0 取得
  FROM   monthly_revenue
)
SELECT
  month,
  revenue,
  prev_revenue,
  ROUND(
    (revenue - prev_revenue)::numeric / NULLIF(prev_revenue, 0), 4  -- ゼロ除算時はNULL
  ) AS mom_growth
FROM   lagged
ORDER BY month;

/*
  実行順序:
  1. CTE lagged
  2. 外側の SELECT
  3. ORDER BY: month 昇順に出力
  */

解説（テーブル変化・ポイント） ▼

WITH lagged AS (
  SELECT
    month,
    COALESCE(revenue, 0) AS revenue,
    LAG(COALESCE(revenue, 0), 1, 0) OVER (ORDER BY month) AS prev_revenue
  FROM   monthly_revenue
)
SELECT
  month, revenue, prev_revenue,
  ROUND(
    (revenue - prev_revenue)::numeric
    / NULLIF(prev_revenue, 0),
    4
  ) AS mom_growth
FROM   lagged
ORDER BY month;

LEGEND ▼

データ取得・読込対象

① FROM monthly_revenue（6行） ▼

FROM monthly_revenuemonthly_revenue テーブルの6行を読み込みます。2024-03 の revenue が NULL（データ未収集）です。この NULL が原因で意図しない伝播が起きないよう、能動的にコントロールしていきます。

1 / 5

month	revenue
2024-01	1000000
2024-02	1200000
2024-03	NULL
2024-04	1100000
2024-05	1400000
2024-06	1350000

6行読込（2024-03 revenue=NULL）

学習ポイント

NULL を放置せず能動的にコントロールする（防御的プログラミング）：計算式の中に NULL が混入すると、結果全体が意図せず NULL になってしまう「NULL 伝播」が起こります。これを防ぐために、計算前に COALESCE で明確なデフォルト値（今回は 0）に補正する ことが実務では極めて重要です。

LAG の「境界 NULL」と「値の NULL」を二段構えで防ぐ：LAG関数の第3引数（default）は、最初の行など「前の行が存在しない」場合に返される境界 NULL を防ぎます。しかし、前の行の値自体が NULL の場合は機能しません。LAG(COALESCE(revenue, 0), 1, 0) と書くことで、前月が NULL の場合（COALESCE で 0 に補正）も、最初の月の場合（第3引数で 0 を指定）も、どんな状況でも安全に 0 を取得できます。

ゼロ除算だけは NULLIF で防ぐ（二段構えの防壁）：NULL を 0 に補正した後、そのまま割り算を行うと「ゼロ除算エラー」でクエリが止まります。これを回避するため、分母に対してのみ NULLIF(prev_revenue, 0) を適用し、0 を意図的に NULL に変換して安全な計算不能（NULL）として出力 します。

アンチパターン

「NULL は無視される」と勘違いして、そのまま計算に渡す：SUM や AVG と異なり、四則演算（+, -, *, /）は NULL を無視しません。1つでも NULL が含まれれば結果は必ず NULL になります。時系列分析や KPI 算出では、必ず CTE 内で COALESCE を使って NULL をクレンジングしてから計算を行ってください。

revenue=NULL の月のデータを「前方補完」せずにそのまま使う（別のアプローチ）：今回はビジネス要件として「0 として扱う」ケースを紹介しましたが、実務では「前月の値で埋める（前方補完）」ことが求められるケースも多々あります。前方補完には BigQuery の LAST_VALUE(revenue IGNORE NULLS) OVER (...) や Snowflake の LAST_VALUE(revenue) IGNORE NULLS OVER (...) が使えます。PostgreSQL は IGNORE NULLS をサポートしていないため、CTE で欠損行を除外してから LAG を使うか、累積グループと MAX などで前方補完を実装します。ビジネス要件がどちらに該当するかを常に確認してください。

実務コラム：時系列分析での NULL 処理戦略

月次 KPI の前月比計算は最も頻繁に依頼される分析の一つです。実務での NULL 処理戦略は 2 つに分かれます。① ゼロ扱い（今回の手法）：データ未収集＝活動なしとして 0 で計算し、急激なマイナス成長として検知する。② 前方補完（ffill）で NULL を埋める：直前の非 NULL 値で NULL を埋めてから前月比を計算する（スナップショットテーブルの手法）。dbt では surrogate key + date spine（全月リスト）との LEFT JOIN でデータのない月を 0 や前月値で補完する「date spine パターン」が標準的です。どちらの戦略を選ぶかはビジネスロジックで決定し、SQL コメントや dbt の description に明記することが重要です。