PostgreSQLのCTE（WITH句）完全解説！複雑なSQLを整理して読みやすくする書き方

1. CTE（WITH句）とは何か？

1. CTE（WITH句）とは何か？

データベースを操作する言語「SQL」の中でも、PostgreSQL（ポストグレスキューエル）という種類で非常によく使われるのがCTE（Common Table Expression）です。日本語では「共通テーブル式」と呼びますが、実際の書き方から「WITH句（ウィズく）」と呼ばれることの方が多いかもしれません。

これは、一言で言うと「使い捨ての仮のテーブル（表）に名前を付けて、その後の処理で再利用する仕組み」のことです。

通常、SQLで複雑な集計をしようとすると、命令の中にさらに別の命令を書き込む「サブクエリ（副問合せ）」というものを使います。しかし、サブクエリが何重にも重なると、まるでマトリョーシカのように中身が複雑になり、人間には読めないスパゲッティのようなコードになってしまいます。CTEを使えば、まず「Aという計算結果を『お弁当箱』という名前にします」と宣言し、その後に「お弁当箱からおかずを取り出す」といった書き方ができるため、上から下へ流れるように自然に読むことができるようになります。

2. CTEを使うメリットと基本的な書き方

2. CTEを使うメリットと基本的な書き方

CTEを使う最大のメリットは、「可読性（読みやすさ）」が劇的に向上することです。また、同じ計算結果を一つのSQLの中で何度も使いまわしたい場合、一度名前を付けておけば何度も同じ命令を書かずに済むため、間違いが減り、修正も簡単になります。

基本的な構文は以下の通りです。

WITH 好きな名前 AS (中身の命令) SELECT ...

これを料理に例えてみましょう。例えば「カレー」を作るとき、「野菜を切る」「肉を炒める」といった準備工程に名前を付けてから、最後にそれらを合わせるような流れです。

3. 【実践例1】特定の条件で絞り込んだリストに名前を付ける

3. 【実践例1】特定の条件で絞り込んだリストに名前を付ける

まずは、簡単な例から見ていきましょう。ここでは「店舗の売上データ」を管理しているデータベースを想像してください。 以下の表は、どのお店で、いくら売れたかを記録した sales テーブルです。

id | store_name | amount | sale_date
---+------------+--------+------------
1  | 東京店      | 5000   | 2025-01-01
2  | 大阪店      | 3000   | 2025-01-02
3  | 東京店      | 8000   | 2025-01-03
4  | 名古屋店    | 4500   | 2025-01-04
5  | 大阪店      | 2000   | 2025-01-05
6  | 福岡店      | 6000   | 2025-01-06

この中から「東京店だけの売上」を抜き出し、さらにその中から「金額が6000円以上のもの」を探すという命令をCTEを使って書いてみます。

WITH tokyo_sales AS (
    SELECT *
    FROM sales
    WHERE store_name = '東京店'
)
SELECT *
FROM tokyo_sales
WHERE amount >= 6000;

このSQLでは、まず tokyo_sales という名前の仮の表を作り、そこに「東京店のデータだけ」を入れました。その後、その仮の表から「6000円以上のデータ」だけを表示させています。

id | store_name | amount | sale_date
---+------------+--------+------------
3  | 東京店      | 8000   | 2025-01-03

このように、まず条件で絞り込んだ塊に名前を付けることで、「何をしているのか」が明確になります。

4. 【実践例2】集計した結果をさらに分析する

4. 【実践例2】集計した結果をさらに分析する

次に、少し応用して「店舗ごとの合計売上」を計算し、その結果を使ってさらに別の計算をしてみましょう。 使用するのは同じ sales テーブルです。

id | store_name | amount | sale_date
---+------------+--------+------------
1  | 東京店      | 5000   | 2025-01-01
2  | 大阪店      | 3000   | 2025-01-02
3  | 東京店      | 8000   | 2025-01-03
4  | 名古屋店    | 4500   | 2025-01-04
5  | 大阪店      | 2000   | 2025-01-05
6  | 福岡店      | 6000   | 2025-01-06

「各店舗の合計売上」を出し、その中から「合計が10,000円を超えている店舗」だけを表示します。

WITH store_totals AS (
    SELECT store_name, SUM(amount) AS total_amount
    FROM sales
    GROUP BY store_name
)
SELECT store_name, total_amount
FROM store_totals
WHERE total_amount > 10000;

SUM（サム）は合計を計算する命令、GROUP BY（グループ・バイ）は同じ名前をまとめる命令です。

store_name | total_amount
-----------+--------------
東京店      | 13000

この書き方をしない場合、一つの文の中に集計関数が入り乱れて非常に読みづらくなりますが、CTEを使えば「まずは合計を出す」「次に条件で絞る」という二段構えで、初心者の方でも論理的に考えることができます。

5. 【実践例3】複数のCTEを組み合わせて使う

5. 【実践例3】複数のCTEを組み合わせて使う

CTEの凄さは、複数の「仮の表」を同時に作れる点にあります。 例えば、「商品のマスター表」と「注文履歴の表」があったとします。

products（商品マスター）テーブル

id | product_name | price
---+--------------+-------
1  | リンゴ        | 100
2  | バナナ        | 80
3  | メロン        | 1000
4  | ブドウ        | 500

orders（注文履歴）テーブル

order_id | product_id | quantity
---------+------------+----------
101      | 1          | 5
102      | 3          | 1
103      | 1          | 2
104      | 4          | 2
105      | 2          | 10

「果物の合計金額」を計算するために、「商品ごとの合計数量」を出し、そこに「価格」を掛け合わせるという複雑な処理を、CTEを2つ使って整理してみます。

WITH summary_quantity AS (
    -- 1. まず商品ごとの注文個数を合計する
    SELECT product_id, SUM(quantity) AS total_qty
    FROM orders
    GROUP BY product_id
),
calculated_sales AS (
    -- 2. 合計個数に商品の単価を掛け算する
    SELECT 
        p.product_name,
        p.price * s.total_qty AS sales_value
    FROM products p
    JOIN summary_quantity s ON p.id = s.product_id
)
-- 3. 最後に結果を表示する
SELECT * FROM calculated_sales
ORDER BY sales_value DESC;

WITHの後にカンマ , で区切ることで、いくつでも仮の表を定義できます。 それぞれのステップが独立しているため、「どこで計算ミスが起きているか」も探しやすくなります。

product_name | sales_value
-------------+-------------
ブドウ        | 1000
メロン        | 1000
バナナ        | 800
リンゴ        | 700

実行結果はこのようになります。複雑に見える計算も、小さなステップに分解すれば、パソコンに詳しくない人でも「なるほど、ここでは個数を数えているんだな」と理解できるようになります。

6. まとめを読まなくても分かる！CTEの重要なポイント

6. まとめを読まなくても分かる！CTEの重要なポイント

これまでに見てきた通り、PostgreSQLのCTE（WITH句）は、データ分析を強力にサポートしてくれる機能です。 特に「ウィンドウ関数」や「JSONB」といった高度な機能を扱うようになると、一度に多くの処理を詰め込みがちになります。そんな時こそ、CTEで一歩ずつ処理を区切ることが、ミスを防ぐ最大の秘訣です。

プログラミング未経験の方がSQLを学ぶ際、最初の一歩として「SELECT」や「WHERE」を覚えるのはもちろん大切ですが、このCTEのように「情報の整理術」を身につけておくと、後々の学習が驚くほどスムーズになります。

データベースは、ただの「データの入れ物」ではなく、あなたの指示次第で自由自在に形を変える「魔法の粘土」のようなものです。CTEという道具を使いこなして、美しく分かりやすい指示書（SQL）を作れるようになりましょう。