PostgreSQL | SQLite＋MySQL経験者向け、30日で習得するPostgreSQL：高度SQL － Day8 CTE（WITH句）

Day8 前半のゴール

「長いSQLを“分割して名前を付ける”感覚を身につける」

今日は高度SQLの入り口、CTE（Common Table Expression）、つまり WITH 句です。
PostgreSQLはCTEがとても強力で、「読めない長いSQL」を「段階に分けて名前を付けたSQL」に変えてくれます。

前半のゴールはこうです。
WITH 句が「一時的なテーブルに名前を付ける仕組み」だと理解できる。
サブクエリだらけのSQLを、CTEで“上から順に読める形”に書き直すイメージを持てる。
「可読性の高いSQL＝CTEで段階を分けたSQL」という感覚を、自分のものにし始める。

CTE（WITH句）とは何か

「“途中結果”に名前を付けて、下で再利用する」

まず、CTEの基本形を見てみます。

WITH recent_users AS (
  SELECT id, email, created_at
  FROM users
  WHERE created_at >= NOW() - INTERVAL '7 days'
)
SELECT *
FROM recent_users
WHERE email LIKE '%@example.com';

WITH recent_users AS (
  SELECT id, email, created_at
  FROM users
  WHERE created_at >= NOW() - INTERVAL '7 days'
)
SELECT *
FROM recent_users
WHERE email LIKE '%@example.com';

ここでやっていることはシンプルです。

上の WITH recent_users AS (...) で、「直近7日以内に登録したユーザー」という“途中結果”に recent_users という名前を付ける。
下の SELECT * FROM recent_users ... で、その“名前付きの途中結果”をテーブルのように扱う。

CTEは、「一時的なテーブルを、そのクエリの中だけで定義して使う」仕組みです。
サブクエリ（FROM (SELECT ...) AS t）と似ていますが、「上にまとめて書ける」「複数回使える」という点が大きな違いです。

サブクエリとの違いをイメージする

「“下に埋め込む”か、“上で宣言してから使う”か」

同じことをサブクエリで書くと、こうなります。

SELECT *
FROM (
  SELECT id, email, created_at
  FROM users
  WHERE created_at >= NOW() - INTERVAL '7 days'
) AS recent_users
WHERE email LIKE '%@example.com';

SELECT *
FROM (
  SELECT id, email, created_at
  FROM users
  WHERE created_at >= NOW() - INTERVAL '7 days'
) AS recent_users
WHERE email LIKE '%@example.com';

動きとしてはほぼ同じですが、読みやすさが違います。

サブクエリ版は、「FROMの中にSELECTが埋め込まれている」ので、
下から上に読まないと意味がつかみにくい。

CTE版は、「上で“直近7日ユーザー”を定義してから、下でそれを使う」ので、
上から順に読めます。

プログラミングで言えば、

サブクエリ：無名関数をその場で書いて、その場でしか使わない。
CTE：名前付き関数を上で定義して、下で呼び出す。

というイメージに近いです。

例題1：売上集計を段階に分けて書く

「“まず日別集計、そのあと月別集計”をCTEで表現する」

少しだけ複雑な例を見てみます。
注文テーブル orders があるとします。

CREATE TABLE orders (
  id         SERIAL PRIMARY KEY,
  user_id    INTEGER NOT NULL,
  amount     INTEGER NOT NULL,
  created_at TIMESTAMPTZ NOT NULL
);

CREATE TABLE orders (
  id         SERIAL PRIMARY KEY,
  user_id    INTEGER NOT NULL,
  amount     INTEGER NOT NULL,
  created_at TIMESTAMPTZ NOT NULL
);

ここで、「日別の売上を集計して、そのあと月別にまとめる」という処理を考えます。

CTEを使うと、こう書けます。

WITH daily_sales AS (
  SELECT
    DATE(created_at) AS day,
    SUM(amount)      AS total_amount
  FROM orders
  GROUP BY DATE(created_at)
),
monthly_sales AS (
  SELECT
    DATE_TRUNC('month', day) AS month,
    SUM(total_amount)        AS monthly_amount
  FROM daily_sales
  GROUP BY DATE_TRUNC('month', day)
)
SELECT *
FROM monthly_sales
ORDER BY month;

WITH daily_sales AS (
  SELECT
    DATE(created_at) AS day,
    SUM(amount)      AS total_amount
  FROM orders
  GROUP BY DATE(created_at)
),
monthly_sales AS (
  SELECT
    DATE_TRUNC('month', day) AS month,
    SUM(total_amount)        AS monthly_amount
  FROM daily_sales
  GROUP BY DATE_TRUNC('month', day)
)
SELECT *
FROM monthly_sales
ORDER BY month;

ここで起きていることを、言葉で整理します。

daily_sales で、「日ごとの売上」を作る。
monthly_sales で、「日ごとの売上を集約して、月ごとの売上」にする。
最後の SELECT で、「月ごとの売上」を取り出す。

CTEを使うことで、「日別集計 → 月別集計」という“処理の段階”が、そのままSQLの構造に現れています。
これが「可読性の高いSQL構築」の典型パターンです。

例題2：フィルタ＋集計＋ランキングを分ける

「“まず対象を絞る、そのあと集計、そのあと順位付け”」

もう1つ、ユーザー別の売上ランキングを作る例を見てみます。

「直近30日間の注文だけを対象にする」
「ユーザーごとに売上合計を出す」
「売上の高い順にランキングする」

これをCTEで書くと、こうなります。

WITH recent_orders AS (
  SELECT *
  FROM orders
  WHERE created_at >= NOW() - INTERVAL '30 days'
),
user_sales AS (
  SELECT
    user_id,
    SUM(amount) AS total_amount
  FROM recent_orders
  GROUP BY user_id
),
ranked_users AS (
  SELECT
    user_id,
    total_amount,
    RANK() OVER (ORDER BY total_amount DESC) AS sales_rank
  FROM user_sales
)
SELECT *
FROM ranked_users
WHERE sales_rank <= 10;

WITH recent_orders AS (
  SELECT *
  FROM orders
  WHERE created_at >= NOW() - INTERVAL '30 days'
),
user_sales AS (
  SELECT
    user_id,
    SUM(amount) AS total_amount
  FROM recent_orders
  GROUP BY user_id
),
ranked_users AS (
  SELECT
    user_id,
    total_amount,
    RANK() OVER (ORDER BY total_amount DESC) AS sales_rank
  FROM user_sales
)
SELECT *
FROM ranked_users
WHERE sales_rank <= 10;

段階を言葉にすると、

recent_orders：直近30日の注文だけに絞る。
user_sales：その注文をユーザーごとに集計する。
ranked_users：売上合計に基づいてランキングを付ける。
最後のSELECT：トップ10だけを取る。

もしこれをサブクエリだけで書こうとすると、
FROM (SELECT ... FROM (SELECT ...) ...) のようにネストが深くなり、
「どこで何をしているか」が一気に分かりにくくなります。

CTEは、「処理のステップに名前を付けて、上から順に読めるようにする」ための道具です。

CTEの“読みやすさ”がなぜ重要か

「SQLも“コード”だから、分割して名前を付けるべき」

ここで、少しだけ本質的な話をします。

SQLは「ただのクエリ」ではなく、「アプリケーションのコードの一部」です。
長くなればなるほど、「何をしているか」が分かりにくくなり、バグの温床になります。

CTEを使うと、次のようなメリットがあります。

処理の段階に名前が付くので、「このWITHは何をしているか」を説明しやすい。
上から順に読めるので、「全体の流れ」を追いやすい。
途中結果を再利用できるので、「同じサブクエリを何度も書く」必要がなくなる。

プログラミングで「関数に切り出す」「変数に意味のある名前を付ける」のと同じで、
SQLでも「CTEで分割して名前を付ける」ことで、可読性と保守性が一気に上がります。

PostgreSQLはCTEを標準的にサポートしているので、
「少し複雑なSQLを書き始めたら、まずWITH句を検討する」という癖をつけると、
後から自分や他人が読んだときのストレスがかなり減ります。

Day8 前半のまとめ

CTE（WITH句）は、「そのクエリの中だけで使える一時的なテーブルに名前を付ける仕組み」であり、WITH recent_users AS (SELECT ... FROM users ...) SELECT * FROM recent_users ... のように「途中結果を上で定義して、下でテーブルのように扱う」ことで、サブクエリだらけのネストを避けて“上から順に読めるSQL”に変えてくれる。
日別売上→月別売上のような「処理の段階」を daily_sales・monthly_sales としてCTEに分けたり、直近30日注文→ユーザー別集計→ランキングの流れを recent_orders・user_sales・ranked_users として名前付きのステップにすることで、「SQLもコードだから、分割して名前を付けるべきものだ」という感覚を持ち、可読性の高いSQL構築の入口に立つ――これが Day8 前半の着地点になる。