0Pricing
SQL Interview Prep · レッスン

OVER、PARTITION BY、ORDER BY

ウィンドウ仕様の構造と、パーティションによって計算がリセットされる仕組みを学びます。

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

面接官がウィンドウ関数を求める理由

ウィンドウ関数は、GROUP BYのように行をまとめることなく、現在の行に関連する行の集合に対して計算を行います。この性質があるため、面接官はウィンドウ関数を好んで使います。すべての詳細行を残したまま、集計値、順位、累計などを同じ行に追加できるからです。

  • GROUP BYはグループごとに1行を返します。
  • ウィンドウ関数はすべての入力行を返し、計算列を追加します。

面接官が「各社員と、その社員の所属部門の平均給与を同じ行に表示してください」と言った場合、自己結合ではなくウィンドウ関数を使えるかを確認しています。

OVER句の構造

すべてのウィンドウ関数には、OVER (...)句が続きます。この句には3つの省略可能な部分があり、それぞれを正確に説明できると面接官に好印象を与えます。

  • PARTITION BY — 行をグループに分け、関数を各グループで再開します。
  • ORDER BY — 各パーティション内の行を並べ替えます（順位付けや累計に必要です）。
  • フレーム — 計算対象にする行を制限します（ROWS/RANGE）。

空のOVER ()は、結果セット全体を1つのパーティションとして扱います。

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

ウィンドウ関数と集計関数：同じ関数、異なる結果

まったく同じ集計関数でも、ウィンドウ関数として使うと動作が異なります。次の2つのクエリを概念的に比較してみましょう。

  • AVG(salary)とGROUP BY departmentの組み合わせは、部門ごとに1行を返します。
  • AVG(salary) OVER (PARTITION BY department)はすべての社員を返し、それぞれの行に部門平均を付加します。

面接では、ウィンドウ関数版はGROUP BYを必要とせず

-- Aggregate: collapses
SELECT department, AVG(salary)
FROM employees
GROUP BY department;

-- Window: preserves every row
SELECT department, name, AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

PARTITION BY：計算のリセット

PARTITION BY は、行をまとめてしまわない点を除けば、集約関数に対する GROUP BY と同じ役割をウィンドウ関数で果たします。パーティションの値ごとに独立した計算が行われます。

この例では、部署ごとに行番号が1から振り直されます。PARTITION BY がなければ、すべての従業員をまたいで番号が連続して振られます。

  • 1つの列でも、複数の列でもパーティション化できます。
  • PARTITION BY がない場合は、全体が1つの巨大なパーティションになります。
SELECT
  department,
  name,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees;

OVER 内の ORDER BY

OVER 内の ORDER BY は、クエリの最後にある ORDER BY と同じものではありません。関数が処理するための、各パーティション内の行の順序だけを定義します。

  • ランキング関数（ROW_NUMBER、RANK）では必須です。ランキングの基準となる順序が必要だからです。
  • パーティションに対する通常の集約関数では、累積計算を行いたい場合を除き、必要ありません。

面接でよくある失敗は、ウィンドウの ORDER BY と出力の表示順を混同することです。

SELECT
  name,
  hire_date,
  ROW_NUMBER() OVER (ORDER BY hire_date) AS seniority_rank
FROM employees
ORDER BY name;  -- output order is independent of the window order

PARTITION BY と ORDER BY の組み合わせ

典型的なランキング用ウィンドウでは、PARTITION BY でグループ化し、ORDER BY で各グループ内の順序を決めます。

以下の仕様は、次のように読みます。「各部署内で、従業員を給与の降順に並べ、番号を振る」。各部署で最も給与が高い従業員が行番号1になります。

この1つの仕様が、グループごとの上位N件を含む、最も一般的なウィンドウ関数の面接問題の基盤になります。

SELECT
  department,
  name,
  salary,
  ROW_NUMBER() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_salary_rank
FROM employees;

ORDER BY による集約動作の変化

面接官が確認する微妙なポイントがあります。集約ウィンドウに ORDER BY を追加すると、暗黙のフレーム（「パーティションの先頭から現在の行まで」）が適用されるため、累積計算になります。

  • SUM(x) OVER (PARTITION BY g) → 各行に同じグループ合計が表示されます。
  • SUM(x) OVER (PARTITION BY g ORDER BY d) → 現在の行までの累積合計になります。

ORDER BY が暗黙的にフレームを追加することを理解しているかどうかで、中級レベルの候補者と初級者との差が表れます。

SELECT
  account_id,
  txn_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY account_id
    ORDER BY txn_date
  ) AS running_balance
FROM transactions;

ウィンドウ関数を使用できる場所

ウィンドウ関数を記述できるのは、SELECT リストと ORDER BY 句だけです。WHERE、GROUP BY、HAVING では使用できません。

理由は論理的な実行順序にあります。ウィンドウ関数は、WHERE、GROUP BY、HAVING の処理が終わった後に評価されます。ウィンドウ関数が行を見る時点では、対象の行がすでに選ばれているのです。

そのため、ランキング結果で絞り込むにはサブクエリまたはCTEが必要になります。この点については後のレッスンで詳しく扱います。

-- This FAILS: window function in WHERE
-- SELECT name FROM employees
-- WHERE ROW_NUMBER() OVER (ORDER BY salary) = 1;

-- This works: window in SELECT, filter outside
SELECT * FROM (
  SELECT name, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
  FROM employees
) t
WHERE rn = 1;

1つのクエリで複数のウィンドウ関数を使う

同じ SELECT 内で複数のウィンドウ関数を使用できます。それぞれに独自の仕様を指定することも、同じ仕様を共有することも可能です。データベースは、パーティション化されたデータを1回走査してこれらを計算します。

面接で、ランキングと部署平均を同時に求める必要がある場合に便利です。2つの関数が同じ仕様を共有する場合は、方言によっては WINDOW 句で名前を付け、繰り返しを避けられます。

SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER w  AS rn,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

実例：給与と部署平均の比較

分析担当者に頻繁に出される質問です。「すべての従業員について、給与、部署平均、そしてその差を一覧にしてください」。1つのウィンドウ式で大部分を処理し、残りを算術演算で求めます。

GROUP BY がなく、すべての従業員の行が残っていることに注目してください。同じ部署の全員に dept_avg が繰り返し表示されるため、行ごとの比較が可能になります。

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg,
  salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees
ORDER BY department, salary DESC;

面接官が注目するよくある間違い

ウィンドウ関数が出てきたときは、次の落とし穴を避けてください。

  • WHERE や HAVING にウィンドウ関数を記述する — 使用できないため、サブクエリを使います。
  • ランキング関数に ORDER BY を付け忘れる — 結果が任意の順序になります。
  • PARTITION BY によって行数が減ると思い込む — 行数は決して減りません。
  • ウィンドウの ORDER BY と最終的な出力順を混同する。
  • 集約ウィンドウに ORDER BY を追加した結果、累積合計になったことに気付かない。

理解度チェック

ウィンドウ仕様をどの程度理解できているか確認しましょう。

まとめ：ウィンドウ仕様

これで OVER (...) の構造を理解できました。

  • ウィンドウ関数は、関連する行をまたいで計算しながら、すべての行を保持します。
  • PARTITION BY はグループ化して計算を振り直しますが、行を削除することはありません。
  • ORDER BY はパーティション内の行の順序を決めます。ランキング関数では必須であり、集約関数を累積計算に変えます。
  • ウィンドウ関数を使用できるのは SELECT と ORDER BY だけで、WHERE/HAVING では使用できません。

次は、ROW_NUMBER を使って決定的な連番を割り当てます。

よくある質問

「OVER、PARTITION BY、ORDER BY」レッスンは無料ですか？

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

「OVER、PARTITION BY、ORDER BY」で何を学びますか？

ウィンドウ仕様の構造と、パーティションによって計算がリセットされる仕組みを学びます。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

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

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

「OVER、PARTITION BY、ORDER BY」レッスンにはどのくらい時間がかかりますか？

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

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

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

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

  1. OVER、PARTITION BY、ORDER BY
  2. ROW_NUMBERで一意の連番を付ける
  3. 同順位におけるRANKとDENSE_RANKの違い
  4. ウィンドウ関数の結果でフィルタリングする
← SQL Interview Prepに戻る