複数のCTEを連結する
互いを参照する名前付きステップのパイプラインを構築します。
「複数のCTEを連結する」はCoddyKit上の無料Coding Interview Prepレッスンです。 これはレッスン2/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはCoding Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 Coding Interview Prepコースには全4レッスンが含まれています。
CTEを連結する理由
実際の面接問題が1ステップで収まることはめったにありません。CTEを連結すると、名前付きステージのパイプラインを構築できます。各ステージが前のステージの出力を変換します。これは、シニアエンジニアが難しいクエリを扱いやすい単位に分解する方法を反映しています。
サブクエリを3階層も深くネストする代わりに、各ステップを1回だけ記述して名前を付け、後続のステップから参照できます。
カンマ区切り構文
複数のCTEを定義するには、WITHを1回だけ書き、その後、名前付きブロックをカンマで区切ります。WITHキーワードを繰り返してはいけません。
- 先頭に
WITHを1つだけ置きます。 - 各CTE定義の間にカンマを置きます。
- 最後のメインクエリの前にはカンマを置きません。
WITH a AS (
SELECT customer_id FROM orders
),
b AS (
SELECT customer_id FROM a
)
SELECT *
FROM b;後続のCTEから先行するCTEを参照できる
連結の威力は、CTEがその前に定義された任意のCTEから読み取れることにあります。この前方のみの可視性によって、依存関係の連鎖を構築できます。
先に定義したCTEから後に定義するCTEを参照することはできないため、順序が重要です。生のデータから最終的な形へ向かう順にステージを並べてください。
WITH filtered AS (
SELECT *
FROM events
WHERE event_type = 'purchase'
),
per_user AS (
SELECT user_id, COUNT(*) AS purchases
FROM filtered
GROUP BY user_id
)
SELECT *
FROM per_user;実例：3段階のパイプライン
質問：1000ドルを超えて支出した顧客の中で、平均支出額はいくらですか？これを3段階に分けます。顧客ごとの合計支出額を求め、高額支出者を絞り込み、最後にその平均を求めます。
各CTEの名前が目的を説明するため、レビュー担当者は処理の流れをすぐに理解できます。
WITH spend AS (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
),
big_spenders AS (
SELECT customer_id, total
FROM spend
WHERE total > 1000
)
SELECT AVG(total) AS avg_big_spend
FROM big_spenders;定義の順序が重要
可視性が前方のみであるため、別のCTEに依存するCTEは、その依存先の後に記述する必要があります。まだ定義されていない名前を参照すると、データベースは「relation does not exist」エラーを発生させます。
よい習慣は、CTEの一覧を上から下へ読み、使用している各名前がその前にすでに登場していることを確認することです。
複数のCTEから1つのCTEを参照する
1つのCTEを複数の後続CTEに供給できます。ここで、連結がネストしたサブクエリに勝ります。基礎となる結果を1回だけ計算し、そこから分岐できるためです。
ここではactiveとrecentの両方がbaseから読み取るため、ロジックの重複を避けています。
WITH base AS (
SELECT * FROM users WHERE deleted = false
),
active AS (
SELECT id FROM base WHERE last_login > NOW() - INTERVAL '7 days'
),
recent AS (
SELECT id FROM base WHERE created_at > NOW() - INTERVAL '30 days'
)
SELECT (SELECT COUNT(*) FROM active) AS active_cnt,
(SELECT COUNT(*) FROM recent) AS recent_cnt;2つのCTEを結合する
連結したCTEは、メインクエリで頻繁に結合されます。それぞれの側を個別に計算してから組み合わせます。これにより、各計算が分離され、結合処理が単純になります。
以下では、注文数と返金数を個別に計算し、その後、顧客ごとに結合します。
WITH orders_cte AS (
SELECT customer_id, COUNT(*) AS orders
FROM orders GROUP BY customer_id
),
refunds_cte AS (
SELECT customer_id, COUNT(*) AS refunds
FROM refunds GROUP BY customer_id
)
SELECT o.customer_id, o.orders, COALESCE(r.refunds, 0) AS refunds
FROM orders_cte o
LEFT JOIN refunds_cte r ON r.customer_id = o.customer_id;ネストよりも可読性を優先する
3階層のネストしたサブクエリと、3つのCTEによるパイプラインを比較してみましょう。ネストしたバージョンでは、読み手が内側から外側へ頭の中で展開しなければなりません。CTEのバージョンは、実行順序に沿って上から下へ読めます。
面接官がCTEのアプローチを評価するのは、それが本番環境で保守したいコードだからです。各ステージに名前を付けることは、古くならないドキュメントになります。
連結でよくある間違い
初心者は、メインのSELECTの直前、最後のCTEの後にカンマを付けてしまいがちです。この末尾のカンマは構文エラーになります。
- カンマを置くのはCTE定義の間だけです。
- 最後の閉じ括弧の直後にはメインクエリを置き、カンマは置きません。
もう1つの落とし穴は、各CTEの括弧内に完全なSELECTが必要であることを忘れることです。
各ステージは個別に実行されるのか
面接で問われる微妙なポイントがあります。論理的にはパイプラインは個別のステップとして読めますが、オプティマイザはそれらをインライン化して、1つの実行計画に統合することがあります。ほとんどのエンジンでは、中間結果をマテリアライズするコストを必ずしも負担する必要はありません。
つまり、連結によって自分自身はクエリを考えやすくなりますが、必ずしもパフォーマンスのコストが発生するわけではありません。この点に触れると、深い理解を示せます。
パイプラインのようにステージに名前を付ける
よいステージ名を付けると、クエリは自己文書化されたコードになります。処理内容ではなく、各ステップの出力を説明する名前を選んでください。
spendやbig_spendersは、step1やstep2より適しています。- 読み手がCTEの名前だけで処理全体の流れを推測できるようにします。
- ステージ間で一貫した命名をすると、メインクエリの結合が明確になります。
面接でステージにわかりやすい名前を付けると、保守しやすい本番用SQLを書く力を示せます。
クイックチェック
連結したCTE同士がどのように参照し合うかを理解できているか確認しましょう。
CTEの連結を振り返る
パイプラインの構築方法を学びました。WITHは1つだけ置き、CTE定義をカンマで区切り、前方のみの可視性によって各ステージから先行するステージを読み取ります。
- 生のデータから最終結果へ向かう順にCTEを並べます。
- 基礎となるCTEを複数の後続ステップで再利用します。
- メインクエリの前に末尾のカンマを置きません。
- 連結によって可読性が向上しますが、必ずしもパフォーマンスが低下するわけではありません。
次は、CTE、サブクエリ、一時テーブルを比較します。
よくある質問
「複数のCTEを連結する」レッスンは無料ですか？
はい。「複数のCTEを連結する」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、Coding Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 Coding Interview Prepコースには全4レッスンが含まれています。
「複数のCTEを連結する」で何を学びますか？
互いを参照する名前付きステップのパイプラインを構築します。 ブラウザで直接実行するハンズオンコードでCoding Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。
Coding Interview Prepを始めるのに経験は必要ですか？
事前経験は必要ありません。CoddyKitのCoding Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン2/4です。
「複数のCTEを連結する」レッスンにはどのくらい時間がかかりますか？
ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。
このCoding Interview Prepレッスンでコードを書いて実行できますか？
はい。すべてのCoding Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。