SQL サブクエリ — CASE×SQ・複数EXISTS・多段CTEの応用

QUESTION 6

CASE式 × サブクエリ — 全体平均をCROSS JOINで取得してユーザーを3段階に分類する

CASE+SQCROSS JOINFROM句SQユーザーセグメント

前提知識 ▼

サブクエリをCASE式の閾値として使うと、データ主導の動的分類が可能になります。1行しか返さないサブクエリをCROSS JOINすることで、全行に同じ集計値（定数）を効率よく付与できます。

SELECT  s.user_id, s.total_spent, g.overall_avg,
  CASE
    WHEN s.total_spent >= 2 * g.overall_avg THEN 'HIGH_VALUE'
    WHEN s.total_spent >= g.overall_avg     THEN 'NORMAL'
    ELSE                                          'LIGHT'
  END AS segment
FROM (...) AS s        -- ユーザー別集計（FROM句SQ）
CROSS JOIN (...) AS g; -- 全体平均（1行）を全行に付与

CROSS JOIN が有効な理由：1行しか返さないサブクエリをCROSS JOINすると直積は「元の行数×1=元の行数」。全行に同じ集計値を1回の評価で付与できます。CASE WHEN に毎回スカラーSQを書くと同じ集計が複数回評価されますが、このパターンは評価が1回で済みます。

問題 ▼

orders の completed注文合計（total_spent）を持つユーザーを全体平均基準で HIGH_VALUE / NORMAL / LIGHT に分類してください。取得列は user_id, name, plan, total_spent, overall_avg, segment、total_spent降順。completedなしのユーザーは除外します。

使用テーブル

▸ users

user_id	name	plan
1	田中太郎	premium
2	佐藤花子	free
3	鈴木一郎	premium
4	山田次郎	standard
5	伊藤三郎	free

▸ orders

order_id	user_id	amount	status
101	1	8000	completed
102	1	12000	completed
103	2	3500	pending
104	3	9500	completed
105	3	4000	completed
106	4	2000	cancelled
107	5	4500	completed
108	2	3000	completed

集計対象と比較単位を整理してから、サブクエリの返す値を決めてください。

期待出力 ▼

user_id	name	plan	total_spent	overall_avg	segment
1	田中太郎	premium	20000	6833	HIGH_VALUE
3	鈴木一郎	premium	13500	6833	NORMAL
5	伊藤三郎	free	4500	6833	LIGHT
2	佐藤花子	free	3000	6833	LIGHT

模範解答コード ▼

SELECT
  u.user_id, u.name, u.plan,
  s.total_spent,
  g.overall_avg,
  CASE
    WHEN s.total_spent >= 2 * g.overall_avg THEN 'HIGH_VALUE'  -- 平均の2倍以上
    WHEN s.total_spent >= g.overall_avg     THEN 'NORMAL'      -- 平均以上
    ELSE                                          'LIGHT'      -- 平均未満
  END AS segment
FROM   users u
INNER JOIN (                        -- 派生テーブル s: ユーザー別completed合計
  SELECT user_id, SUM(amount) AS total_spent
  FROM   orders
  WHERE  status = 'completed'
  GROUP BY user_id
) AS s ON u.user_id = s.user_id    -- completedなしのユーザーはここで自動除外
CROSS JOIN (                        -- 派生テーブル g: 全体平均を1行で取得
  SELECT ROUND(AVG(amount)) AS overall_avg
  FROM   orders
  WHERE  status = 'completed'
) AS g                               -- 1行なので CROSS JOIN で全行に付与される
ORDER BY s.total_spent DESC;

/*
  実行順序（SQLの論理的な評価順）:
  1. 派生テーブル s を評価（WHERE  → GROUP BY）
  2. 派生テーブル g を評価
  3. FROM users u INNER JOIN s ON user_id
  4. CROSS JOIN g
  5. SELECT + CASE
  6. ORDER BY total_spent DESC
  */

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

SELECT
  u.user_id, u.name, u.plan,
  s.total_spent, g.overall_avg,
  CASE
    WHEN s.total_spent >= 2 * g.overall_avg THEN 'HIGH_VALUE'
    WHEN s.total_spent >= g.overall_avg     THEN 'NORMAL'
    ELSE  'LIGHT'
  END AS segment
FROM   users u
INNER JOIN (
  SELECT user_id, SUM(amount) AS total_spent
  FROM   orders
  WHERE  status = 'completed'
  GROUP BY user_id
) AS s ON u.user_id = s.user_id
CROSS JOIN (
  SELECT ROUND(AVG(amount)) AS overall_avg
  FROM   orders
  WHERE  status = 'completed'
) AS g
ORDER BY s.total_spent DESC;

LEGEND ▼

グループ化キー・集計対象

グループ分類

① 派生テーブル s（ユーザー別集計） ▼

SELECT user_id, SUM(amount) FROM orders WHERE status='completed' GROUP BY user_idcompletedの注文をuser_idでGROUP BY集計。user4はcompletedがないため派生テーブルsに含まれません。

1 / 5

user_id	対象 completed 注文	total_spent
1	101(8000) + 102(12000)	20000
2	108(3000)	3000
3	104(9500) + 105(4000)	13500
5	107(4500)	4500

→ 派生テーブル s: 4行（user4はcompletedなし）

学習ポイント

CASE式の閾値にサブクエリ結果を使う：CASE WHEN total_spent >= 2 * overall_avg のように、サブクエリで事前に算出した集計値を閾値として CASE に渡すことで「データ主導の動的分類」が実現します。ハードコードの数値（例: 10000）と違い、データが変化しても自動追従します。

CROSS JOIN で「定数」を全行に付与するパターン：1行しか返さないサブクエリを CROSS JOIN すると「元の行数×1=元の行数」のままです。CASE WHEN に毎回スカラーSQを書くと最大3回評価されますが、CROSS JOIN パターンは1回の評価で済みます。PostgreSQL ではサブクエリ結果をスカラーとして SELECT 句に3回書く方法も使えますが、CROSS JOIN の方が効率的です。

INNER JOIN で「対象なしユーザー」を自然に除外：completedの集計結果の派生テーブル s と INNER JOIN することで、completedが一件もない user4 が自動的に除外されます。「このユーザーも含めたい（0と表示したい）」場合は LEFT JOIN + COALESCE を使います。JOIN の種類で除外意図を表現するのは実務の重要な設計ポイントです。

アンチパターン

CASE WHEN に同じスカラーSQを繰り返し書く：WHEN total_spent >= 2*(SELECT AVG(amount)...) のように CASE の各 WHEN に直接スカラーSQを書くと同じ集計クエリが最大3回評価されます。CROSS JOIN で一度だけ評価した値（g.overall_avg）を参照する今回のパターンが推奨です。

CROSS JOIN 元が複数行になる場合の直積爆発：today のパターンでは g が1行なので問題ありませんが、CROSS JOIN元が N行になると「元の行数 × N」に膨れ上がります。CROSS JOIN は「必ず1行を返す」ことを確認してから使ってください。

実務コラム：データ主導のユーザーセグメント分析

ECサイト・SaaSプロダクトではユーザーをspend量で分類し、HIGH_VALUEにはVIP施策、LIGHTには活性化施策を打ち分けるのが一般的です。固定値ではなく「全体平均との相対比較」で分類することで、データのトレンドが変化しても自動的に閾値が追従します。このパターンはBigQueryやRedshiftでも同様に使え、SQLだけでBIダッシュボード用のセグメント列を動的生成できます。

QUESTION 7

複数EXISTS連結 — AND/NOT EXISTSを組み合わせてターゲット顧客を絞り込む

複数EXISTSNOT EXISTS相関SQキャンペーン対象

前提知識 ▼

WHERE 句に EXISTS / NOT EXISTS を AND で連結することで、複数の独立した条件をチェーンできます。各 EXISTS は完全に独立した相関サブクエリとして評価されるため、「Aを持ち、かつBを持ち、かつCを持たない」という複合フィルタを実現できます。

SELECT u.user_id, u.name
FROM   users u
WHERE  EXISTS     (SELECT 1 FROM orders o1 WHERE o1.user_id = u.user_id AND ...)
  AND  EXISTS     (SELECT 1 FROM orders o2 WHERE o2.user_id = u.user_id AND ...)
  AND  NOT EXISTS (SELECT 1 FROM orders o3 WHERE o3.user_id = u.user_id AND ...);

AND の短絡評価（Short-circuit）：最初の EXISTS が FALSE になった時点でその行への後続条件の評価は省略されます。user4 は ① で FALSE になるため、② ③ は評価されません。実行コストの低い条件を先頭に置くと効率が上がります。

問題 ▼

users テーブルから以下の3条件をすべて満たすユーザーを取得してください：
① completedの注文が1件以上ある（EXISTS）
② 2024-05-16以降の注文が1件以上ある（EXISTS）
③ cancelledの注文が1件もない（NOT EXISTS）
取得列は user_id, name, plan、user_id昇順。

使用テーブル

▸ users

user_id	name	plan
1	田中太郎	premium
2	佐藤花子	free
3	鈴木一郎	premium
4	山田次郎	standard
5	伊藤三郎	free

▸ orders（ordered_at付き）

order_id	user_id	amount	status	ordered_at
101	1	8000	completed	2024-04-10
102	1	12000	completed	2024-05-20
103	2	3500	pending	2024-05-10
104	3	9500	completed	2024-05-15
105	3	4000	completed	2024-06-01
106	4	2000	cancelled	2024-05-08
107	5	4500	completed	2024-04-20
108	2	5500	completed	2024-06-10

期待出力 ▼

user_id	name	plan
1	田中太郎	premium
2	佐藤花子	free
3	鈴木一郎	premium

模範解答コード ▼

SELECT u.user_id, u.name, u.plan
FROM   users u
WHERE  EXISTS (                       -- ① completedの注文が1件以上ある
  SELECT 1 FROM orders o1
  WHERE  o1.user_id = u.user_id       -- 相関条件（外側のu.user_idを参照）
    AND  o1.status  = 'completed'
)
  AND  EXISTS (                       -- ② 2024-05-16以降の注文が1件以上ある
  SELECT 1 FROM orders o2
  WHERE  o2.user_id   = u.user_id
    AND  o2.ordered_at >= '2024-05-16' -- 直近30日の基準日
)
  AND  NOT EXISTS (                   -- ③ cancelledの注文が1件もない
  SELECT 1 FROM orders o3
  WHERE  o3.user_id = u.user_id
    AND  o3.status  = 'cancelled'    -- キャンセル歴を検出
)
ORDER BY u.user_id;

/*
  実行順序（各ユーザー行に対して評価）:
  1. FROM users u           → 5行を1行ずつ処理
  2. EXISTS(completed)      → completed ありを判定
  3. EXISTS(直近注文)           → 直近注文ありを判定
  4. NOT EXISTS(cancelled)  → キャンセルなしを判定
  5. ORDER BY user_id       → 昇順
  */

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

SELECT u.user_id, u.name, u.plan
FROM   users u
WHERE  EXISTS (
  SELECT 1 FROM orders o1
  WHERE  o1.user_id = u.user_id
    AND  o1.status = 'completed'
)
  AND  EXISTS (
  SELECT 1 FROM orders o2
  WHERE  o2.user_id   = u.user_id
    AND  o2.ordered_at >= '2024-05-16'
)
  AND  NOT EXISTS (
  SELECT 1 FROM orders o3
  WHERE  o3.user_id = u.user_id
    AND  o3.status = 'cancelled'
)
ORDER BY u.user_id;

LEGEND ▼

データ取得・読込対象

① FROM users ▼

FROM users uusersテーブル全5行をベースとして、これに対して3つの相関EXISTSが順に評価されます。

1 / 5

user_id	name	plan
1	田中太郎	premium
2	佐藤花子	free
3	鈴木一郎	premium
4	山田次郎	standard
5	伊藤三郎	free

全 5行読込

学習ポイント

複数EXISTSのAND連結：WHERE EXISTS(...) AND EXISTS(...) AND NOT EXISTS(...) のように複数の独立した条件をチェーンできます。各EXISTSは独立した相関SQとして評価されるため、「Aを持ち、かつBを持ち、かつCを持たない」という複合フィルタが直感的に記述できます。

AND の短絡評価でコスト削減：SQLエンジンは AND の左側がFALSEなら右側を評価しません（短絡評価）。user4は ① でFALSEになるため ② ③ は実行されません。最も絞り込みの強い（FALSE率が高い）条件を先頭に置くと実行コストが下がります。

別名（o1, o2, o3）で独立性を明示：3つのサブクエリが同じ orders テーブルを参照するため、o1・o2・o3 とエイリアスを変えることで各SQの独立性を明示します。同じ名前を使っても動作しますが、読み手への誤解を防ぐために別名を付けるのがベストプラクティスです。

アンチパターン

NOT IN での NULL 問題：WHERE user_id NOT IN (SELECT user_id FROM orders WHERE status='cancelled') を使うと、orders に user_id=NULL の行があった場合、全行が除外されます。NOT EXISTS はこの問題がないため、否定条件には常にNOT EXISTSを使うのが実務の鉄則です。

相関条件の書き忘れ：各サブクエリ内に WHERE o1.user_id = u.user_id という外側との結合条件がないと、orders に1行でも該当するデータがあれば全ユーザーが通過/除外されます。複数EXISTSでは特に各SQの相関条件を確認しましょう。

実務コラム：マーケティングのターゲットリスト生成

「先月までに購入完了があり、直近30日以内に訪問があり、かつキャンセル歴のないユーザーにだけクーポンを送る」のような複合条件は実務で頻出です。EXISTSチェーンはこうした「複数の独立したフィルタ」を組み合わせる際の定番パターンです。条件が増えてもORを混在させず、ANDでチェーンし続けることが可読性と保守性の鍵です。

QUESTION 8

Top-N相関サブクエリ — カテゴリ別上位2件をサブクエリで取得する

相関SQTop-N抽出行番号付けランキング

前提知識 ▼

「カテゴリ別上位N件」の抽出は実務で非常に頻出です。相関サブクエリで「自分より高い行が何件あるか」を数えると、ROW_NUMBER()ウィンドウ関数なしでもTop-N抽出が実現できます。

SELECT p.*
FROM   products p
WHERE  (
  SELECT COUNT(*)
  FROM   products p2
  WHERE  p2.category = p.category   -- 同じカテゴリ内で
    AND  p2.price    >  p.price      -- 自分より高い商品が
) < 2;                               -- 2件未満 → 自分は上位2件

仕組み：「同カテゴリで自分より price が高い行が 0件 → 1位、1件 → 2位、2件以上 → 3位以下」。上位2件を取るには「自分より高い行が 2件未満（COUNT(*) < 2）」という条件になります。同価格の行が複数ある場合は全て「同順位」として返ります。

問題 ▼

products テーブルから、カテゴリ別に price 降順で上位2件の商品を取得してください。取得列は product_id, name, category, price、category 昇順・同一カテゴリ内は price 降順で並べてください。
サブクエリのみで実装し、ウィンドウ関数は使わないこと。

使用テーブル

▸ products

product_id	name	category	price
1	プランA	service	9800
2	プランB	service	4900
3	プランC	service	2500
4	テンプレートX	content	5500
5	テンプレートY	content	3800
6	テンプレートZ	content	1200
7	APIアドオン	option	3500
8	サポート拡張	option	2200
9	ストレージ追加	option	1500

期待出力 ▼

product_id	name	category	price
4	テンプレートX	content	5500
5	テンプレートY	content	3800
7	APIアドオン	option	3500
8	サポート拡張	option	2200
1	プランA	service	9800
2	プランB	service	4900

模範解答コード ▼

SELECT
  p.product_id,
  p.name,
  p.category,
  p.price
FROM   products p
WHERE  (
  SELECT COUNT(*)              -- 「自分より高い行が何件あるか」を数える
  FROM   products p2
  WHERE  p2.category = p.category  -- 同じカテゴリ内（相関条件①）
    AND  p2.price    >  p.price    -- 自分より価格が高い行（相関条件②）
) < 2                             -- 2件未満 = 自分は上位2位以内
ORDER BY p.category, p.price DESC;

/*
  実行順序（各商品行に対して評価）:
  1. FROM products p                → 9行を1行ずつ処理
  2. 相関SQ で上位件数を判定                  → 同カテゴリで自分より高い行数で絞る
  3. ORDER BY category, price DESC  → カテゴリ昇順・価格降順
  */

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

SELECT
  p.product_id, p.name, p.category, p.price
FROM   products p
WHERE  (
  SELECT COUNT(*)
  FROM   products p2
  WHERE  p2.category = p.category
    AND  p2.price    > p.price
) < 2
ORDER BY p.category, p.price DESC;

LEGEND ▼

データ取得・読込対象

① FROM products p ▼

FROM products pproductsテーブル全9行を1行ずつ処理します。各行に対して「同カテゴリで自分より高い行の件数」を数える相関SQが実行されます。

1 / 3

product_id	name	category	price
1	プランA	service	9800
2	プランB	service	4900
3	プランC	service	2500
4	テンプレートX	content	5500
5	テンプレートY	content	3800
6	テンプレートZ	content	1200
7	APIアドオン	option	3500
8	サポート拡張	option	2200
9	ストレージ追加	option	1500

全 9行読込

学習ポイント

「自分より上が何人いるか」で順位を表現する：COUNT(*) < N というパターンは「自分より高い値の行が N件未満 = 自分は上位N位以内」を意味します。これはROW_NUMBER()が使えない古いDBや、相関SQの理解深化として非常に重要なパターンです。

相関条件が2つある点に注目：p2.category = p.category（同カテゴリ内に限定）と p2.price > p.price（自分より高い行）という2つの相関条件が組み合わさって「カテゴリ別上位N」を実現しています。どちらかが欠けると意図しない結果になります。

ウィンドウ関数との比較：PostgreSQL・MySQL 8.0+では ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) を使う方が効率的で可読性も高いです。ただし相関SQパターンはあらゆるRDBで動作し、「上位N件」というロジックの本質を理解するのに最適です。

アンチパターン

同率順位による複数行返却：price が同じ行が複数ある場合、COUNT が同じになるため、上位2件のつもりが3件以上返ることがあります。例えばカテゴリ内に price=5000 の商品が2つあると両方が「1位」になります。同率の扱いは仕様を確認してください。

大量データでの性能問題：各行に対してサブクエリが実行されるため、products が 10万行あれば最大 10万回の内側クエリが走ります。本番環境では ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) + CTEによる実装を検討してください。

実務コラム：Top-N抽出の3つの実装パターン比較

カテゴリ別Top-Nの実装には ①相関SQ（COUNT < N）②FROM句SQ + RANK() ③CTE + ROW_NUMBER() の3パターンがあります。古いMySQL(5.x)では①しか使えませんが、PostgreSQL・MySQL8.0+では③が最も効率的です。面接やコードレビューで「ウィンドウ関数を使わずTop-N取得」と問われた際の回答が①になります。DBのバージョンと要件に応じてパターンを選択しましょう。

QUESTION 9

多段CTE — WITH句を3段階で積み上げてコホート分析を実装する

多段CTEWITH句コホート分析ユーザー行動分析

前提知識 ▼

複数のCTE（WITH句）を連鎖させる多段CTEは、複雑な分析クエリを段階的に分解する実務の標準手法です。各CTEが前のCTEを参照できるため、「ステップ1の結果をステップ2で絞り込み、その結果をステップ3で集計」というパイプラインを直感的に記述できます。

WITH
  step1 AS (                         -- 第1段：基礎集計
    SELECT ... FROM source_table
  ),
  step2 AS (                         -- 第2段：step1を参照して絞り込み
    SELECT ... FROM step1 WHERE ...
  ),
  step3 AS (                         -- 第3段：step2をさらに加工
    SELECT ... FROM step2 JOIN ...
  )
SELECT * FROM step3;

多段CTEの価値：複雑なネストを「名前付きの段階」に分解することで、①各ステップを単独で実行・デバッグできる ②チームへの意図伝達が容易になる ③仕様変更時に影響範囲を特定しやすいというメリットがあります。

問題 ▼

以下の3ステップを多段CTEで実装してください：
① user_stats：ユーザー別に completed注文の件数（order_cnt）と合計金額（total_spent）を集計
② active_users：①から order_cnt が 2 以上のユーザーだけを抽出
③ 最終SELECT：②と usersテーブルを JOIN し、user_id, name, plan, order_cnt, total_spent を total_spent 降順で返す

使用テーブル

▸ users

user_id	name	plan
1	田中太郎	premium
2	佐藤花子	free
3	鈴木一郎	premium
4	山田次郎	standard
5	伊藤三郎	free

▸ orders

order_id	user_id	amount	status
101	1	8000	completed
102	1	12000	completed
103	2	3500	pending
104	3	9500	completed
105	3	4000	completed
106	4	2000	cancelled
107	5	4500	completed
108	1	6500	completed

期待出力 ▼

user_id	name	plan	order_cnt	total_spent
1	田中太郎	premium	3	26500
3	鈴木一郎	premium	2	13500

模範解答コード ▼

WITH
  user_stats AS (               -- ① ユーザー別 completed 集計
    SELECT
      user_id,
      COUNT(*)    AS order_cnt,   -- completed注文件数
      SUM(amount) AS total_spent  -- completed合計金額
    FROM   orders
    WHERE  status = 'completed'
    GROUP BY user_id
  ),
  active_users AS (            -- ② user_statsを参照して絞り込み
    SELECT *
    FROM   user_stats             -- 前のCTEを普通のテーブルとして参照
    WHERE  order_cnt >= 2        -- 2件以上の購入者のみ
  )
SELECT                           -- ③ active_usersとusersをJOINして出力
  u.user_id,
  u.name,
  u.plan,
  a.order_cnt,
  a.total_spent
FROM   users u
INNER JOIN active_users a        -- CTEも通常のテーブルと同様にJOIN可能
  ON  u.user_id = a.user_id
ORDER BY a.total_spent DESC;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE user_stats を評価（WHERE  → GROUP BY
  2. CTE active_users を評価（user_stats を参照）
  3. FROM users u INNER JOIN active_users a
  4. SELECT 5列を選択
  5. ORDER BY total_spent DESC
  */

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

WITH
  user_stats AS (
    SELECT
      user_id,
      COUNT(*) AS order_cnt,
      SUM(amount) AS total_spent
    FROM   orders
    WHERE  status = 'completed'
    GROUP BY user_id
  ),
  active_users AS (
    SELECT *
    FROM   user_stats
    WHERE  order_cnt >= 2
  )
SELECT
  u.user_id, u.name, u.plan,
  a.order_cnt, a.total_spent
FROM   users u
INNER JOIN active_users a
  ON  u.user_id = a.user_id
ORDER BY a.total_spent DESC;

LEGEND ▼

グループ化キー・集計対象

グループ分類

① CTE: user_stats（全ユーザー集計） ▼

WITH user_stats AS (SELECT user_id, COUNT(*), SUM(amount) FROM orders WHERE status='completed' GROUP BY user_id)completed注文をuser_id別にGROUP BY集計してCTE「user_stats」を作ります。この段階ではorder_cntによる絞り込みはまだ行いません。

1 / 4

user_id	completed注文	order_cnt	total_spent
1	101+102+108	3	26500
3	104+105	2	13500
5	107	1	4500

→ CTE user_stats: 3行（completedあるユーザーのみ）

学習ポイント

多段CTEは「段階的なパイプライン」として読む：WITH a AS (...), b AS (...) のように前のCTEを後のCTEが参照できます。各CTEに意味のある名前（user_stats, active_users）を付けることで、SQLがドキュメントとして機能します。「最初に何を集計し、次に何で絞り込み、最後にどのテーブルとJOINするか」の設計意図がそのまま読み取れます。

CTEはデバッグが容易：多段CTEでは「WITH user_stats AS (...)　SELECT * FROM user_stats」のように途中のCTEだけを実行して中間結果を確認できます。ネストしたサブクエリでは内側だけを取り出す操作が煩雑ですが、CTEなら各ステップを独立して検証できます。

CTEも JOINの対象になる：active_users という CTE名を FROM句・JOIN句に普通のテーブル名として書けます。これがCTEと派生テーブルの最大の違いで、同じCTEを複数箇所から参照できる点もCTEの強みです。

アンチパターン

CTE内での ORDER BY：CTE内部に ORDER BY を書いても多くのDBでは無視されます（最終出力の ORDER BY のみが有効）。CTEはあくまで「名前付き中間テーブル」であり、行の順序は保証されません。並び替えは常に最終 SELECT の ORDER BY で行いましょう。

CTE の過度な分解：単純な条件でも全てCTEに分解すると逆に可読性が下がることがあります。「1つのCTEが1つの明確な役割を持つ」を目安に、3〜5段以内に収めるのが実務の目安です。それ以上複雑な場合はビューやストアドプロシージャの検討を。

実務コラム：多段CTEでBIダッシュボード用クエリを書く

BigQuery・Redshiftなどデータウェアハウス上でのレポート用クエリは多段CTEが主流です。例えば「① 日次売上集計 → ② 前月比計算 → ③ KPI閾値との比較フラグ付与 → ④ 最終レポート出力」という4段CTEをコメント付きで書くと、SQLがスペック書として機能します。「3段を超えたらCTEで」がサブクエリからCTEへの移行の目安です。

QUESTION 10

CTEスコアリング — 複数指標を独立集計してスコアを合算するユーザー評価クエリ

CTEスコアリングCASE+CTE複合応用リテンション分析

前提知識 ▼

実務では「合計金額・注文件数・活動頻度」など複数の指標を合算したスコアで顧客ランクを決めるケースがよくあります。各指標を独立したCTEで集計し、最終CTEでスコアを合算するパターンは保守性・可読性に優れた設計です。

WITH metric_a AS (                 -- ① 指標ごとに独立して集計する
  SELECT key_col, SUM(num_col) AS total
  FROM   table_a
  GROUP BY key_col
), metric_b AS (
  SELECT key_col, COUNT(*) AS cnt
  FROM   table_b
  GROUP BY key_col
), scored AS (                       -- ② 最終CTEでスコアを合算する
  SELECT a.key_col,
         CASE WHEN a.total >= 1000 THEN 3 ELSE 1 END
       + CASE WHEN b.cnt   >= 10   THEN 2 ELSE 0 END AS score
  FROM   metric_a a
  JOIN   metric_b b ON b.key_col = a.key_col
)
SELECT * FROM scored;

設計のポイント：スコアの計算ロジックを変えたいとき（例: 合計金額の重みを変える）は、該当CTEだけを修正すれば済みます。1つの巨大なSQLに全てのロジックを埋め込むより、変更箇所が明確で、チームレビューも容易です。

問題 ▼

以下の手順でユーザーの総合スコアを算出し、スコア降順・同スコアは user_id 昇順でランキングを返してください：
① spend_score：ユーザー別の completed合計金額（total_spent）に基づき 10,000以上 → 3点 / 5,000以上 → 2点 / それ以下 → 1点（completedなしは0点）
② freq_score：ユーザー別の completed注文件数（order_cnt）に基づき 3件以上 → 3点 / 2件以上 → 2点 / それ以下 → 1点（completedなしは0点）
③ 最終SELECT：全ユーザーを対象に user_id, name, spend_score, freq_score, total_score（合計） を返す

使用テーブル

▸ users

user_id	name	plan
1	田中太郎	premium
2	佐藤花子	free
3	鈴木一郎	premium
4	山田次郎	standard
5	伊藤三郎	free

▸ orders

order_id	user_id	amount	status
101	1	8000	completed
102	1	12000	completed
103	2	3500	pending
104	3	9500	completed
105	3	4000	completed
106	4	2000	cancelled
107	5	4500	completed
108	1	6500	completed

集計対象と比較単位を整理してから、サブクエリの返す値を決めてください。

期待出力 ▼

user_id	name	spend_score	freq_score	total_score
1	田中太郎	3	3	6
3	鈴木一郎	3	2	5
5	伊藤三郎	1	1	2
2	佐藤花子	0	0	0
4	山田次郎	0	0	0

模範解答コード ▼

WITH
  user_completed AS (           -- 共通基礎：ユーザー別 completed 集計
    SELECT
      user_id,
      COUNT(*)    AS order_cnt,
      SUM(amount) AS total_spent
    FROM   orders
    WHERE  status = 'completed'
    GROUP BY user_id
  ),
  spend_score AS (               -- ① 合計金額スコア（全ユーザー対象）
    SELECT
      u.user_id,
      CASE
        WHEN c.total_spent >= 10000 THEN 3  -- 高額：3点
        WHEN c.total_spent >=  5000 THEN 2  -- 中額：2点
        WHEN c.total_spent IS NOT NULL THEN 1 -- 低額：1点
        ELSE 0                            -- completedなし：0点
      END AS spend_score
    FROM   users u
    LEFT JOIN user_completed c    -- completedなしのユーザーもNULLで残す
      ON  u.user_id = c.user_id
  ),
  freq_score AS (                -- ② 注文件数スコア（全ユーザー対象）
    SELECT
      u.user_id,
      CASE
        WHEN c.order_cnt >= 3 THEN 3       -- 高頻度：3点
        WHEN c.order_cnt >= 2 THEN 2       -- 中頻度：2点
        WHEN c.order_cnt IS NOT NULL THEN 1 -- 低頻度：1点
        ELSE 0                             -- completedなし：0点
      END AS freq_score
    FROM   users u
    LEFT JOIN user_completed c
      ON  u.user_id = c.user_id
  )
SELECT                            -- ③ 全スコアをJOINして合算
  u.user_id,
  u.name,
  s.spend_score,
  f.freq_score,
  s.spend_score + f.freq_score AS total_score  -- スコア合算
FROM   users u
JOIN   spend_score s ON u.user_id = s.user_id  -- spend_score CTE を参照
JOIN   freq_score  f ON u.user_id = f.user_id  -- freq_score CTE を参照
ORDER BY total_score DESC, u.user_id;          -- スコア降順・同スコアはID昇順

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE user_completed を評価                       → ユーザーごとに completed を集計
  2. CTE spend_score を評価                          → 支出でスコア付与（LEFT JOIN）
  3. CTE freq_score を評価                           → 頻度でスコア付与（LEFT JOIN）
  4. FROM users JOIN spend_score JOIN freq_score  → 結合
  5. SELECT spend+freq                            → 合計スコアを算出
  6. ORDER BY total_score DESC, user_id           → 降順
  */

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

WITH
  user_completed AS (
    SELECT user_id, COUNT(*) AS order_cnt, SUM(amount) AS total_spent
    FROM   orders  WHERE  status = 'completed'  GROUP BY user_id
  ),
  spend_score AS (
    SELECT u.user_id,
      CASE
        WHEN c.total_spent >= 10000 THEN 3
        WHEN c.total_spent >= 5000  THEN 2
        WHEN c.total_spent IS NOT NULL THEN 1
        ELSE 0
      END AS spend_score
    FROM   users u  LEFT JOIN user_completed c  ON u.user_id = c.user_id
  ),
  freq_score AS ( ... similar CASE on order_cnt ... )
SELECT
  u.user_id, u.name, s.spend_score, f.freq_score,
  s.spend_score + f.freq_score AS total_score
FROM   users u
JOIN   spend_score s ON u.user_id = s.user_id
JOIN   freq_score  f ON u.user_id = f.user_id
ORDER BY total_score DESC, u.user_id;

LEGEND ▼

グループ化キー・集計対象

グループ分類

① CTE: user_completed（基礎集計） ▼

WITH user_completed AS (...)completedの注文のみを集計した基礎テーブル。後続の両CTEがこれを参照します。completedがないuser2・user4はここには含まれません。

1 / 4

user_id	order_cnt	total_spent
1	3	26500
3	2	13500
5	1	4500

→ CTE user_completed: 3行

学習ポイント

「独立集計 → スコア付与 → 合算」の設計パターン：各指標を独立したCTEで集計し、最終SELECTで合算するパターンはスコアリングエンジンと呼ばれる実務の定番設計です。spend_scoreのロジックを変えたい場合は該当CTEだけ修正すれば済むため、保守性が高くなります。

LEFT JOIN + ELSE 0 で「全ユーザーをカバー」：completedのないuser2・user4を除外せず「0点」として扱うために、INNER JOINではなくLEFT JOINを使っています。結合に失敗した（completedなし）場合はNULLが返るため、CASE の ELSE 0 が発動します。全員をランキングに含めたい場合はこのパターンが必須です。

共通基礎CTEの再利用：user_completedというCTEをspend_scoreとfreq_scoreの両方が参照しています。同じ集計を2回書かずに済み、データの整合性も保たれます。「1つのCTEを複数のCTEから参照できる」のはサブクエリにはできないCTE固有の強みです。

アンチパターン

NULLスコアの合算に注意：spend_score + freq_score の計算でどちらかが NULL だと合計も NULL になります（NULL + 数値 = NULL）。COALESCE(spend_score, 0) + COALESCE(freq_score, 0) のようにNULLを0に置き換えるか、今回のようにCASEのELSEで0を保証しておくことが重要です。

CTEは一時テーブルではない（多くのDB）：PostgreSQL・MySQL 8.0+ではCTEはデフォルトでインライン展開（サブクエリと同等）されます。「CTEに書けばキャッシュされる」という誤解があるため注意してください。本当にキャッシュしたい場合は一時テーブル（CREATE TEMP TABLE）やマテリアライズドCTE（PostgreSQLの MATERIALIZED ヒント）を使います。

実務コラム：RFMスコアリングへの応用

今回のパターンはマーケティングのRFM分析（Recency・Frequency・Monetary）に直接応用できます。① Recency CTE：最終注文からの経過日数でスコア ② Frequency CTE：注文件数でスコア ③ Monetary CTE：合計金額でスコア → 最終SELECT でR+F+Mを合算してユーザーランクを決定。このSQL構造はBigQuery・Redshiftでのデータ分析基盤でも広く使われるパターンです。