PostgreSQL初心者必見！CTE（WITH句）とサブクエリの使い方を徹底解説
生徒
「SQLで複雑なデータの取り出し方を調べていたら、『サブクエリ』とか『CTE』っていう言葉が出てきたんですけど、これって何ですか？」
先生
「それは、大きな表の中から『特定の条件で絞り込んだ小さな表』を作って、それを使ってさらに計算や検索をするテクニックのことですよ。サブクエリは『命令の中の命令』、CTE（WITH句）は『一時的な作業机』のようなイメージですね。」
生徒
「作業机ですか？難しそうに聞こえるけど、私でも使いこなせるようになりますか？」
先生
「もちろんです！まずは料理の下ごしらえをイメージしてみてください。材料（データ）を一度切っておいてから、最後に鍋（メインの命令）に入れるような手順を説明しますね。これを知ると、長い命令もスッキリ整理して書けるようになりますよ。」
1. PostgreSQLにおけるサブクエリとCTEの役割とは？
PostgreSQL（ポストグレスキューエル）などのデータベースを操作するとき、単純な「名簿から全員を表示する」という操作だけでは足りないことがあります。例えば、「平均点以上の生徒だけを抜き出す」といった処理をしたい場合、まず「平均点は何点か？」を計算し、その結果を使ってさらに「その点数より高い人」を探すという2段階のステップが必要になります。
このように、一つの命令（クエリ）の中で別の命令を実行することをサブクエリと呼びます。そして、このサブクエリをより読みやすく、整理して書くための仕組みがCTE（共通テーブル式、Common Table Expressions）です。初心者の方にとっては「WITH句（ウィズく）」と覚えるのが一番近道です。これらは、複雑なパズルを小さなピースに分けて組み立てるための便利な道具なのです。
2. サブクエリ（副問合せ）の基本的な考え方
サブクエリは、大きな指示の中に含まれる「小さな指示」です。例えば、お母さんに「冷蔵庫にある賞味期限が一番近い卵を使って料理して」と頼まれたとします。このとき、「賞味期限が一番近い卵はどれか？」を調べるのがサブクエリです。その結果（特定の卵）を使って「料理する」のがメインの指示になります。
ITの世界では、この「（ ）」カッコで囲った小さな命令を先に実行し、その答えを外側の命令に渡します。まずは簡単なデータを見てみましょう。あるお店の商品の在庫リストをイメージしてください。
【実行前のテーブル：products（商品リスト）】
id | name | price | category
---+------------+-------+----------
1 | リンゴ | 100 | フルーツ
2 | バナナ | 80 | フルーツ
3 | キャベツ | 150 | 野菜
4 | トマト | 120 | 野菜
5 | 牛肉 | 500 | 肉
6 | 鶏肉 | 300 | 肉
ここで、「全体の平均価格よりも高い商品」を探してみます。
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);
【実行結果】
name | price
---------+-------
キャベツ | 150
牛肉 | 500
鶏肉 | 300
この命令では、カッコの中の SELECT AVG(price) FROM products が先に動いて「平均価格」を計算し、その数字を使って外側の命令が動いています。
3. CTE（WITH句）は「名前付きの作業机」
サブクエリは便利ですが、命令が複雑になるとカッコが何重にもなり、後で読み返したときに「何をしているのか」が分からなくなりがちです。そこで登場するのがCTE（WITH句）です。CTEを使うと、一時的な計算結果に「名前」をつけて保存しておくことができます。
料理に例えると、サブクエリは「料理をしながらその場で野菜を切る」ようなものですが、CTEは「あらかじめ野菜を切ってボウルに入れ、『刻み野菜』という名前のシールを貼って机に置いておく」ようなものです。最後にそれらのボウルを使って一気に調理するので、流れが非常にスムーズになります。
CTEを使うときの基本ルールは、WITH 名前 AS (小さな命令) と書くことです。これにより、プログラムの読みやすさが劇的に向上します。
4. CTEの書き方と基本ルール
それでは、具体的にCTEを使ってデータを抽出してみましょう。今回は「部署ごとの売上合計」を計算し、そこからさらに条件を絞る例を考えます。まずは、社員の売上データを見てみましょう。
【実行前のテーブル：sales（売上データ）】
id | staff_name | department | amount
---+------------+------------+-------
1 | 田中 | 営業 | 1000
2 | 佐藤 | 営業 | 1500
3 | 鈴木 | 開発 | 2000
4 | 高橋 | 開発 | 800
5 | 伊藤 | 総務 | 500
このデータから、「営業部の売上だけをまとめた表」を一時的に作り、そこからデータを取り出します。
WITH sales_summary AS (
SELECT staff_name, amount
FROM sales
WHERE department = '営業'
)
SELECT *
FROM sales_summary
WHERE amount > 1200;
【実行結果】
staff_name | amount
-----------+-------
佐藤 | 1500
WITH sales_summary AS (...) の部分で、「営業部だけの表（sales_summary）」という名前の作業机を作りました。その後、その机を使って 1200円より多い売上の人を探しています。このように、やりたいことを順番に整理して書けるのがCTEの強みです。
5. 複数のCTEを連結して使う方法
CTEの素晴らしいところは、一つの作業机だけでなく、複数の机を並べて使えることです。例えば、「まず全体の合計を出し、次に平均を出し、最後に比較する」といった複雑な手順を、カンマ（,）で区切って並べるだけで実現できます。
プログラミング未経験の方でも、上から順番に読んでいけば内容が理解できるはずです。以下の例では、まず「商品ごとの利益」を計算し、次に「その平均利益」を出し、最後に「平均以上の利益を出している商品」を表示します。
【実行前のテーブル：items（商品単価とコスト）】
item_id | item_name | price | cost
--------+-----------+-------+------
1 | パソコン | 100000| 70000
2 | マウス | 3000 | 1000
3 | キーボード| 5000 | 4000
4 | モニター | 20000 | 12000
WITH profit_table AS (
SELECT item_name, (price - cost) AS profit
FROM items
),
average_profit AS (
SELECT AVG(profit) AS avg_p
FROM profit_table
)
SELECT *
FROM profit_table
WHERE profit > (SELECT avg_p FROM average_profit);
【実行結果】
item_name | profit
----------+-------
パソコン | 30000
モニター | 8000
profit_table で利益を計算し、その結果を使って average_profit で平均を出しています。前のCTEを次のCTEで利用できるため、思考のプロセスをそのままコードに落とし込むことができるのです。
6. サブクエリとCTEはどちらを使うべき？
「どちらも同じようなことができるなら、どっちを使えばいいの？」と迷うかもしれません。結論から言うと、基本的にはCTE（WITH句）を使うのがおすすめです。理由は主に3つあります。
- 読みやすさ： 上から下に順番に処理が書けるので、人間にとって理解しやすいです。
- 再利用性： 同じ一時的な表を、一つの命令の中で何度も使い回すことができます。
- メンテナンス性： 後で修正が必要になったとき、どの部分が何をしているのか一目で分かります。
一方で、非常にシンプルで短い条件（例：1つの数値だけを比較したい場合など）であれば、サブクエリの方が手軽に書けることもあります。状況に合わせて使い分けられるようになると、あなたも立派なSQL使いの仲間入りです。
7. データベース操作を支える用語解説
ここまでの説明で出てきた、パソコン初心者の方が「おや？」と思うかもしれない用語を整理しておきましょう。
・データベース： 情報を整理して保存しておくための、デジタルな引き出しのようなものです。
・レコード： 表の中の「横一行分」のデータのことです。一人の情報や、一つの商品の情報を指します。
・カラム： 表の中の「縦一列分」の項目のことです。「名前」「年齢」などの項目名を指します。
・SELECT（セレクト）： 「選ぶ」という意味で、データを取り出すときに出す一番基本的な命令です。
・FROM（フロム）： 「どこから」という意味で、どの表（テーブル）からデータを持ってくるかを指定します。
・WHERE（ウェア）： 「どこで」という意味で、データを取り出すときの条件を指定します。
これらの用語は、SQLを扱う上で毎日使うものです。今は完璧に覚えなくても、「あ、あの引き出しのことね」くらいに思っておけば大丈夫ですよ。
8. 初心者がハマりやすいポイントと注意点
CTEを使うとき、一つだけ注意してほしいのが「セミコロン（;）」の位置です。SQLの命令は、最後に「ここで終わりですよ」という意味でセミコロンを打ちます。WITH句を使っている間はまだ命令が続いているので、一番最後にだけセミコロンを置くようにしてください。
また、CTEで作った名前付きの表は、その命令が終わると消えてしまいます。保存される本当のテーブルではないので、あくまで「その場限りの作業用」であることを覚えておきましょう。
さらにもう一点、PostgreSQLでは、CTEの中に「データを追加（INSERT）」したり「変更（UPDATE）」したりする命令を入れることもできますが、まずは今回紹介した「データの抽出（SELECT）」から慣れていくのが上達のコツです。
9. 実際の業務でどう使われるのか？
実際の仕事の現場では、数万、数百万という膨大なデータから特定の情報を探し出します。例えば、ネットショップの管理画面で「先月、3回以上買い物をしてくれたお客様の中で、まだ今月は一度も買い物をしていない人を抽出して、クーポンを送るリストを作る」といったときに、CTEが真価を発揮します。
1. まず「先月3回以上買った人」のリストをCTEで作る。
2. 次に「今月買った人」のリストをCTEで作る。
3. 最後に1のリストから2のリストに含まれない人を引き算する。
このように、複雑なビジネスのルールも、CTEを使えば一つずつ階段を登るように解決できるのです。これができると、データ分析のスキルがぐんと上がります。
10. PostgreSQLを触ってみよう
最後に、もっとも大切なことは、実際に手を動かしてコードを書いてみることです。PostgreSQLは世界中で使われている非常に安定したデータベースシステムです。最初はエラーが出て真っ赤な文字が表示されるかもしれませんが、怖がる必要はありません。エラーメッセージは「ここがちょっと違うよ」というヒントに過ぎません。
今回学んだ「サブクエリ」と「CTE」を使って、自分の身の回りにあるデータ（家計簿や本のリストなど）を整理してみると、驚くほど便利に感じられるはずです。まずは小さな成功体験を積み重ねていきましょう。応援しています！
まとめ
PostgreSQL（ポストグレスキューエル）を用いたデータ操作において、サブクエリとCTE（共通テーブル式、WITH句）は、複雑なデータ分析や抽出作業を効率化するために欠かせない強力なツールです。本記事では、初心者がつまずきやすいこれらの概念を、料理の下ごしらえや作業机といった具体的なイメージを用いて解説しました。
サブクエリとCTEの決定的な違いとメリット
サブクエリは「命令の中に別の命令を入れ込む」手法であり、直感的かつ簡潔に書けるのが特徴です。一方のCTEは、一時的な結果セットに名前を付けて定義することで、コードの可読性を飛躍的に高めることができます。特に大規模なデータを扱う実務現場では、後からコードを見直す際のメンテナンス性や、同じ計算結果を何度も参照できる再利用性の観点から、CTEの利用が推奨されるケースが非常に多いです。
実践的なSQLサンプルプログラム
ここでは、さらに理解を深めるために、実務でよくある「店舗ごとの売上目標達成率」を計算するシナリオを考えてみましょう。まず、各店舗の売上データと目標額が設定されたテーブルを用意します。
【実行前のテーブル：shop_sales（店舗別売上データ）】
id | shop_name | sales_amount | target_amount | region
---+------------+--------------+---------------+--------
1 | 東京本店 | 5000000 | 4000000 | 関東
2 | 横浜店 | 3200000 | 3500000 | 関東
3 | 大阪支店 | 4500000 | 4000000 | 関西
4 | 名古屋店 | 2800000 | 3000000 | 中部
5 | 福岡店 | 3500000 | 3200000 | 九州
6 | 札幌店 | 2100000 | 2500000 | 北海道
このデータから、「目標を達成している店舗（達成率100%以上）」を抽出し、さらに「全店舗の平均達成率」と比較する複雑な処理をCTEで記述します。
WITH achievement_calc AS (
-- 各店舗の達成率を計算するCTE
SELECT
shop_name,
region,
sales_amount,
target_amount,
(CAST(sales_amount AS FLOAT) / target_amount) * 100 AS achievement_rate
FROM shop_sales
),
high_achievers AS (
-- 達成率が100%以上の店舗だけを絞り込むCTE
SELECT * FROM achievement_calc
WHERE achievement_rate >= 100
)
-- 最終的な結果として、達成店舗と全体の平均達成率を比較表示
SELECT
shop_name,
ROUND(CAST(achievement_rate AS NUMERIC), 1) AS rate_percent,
(SELECT ROUND(AVG(achievement_rate)::NUMERIC, 1) FROM achievement_calc) AS overall_avg_rate
FROM high_achievers;
【実行結果】
shop_name | rate_percent | overall_avg_rate
----------+--------------+------------------
東京本店 | 125.0 | 104.9
大阪支店 | 112.5 | 104.9
福岡店 | 109.4 | 104.9
PostgreSQLをマスターするためのステップ
SQL学習において、SELECT文やWHERE句といった基本をマスターした後の次のステップが、このCTEやサブクエリです。これらを使いこなすことで、Excelでは処理が重くなってしまうような大量のデータに対しても、数秒で正確な集計結果を導き出すことが可能になります。
「WITH句を使うときはカンマで区切る」「セミコロンは最後に一つだけ」といった文法的なルールを守りつつ、まずは簡単な集計から練習してみましょう。データ分析、マーケティング、システム開発など、あらゆるIT分野でPostgreSQLのスキルは一生モノの武器になります。
生徒
「先生、まとめまで読んでみて、CTE（WITH句）の凄さがやっと分かりました！サブクエリだとカッコが入れ子になって『あれ、このカッコどこで閉じるんだっけ？』って迷子になることが多かったんですけど、CTEなら上から順番に定義していけるから迷わないですね。」
先生
「その通りです！まさにそこがCTEの最大のメリットなんですよ。プログラミングで変数に値を代入する感覚に近いですよね。achievement_calc という名前をつけた『作業机』を、その後の処理で何度も使い回せるのが便利だと思いませんか？」
生徒
「はい！さっきの店舗売上のコードも、まず計算をして、次に絞り込みをして、最後に表示する…という手順がすごく明確でした。でも、実行結果の ::NUMERIC とか CAST というのは何ですか？」
先生
「おっ、鋭いところに気づきましたね。PostgreSQLでは割り算をするとき、整数同士だと結果も整数（小数点以下切り捨て）になってしまうことがあるんです。なので、『これは小数として扱ってね』と教えてあげるのが CAST や :: という書き方です。これも実務でよく使うテクニックですよ。」
生徒
「なるほど、データ型も大事なんですね。あと、CTEってその場限りのテーブルなんですよね？データベースにずっと残るわけじゃないから、実験もしやすそうです。」
先生
「そうなんです。実際のテーブルを汚さずに、複雑な試行錯誤ができるのが魅力です。実務では『去年の売上』と『今年の売上』を別々のCTEで集計して、最後にガッチャンコして比較する、なんて使い方がよくされます。これができると、データサイエンティストやエンジニアとして一目置かれるようになりますよ。」
生徒
「一目置かれる！それは頑張りがいがありますね。まずは自分の家計簿データをデータベースに入れて、月ごとの平均支出を超える日をCTEで探す練習をしてみます！」
先生
「素晴らしい意気込みです。エラーが出ても、それは成長のチャンス。PostgreSQLのドキュメントを読みながら、色々なクエリに挑戦してみてくださいね。応援しています！」
