SQL 重複データ管理 — 集合演算・ウィンドウ関数の応用

QUESTION 6

DISTINCT ON で「グループごとの最新1行」を取る — 組み合わせのユニーク化から代表行の選抜へ

DISTINCT ONPostgreSQLグループ代表行最新1件

前提知識 ▼

基礎編の複数列 DISTINCT は「(user_id, category) の組み合わせの種類」を返しましたが、実務では「各ユーザーの最新の購入行そのもの」のように、グループごとに代表1行（他の列も含めた行全体）が欲しい場面が頻出します。PostgreSQL の DISTINCT ON はこれを1クエリでエレガントに解決します。

SELECT DISTINCT ON (user_id)  -- user_id ごとに「先頭の1行」だけ残す
  user_id, order_id, purchased_at
FROM purchase_logs
ORDER BY user_id, purchased_at DESC;  -- 並び順が「先頭」を決める

ORDER BY が「どの1行を残すか」を決める：DISTINCT ON は「ソート後にグループの先頭に来た行」を採用します。そのため ORDER BY の先頭は必ず DISTINCT ON の列と一致させ、その後ろに「残したい行が先頭に来る並び順」（最新なら日付の DESC）を書きます。

問題 ▼

purchase_logs には各ユーザーの購入履歴が時系列で蓄積されています。「各ユーザーの最新の購入行」（order_id・category・amount を含む行全体）を、ユーザーごとに1行ずつ取得してください。

使用テーブル

▸ purchase_logs

order_id	user_id	category	amount	purchased_at
1	101	Books	1500	2026-01-10
2	102	Food	800	2026-01-12
3	101	Electronics	3000	2026-02-05
4	103	Books	1200	2026-02-08
5	102	Electronics	5000	2026-03-01
6	101	Food	600	2026-03-15
7	103	Food	900	2026-02-20

期待出力 ▼

user_id	order_id	category	amount	purchased_at
101	6	Food	600	2026-03-15
102	5	Electronics	5000	2026-03-01
103	7	Food	900	2026-02-20

模範解答コード ▼

SELECT DISTINCT ON (user_id)  -- user_id ごとに先頭1行を採用
  user_id,
  order_id,
  category,
  amount,
  purchased_at
FROM purchase_logs
ORDER BY user_id, purchased_at DESC;  -- 最新が先頭になるよう整列

/*
  実行順序:
  1. FROM purchase_logs                   → 行を読み込む
  2. ORDER BY user_id, purchased_at DESC  → 最新が先頭になるよう整列
  3. DISTINCT ON (user_id)                → 各ユーザーの先頭1行を採用
  4. SELECT 5列を出力                         → 最新購入の行全体
  */

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

SELECT DISTINCT ON (user_id)
  user_id, order_id, category,
  amount, purchased_at
FROM purchase_logs
ORDER BY user_id,
         purchased_at DESC;

LEGEND ▼

データ取得・読込対象

① 元データ ▼

FROM purchase_logspurchase_logs テーブル（7行）を読み込みます。各 user_id に複数の購入行があり、このうち purchased_at が最も新しい1行だけを残したい、という課題です。

1 / 4

order_id	user_id	category	amount	purchased_at
1	101	Books	1500	2026-01-10
2	102	Food	800	2026-01-12
3	101	Electronics	3000	2026-02-05
4	103	Books	1200	2026-02-08
5	102	Electronics	5000	2026-03-01
6	101	Food	600	2026-03-15
7	103	Food	900	2026-02-20

purchase_logs: 7行（user_id ごとに複数の購入履歴）

学習ポイント

DISTINCT ON — GROUP REPRESENTATIVE ROW

DISTINCT ON — ソート順でグループ代表の「行全体」を選抜

複数列 DISTINCT が「組み合わせの種類」なら、DISTINCT ON は「グループごとの代表1行」
ORDER BY user_id, purchased_at DESC → 各 user_id の先頭 = 最新行

DISTINCT ON は「ソート → 先頭採用」の2段動作：DISTINCT ON (user_id) は、ORDER BY で整列した行を上から走査し、user_id が初めて登場した行だけを残します。つまり「何を残すか」は DISTINCT ON が、「どれを残すか」は ORDER BY が決めるという分業です。最新を残すなら purchased_at DESC、最古なら ASC と、2番目以降のソートキーを変えるだけで選抜基準を切り替えられます。

GROUP BY + MAX との決定的な違い — 行全体が取れる：SELECT user_id, MAX(purchased_at) FROM purchase_logs GROUP BY user_id では最新の日付しか取れず、その行の order_id や amount を取るには自己結合が必要でした。DISTINCT ON なら代表行の全列を1クエリで取得できます。同等の標準SQLは ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchased_at DESC) + rn = 1 で、Q10 でその削除版を学びます。

同率1位はタイブレークで決定的にする：同一ユーザーに同時刻の購入が2件あると、どちらが「先頭」かは不定になります。ORDER BY の末尾に order_id DESC のような一意キーを足すことで、何度実行しても同じ行が選ばれる「決定的なクエリ」になります。再現性は実務SQLの基本品質です。

アンチパターン

ORDER BY の先頭が DISTINCT ON の列と不一致：SELECT DISTINCT ON (user_id) ... ORDER BY purchased_at DESC と書くと、PostgreSQL は「SELECT DISTINCT ON expressions must match initial ORDER BY expressions」エラーを返します。ORDER BY は必ず DISTINCT ON の列から書き始め、その後ろに選抜基準の列を続けるのが文法ルールです。

移植性を考えず DISTINCT ON に依存する：DISTINCT ON は PostgreSQL 独自拡張で、MySQL や SQL Server には存在しません。複数DBをまたぐプロダクトでは、標準SQLの ROW_NUMBER() + サブクエリ（rn = 1）パターンを採用する方が安全です。「社内の分析クエリは DISTINCT ON、アプリ組み込みSQLはウィンドウ関数」のような使い分けが現実解です。

実務コラム

「グループごとの最新1行」は実務SQLの最頻出パターンのひとつです。応用例: 各顧客の最新注文の取得（CRM のダッシュボード）、センサーごとの最新計測値（IoT モニタリング）、従業員ごとの最新の役職レコード（履歴テーブルからの現在値復元）。履歴を残す設計（イミュータブルなログテーブル）が主流になった現代では、「履歴から現在のスナップショットを復元する」この型を手が覚えるまで練習する価値があります。

QUESTION 7

EXCEPT ALL で「件数の差」まで検出する — 多重集合の引き算で在庫を突合

EXCEPT ALL多重集合件数差分在庫突合

前提知識 ▼

基礎編の EXCEPT は差集合を返しますが、内部で重複除去が行われるため「左に3件・右に2件」のような数量の差は消えてしまいます。EXCEPT ALL は重複を保持したまま「同じ値1件につき1件だけ打ち消す」多重集合（マルチセット）の引き算を行います。

-- A = {x, x, x, y} / B = {x, y} のとき
A EXCEPT B      -- → {}        値レベルで x も y も B にある → 全消し
A EXCEPT ALL B  -- → {x, x}    x は 3−1=2 件残る（件数で相殺）

「あるか/ないか」と「何件足りないか」は別の問い：在庫突合・台帳照合のように数量がズレた分を検出したい場面で EXCEPT を使うと、差異が1件でも100件でも「1値」に潰れてしまいます。数量の問題には必ず ALL 付きを選びます。

問題 ▼

倉庫の棚卸しで、帳簿在庫 system_stock（商品1個 = 1行）と実棚カウント counted_stock を突合します。「帳簿には存在するが実棚で見つからなかった在庫」を、不足している個数分の行数で取得してください。

使用テーブル

▸ system_stock（帳簿）

stock_id	sku
1	A001
2	A001
3	A001
4	B002
5	B002
6	C003
7	D004

▸ counted_stock（実棚）

scan_id	sku
1	A001
2	A001
3	B002
4	B002
5	C003

期待出力 ▼

sku
A001
D004

模範解答コード ▼

SELECT sku FROM system_stock
EXCEPT ALL                     -- 左から右を引く（重複も保持）
SELECT sku FROM counted_stock
ORDER BY sku;

/*
  実行順序:
  1. 左 SELECT: system_stock から sku を全7行取得（重複保持）
  2. 右 SELECT: counted_stock から sku を全5行取得（重複保持）
  3. EXCEPT ALL: 多重集合の引き算
  4. ORDER BY sku
  */

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

SELECT sku FROM system_stock
EXCEPT ALL
SELECT sku FROM counted_stock
ORDER BY sku;

LEGEND ▼

データ取得・読込対象

① 左 SELECT — 帳簿在庫 ▼

SELECT sku FROM system_stock帳簿在庫から sku を取り出します。商品1個 = 1行なので、A001 が3行あることは「A001 の帳簿在庫が3個」を意味します。EXCEPT ALL ではこの重複（= 個数）がそのまま意味を持ちます。

1 / 4

sku
A001
A001
A001
B002
B002
C003
D004

system_stock: 7行（A001×3, B002×2, C003×1, D004×1）

学習ポイント

MULTISET DIFFERENCE

EXCEPT ALL — 件数つき差集合（多重集合の引き算）

「値の有無」ではなく「件数の過不足」を検出する集合演算
帳簿(7) EXCEPT ALL 実棚(5) = 不足分2行

EXCEPT と EXCEPT ALL は答える問いが違う：EXCEPT は「左にあって右に1件もない値はどれか」、EXCEPT ALL は「左の各行のうち右と相殺しきれない行はどれか」に答えます。本問で ALL を外すと、A001 は「実棚に存在する値」なので結果から消え、D004 の1行だけになります。数量の差異検出には必ず ALL、存在差分の検出には ALL なし、と問いに合わせて選びます。

相殺は「値ごとの件数比較」で考える：EXCEPT ALL の結果は「左の件数 − 右の件数（負なら0）」を値ごとに計算したものと等価です。本問なら A001: 3−2=1、B002: 2−2=0、C003: 1−1=0、D004: 1−0=1。この件数モデルで考えると、UNION ALL（足し算）・EXCEPT ALL（引き算）・INTERSECT ALL（最小値）の3演算を統一的に理解できます。

逆向き（実棚 EXCEPT ALL 帳簿）は「過剰在庫」：左右を入れ替えると「実棚にあるのに帳簿にない在庫」= 過剰・計上漏れの検出になります。実務の棚卸しでは両方向の EXCEPT ALL を UNION ALL でつなぎ、不足と過剰を1本のクエリで一覧化するのが定番です。

アンチパターン

数量突合に ALL なしの EXCEPT を使う：最も多い実務バグです。「差異ゼロ」とレポートされたのに実際は A001 が1個足りない——原因は EXCEPT の暗黙の重複除去です。行の重複に意味があるテーブル（明細・ログ・スキャン記録）の比較は常に ALL 付きを疑ってください。逆にマスタ同士の比較なら ALL なしで十分です。

「どちらが何個」かを失ったまま報告する：EXCEPT ALL の結果は不足分の行が並ぶだけで、そのままでは数えにくい形です。報告用には SELECT sku, COUNT(*) AS shortage FROM (…EXCEPT ALL…) t GROUP BY sku のように外側で集計して「品目×不足数」に整形しましょう。生の差分行とサマリの2段構えが読み手に親切です。

実務コラム

多重集合の差は「件数が意味を持つデータ」の照合すべてに使えます。応用例: 棚卸しの帳簿 vs 実棚差異、決済ログ vs 入金明細の突合（同額取引が複数あっても件数単位で照合）、データ移行前後の行数レベル検証（移行元 EXCEPT ALL 移行先が0行なら欠落なし）。特に移行検証では「両方向の EXCEPT ALL がともに空」を確認するのがゴールデンルールで、これ1本で欠落・重複混入の両方を機械的に検出できます。

QUESTION 8

COUNT(*) OVER で重複行を1パスで全取得 — サブクエリ + IN をウィンドウ関数で進化させる

ウィンドウ関数COUNT() OVERPARTITION BY重複行全取得

前提知識 ▼

基礎編では「GROUP BY + HAVING のサブクエリ → IN で本体を再検索」という2回スキャンの構成で重複行を取得しました。ウィンドウ関数 COUNT(*) OVER (PARTITION BY …) を使うと、行を畳まずに各行へ「自分と同じキーの行数」を付与でき、テーブル1回の読み取りで同じ結果＋重複件数まで得られます。

COUNT(*) OVER (PARTITION BY member_id, class_id)
-- GROUP BY と違い行は減らない。各行に「同じ組の行数」が列として付く

ウィンドウ関数は WHERE に直接書けない：ウィンドウ関数は SELECT 句の評価段階（WHERE より後）で計算されるため、WHERE COUNT(*) OVER (…) > 1 はエラーです。サブクエリ（または CTE）で一度列にしてから外側で絞るのが定石です。

問題 ▼

フィットネスジムの reservations には、システム不具合で同じ会員が同じクラスを二重予約したレコードが混在しています。重複している (member_id, class_id) の組を持つすべての予約行を、重複件数（dup_cnt）付きで取得してください。

使用テーブル

▸ reservations

reservation_id	member_id	class_id	reserved_at
1	201	Y01	2026-01-05
2	202	P02	2026-01-06
3	201	Y01	2026-01-07
4	203	Y01	2026-01-08
5	202	P02	2026-01-09
6	202	S03	2026-01-10

期待出力 ▼

reservation_id	member_id	class_id	reserved_at	dup_cnt
1	201	Y01	2026-01-05	2
3	201	Y01	2026-01-07	2
2	202	P02	2026-01-06	2
5	202	P02	2026-01-09	2

模範解答コード ▼

SELECT
  reservation_id, member_id, class_id, reserved_at, dup_cnt
FROM (
  SELECT
    r.*,
    COUNT(*) OVER (
      PARTITION BY member_id, class_id  -- 同じ組ごとに行数を数える
    ) AS dup_cnt
  FROM reservations r
) t
WHERE dup_cnt > 1
ORDER BY member_id, class_id, reservation_id;

/*
  実行順序:
  1. 内側 FROM reservations  → 全6行を読み込む
  2. COUNT(*) OVER (PARTITION BY member_id, class_id)
  3. 外側 WHERE dup_cnt > 1  → 重複している組の行だけ残す
  4. SELECT 5列を出力
  5. ORDER BY member_id, class_id, reservation_id
  */

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

SELECT reservation_id, member_id,
  class_id, reserved_at, dup_cnt
FROM (
  SELECT r.*,
    COUNT(*) OVER (
      PARTITION BY member_id,
                   class_id
    ) AS dup_cnt
  FROM reservations r
) t
WHERE dup_cnt > 1
ORDER BY member_id, class_id,
         reservation_id;

LEGEND ▼

データ取得・読込対象

① 元データ ▼

FROM reservations rreservations テーブル（6行）を読み込みます。(member_id, class_id) を見ると (201, Y01) と (202, P02) がそれぞれ2回登場しており、これが二重予約です。

1 / 4

reservation_id	member_id	class_id	reserved_at
1	201	Y01	2026-01-05
2	202	P02	2026-01-06
3	201	Y01	2026-01-07
4	203	Y01	2026-01-08
5	202	P02	2026-01-09
6	202	S03	2026-01-10

reservations: 6行（(member_id, class_id) に重複あり）

学習ポイント

WINDOW COUNT — ONE-PASS DUP DETECTION

COUNT(*) OVER — 行を畳まず「同じ組の行数」を各行に付与

GROUP BY は行を減らす、ウィンドウ関数は行を保ったまま集計値を添える
6行 → 6行 + dup_cnt 列 → WHERE dup_cnt > 1 → 4行

GROUP BY との対比 — 「畳む集計」と「添える集計」：GROUP BY は同じキーの行を1行に畳むため、明細（reservation_id や reserved_at）が消えます。ウィンドウ関数は行を保ったまま集計値を添えるため、「明細を残しつつ件数も知りたい」という重複調査の要求にぴったり適合します。基礎編でサブクエリ + IN が必要だったのは、GROUP BY で消えた明細を取り戻すためだった、と捉え直せます。

1パスで済む — 性能と可読性の両得：サブクエリ + IN はテーブルを2回参照しますが、ウィンドウ関数版は1回の読み取り + パーティション分割で完結します。大きなテーブルほど差が出やすく、さらに「dup_cnt が結果に含まれる」という情報量の優位もあります。重複検出の第一選択として覚える価値があります。

サブクエリで「列に降ろしてから」絞るのが定石：SQLの論理評価順序では WHERE はウィンドウ関数より先に評価されるため、ウィンドウ関数の結果で絞るには一段内側で列として確定させる必要があります。本問のインラインビュー (…) t を CTE（WITH t AS (…)）に書き換えても等価で、Q10 ではその CTE 形を DELETE と組み合わせます。

アンチパターン

WHERE に直接ウィンドウ関数を書く：WHERE COUNT(*) OVER (PARTITION BY …) > 1 は「window functions are not allowed in WHERE」エラーになります。HAVING や QUALIFY（Snowflake/BigQuery等の拡張）と混同しやすいポイントです。PostgreSQL では必ずサブクエリか CTE を一段挟むと覚えてください。

PARTITION BY の列を間違えて「全行が重複」になる：PARTITION BY member_id だけにすると「同じ会員の予約全部」が同じ窓に入り、別クラスの予約まで dup_cnt が膨らみます。重複の定義（どの列の組が同じなら重複か）を先に言語化し、その列だけを PARTITION BY に並べることが正確さの要です。基礎編の「判定キーの選定」と同じ規律がここでも効きます。

実務コラム

COUNT(*) OVER による重複検出は、モダンSQLでの重複調査の標準形です。応用例: 二重予約・二重決済の調査レポート（明細 + 件数を一画面で）、データ品質ダッシュボード（dup_cnt 別の分布を可視化）、ETL の中間検証（ロード後に dup_cnt > 1 が0行であることを確認）。さらに ROW_NUMBER() OVER に置き換えれば「何件目の重複か」が分かり、削除パターンへ直結します。COUNT → ROW_NUMBER の流れで覚えると応用が一気に広がります。

QUESTION 9

INTERSECT を連結して「全期間に共通」を抽出 — 3ヶ月連続アクティブユーザーの特定

INTERSECT 連結集合演算継続ユーザーN期間共通

前提知識 ▼

基礎編の INTERSECT は2つの集合の共通部分でした。INTERSECT は何段でも連結でき、「A ∩ B ∩ C」のようにすべての集合に共通する要素を積み上げて絞り込めます。リテンション分析の「3ヶ月連続アクティブ」はこの型の代表例です。

SELECT user_id FROM logins WHERE login_month = '2026-01'
INTERSECT   -- 1月 ∩ 2月
SELECT user_id FROM logins WHERE login_month = '2026-02'
INTERSECT   -- (1月 ∩ 2月) ∩ 3月
SELECT user_id FROM logins WHERE login_month = '2026-03';

INTERSECT は重複除去込みで「共通」を判定：同じ月に何度ログインしていても集合上は1要素として扱われます。「各期間に1回以上存在するか」という存在判定であり、回数は問いません（回数も問うなら INTERSECT ALL や GROUP BY を使います）。

問題 ▼

logins には月次のログイン記録が格納されています（同月内の重複ログインあり）。2026年1月・2月・3月の3ヶ月すべてにログインした継続ユーザーの user_id を取得してください。

使用テーブル

▸ logins

login_id	user_id	login_month
1	101	2026-01
2	102	2026-01
3	103	2026-01
4	101	2026-01
5	101	2026-02
6	103	2026-02
7	104	2026-02
8	101	2026-03
9	103	2026-03
10	105	2026-03
11	103	2026-03

期待出力 ▼

user_id
101
103

模範解答コード ▼

SELECT user_id FROM logins WHERE login_month = '2026-01'
INTERSECT                                                 -- 1・2月の共通ユーザー
SELECT user_id FROM logins WHERE login_month = '2026-02'
INTERSECT                                                 -- さらに3月とも共通
SELECT user_id FROM logins WHERE login_month = '2026-03'
ORDER BY user_id;

/*
  実行順序:
  1. 各 SELECT          → 月ごとのユーザー集合を抽出
  2. 1段目の INTERSECT    → 共通ユーザーを抽出
  3. 2段目の INTERSECT    → さらに共通を抽出
  4. ORDER BY user_id  → 並べ替えて出力
  */

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

SELECT user_id FROM logins
  WHERE login_month = '2026-01'
INTERSECT
SELECT user_id FROM logins
  WHERE login_month = '2026-02'
INTERSECT
SELECT user_id FROM logins
  WHERE login_month = '2026-03'
ORDER BY user_id;

LEGEND ▼

データ取得・読込対象

① 集合1 — 1月のログインユーザー ▼

SELECT user_id FROM logins WHERE login_month = '2026-01'1月のログイン行を抽出します。user_id=101 は1月に2回ログインしていますが（login_id=1, 4）、INTERSECT が集合として扱うため最終的に1要素になります。集合は {101, 102, 103} です。

1 / 5

login_id	user_id	login_month
1	101	2026-01
2	102	2026-01
3	103	2026-01
4	101	2026-01 ※重複

1月: 4行 → 集合 {101, 102, 103}

学習ポイント

CHAINED INTERSECTION

INTERSECT 連結 — 全集合に共通する要素だけを残す絞り込み

段を重ねるごとに条件が AND で積み上がる、宣言的なリテンション抽出
{101,102,103} ∩ {101,103,104} ∩ {101,103,105} = {101,103}

連結は「左から段階的に」評価される：INTERSECT は同順位の集合演算同士では左結合的に評価され、(1月 ∩ 2月) の中間結果に対して3月を重ねます。共通部分は段を経るごとに単調に縮小するため、どの月で誰が脱落したかをステップごとに追えるのが学習・デバッグ両面の利点です。なお UNION と混在させる場合、INTERSECT は UNION より優先されるため括弧で意図を明示しましょう。

HAVING COUNT(DISTINCT) との等価性と使い分け：同じ結果は

SELECT user_id FROM logins WHERE login_month IN ('2026-01','2026-02','2026-03') GROUP BY user_id HAVING COUNT(DISTINCT login_month) = 3

でも得られます。期間数が可変（パラメータ化したい）なら HAVING 方式、固定数で「すべての期間に存在」という意図を構文で語りたいなら INTERSECT が読みやすい、という使い分けです。COUNT(DISTINCT) にしないと同月内の重複ログインで件数が水増しされる点も重要です。

集合演算の総仕上げ — UNION / EXCEPT / INTERSECT の使い分け地図：基礎編から通して、UNION =「どれかに存在（OR）」、EXCEPT =「左だけに存在（差）」、INTERSECT =「すべてに存在（AND）」が出揃いました。リテンション分析では「今月 EXCEPT 先月 = 新規」「先月 EXCEPT 今月 = 離脱」「先月 INTERSECT 今月 = 継続」と、3演算がそのままユーザー区分に対応します。

アンチパターン

「3ヶ月で3回以上ログイン」と混同する：GROUP BY user_id HAVING COUNT(*) >= 3 は「合計3回以上」であり、1月に3回ログインしただけのユーザーも通過してしまいます。「各期間に1回以上」と「合計N回以上」は別の要件です。INTERSECT 連結（または COUNT(DISTINCT login_month) = 3）は前者を正しく表現します。要件の言語化を雑にしたまま書き始めるのが最大のアンチパターンです。

列数・型の不一致と途中の ORDER BY：UNION / EXCEPT と同様、INTERSECT も連結する各 SELECT の列数・型が一致しないとエラーです。また各段の SELECT に個別の ORDER BY は書けず、並べ替えは全体の末尾に1回だけ。3段以上に連結すると忘れがちなので、集合演算の共通ルールとして体に入れておきましょう。

実務コラム

N期間共通の抽出はリテンション・継続率分析の中核です。応用例: 3ヶ月連続課金ユーザーの抽出（ロイヤル顧客の特定）、全四半期で取引のあった顧客（安定取引先リスト）、全環境（dev/stg/prod）に存在する設定キーの確認（リリース検証）。期間が長くなる場合は INTERSECT を並べるより HAVING COUNT(DISTINCT) 方式やウィンドウ関数での連続判定に切り替えるのが実務的で、「まず INTERSECT で要件を確認し、運用クエリでは集計形に書き換える」という2段活用も有効です。

QUESTION 10

ROW_NUMBER + DELETE で重複を物理削除 — 最新を残すクレンジングの完成形

ROW_NUMBERCTE + DELETE重複削除RETURNING

前提知識 ▼

基礎編では MIN(id) + GROUP BY で「残す行」を選びました。応用編の総仕上げは、ROW_NUMBER() で削除対象を採番し、DELETE で物理削除する実務のクレンジング本番手順です。「最新を残す」「タイブレークで決定的にする」「削除行を RETURNING で記録する」の3点を一度に押さえます。

ROW_NUMBER() OVER (
  PARTITION BY email                      -- 重複グループ
  ORDER BY created_at DESC, contact_id DESC  -- 残す行が rn=1 になる順
) AS rn   -- rn=1 を残し、rn>1 を削除する

DELETE は不可逆 — 必ず3点セットで：①削除対象を同じ条件の SELECT で事前確認、②トランザクション内で実行して件数を確認してから COMMIT、③ RETURNING で削除行を記録。この手順を省いた重複削除は事故の典型例です。

問題 ▼

顧客マスタ contacts には同一 email の重複登録が蓄積されています。email ごとに最新（created_at が最大）の1行を残し、それ以外の古い重複行を DELETE してください。削除した行は RETURNING で contact_id・email・created_at を返してください。

使用テーブル

▸ contacts

contact_id	email	name	created_at
1	sato@ex.com	佐藤	2026-01-10
2	suzuki@ex.com	鈴木	2026-01-12
3	sato@ex.com	佐藤	2026-02-01
4	tanaka@ex.com	田中	2026-02-05
5	sato@ex.com	佐藤	2026-03-01
6	suzuki@ex.com	鈴木	2026-02-20

期待出力 ▼

contact_id	email	created_at
1	sato@ex.com	2026-01-10
2	suzuki@ex.com	2026-01-12
3	sato@ex.com	2026-02-01

模範解答コード ▼

WITH ranked AS (
  SELECT
    contact_id,
    ROW_NUMBER() OVER (
      PARTITION BY email
      ORDER BY created_at DESC, contact_id DESC  -- 最新が rn=1。同時刻はIDでタイブレーク
    ) AS rn
  FROM contacts
), deleted AS (
  DELETE FROM contacts
  WHERE contact_id IN (
    SELECT contact_id FROM ranked WHERE rn > 1
  )
  RETURNING contact_id, email, created_at
)
SELECT contact_id, email, created_at
FROM deleted
ORDER BY contact_id;

/*
  実行順序:
  1. CTE ranked  → email ごとに最新順で採番
  2. サブクエリ       → rn > 1 の削除対象を抽出
  3. DELETE      → 対象行を物理削除
  4. RETURNING   → 削除行を CTE に返す
  5. SELECT      → contact_id 順で結果を返す
  */

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

WITH ranked AS (
  SELECT contact_id,
    ROW_NUMBER() OVER (
      PARTITION BY email
      ORDER BY created_at DESC,
               contact_id DESC
    ) AS rn
  FROM contacts
)
DELETE FROM contacts
WHERE contact_id IN (
  SELECT contact_id FROM ranked
  WHERE rn > 1
)
RETURNING contact_id, email,
          created_at;

LEGEND ▼

データ取得・読込対象

① 元データ ▼

FROM contactscontacts テーブル（6行）。sato@ex.com が3件、suzuki@ex.com が2件重複しています。email ごとに created_at が最大の行（5, 6, 4）だけを残すのがゴールです。

1 / 5

contact_id	email	name	created_at
1	sato@ex.com	佐藤	2026-01-10
2	suzuki@ex.com	鈴木	2026-01-12
3	sato@ex.com	佐藤	2026-02-01
4	tanaka@ex.com	田中	2026-02-05
5	sato@ex.com	佐藤	2026-03-01
6	suzuki@ex.com	鈴木	2026-02-20

contacts: 6行（sato×3, suzuki×2, tanaka×1）

学習ポイント

DEDUP DELETE — KEEP THE LATEST

ROW_NUMBER + DELETE — 最新を残して古い重複を物理削除

採番（rn）→ 削除対象抽出（rn>1）→ DELETE → RETURNING で証跡
6行 → DELETE {1,2,3} → email ユニークな3行

「残す行を選ぶ」発想の進化 — MIN(id) から ROW_NUMBER へ：基礎編の MIN(contact_id) は「ID最小 = 最古を残す」しか表現できず、連番でないIDでは破綻しました。ROW_NUMBER の ORDER BY は任意の基準で「残す1行」を定義できます。最新を残すなら created_at DESC、情報量の多い行を残すなら NULL の少なさでソート、と要件をそのまま並び順に写せるのが決定的な進化です。

タイブレークが「決定的な削除」を保証する：同一 email・同一 created_at の行が2件あると、ORDER BY created_at DESC だけでは rn=1 がどちらになるか実行ごとに揺れる可能性があります。末尾に contact_id DESC を足すことで順序が一意に定まり、何度実行しても同じ行が残るクエリになります。再実行・リハーサル環境との突合が前提の本番作業では必須の作法です。

RETURNING は「不可逆操作の証跡」：DELETE/UPDATE/INSERT に付けられる RETURNING は、影響を受けた行をその場で返す PostgreSQL の強力な機能です。削除行を INSERT INTO contacts_archive SELECT … せずとも、アプリ側で受け取って監査ログへ書き出せます。さらに WITH deleted AS (DELETE … RETURNING *) INSERT INTO archive SELECT * FROM deleted と書けば、削除とアーカイブを1文・1トランザクションで完結できます。

アンチパターン

事前確認なしでいきなり DELETE：最も危険なアンチパターンです。必ず DELETE FROM contacts の部分を SELECT * FROM contacts に差し替えて削除対象を確認し、本番では BEGIN; → DELETE（件数確認）→ COMMIT/ROLLBACK; のトランザクション手順を踏みます。「想定3行のはずが3万行」に気づける最後の砦は、COMMIT 前の件数チェックです。

削除だけして再発防止をしない：クレンジング後に CREATE UNIQUE INDEX ON contacts (email) などの一意制約を張らないと、重複は必ず再発し、削除作業が定期業務化してしまいます。アプリ側は INSERT … ON CONFLICT (email) DO UPDATE（UPSERT）に改修して、入口で重複を防ぐところまでが「重複データ管理」の完成です。

実務コラム

本問は Q1〜集大成です。実務の重複クレンジングは「検出（Q3/Q8: GROUP BY + HAVING、COUNT() OVER）→ 調査（重複行の全取得と原因分析）→ 代表行の決定（Q4/Q10: MIN、DISTINCT ON、ROW_NUMBER）→ 削除 + 証跡（DELETE + RETURNING）→ 再発防止（一意制約 + UPSERT）」という一連のワークフローで進みます。応用例: 会員マスタの名寄せ、二重取込されたETLデータの修復、ログ基盤の重複イベント除去。どの現場でもこの5段階の地図を持っているだけで、安全かつ再現性のある重複対応ができるようになります。