EXCEPT・INTERSECTで顧客セグメント差分を抽出するSQL

集合演算は「WHEREで書けるから使わない」ではもったいない

EXCEPT（差集合）と INTERSECT（積集合）は、SQL の集合演算子だ。UNION（和集合）と並んで教科書に載っているが、「NOT IN や LEFT JOIN で代替できるから使わない」と思われがちな存在でもある。

しかし適切な場面で使うと、クエリが劇的にシンプルになる。特に「2つの顧客グループを比較して差分を取る」という操作は、セグメント分析やメルマガ配信リストの管理で頻繁に発生する。

この記事では EXCEPT と INTERSECT の動作原理を整理したうえで、NOT IN / LEFT JOIN + IS NULL との使い分け基準を明確にする。そして通販現場でよく求められる「先月買ったが今月買っていない顧客」「両方のキャンペーンに参加した顧客」といったセグメント抽出を実装する。

集合演算の3兄弟を整理する

集合 A = {C001, C002, C003, C004}（先月購買した顧客）
集合 B = {C002, C004, C005, C006}（今月購買した顧客）

演算子	意味	結果
`A UNION B`	AまたはBに含まれる（和集合）	C001, C002, C003, C004, C005, C006
`A INTERSECT B`	AとB両方に含まれる（積集合）	C002, C004
`A EXCEPT B`	Aに含まれるがBには含まれない（差集合）	C001, C003

EXCEPT と INTERSECT はいずれも重複排除を自動で行う（DISTINCT 相当）。同じ顧客IDが複数行あっても1行にまとめられる。

使用するテーブル

-- orders テーブル
-- order_id     : 注文ID
-- customer_id  : 顧客ID
-- order_date   : 注文日
-- total_amount : 注文金額
-- status       : 'completed' / 'cancelled'

-- mail_deliveries テーブル（メルマガ配信ログ）
-- delivery_id  : 配信ID
-- customer_id  : 顧客ID
-- campaign_id  : キャンペーンID
-- delivered_at : 配信日時

STEP 1 ― EXCEPT で「先月買ったが今月買っていない顧客」を取る

最も典型的な EXCEPT の使いどころだ。「先月購買したが今月はまだ購買していない顧客」はウィンバック施策や購買リマインドの対象になる。

-- STEP1: EXCEPT で差分を取る

-- 先月購買した顧客
SELECT DISTINCT customer_id
FROM orders
WHERE
    status     = 'completed'
    AND order_date >= DATE_TRUNC('month', DATE_ADD('month', -1, CURRENT_DATE))
    AND order_date <  DATE_TRUNC('month', CURRENT_DATE)

EXCEPT

-- 今月購買した顧客
SELECT DISTINCT customer_id
FROM orders
WHERE
    status     = 'completed'
    AND order_date >= DATE_TRUNC('month', CURRENT_DATE)
    AND order_date <  DATE_ADD('month', 1, DATE_TRUNC('month', CURRENT_DATE));

出力イメージ

customer_id
C001
C003
C007
…

先月は買ったが今月まだ買っていない顧客の一覧がシンプルに出る。EXCEPT の後の SELECT が「除外したいグループ」だ。「AからBを引く」という順序を間違えないようにしよう。

STEP 2 ― INTERSECT で「両方のキャンペーンに参加した顧客」を取る

INTERSECT は「2つのグループの共通部分」を取る。例えば「キャンペーンAにもキャンペーンBにも参加した顧客」という重複チェックや、「先月も今月も継続購買した顧客（継続率の分子）」の抽出に使える。

-- STEP2: INTERSECT で共通部分を取る

-- キャンペーンAの配信対象
SELECT DISTINCT customer_id
FROM mail_deliveries
WHERE campaign_id = 'CAMP_A'

INTERSECT

-- キャンペーンBの配信対象
SELECT DISTINCT customer_id
FROM mail_deliveries
WHERE campaign_id = 'CAMP_B';

これで「両方のキャンペーンを受け取った顧客」が出る。両方受け取ったのにどちらも購買しなかった顧客を分析したいなら、さらに EXCEPT で購買者を除けばよい。

-- 両キャンペーン受け取ったが購買しなかった顧客

-- 両方のキャンペーンに参加した顧客
(
    SELECT DISTINCT customer_id
    FROM mail_deliveries
    WHERE campaign_id = 'CAMP_A'

    INTERSECT

    SELECT DISTINCT customer_id
    FROM mail_deliveries
    WHERE campaign_id = 'CAMP_B'
)

EXCEPT

-- 配信後7日以内に購買した顧客
SELECT DISTINCT o.customer_id
FROM orders o
JOIN mail_deliveries d ON o.customer_id = d.customer_id
WHERE
    o.status    = 'completed'
    AND d.campaign_id IN ('CAMP_A', 'CAMP_B')
    AND o.order_date BETWEEN CAST(d.delivered_at AS DATE)
                         AND DATE_ADD('day', 7, CAST(d.delivered_at AS DATE));

集合演算は入れ子にしたり連鎖させたりできるのが強みだ。複雑な「〜かつ〜でない」条件を、WHERE の長い AND/OR の連鎖よりも直感的に表現できる。

STEP 3 ― NOT IN / LEFT JOIN との比較

同じ結果は NOT IN や LEFT JOIN + IS NULL でも出せる。それぞれの書き方を比べてみよう。

-- 「先月買ったが今月買っていない顧客」を3つの書き方で実装

-- 書き方1: EXCEPT（最もシンプル）
SELECT DISTINCT customer_id
FROM orders
WHERE status = 'completed'
AND order_date >= DATE_TRUNC('month', DATE_ADD('month', -1, CURRENT_DATE))
AND order_date <  DATE_TRUNC('month', CURRENT_DATE)

EXCEPT

SELECT DISTINCT customer_id
FROM orders
WHERE status = 'completed'
AND order_date >= DATE_TRUNC('month', CURRENT_DATE)
AND order_date <  DATE_ADD('month', 1, DATE_TRUNC('month', CURRENT_DATE));


-- 書き方2: NOT IN（簡潔だがNULLの罠に注意）
SELECT DISTINCT customer_id
FROM orders
WHERE status = 'completed'
AND order_date >= DATE_TRUNC('month', DATE_ADD('month', -1, CURRENT_DATE))
AND order_date <  DATE_TRUNC('month', CURRENT_DATE)
AND customer_id NOT IN (
    SELECT customer_id  -- customer_id に NULL が含まれると全件がFALSEになる
    FROM orders
    WHERE status = 'completed'
    AND order_date >= DATE_TRUNC('month', CURRENT_DATE)
    AND order_date <  DATE_ADD('month', 1, DATE_TRUNC('month', CURRENT_DATE))
);


-- 書き方3: LEFT JOIN + IS NULL（明示的で安全）
SELECT DISTINCT a.customer_id
FROM orders a
LEFT JOIN orders b
    ON  a.customer_id = b.customer_id
    AND b.status      = 'completed'
    AND b.order_date  >= DATE_TRUNC('month', CURRENT_DATE)
    AND b.order_date  <  DATE_ADD('month', 1, DATE_TRUNC('month', CURRENT_DATE))
WHERE
    a.status     = 'completed'
    AND a.order_date >= DATE_TRUNC('month', DATE_ADD('month', -1, CURRENT_DATE))
    AND a.order_date <  DATE_TRUNC('month', CURRENT_DATE)
    AND b.customer_id IS NULL;  -- 今月の購買がない

3つの書き方の使い分け

観点	EXCEPT	NOT IN	LEFT JOIN + IS NULL
可読性	最も高い（集合の意図が明確）	高い（条件が一カ所）	やや低い（JOINの方向を読む必要）
NULL の安全性	安全（NULLを自動的に除外）	危険（サブクエリにNULLがあると全件FALSE）	安全
追加カラムの取得	難しい（IDのみの比較）	可能（外側のSELECTで自由に指定）	容易（JOIN後に任意のカラムを取得可能）
パフォーマンス	エンジン依存	サブクエリが大きいと遅い	大規模データでも安定しやすい
複数カラムでの比較	可能（SELECTの列数を揃える）	`NOT IN (SELECT a, b...)` は使えない	ON句で複数条件を指定できる

選択の基準

IDだけ取れればよい、意図をシンプルに伝えたい → EXCEPT / INTERSECT
差分の顧客の詳細情報も一緒に取りたい → LEFT JOIN + IS NULL
NOT IN は使わない：サブクエリに NULL が含まれると全件が意図せず除外される有名な罠がある。customer_id は通常 NOT NULL だが、念のため使用を避けるか NOT IN の後に WHERE customer_id IS NOT NULL を加える

STEP 4 ― 複数月にわたる継続購買・離脱を集合演算で追跡する

3ヶ月連続購買した顧客（先月・先々月・先先々月すべてで購買）を INTERSECT で取り出す。

-- STEP4: 3ヶ月連続購買した顧客をINTERSECTで取得

-- 3ヶ月前に購買
SELECT DISTINCT customer_id
FROM orders
WHERE status = 'completed'
AND order_date >= DATE_TRUNC('month', DATE_ADD('month', -3, CURRENT_DATE))
AND order_date <  DATE_TRUNC('month', DATE_ADD('month', -2, CURRENT_DATE))

INTERSECT

-- 2ヶ月前に購買
SELECT DISTINCT customer_id
FROM orders
WHERE status = 'completed'
AND order_date >= DATE_TRUNC('month', DATE_ADD('month', -2, CURRENT_DATE))
AND order_date <  DATE_TRUNC('month', DATE_ADD('month', -1, CURRENT_DATE))

INTERSECT

-- 先月に購買
SELECT DISTINCT customer_id
FROM orders
WHERE status = 'completed'
AND order_date >= DATE_TRUNC('month', DATE_ADD('month', -1, CURRENT_DATE))
AND order_date <  DATE_TRUNC('month', CURRENT_DATE);

INTERSECT を連鎖させることで、「N個の条件をすべて満たす顧客」を集合演算のみで表現できる。これを WHERE の AND で書くと自己JOINが必要になるか、かなり複雑になる。

STEP 5 ― 完成版：セグメント別に顧客数とサマリを出す

EXCEPT と INTERSECT の結果を使ってセグメント別の顧客数・購買金額を集計する完成版だ。

-- STEP5: 先月・今月の購買状況で顧客をセグメント分類してサマリを出す

WITH last_month_buyers AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE status = 'completed'
    AND order_date >= DATE_TRUNC('month', DATE_ADD('month', -1, CURRENT_DATE))
    AND order_date <  DATE_TRUNC('month', CURRENT_DATE)
),
this_month_buyers AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE status = 'completed'
    AND order_date >= DATE_TRUNC('month', CURRENT_DATE)
    AND order_date <  DATE_ADD('month', 1, DATE_TRUNC('month', CURRENT_DATE))
),
-- 集合演算でセグメントを定義
continuing AS (
    -- 先月も今月も購買（継続顧客）
    SELECT customer_id, '継続購買' AS segment FROM last_month_buyers
    INTERSECT
    SELECT customer_id, '継続購買' FROM this_month_buyers
),
lapsed AS (
    -- 先月買ったが今月は未購買（離脱危機）
    SELECT customer_id, '先月のみ購買' AS segment FROM last_month_buyers
    EXCEPT
    SELECT customer_id, '先月のみ購買' FROM this_month_buyers
),
new_this_month AS (
    -- 今月初めて（または先月は未購買で今月購買）
    SELECT customer_id, '今月のみ購買' AS segment FROM this_month_buyers
    EXCEPT
    SELECT customer_id, '今月のみ購買' FROM last_month_buyers
),
all_segments AS (
    SELECT * FROM continuing
    UNION ALL
    SELECT * FROM lapsed
    UNION ALL
    SELECT * FROM new_this_month
),
-- 今月の購買金額と結合（継続・今月のみが対象）
this_month_sales AS (
    SELECT
        customer_id,
        SUM(total_amount)  AS monthly_sales
    FROM orders
    WHERE status = 'completed'
    AND order_date >= DATE_TRUNC('month', CURRENT_DATE)
    AND order_date <  DATE_ADD('month', 1, DATE_TRUNC('month', CURRENT_DATE))
    GROUP BY customer_id
)
SELECT
    s.segment,
    COUNT(s.customer_id)                         AS customer_count,
    COALESCE(SUM(t.monthly_sales), 0)            AS total_sales,
    COALESCE(ROUND(AVG(t.monthly_sales), 0), 0)  AS avg_sales_per_customer
FROM all_segments        s
LEFT JOIN this_month_sales  t  ON s.customer_id = t.customer_id
GROUP BY s.segment
ORDER BY
    CASE s.segment
        WHEN '継続購買'    THEN 1
        WHEN '今月のみ購買' THEN 2
        WHEN '先月のみ購買' THEN 3
    END;

完成した出力イメージ

segment	customer_count	total_sales	avg_sales_per_customer
継続購買	1,284	22,470,000	17,500
今月のみ購買	742	8,162,000	11,000
先月のみ購買	891	0	0

継続購買顧客は今月のみ購買顧客より平均購買額が59%高い（17,500円 vs 11,000円）。そして891人が先月買ったが今月まだ購買していない。この891人が「今月中に動かせるかどうか」が来月の継続率を左右する。

実務での運用ヒント

① EXCEPT は列数と型が一致していないと動かない

EXCEPT / INTERSECT の上下の SELECT は、列の数と各列のデータ型が一致している必要がある。SELECT customer_id, order_date EXCEPT SELECT customer_id はエラーになる。IDだけを比較する用途に向いており、詳細情報が必要なら LEFT JOIN で後から付与する。

② Treasure Data（Presto）での動作確認

Presto では EXCEPT と INTERSECT は EXCEPT ALL / INTERSECT ALL（重複を保持）と EXCEPT / INTERSECT（重複排除）の両方が使える。通常は重複排除版（EXCEPT）で問題ないが、重複を保持したい場合は ALL を付ける。

③ 3つ以上のグループの積集合はROW_NUMBERでも書ける

INTERSECT を何回も連鎖させると可読性が落ちる。「N個の月すべてで購買した顧客」は、ストリーク分析（GAP and ISLAND）の手法でも表現できる。長期連続購買の分析は INTERSECT の連鎖よりストリーク分析のほうが柔軟だ。

まとめ

EXCEPT と INTERSECT の使いどころをまとめると

EXCEPT → 「〜だが〜ではない顧客」の差分抽出。離脱検知・除外リスト作成
INTERSECT → 「〜かつ〜の顧客」の共通部分。継続購買・複数施策参加者の特定

NOT IN との最大の違いは NULL の安全性と可読性だ。「集合AからBを引く」「AとBの共通部分を取る」という意図を最もダイレクトに表現できるのが集合演算子だ。

詳細情報が必要なら LEFT JOIN、IDだけでよくシンプルに書きたいなら EXCEPT / INTERSECT と使い分けることで、クエリの可読性と保守性が上がる。

すべての記事

F2転換を改善する方法｜2回目割引に頼らないCRM施策設計

再帰CTEの使い方｜SQLでカレンダーと階層構造を生成する方法

PIVOT・UNPIVOTをSQLで実装する方法｜CASE WHENで縦横変換する

勇者は最初から勇者ではない｜未経験からキャリアを育てる転職論

SQLウィンドウ関数の使い方｜分析クエリで必須のOVER句を理解する

ファーストパーティデータとは何か｜誤解されやすい意味とCDP活用の本質

Treasure Data導入で失敗する会社の共通点｜CDPを使いこなせない5つの理由

ROLLUP・CUBEで小計と総計をSQL集計する方法