Coding Interview Prep · レッスン

ウィンドウ関数の結果でフィルタリングする

Q: 「ウィンドウ関数の結果でフィルタリングする」レッスンは無料ですか？

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

Q: 「ウィンドウ関数の結果でフィルタリングする」で何を学びますか？

ウィンドウ関数をサブクエリまたはCTEで包まないとフィルタリングできない理由を学びます。 ブラウザで直接実行するハンズオンコードでCoding Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

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

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

Q: 「ウィンドウ関数の結果でフィルタリングする」レッスンにはどのくらい時間がかかりますか？

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

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

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

ウィンドウ関数をサブクエリまたはCTEで包まないとフィルタリングできない理由を学びます。

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

WHEREでウィンドウ関数を絞り込めない理由

面接でよくある「ひっかけ」です。WHERE ROW_NUMBER() OVER (...) = 1 と書くとエラーになります。ウィンドウ関数は WHERE、GROUP BY、HAVING では使用できません。

理由は論理的な実行順序にあります。WHERE は、ウィンドウ関数が評価される前に行を選択するために実行されます。つまり、その時点ではウィンドウ関数の計算がまだ行われておらず、フィルターで参照できません。

実行順序による説明

ウィンドウ関数は専用のフェーズで計算されます。このフェーズは FROM、WHERE、GROUP BY、HAVING の後で、最終的な ORDER BY と LIMIT の前に位置します。

そのため、WHERE が実行される時点では、順位や行番号はまだ存在しません。これを条件に使うには、まずウィンドウ関数の計算を完了させ、その後、生成された列を外側のクエリ層で絞り込む必要があります。

サブクエリでラップするパターン

標準的な解決方法は、内側のクエリ（派生テーブル）でウィンドウ関数を計算し、その結果に別名を付けてから、外側の WHERE でその別名を絞り込むことです。

派生テーブルには必ず別名が必要です（ここでは t）。これを忘れる候補者は面接官に注目されます。これで rn は、外側のクエリが比較できる通常の列になります。

SELECT *
FROM (
  SELECT
    name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
) t
WHERE rn = 1;

CTEパターン（より読みやすい方法）

共通テーブル式（Common Table Expression）は、より読みやすい構造で同じ処理を行います。WITH のステップでランキングを定義し、メインクエリで絞り込みます。

機能的にはサブクエリと同じですが、意図を上から下へ読み取れるため、ライブコーディングでは面接官が通常CTEを好みます。

WITH ranked AS (
  SELECT
    name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rn = 1;

実例：グループごとの上位N件

ウィンドウ関数で最も頻繁に扱う問題は、「部署ごとに給与の高い従業員を上位3人取得する」です。CTE内で順位を付け、外側で rn <= 3 を残します。

同順位の扱いに応じてランキング関数を選んでください。ROW_NUMBER なら部署ごとにちょうど3行で打ち切られます。境界の順位に同順位の行も含める必要がある場合は、RANK/DENSE_RANK に切り替えます。

WITH ranked AS (
  SELECT department, name, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department ORDER BY salary DESC
         ) AS rn
  FROM employees
)
SELECT department, name, salary
FROM ranked
WHERE rn <= 3
ORDER BY department, rn;

実例：累計を条件に絞り込む

ラップするパターンはランキングだけに使うものではありません。累計、移動平均、LAG による差分など、あらゆるウィンドウ関数の結果を同じ方法で絞り込む必要があります。

ここでは累計残高を計算し、その値が初めて1000を超えた行だけを残します。フィルターはウィンドウ関数の層の外側に置きます。

WITH balances AS (
  SELECT
    account_id, txn_date, amount,
    SUM(amount) OVER (
      PARTITION BY account_id ORDER BY txn_date
    ) AS running_balance
  FROM transactions
)
SELECT *
FROM balances
WHERE running_balance > 1000;

QUALIFY：一部のデータベースで使える短縮方法

Snowflake、BigQuery、Teradata、DuckDBには、ラッパーを使わずにウィンドウ関数の結果を直接絞り込める QUALIFY 句があります。ウィンドウ関数の後に実行されるため、まさに必要な場所で使えます。

知識の幅を示すために QUALIFY に言及するのはよいですが、これはSQL標準ではなく、PostgreSQL、MySQL、SQL Serverにはありません。これらでは引き続きサブクエリまたはCTEが必要です。

-- Snowflake / BigQuery only:
SELECT department, name, salary
FROM employees
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY department ORDER BY salary DESC
) = 1;

HAVINGとウィンドウ関数の絞り込みを混同しない

候補者はときどき、ランキングを絞り込むために HAVING を使おうとします。HAVING は GROUP BY による集約の後にグループを絞り込むものですが、ウィンドウ関数より前に実行されるため、ウィンドウ列を参照することもできません。

WHERE → グループ化およびウィンドウ関数の前に行を絞り込みます。
HAVING → 集約されたグループを絞り込みますが、やはりウィンドウ関数の前に実行されます。
ウィンドウ関数の結果の絞り込み → 外側のクエリ（または QUALIFY）が必要です。

事前フィルターとウィンドウ関数のフィルターを組み合わせる

ウィンドウ関数の前後両方で絞り込むことはよくあります。通常の行フィルターは内側の WHERE で適用し（これによりウィンドウ関数は関係する行だけを対象にします）、その後、外側のクエリでウィンドウ関数の結果を絞り込みます。

この例では、まず在籍中の従業員に限定し、その中から各部署で最も給与の高い従業員を選びます。WHERE active を内側に置くことで、ランキングの対象となる行が変わります。

WITH ranked AS (
  SELECT department, name, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department ORDER BY salary DESC
         ) AS rn
  FROM employees
  WHERE is_active = true        -- pre-filter before ranking
)
SELECT * FROM ranked
WHERE rn = 1;                   -- post-filter on the window

パフォーマンスに関する注意点

面接官から、ラッパーによってパフォーマンスが低下するか聞かれることがあります。通常は低下しません。オプティマイザーはサブクエリやCTEを1つの実行計画の一部として扱い、ウィンドウ関数を1回だけ計算します。ラップしたというだけで追加のスキャンが発生することはありません。

ただし、一部のエンジンではCTEが最適化の境界（マテリアライズされる境界）になる場合があります。そのため、負荷の高い処理では派生テーブルや QUALIFY の方が適切な実行計画になることがあります。重要な場合は EXPLAIN でプロファイルしてください。

よくある間違い

最後に確認しましょう。

ウィンドウ関数を WHERE/HAVING に置かないでください。エラーになります。
派生テーブルには必ず別名を付けてください。FROM 内の名前のないサブクエリは拒否されます。
質問が求める同順位の扱いに応じて、ランキング関数を選んでください。
対応しているデータベースでのみ QUALIFY を使い、それ以外ではCTEまたはサブクエリでラップしてください。

理解度チェック

なぜウィンドウ関数の結果を絞り込むにはラッパーが必要なのでしょうか。

まとめ：ウィンドウ関数の結果を絞り込む

これで、ランキング用ウィンドウ関数の一連の流れを理解できました。

ウィンドウ関数は WHERE/GROUP BY/HAVING の後に実行されるため、そこで結果を絞り込むことはできません。
ウィンドウ関数をサブクエリまたはCTE（必ず別名を付けます）でラップし、外側のクエリで結果を絞り込みます。
この方法は、グループごとの上位N件、キーごとの最新行、累計のしきい値などに使われます。
QUALIFY は、Snowflake/BigQueryで使える便利な非標準の短縮方法です。

これで、面接官が最もよく確認するランキングの基本ツールをすべて身に付けました。

よくある質問