サブクエリとCTE: SQLを読みやすく分ける

Stage 4 — 第1章 | データ分析基礎カリキュラム 推定学習時間：30〜40分 | 難易度：★★★☆☆

この章で学ぶこと

分析SQLは、条件で絞る、集計する、別の表と結合する、さらに計算する、という処理が重なって長くなりがちです。 長いSQLを一気に書くと、どこで何をしているのか分かりにくくなります。

この章では、SQLを段階に分けて読みやすくする サブクエリ と CTE を学びます。

この章を終えると、こんなことができるようになります：

• サブクエリで集計結果を次の処理に使える
• CTEを使ってSQLを手順ごとに分けられる
• 複雑な分析SQLを読みやすく整理できる
• 実務でSQLを共有するときの見通しをよくできる

1. 分析SQLは段階に分けると読みやすい

たとえば「顧客ごとの累計購入金額を出し、10,000円以上の顧客だけを見る」という分析を考えます。 必要な処理は、次のように分けられます。

一つひとつは難しくありません。 しかし、すべてを一文に詰め込むと読みにくくなります。

分析SQLでは、「処理の手順を見える形にする」ことが大切です。

2. サブクエリで集計結果を使う

サブクエリは、SQLの中に書く別のSQLです。 次の例では、まず顧客ごとの売上を集計し、その結果から条件に合う顧客を取り出します。

SELECT
            customer_id,
            total_sales
          FROM (
            SELECT
              customer_id,
              SUM(total_amount) AS total_sales
            FROM orders
            WHERE status = 'completed'
            GROUP BY customer_id
          ) AS customer_sales
          WHERE total_sales >= 10000;
          

内側のSQLが customer_sales という一時的な表のように扱われます。 外側のSQLでは、その結果に対して WHERE total_sales >= 10000 を指定しています。

サブクエリは便利ですが、処理が増えると括弧の範囲が見えにくくなることがあります。

3. CTEで手順に名前をつける

CTEは、WITH 句を使ってSQLの途中結果に名前をつける書き方です。 先ほどの例をCTEで書くと、次のようになります。

WITH customer_sales AS (
            SELECT
              customer_id,
              SUM(total_amount) AS total_sales
            FROM orders
            WHERE status = 'completed'
            GROUP BY customer_id
          )
          SELECT
            customer_id,
            total_sales
          FROM customer_sales
          WHERE total_sales >= 10000;
          

customer_sales という名前を見るだけで、「顧客別売上を作っている」と分かります。 実務では、サブクエリよりCTEのほうが読みやすい場面が多くあります。

4. 複数のCTEで分析の流れを作る

CTEは複数並べることもできます。 次の例では、完了注文を取り出してから、顧客別に集計し、最後に顧客情報を結合します。

WITH completed_orders AS (
            SELECT
              order_id,
              customer_id,
              order_date,
              total_amount
            FROM orders
            WHERE status = 'completed'
          ),
          customer_sales AS (
            SELECT
              customer_id,
              COUNT(*) AS order_count,
              SUM(total_amount) AS total_sales
            FROM completed_orders
            GROUP BY customer_id
          )
          SELECT
            c.customer_id,
            c.customer_name,
            c.prefecture,
            s.order_count,
            s.total_sales
          FROM customer_sales AS s
          JOIN customers AS c
            ON s.customer_id = c.customer_id
          ORDER BY s.total_sales DESC;
          

このSQLは少し長いですが、処理の流れは追いやすくなっています。

• completed_orders: 分析対象の注文を作る
• customer_sales: 顧客ごとの指標を作る
• 最後の SELECT: 顧客名や都道府県を付けて表示する

CTE名は、処理の意味が分かる名前にしましょう。

5. CTEを書くときの注意点

CTEは便利ですが、何でも細かく分ければよいわけではありません。 読みやすさを上げるために使います。

CTEを使うときは、各CTEが「何を表す表なのか」を説明できるようにします。 tmp1, tmp2 のような名前は避け、completed_orders, customer_sales のように意味が伝わる名前にしましょう。

実務での使いどころ: 分析手順に名前をつける

CTEは、複雑な分析SQLを読みやすくするための道具です。 実務では、最終結果だけでなく「どの手順でその結果を作ったか」が重要になります。

WITH completed_orders AS (
            SELECT
              order_id,
              customer_id,
              total_amount
            FROM orders
            WHERE status = 'completed'
          ),
          customer_sales AS (
            SELECT
              customer_id,
              SUM(total_amount) AS sales
            FROM completed_orders
            GROUP BY customer_id
          )
          SELECT *
          FROM customer_sales;
          

このSQLでは、完了注文を作る手順と、顧客別に集計する手順を分けています。 CTE名を見るだけで、分析の流れが分かります。

CTEは長いSQLを短くするものではなく、分析の手順を読みやすくするものです。

ミニ演習

次の分析をCTEに分けるなら、どんな名前をつけるか考えてください。

1. 完了注文だけを抽出するCTE。
2. 顧客別売上を作るCTE。
3. 月別売上を作るCTE。
4. CTEを使うとレビューしやすくなる理由を説明する。

まとめ

この章のキーメッセージ： 分析SQLは、正しく動くだけでなく、後から読めることも重要です。サブクエリやCTEを使い、処理を意味のある単位に分けましょう。

この章の確認

1. サブクエリはどのような場面で使いますか？
2. CTEを使うと、SQLの読みやすさはどのように改善されますか？
3. 完了注文だけを取り出すCTE completed_orders を書いてください。
4. CTE名をつけるときに避けたい名前の例を説明してください。

関連演習

• SQL集計の基礎
• SQL集計（GROUP BY）
• SQL結合の基礎