SQL カーディナリティ — FULL OUTER JOIN・FILTERの応用

QUESTION 6

N番目のレコード抽出 — ROW_NUMBER(rn=2) と LEFT JOIN で「2回目の購入」を取得する

ROW_NUMBER応用1:N→0..1LEFT JOINON句の追加条件

前提知識 ▼

基礎編では ROW_NUMBER + rn=1 で「最新の1件」を取得しました。応用編では「N番目」の任意の行を取り出します。ポイントは2つ。① ORDER BY の向きが「何番目」の意味を決めること、② 該当行が存在しないユーザーを保持するために LEFT JOIN が必要になることです。

-- ORDER BY の向きで rn の意味が変わる
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at DESC)  -- rn=1 が「最新」
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at ASC)   -- rn=2 が「2回目」

rn = 2 をどこに書くか — ON句と WHERE句の決定的な違い：
LEFT JOIN ... ON ... AND r.rn = 2 → 購入1件のユーザーも NULL行で保持される。
LEFT JOIN ... WHERE r.rn = 2 → NULL行は NULL = 2 が UNKNOWN となり結果から消える（事実上のINNER JOIN化）。

問題 ▼

すべての顧客について、2回目に購入した商品名（second_product）とその日時（second_ordered_at）を取得してください。購入が1件以下の顧客も NULL で必ず出力すること。

使用テーブル

▸ customers

customer_id	name
1	田中
2	佐藤
3	山田

▸ orders

order_id	customer_id	product_name	ordered_at
101	1	キーボード	2024-04-01 10:00
102	1	マウス	2024-04-03 14:30
103	1	モニター	2024-04-10 09:15
104	2	イヤホン	2024-04-02 18:00
105	2	充電器	2024-04-08 12:00
106	3	ケーブル	2024-04-05 11:00

期待出力 ▼

name	second_product	second_ordered_at
田中	マウス	2024-04-03 14:30
佐藤	充電器	2024-04-08 12:00
山田	NULL	NULL

模範解答コード ▼

WITH ranked_orders AS (
  SELECT
    customer_id,
    product_name,
    ordered_at,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY ordered_at ASC   -- 古い順 → rn=2 が「2回目の購入」
    ) AS rn
  FROM orders
)
SELECT
  c.name,
  r.product_name AS second_product,
  r.ordered_at   AS second_ordered_at
FROM customers AS c
LEFT JOIN ranked_orders AS r
  ON  c.customer_id = r.customer_id
  AND r.rn = 2                -- ON句に書く: 該当なし顧客はNULL行で保持
ORDER BY c.customer_id;

/*
  実行順序:
  1. CTE ranked_orders        → orders を読み込む
  2. ROW_NUMBER() OVER (...)  → 顧客ごとに古い順で番号付け
  3. FROM customers AS c      → customers を読み込む
  4. LEFT JOIN (rn=2)         → 2回目購入のみ結合
  5. SELECT                   → 3列を射影
  6. ORDER BY c.customer_id   → 並び替えて出力
  */

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

WITH ranked_orders AS (
  SELECT
    customer_id,
    product_name,
    ordered_at,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY ordered_at ASC
    ) AS rn
  FROM orders
)
SELECT
  c.name,
  r.product_name AS second_product,
  r.ordered_at   AS second_ordered_at
FROM customers AS c
LEFT JOIN ranked_orders AS r
  ON  c.customer_id = r.customer_id
  AND r.rn = 2
ORDER BY c.customer_id;

LEGEND ▼

データ取得・読込対象

① CTE — 元データ ▼

FROM orders（1:N の購入履歴）orders テーブル（6行）を読み込みます。顧客ごとに購入回数が 3件・2件・1件とバラバラです。この中から「各顧客の2回目」という特定の1行を取り出すのが目標です。

1 / 7

order_id	customer_id	product_name	ordered_at
101	1	キーボード	2024-04-01 10:00
102	1	マウス	2024-04-03 14:30
103	1	モニター	2024-04-10 09:15
104	2	イヤホン	2024-04-02 18:00
105	2	充電器	2024-04-08 12:00
106	3	ケーブル	2024-04-05 11:00

orders: 6行（顧客ごとの購入回数は 3 / 2 / 1 件）

学習ポイント

N-TH ROW EXTRACTION

ROW_NUMBER(ASC) + rn=2 — 「N番目」を 0..1 の関係に変換して結合

該当行が存在しない顧客もNULLで保持するのが応用の核心
orders(1:N) → rn=2に絞る(0..1) → customers と LEFT JOIN

カーディナリティが 1:1 ではなく「0..1」になる：rn=1（最新）は全顧客に必ず存在しますが、rn=2 は購入2件以上の顧客にしか存在しません。つまり customers から見た関係は 1:1 ではなく 0..1（オプショナル）です。関係が 0..1 になった瞬間、INNER JOIN では行が欠落するため LEFT JOIN が必須になります。「N番目」の N が大きいほど該当なしが増える——この関係性の変化を読むのが応用力です。

ORDER BY の向きと rn の意味の対応：ORDER BY ordered_at ASC なら rn=2 は「2回目（古い方から2番目）」、DESC なら rn=2 は「最新から2番目（前回）」です。同じ rn=2 でも意味が真逆になるため、要件の日本語（「2回目」か「直近の1つ前」か）を ORDER BY に正確に翻訳する習慣が重要です。

アンチパターン

WHERE r.rn = 2 と書いて「該当なし」顧客を消す：LEFT JOIN で保持された NULL 行に対して WHERE で r.rn = 2 を評価すると NULL = 2 は UNKNOWN となり行が除外されます。右テーブル列への条件は ON 句に、左テーブル列への条件は WHERE 句に——LEFT JOIN におけるこの使い分けは実務SQLの最重要原則の1つです。

OFFSET 1 LIMIT 1 で代替しようとする：ORDER BY ordered_at OFFSET 1 LIMIT 1 は「テーブル全体の2行目」であり「顧客ごとの2回目」ではありません。顧客ごとに相関サブクエリで書くことも可能ですが、顧客数だけサブクエリが実行され性能が劣化します。グループごとのN番目は ROW_NUMBER の独壇場です。

実務コラム

ECやアプリ分析で頻出の「F2転換分析」（初回購入者が2回目を購入したか＝リピート転換率）はまさに本問のクエリです。他にも 2番目に高い給与の社員（面接の定番問題: ORDER BY salary DESC で rn=2）、オンボーディングの2回目利用日の取得など、「rn=Nの行を取り出し、該当なしはNULL」というパターンは分析SQLの基本装備です。rn=1 との違いは 0..1 関係を意識して LEFT JOIN を選ぶ判断にあります。

QUESTION 7

完全外部結合（FULL OUTER JOIN） — 2システムの商品マスタを突合して差分を検出する

FULL OUTER JOINCOALESCE差分検出3値論理（NULL比較）

前提知識 ▼

基幹システムとECサイト——同じ「商品マスタ」が2か所にあるとき、両者のズレ（差分）を見つける作業を突合（とつごう）と呼びます。LEFT JOIN は「左にしかない行」しか保持できませんが、突合では「右にしかない行」も同時に検出する必要があります。そこで登場するのが、両側の行をすべて保持するFULL OUTER JOIN（完全外部結合）です。

-- 結合結果は3つのゾーンに分かれる
--   ① 両方に存在（キー一致）     → 値の比較が可能
--   ② 左にしか存在しない         → 右側の列はすべて NULL
--   ③ 右にしか存在しない         → 左側の列はすべて NULL
FULL OUTER JOIN ... ON a.product_id = b.product_id

NULL との比較は UNKNOWN（3値論理）：
片側にしかない行では a.price <> b.price が「100 <> NULL」となり、TRUE でも FALSE でもなく UNKNOWN と評価されます。WHERE は UNKNOWN の行を通しません。つまり「値の不一致」条件だけでは未登録行を拾えない——突合クエリの最大の落とし穴です。IS NULL 条件を OR で明示的に並べるか、NULL 安全な比較演算子を使います。

問題 ▼

基幹システムの products_kikan と ECサイトの products_ec を product_id で突合し、差分がある商品だけを抽出してください。差分は3種類——価格不一致 / ECに未登録 / 基幹に未登録——を status 列で示し、両側の価格を並べて出力すること。完全に一致する商品（id=101）は出力しません。

使用テーブル

▸ products_kikan

product_id	name	price
101	ノート	300
102	ペン	150
103	消しゴム	100
104	定規	200

▸ products_ec

product_id	name	price
101	ノート	300
102	ペン	180
105	マーカー	250

期待出力 ▼

product_id	name	price_kikan	price_ec	status
102	ペン	150	180	価格不一致
103	消しゴム	100	NULL	ECに未登録
104	定規	200	NULL	ECに未登録
105	マーカー	NULL	250	基幹に未登録

模範解答コード ▼

SELECT
  COALESCE(a.product_id, b.product_id) AS product_id,  -- 片側NULLでもキーを復元
  COALESCE(a.name, b.name)             AS name,
  a.price AS price_kikan,
  b.price AS price_ec,
  CASE
    WHEN a.product_id IS NULL THEN '基幹に未登録'
    WHEN b.product_id IS NULL THEN 'ECに未登録'
    WHEN a.price <> b.price  THEN '価格不一致'
  END AS status
FROM products_kikan AS a
FULL OUTER JOIN products_ec AS b
  ON a.product_id = b.product_id
WHERE a.product_id IS NULL      -- ③ 基幹に未登録
   OR b.product_id IS NULL      -- ② ECに未登録
   OR a.price <> b.price        -- ① 価格不一致（NULL行はUNKNOWNだが上2条件が救済）
ORDER BY product_id;

-- FULL OUTER JOIN 非対応のDB（MySQL 8.0以前等）への移植版:
--   LEFT JOIN の結果
--   UNION
--   RIGHT JOIN の結果（または左右を入れ替えた LEFT JOIN）
-- で同じ3ゾーンを再現できます。

/*
  実行順序:
  1. FROM products_kikan AS a          → 基幹マスタを読み込む
  2. FULL OUTER JOIN products_ec AS b  → 両表を突合
  3. WHERE 差分条件                        → 完全一致を除外
  4. SELECT                            → COALESCEで復元しCASEで分類
  5. ORDER BY product_id               → 並び替えて出力
  */

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

SELECT
  COALESCE(a.product_id, b.product_id) AS product_id,
  COALESCE(a.name, b.name) AS name,
  a.price AS price_kikan,
  b.price AS price_ec,
  CASE
    WHEN a.product_id IS NULL THEN '基幹に未登録'
    WHEN b.product_id IS NULL THEN 'ECに未登録'
    WHEN a.price <> b.price THEN '価格不一致'
  END AS status
FROM products_kikan AS a
FULL OUTER JOIN products_ec AS b
  ON a.product_id = b.product_id
WHERE a.product_id IS NULL
   OR b.product_id IS NULL
   OR a.price <> b.price
ORDER BY product_id;

LEGEND ▼

データ取得・読込対象

① 左テーブル — 基幹システムの商品マスタ ▼

FROM products_kikan AS a基幹システム側のマスタ（4行）です。突合の前提は「product_id がそれぞれのテーブル内で一意」であること。つまり対応関係は 1:0..1 ——相手側に「ある（1）か、ない（0）か」のどちらかです。

1 / 7

product_id	name	price
101	ノート	300
102	ペン	150
103	消しゴム	100
104	定規	200

products_kikan: 4行（product_id はテーブル内で一意）

学習ポイント

FULL OUTER JOIN

完全外部結合 — 両側の行をすべて保持して突合する

一致＋左のみ＋右のみ＝結合後の行数
NULL の位置がゾーン（差分の種類）を教えてくれる

突合のカーディナリティは 1:0..1、行数は「和集合」：両テーブルのキーが一意なら、FULL OUTER JOIN の行数は 一致件数＋左のみ＋右のみ（本問: 2+2+1=5行）——つまりキーの和集合と一致します。逆に言うと、結合後の行数が和集合より多ければどちらかのキーに重複があるサイン（1:N や N:N に化けて膨張）。突合クエリを書く前に「両側のキーは一意か」を確認するのが実務の作法です。

NULL の位置が情報になる：内部結合では NULL は「データ欠損」ですが、外部結合の NULL は「相手側に行がなかった」という構造情報です。CASE 式は a 側 / b 側どちらが NULL かを読んで status を分類し、COALESCE は「NULL でない側」からキーを復元します。なお PostgreSQL なら a.price IS DISTINCT FROM b.price という NULL 安全な比較演算子で、IS NULL の OR 並べを1条件に畳むこともできます（NULL と値の比較も TRUE/FALSE で返る）。

アンチパターン

LEFT JOIN による片側突合：左側の視点しか持たないため、「右にしかない行」（本問の105）は結合結果に行ごと存在せず、どんな WHERE を書いても検出できません。エラーも警告も出ないまま差分の半分を見落とします。双方向の差分が要件なら FULL OUTER JOIN、非対応のDBでは LEFT JOIN ... UNION ... RIGHT JOIN（または左右入替の LEFT JOIN）で3ゾーンを再現します。

「a.price <> b.price」だけで差分検出したつもり：未登録行では比較が UNKNOWN になり、WHERE で全滅——出力が「価格不一致」だけになります（4行のはずが1行に）。NULL が混ざる前提の列を比較するときは、IS NULL の分岐を必ずセットで書くこと。3値論理（TRUE / FALSE / UNKNOWN）は外部結合と組み合わさった瞬間に牙をむきます。

実務コラム

FULL OUTER JOIN による突合は「2つのデータソースの整合性確認」という実務の定番です。応用例: システム移行のデータ検証（旧DB×新DBを全件突合し、欠落・余剰・値ズレを一括レポート）、経理の入出金突合（銀行明細×売掛データ — 消込候補と未消込を同時に抽出）、在庫の棚卸差異（システム在庫×実地棚卸）、ETLパイプラインの検証（転送元×転送先の件数・金額照合）。どれも「左右どちらの欠落も見逃せない」業務であり、LEFT JOIN 2本を別々に流すより、FULL OUTER JOIN 1本で3ゾーンを一望するほうが安全で読みやすくなります。

QUESTION 8

階層ロールアップ — 自己結合で子部門の売上・目標を親部門に丸め上げる

自己結合階層構造事前集約ファントラップ回避

前提知識 ▼

組織や商品カテゴリの親子関係は、同一テーブル内の自己参照（隣接リストモデル: parent_id が同じテーブルの dept_id を指す）で表現するのが定石です。子の値を親単位に集計する操作をロールアップ（Roll-up）と呼び、自己結合で親の情報を付与してから GROUP BY します。

-- 隣接リストモデル: 1テーブルで親子を表現
departments(dept_id, dept_name, parent_id)
  -- parent_id = NULL → 親部門（本部）/ parent_id = 1 → dept_id=1 の子部門

-- 子→親のロールアップは「同一テーブルの自己結合」（N:1 なので行数は増えない）
FROM departments AS c                              -- c = 子部門の顔
JOIN departments AS p ON c.parent_id = p.dept_id  -- p = 親部門の顔

階層 × ファントラップ：子部門には売上 sales（1:N）と目標 dept_targets（1:1）の両方がぶら下がっています。売上を事前集約せずに両方を同時にJOINすると、目標が売上行数分コピーされて膨張します（ファントラップが階層構造でも発生）。

問題 ▼

親部門ごとに、配下の子部門の売上合計（total_sales）・目標合計（total_target）・達成率（achievement_rate: %, 小数点1桁）を取得してください。売上を子部門粒度に事前集約してから、自己結合で親部門に丸め上げる正しいアプローチで解いてください。

使用テーブル

▸ departments

dept_id	dept_name	parent_id
1	営業本部	NULL
2	開発本部	NULL
11	国内営業	1
12	海外営業	1
21	アプリ開発	2
22	基盤開発	2

▸ sales

sale_id	dept_id	amount
1	11	300000
2	11	150000
3	12	400000
4	21	500000
5	21	250000
6	22	250000

▸ dept_targets

dept_id	target
11	400000
12	500000
21	800000
22	300000

期待出力 ▼

parent_dept	total_sales	total_target	achievement_rate
営業本部	850000	900000	94.4
開発本部	1000000	1100000	90.9

模範解答コード ▼

WITH child_sales AS (
  SELECT dept_id, SUM(amount) AS sales_total
  FROM sales
  GROUP BY dept_id              -- 1:N → 1:1（子部門粒度に丸める）
)
SELECT
  p.dept_name AS parent_dept,
  SUM(cs.sales_total) AS total_sales,
  SUM(t.target)       AS total_target,
  ROUND(SUM(cs.sales_total)::NUMERIC / SUM(t.target) * 100, 1) AS achievement_rate
FROM departments AS c                                  -- 子部門
INNER JOIN child_sales  AS cs ON c.dept_id   = cs.dept_id  -- 1:1
INNER JOIN dept_targets AS t  ON c.dept_id   = t.dept_id   -- 1:1
INNER JOIN departments  AS p  ON c.parent_id = p.dept_id   -- 自己結合（子N:親1）
GROUP BY p.dept_id, p.dept_name   -- 親部門粒度に丸め上げる（Roll-up）
ORDER BY p.dept_id;

/*
  実行順序:
  1. CTE child_sales                  → sales を事前集約
  2. FROM departments AS c            → 子部門側を読み込む
  3. INNER JOIN child_sales AS cs     → dept_id で結合
  4. INNER JOIN dept_targets AS t     → dept_id で結合
  5. INNER JOIN departments AS p      → 自己結合で親部門名を付与
  6. GROUP BY p.dept_id, p.dept_name  → 親部門単位に集約
  7. SUM, ROUND                       → 売上・目標・達成率を計算
  8. SELECT, ORDER BY                 → 列を射影し並び替え
  */

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

WITH child_sales AS (
  SELECT dept_id, SUM(amount) AS sales_total
  FROM sales GROUP BY dept_id
)
SELECT
  p.dept_name AS parent_dept,
  SUM(cs.sales_total) AS total_sales,
  SUM(t.target) AS total_target,
  ROUND(SUM(cs.sales_total)::NUMERIC / SUM(t.target) * 100, 1) AS achievement_rate
FROM departments AS c
INNER JOIN child_sales  AS cs ON c.dept_id   = cs.dept_id
INNER JOIN dept_targets AS t  ON c.dept_id   = t.dept_id
INNER JOIN departments  AS p  ON c.parent_id = p.dept_id
GROUP BY p.dept_id, p.dept_name
ORDER BY p.dept_id;

LEGEND ▼

データ取得・読込対象

① CTE元データ — sales ▼

FROM sales（子部門に対して 1:N）売上テーブル（6行）です。売上は子部門単位で記録され、子部門ごとに行数が異なります（国内営業2件・海外営業1件・アプリ開発2件・基盤開発1件）。このまま目標テーブルとJOINすると膨張するため、まず子部門粒度に集約します。

1 / 7

sale_id	dept_id	amount
1	11	300000
2	11	150000
3	12	400000
4	21	500000
5	21	250000
6	22	250000

sales: 6行（子部門に対して 1:N）

学習ポイント

HIERARCHY ROLL-UP

自己結合 + 2段階集約 — 子の 1:N を丸めてから親へ丸め上げる

「集約の2段構え」が階層×ファントラップを同時に解決する
sales(1:N) → 子粒度に集約 → 自己結合で親付与 → 親粒度に再集約

自己結合は「同じテーブルに2つの顔を与える」操作：departments AS c（子の顔）と departments AS p(親の顔) は物理的には同一テーブルです。c.parent_id = p.dept_id は「N個の子が1つの親を参照する」N:1 結合なので行数は増えません。エイリアスの役割（cは明細側・pは参照側）を意識すると、自己結合は普通のJOINと同じ感覚で読めるようになります。

2段階集約 — 粒度を1段ずつ粗くする：本問の集約は「①売上明細 → 子部門粒度（CTE）」「②子部門粒度 → 親部門粒度（GROUP BY）」の2段構えです。一気に親まで集約せず段階を分けることで、1:1 の dept_targets を安全に同伴でき、各段階で値の検証もできます。粒度の階段を1段ずつ昇るイメージが、複雑な集計クエリを設計する際の指針になります。

アンチパターン

事前集約せずに sales と dept_targets を同時JOIN：1:N（売上）と 1:1（目標）を同時に結合すると、売上行数分だけ target がコピーされます。本問では営業本部の SUM(target) が 90万 → 130万に膨張し、達成率が 94.4% → 65.4% へ大きく狂います。1:N が見えたらまず CTE で 1:1 に変換——基礎編で学んだ原則は階層構造でもそのまま通用します。

本部直轄の売上を想定しない設計の鵜呑み：本問の INNER 自己結合は「売上は必ず子部門に紐づく」前提です。もし本部（parent_id=NULL）に直轄売上が存在する設計なら、本部の行は c.parent_id = p.dept_id にマッチせず売上が集計から漏れます。COALESCE(c.parent_id, c.dept_id) で「親がなければ自分自身を親とみなす」等の対処が必要です。階層テーブルでは「ルート行にも事実データが付くか」を必ず確認しましょう。

実務コラム

階層ロールアップはBI・経営レポートの中核です。応用例: 商品カテゴリ階層の売上集計（小分類→中分類→大分類）、勘定科目ツリーの財務集計（補助科目→勘定科目→財務諸表項目）、組織ツリーの人件費集計。本問は2階層なので自己結合1回で済みますが、深さが不定の階層は再帰CTE（WITH RECURSIVE）で経路を辿るのが次のステップです。「隣接リスト＋自己結合」を理解しておくと、再帰CTEの各イテレーションが「自己結合の繰り返し」だと自然に読めるようになります。

QUESTION 9

非等価結合（Non-Equi JOIN） — BETWEEN で契約期間内の利用日数をカウントする

非等価結合BETWEENCOUNT(DISTINCT)ON句の範囲条件

前提知識 ▼

これまでの JOIN はすべて「=」によるキー一致（等価結合）でした。実は ON 句には範囲条件も書けます。これを非等価結合（Non-Equi JOIN）と呼び、「契約期間（月次マスタ）の中に入る利用ログ（日次）」のように、キーの一致ではなく範囲の包含で対応づくテーブル同士を結合できます。

-- 等価結合: キーの一致で対応づける
ON s.user_id = l.user_id

-- 非等価結合: 等価条件 ＋ 範囲条件で「期間内のログ」だけを対応づける
ON  s.user_id = l.user_id
AND l.used_on BETWEEN s.start_date AND s.end_date

「数える粒度」を要件から読み取る：
COUNT(*) = 利用回数（イベント粒度: 同日2回は2）。
COUNT(DISTINCT l.used_on) = 利用日数（日粒度: 同日2回は1）。
さらに LEFT JOIN 時、COUNT(*) は NULL 行も1と数えます（基礎編の罠が再登場）。「利用日数」という日本語をどの集計に翻訳するかが本問の核心です。

問題 ▼

月次契約マスタ subscriptions と日次利用ログ usage_logs を結合し、各契約について契約期間内の利用日数（active_days）をカウントしてください。利用ゼロの契約も 0 で出力し、同日複数回の利用は1日と数えること。

使用テーブル

▸ subscriptions

sub_id	user_id	plan	start_date	end_date
1	1	Pro	2024-04-01	2024-04-30
2	2	Lite	2024-04-10	2024-05-09
3	3	Pro	2024-04-15	2024-05-14

▸ usage_logs

log_id	user_id	used_on
1	1	2024-04-05
2	1	2024-04-05
3	1	2024-04-20
4	1	2024-04-28
5	1	2024-05-02
6	2	2024-04-08
7	2	2024-04-12
8	2	2024-04-25

期待出力 ▼

user_id	plan	active_days
1	Pro	3
2	Lite	2
3	Pro	0

模範解答コード ▼

SELECT
  s.user_id,
  s.plan,
  COUNT(DISTINCT l.used_on) AS active_days  -- 「日数」粒度: 同日重複を除外、NULLはスキップ
FROM subscriptions AS s
LEFT JOIN usage_logs AS l
  ON  s.user_id = l.user_id
  AND l.used_on BETWEEN s.start_date AND s.end_date  -- 非等価結合（ON句に書く）
GROUP BY s.user_id, s.plan
ORDER BY s.user_id;

/*
  実行順序:
  1. FROM subscriptions AS s     → 契約マスタを読み込む
  2. LEFT JOIN usage_logs AS l   → 期間内のログを結合（非等価）
  3. GROUP BY s.user_id, s.plan  → 契約単位に集約
  4. COUNT(DISTINCT l.used_on)   → 重複日を除き日数を集計
  5. SELECT, ORDER BY            → 列を射影し並び替え
  */

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

SELECT
  s.user_id,
  s.plan,
  COUNT(DISTINCT l.used_on) AS active_days
FROM subscriptions AS s
LEFT JOIN usage_logs AS l
  ON  s.user_id = l.user_id
  AND l.used_on BETWEEN s.start_date AND s.end_date
GROUP BY s.user_id, s.plan
ORDER BY s.user_id;

LEGEND ▼

データ取得・読込対象

① 左テーブル — 契約マスタ（月次粒度） ▼

FROM subscriptions AS s契約マスタ（3行、1行 = 1契約期間）です。利用ログとの対応は「user_id の一致」だけでは決まらず、「used_on が期間内か」という範囲条件で初めて確定します。これが非等価結合が必要になる理由です。

1 / 7

sub_id	user_id	plan	start_date	end_date
1	1	Pro	2024-04-01	2024-04-30
2	2	Lite	2024-04-10	2024-05-09
3	3	Pro	2024-04-15	2024-05-14

subscriptions: 3行（LEFT JOIN の左側 → 全契約を保持）

学習ポイント

NON-EQUI JOIN

非等価結合 — 「=」ではなく範囲の包含でテーブルを対応づける

契約期間（粗い粒度）×利用ログ（細かい粒度）を BETWEEN で結ぶ
ON 等価条件 AND used_on BETWEEN start AND end

非等価結合のカーディナリティは「範囲に入る行すべて」：等価結合では対応行がキー一致で決まりますが、範囲結合では「期間に包含されるすべてのログ」が対応します（1契約:Nログ）。重要なのはこのクエリの暗黙の前提——同一ユーザーの契約期間が互いに重ならないこと。もし期間が重複する契約設計なら1つのログが複数契約にマッチして N:N となり、行が膨張します。非等価結合を書くときは「範囲の排他性」をデータ設計レベルで確認してください。

「数える単位」＝出力の意味を決める粒度：同じ7行の結合結果でも、COUNT(*) なら利用回数（イベント粒度）、COUNT(DISTINCT used_on) なら利用日数（日粒度）と意味が変わります。さらに DISTINCT 版は NULL をスキップするため、LEFT JOIN の「利用ゼロ=0」も同時に正しく扱えます。集計関数の引数の選択は、要件の粒度をSQLに翻訳する行為そのものです。

アンチパターン

範囲条件を WHERE 句に書いて LEFT JOIN を無効化：WHERE l.used_on BETWEEN ... は user3 の NULL 行を UNKNOWN 判定で除外し、LEFT JOIN を事実上の INNER JOIN に変えます。「全契約を出力する」要件が静かに壊れる典型事故です。右テーブルの列に対する絞り込みは ON 句、が鉄則です。

BETWEEN と TIMESTAMP の境界バグ：used_on が TIMESTAMP 型の場合、BETWEEN ... AND '2024-04-30' は「04-30 の 00:00:00 まで」を意味し、4月30日中のログがほぼすべて漏れます。日付粒度を揃えてから比較するか、l.used_on >= start AND l.used_on < end_date + INTERVAL '1 day' の半開区間で書くのが安全です。期間比較では「境界の含む/含まない」を必ず仕様確認しましょう。

実務コラム

非等価結合は「期間マスタ × イベントログ」という実務最頻出の組み合わせを支えます。応用例: サブスクの請求対象利用の集計（本問そのもの）、キャンペーン期間中の購買分析（campaigns × orders）、為替・税率の適用（取引日が属するレート期間を検索 — rate_history × transactions）、勤務シフトと打刻の突合。どれも「マスタ側は期間で1行、ログ側は日時で1行」という粒度のズレを BETWEEN が橋渡しします。等価結合しか知らないと EXISTS やアプリ側ループで遠回りしがちな領域です。

QUESTION 10

条件付き集約（Conditional Aggregation） — 「AまたはBは買ったが、Cは買っていない」を HAVING で判定する

条件付き集約HAVINGFILTER / CASE WHEN集合への述語

前提知識 ▼

「コーヒーまたは紅茶を買ったことがある」「ケーキを一度も買っていない」——これらは1行を見ても判定できません。そのユーザーの購買履歴全体（行の集合）に対する条件だからです。WHERE は行を1本ずつ裁く句なので、ここでは GROUP BY でユーザー単位の集合を作り、条件付き集約でその集合の中身を数えて HAVING で判定します。

-- 条件付き集約: 「条件を満たす行だけ」を数える
COUNT(*) FILTER (WHERE 条件)            -- 標準SQL / PostgreSQL
SUM(CASE WHEN 条件 THEN 1 ELSE 0 END)  -- 移植版（MySQL等もOK）

行レベルの否定 ≠ 集合レベルの否定：
WHERE product_name <> 'ケーキ' は「ケーキの行を除く」だけで、「ケーキを買ったユーザーを除く」ではありません。コーヒーとケーキを両方買った人は、コーヒーの行が生き残って結果に混入します。「〜を買っていない」は COUNT(条件付き) = 0 という集合への述語に翻訳するのが本問の核心です。

問題 ▼

users と purchases を結合し、「コーヒー」または「紅茶」を買ったことがあり、かつ「ケーキ」を一度も買っていないユーザーの user_id と user_name を抽出してください。

使用テーブル

▸ users

user_id	user_name
1	田中
2	佐藤
3	山田
4	鈴木
5	高橋

▸ purchases

purchase_id	user_id	product_name
1	1	コーヒー
2	1	ケーキ
3	2	コーヒー
4	2	サンドイッチ
5	3	ケーキ
6	4	紅茶
7	5	サンドイッチ

期待出力 ▼

user_id	user_name
2	佐藤
4	鈴木

模範解答コード ▼

SELECT
  u.user_id,
  u.user_name
FROM users AS u
JOIN purchases AS p
  ON u.user_id = p.user_id
GROUP BY u.user_id, u.user_name
HAVING COUNT(*) FILTER (WHERE p.product_name IN ('コーヒー', '紅茶')) >= 1  -- 包含: AまたはBを1回以上
   AND COUNT(*) FILTER (WHERE p.product_name = 'ケーキ') = 0          -- 除外: Cはゼロ回
ORDER BY u.user_id;

-- FILTER 句が使えないDB（MySQL等）への移植版:
--   HAVING SUM(CASE WHEN p.product_name IN ('コーヒー','紅茶') THEN 1 ELSE 0 END) >= 1
--      AND SUM(CASE WHEN p.product_name = 'ケーキ' THEN 1 ELSE 0 END) = 0

/*
  実行順序:
  1. FROM users AS u                  → users を読み込む
  2. JOIN purchases AS p              → 購買履歴と結合（1:N）
  3. GROUP BY u.user_id, u.user_name  → ユーザー単位に集約
  4. HAVING の条件付き集約                   → FILTERで条件を満たす group を抽出
  5. SELECT, ORDER BY                 → 列を射影し並び替え
  */

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

SELECT
  u.user_id,
  u.user_name
FROM users AS u
JOIN purchases AS p
  ON u.user_id = p.user_id
GROUP BY u.user_id, u.user_name
HAVING COUNT(*) FILTER (WHERE p.product_name IN ('コーヒー', '紅茶')) >= 1
   AND COUNT(*) FILTER (WHERE p.product_name = 'ケーキ') = 0
ORDER BY u.user_id;

LEGEND ▼

データ取得・結合対象

① FROM + JOIN — ユーザー × 購買履歴（1:N） ▼

FROM users AS u JOIN purchases AS p ON u.user_id = p.user_idユーザーマスタ（5行）と購買履歴（7行）を等価結合します。1ユーザー:N購買の関係なので、結合後はユーザー情報が購買行の数だけ複製された7行の仮想テーブルになります。この時点では「誰が何を買ったか」が行単位で並んでいるだけです。

1 / 6

▸ users（u）5行

user_id	user_name
1	田中
2	佐藤
3	山田
4	鈴木
5	高橋

⋈

▸ purchases（p）7行

user_id	product_name
1	コーヒー
1	ケーキ
2	コーヒー
2	サンドイッチ
3	ケーキ
4	紅茶
5	サンドイッチ

等価結合 → 7行（1ユーザー : N購買）

学習ポイント

CONDITIONAL AGGREGATION

条件付き集約 — 行の集合に対して「含む / 含まない」を判定する

WHERE は行を裁き、HAVING + 条件付き集約は集合を裁く
HAVING COUNT(*) FILTER (WHERE 条件) = 0

行レベルの述語と集合レベルの述語を区別する：「コーヒーの行か？」は1行で判定できる行への述語（WHERE の領分）。一方「ケーキを一度も買っていないか？」は購買履歴全体を見ないと判定できない集合への述語です。集合への述語は「GROUP BY で集合を作る → 条件付き集約で要約値に変換する → HAVING で要約値を判定する」という3段の翻訳で表現します。1:N の N 側全体に対する条件は集合レベル——これが本問のカーディナリティ的な核心です。

FILTER / CASE WHEN と NOT EXISTS の使い分け：除外条件「Cを買っていない」は NOT EXISTS (SELECT 1 FROM purchases WHERE user_id = u.user_id AND product_name = 'ケーキ') でも書けます。条件付き集約は複数の包含・除外条件を1回のスキャンでまとめて判定でき、件数の閾値（2回以上など）にも自然に拡張できるのが強み。NOT EXISTS は条件が1つだけの除外や、結合せずに判定したい場面で読みやすくなります。なお NOT IN (サブクエリ) はサブクエリに NULL が混ざると全行 UNKNOWN になる罠があるため、除外には NOT EXISTS か本問の COUNT = 0 が安全です。

アンチパターン

WHERE での行レベル否定（<> 'ケーキ'）：消えるのは「ケーキの行」だけで、「ケーキを買ったユーザー」は他の購買行で生き残ります。田中（コーヒー＋ケーキ）が混入し、結果が静かに膨らむ典型事故。エラーが出ないぶん、レビューでも気づかれにくい誤りです。「〜していないユーザー」という日本語を見たら集合レベルの否定を疑う癖をつけましょう。

包含条件を WHERE に書いて除外判定の材料を失う：WHERE product_name IN ('コーヒー','紅茶','ケーキ') のように先に行を絞ると、佐藤のサンドイッチ行などが消えるのは無害ですが、絞り込みを誤ってケーキの行まで落とすと「C件数 = 0」の判定が全員 0 になり、除外が機能しなくなります。条件付き集約を使うクエリでは、判定に使う行を WHERE で削らない（条件は FILTER / CASE WHEN 側に寄せる）のが安全な設計です。

実務コラム

「Aは満たすがBは満たさない」型の抽出は実務の宝庫です。応用例: マーケティングのセグメント抽出（無料プランは使ったが有料転換していないユーザー）、休眠・離反分析（昨年は購入があるが今年はゼロの顧客 — 年を条件にした FILTER 2本で書けます）、機能利用のギャップ分析（機能Aは使うが機能Bは未使用）、さらに FILTER を SELECT 句に並べればピボット集計（商品別の購入件数を横持ちに展開）にもそのまま発展します。GROUP BY + 条件付き集約は「集合への問い」を何本でも並べられる、応用範囲の広いパターンです。