SQL Interview Prep · レッスン

ネストしたクエリをCTEにリファクタリングする

Q: 「ネストしたクエリをCTEにリファクタリングする」で何を学びますか？

面接で使える、読みにくいネストクエリを段階的なCTEに変えるパターンを学びます。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

面接で使える、読みにくいネストクエリを段階的なCTEに変えるパターンを学びます。

「ネストしたクエリをCTEにリファクタリングする」はCoddyKit上の無料SQL Interview Prepレッスンです。これはレッスン4/4です。下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。これはSQL Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Interview Prepコースには全4レッスンが含まれています。

ライブコーディング面接でのリファクタリング

中級レベルの面接でよく出る質問に、「このクエリを読みやすくしてください」というものがあります。面接官は深くネストされたSELECTを提示し、あなたがどのように分解するかを見ます。ネストを名前付きCTEの連続したステップに変えるのが、最も整理された答えです。

このレッスンでは、ホワイトボード上でも落ち着いて実行できるよう、具体的な手順を学びます。

最も内側のクエリから始める

ネストしたサブクエリは、概念的には内側から外側へ実行されます。そのため、クエリも内側から外側へ読みます。まず最も深い括弧内のSELECTを見つけます。そこが最初のパイプラインステージです。

そのステージに説明的な名前を付け、CTEに切り出します。その内側のブロックを参照していた箇所は、すべて代わりにCTE名を参照するようになります。

SELECT *
FROM (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
) t
WHERE t.total > 1000;

1階層をCTEに切り出す

最も内側にある派生テーブルを取り出し、CTEに昇格させます。外側のクエリは、名前付きCTEから選択するようになる点を除けば、そのままです。

この1回の操作だけで、頭の中で展開しなければならないネストが1階層減り、そのステップに意味のある名前を付けられます。

WITH spend AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
)
SELECT *
FROM spend
WHERE total > 1000;

本格的なネストの例

リファクタリングが難しい例を見てみましょう。2階層のネストに加えて、相関サブクエリ風のフィルタがあります。目的は、支出額が上位の層にいる顧客の平均注文額を求めることです。

正しいクエリではありますが、読みにくい構造です。これをステージごとに分解していきます。

SELECT AVG(o.amount) AS avg_order
FROM orders o
WHERE o.customer_id IN (
    SELECT customer_id
    FROM (
        SELECT customer_id, SUM(amount) AS total
        FROM orders
        GROUP BY customer_id
    ) s
    WHERE s.total > 1000
);

最初のステージに名前を付ける

最も深いブロックでは、顧客ごとの合計支出額を計算しています。これをspendという名前のCTEに切り出します。これで中間層は、そのCTEを単純に絞り込むだけになります。

抽出するたびにネストの深さが1つ減り、自己文書化された名前が追加される点に注目してください。

WITH spend AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
)
SELECT AVG(o.amount) AS avg_order
FROM orders o
WHERE o.customer_id IN (
    SELECT customer_id FROM spend WHERE total > 1000
);

2番目のステージに名前を付ける

spendに対するフィルタを独立したCTEであるbig_spendersに切り出します。残ったメインクエリは、明確な名前の集合に対するフラットな結合または所属判定になります。

これで各ステージが1つの責務だけを持つようになります。これは、整理されたSQLの特徴です。

WITH spend AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders GROUP BY customer_id
),
big_spenders AS (
    SELECT customer_id FROM spend WHERE total > 1000
)
SELECT AVG(o.amount) AS avg_order
FROM orders o
JOIN big_spenders b ON b.customer_id = o.customer_id;

リファクタリング中も意味を維持する

鉄則は、リファクタリングによって結果を変えないことです。出力を気付かないうちに変えてしまう落とし穴に注意してください。

INをJOINに変更すると、右側がdistinctでない場合に重複行が発生することがあります。
NULLを含むNOT INは、NOT EXISTSとは異なる動作をします。
集約の粒度を同じまま維持する必要があります。

こうしたリスクを声に出して説明すると、慎重に作業していることを示せます。

リファクタリングを検証する

リファクタリングが元の処理に忠実であることを、どのように証明しますか。両方のバージョンを実行して行数とチェックサムを比較する、またはサンプルの結果セットを比較する、と説明してください。

面接で、行数といくつかのサンプル行を比較して検証しますと説明するだけでも、構文を書き換える以上のエンジニアリング上の規律を示せます。

SELECT COUNT(*), SUM(amount)
FROM orders
WHERE customer_id IN (SELECT customer_id FROM big_spenders);

リファクタリングしない場合

リファクタリングが常に改善につながるとは限りません。単純で浅いサブクエリはそのままのほうが分かりやすい場合があります。また、多数の小さなCTEに分割しすぎると、かえって可読性が損なわれることもあります。

適切に判断してください。ネストによって意図が分かりにくくなっている場合や、ロジックを再利用する場合にリファクタリングします。クエリが名前付きの個別の手順として上から下へ読めるようになった時点で、そこで止めると面接官に伝えてください。

リファクタリングのチェックリスト

繰り返し使える方法として、次のように説明できます。

内側から読み、最も深いサブクエリを見つけます。
それを名前付きCTEに切り出します。
一度に1段階ずつ、外側に向かって繰り返します。
各段階が生成するものに基づいて名前を付けます。
結果が変わっていないことを確認します（IN/JOINとNULLに関する落とし穴に注意します）。

これにより、複雑で不安に感じるネストされたクエリを、落ち着いて段階的に書き換えられます。

リファクタリングを説明する

作業しながら説明してください。最も内側のブロックは顧客ごとの支出額なので、spendという名前にします。次の層では支出額の大きい顧客を絞り込みます。そして外側のクエリで、その顧客たちの注文金額を平均します。

面接官は正しさだけでなく、説明力も評価します。段階ごとに説明しながらリファクタリングすれば、面接官が求める中堅レベルの成熟度を明確に示せます。

クイックチェック

深くネストされたクエリをCTEにリファクタリングするとき、最初に行うべき正しい操作を特定してください。

まとめ：CTEへのリファクタリング

落ち着いて繰り返し実践できるリファクタリング方法を学びました。内側から読み、最も深いサブクエリを名前付きCTEに切り出し、一度に1段階ずつ外側へ進めます。

各段階が生成するものに基づいて名前を付けます。
意味を保ちます。INとJOINによる重複の違いや、NULLに関する落とし穴に注意します。
行数とサンプル行を比較して検証します。
分割しすぎないでください。クエリが明確な名前付きの手順として読めるようになったら止めます。

これでCTEコースは完了です。実際の面接でも、自信を持ってリファクタリングできるようになりました。

よくある質問

「ネストしたクエリをCTEにリファクタリングする」レッスンは無料ですか？

はい。「ネストしたクエリをCTEにリファクタリングする」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Interview Prepコースには全4レッスンが含まれています。

「ネストしたクエリをCTEにリファクタリングする」で何を学びますか？

面接で使える、読みにくいネストクエリを段階的なCTEに変えるパターンを学びます。ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Interview Prepを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。これはレッスン4/4です。

「ネストしたクエリをCTEにリファクタリングする」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Interview Prepレッスンでコードを書いて実行できますか？

はい。すべてのSQL Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

最初のCTEを書く
複数のCTEを連結する
CTE、サブクエリ、一時テーブルの比較
ネストしたクエリをCTEにリファクタリングする

← SQL Interview Prepに戻る