SQL ウィンドウ関数入門 - OVER と PARTITION BY、ROW_NUMBER・RANK・LAG の使い分け

「部署ごとの売上順位を出したい」「前日との差分を並べたい」「日ごとの累計を出したい」。こうした集計を GROUP BY と自己結合だけで書こうとすると、クエリは急に複雑になります。ウィンドウ関数を使うと、どれも1つの SELECT で書けます。

一方で、OVER の中に何を書くとどの範囲が計算対象になるのかが見えにくく、「ORDER BY を足したら合計が変わった」「LAST_VALUE が最後の値を返さない」といったところでつまずきやすい機能でもあります。

この記事では、小さなサンプルテーブルを最初に定義し、すべてのクエリをそのデータに対して実行して結果を並べます。構文の意味を押さえたうえで、順位付け、前後の行の参照、累計と移動平均、フレーム句の落とし穴、グループごとの上位 N 件までを順に確認します。

ウィンドウ関数とは - 行をまとめずに集計する

ウィンドウ関数は、現在の行と関係のある複数の行を使って計算し、その結果を各行に付ける関数です。PostgreSQL の公式チュートリアルは、集約関数でできる計算と似ているが、集約関数のように行が1つの出力行にまとめられることはなく、各行はそのまま残る、と説明しています。

見分け方は単純で、関数呼び出しの後ろに OVER (...) が付いていればウィンドウ関数です。

関数名(引数) OVER (
  PARTITION BY 区切る列      -- どの行どうしを同じグループとして扱うか
  ORDER BY     並べる列      -- グループの中でどう並べるか
  フレーム句                 -- 並べた中のどこからどこまでを計算に使うか
)

OVER の中の3つはどれも省略できます。それぞれが「計算に使う行の範囲（ウィンドウ）」を絞り込む役割を持っており、この3段階を区別できれば、ウィンドウ関数のほとんどの挙動は説明がつきます。

サンプルデータ

以降の例はすべて次のデータを使います。サンプル商事（仮）の営業部の売上と、日々の入金の記録です。

CREATE TABLE sales (
  id     INTEGER PRIMARY KEY,
  dept   TEXT NOT NULL,
  name   TEXT NOT NULL,
  amount INTEGER NOT NULL
);
 
INSERT INTO sales VALUES
  (1, '営業1課', '社員A', 500), (2, '営業1課', '社員B', 400),
  (3, '営業1課', '社員C', 400), (4, '営業1課', '社員D', 300),
  (5, '営業2課', '社員E', 600), (6, '営業2課', '社員F', 600),
  (7, '営業2課', '社員G', 200);
 
CREATE TABLE payments (
  id      INTEGER PRIMARY KEY,
  paid_on TEXT NOT NULL,
  amount  INTEGER NOT NULL
);
 
INSERT INTO payments VALUES
  (1, '2026-11-01', 100), (2, '2026-11-02', 200), (3, '2026-11-02', 300),
  (4, '2026-11-03', 150), (5, '2026-11-04', 250);

ポイントは、同じ値の行をわざと入れてあることです。

• sales では、社員B と社員C、社員E と社員F の売上が同額（順位付けで同順位になる）
• payments では、11月2日の入金が2件ある（累計の計算で ROWS と RANGE の差が出る）

GROUP BY との違い

部署ごとの売上合計を GROUP BY で出すと、結果は部署の数だけの行になります。

SELECT dept, SUM(amount) AS dept_total
FROM sales
GROUP BY dept;

7行あった明細は2行にまとめられ、個々の社員の名前や金額は取り出せなくなりました。同じ SUM に OVER を付けると結果が変わります。

SELECT name, dept, amount,
       SUM(amount) OVER (PARTITION BY dept) AS dept_total,
       SUM(amount) OVER ()                  AS all_total
FROM sales
ORDER BY id;

行数は7行のままで、各行に「自分の部署の合計」と「全体の合計」が付きました。違いを整理すると次のとおりです。

3つ目が実用上は大きな違いです。上の例では、部署単位の合計と全体の合計という区切り方の違う2つの集計を、1つの SELECT に並べています。明細と集計が同じ行にあるので、構成比もそのまま計算できます。

SELECT name, dept, amount,
       ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY dept), 1) AS pct
FROM sales
ORDER BY id;

同じことを GROUP BY で書くなら、部署ごとの合計をサブクエリで作り、元のテーブルに JOIN し直すことになります。

OVER・PARTITION BY・ORDER BY の役割

OVER - ウィンドウ関数であることの印

OVER は、その関数をウィンドウ関数として呼び出すための句です。SUM、AVG、COUNT、MAX、MIN のような集約関数は、OVER を付ければウィンドウ関数に、付けなければ通常の集約関数になります。ROW_NUMBER や LAG のようなウィンドウ関数専用の関数は、OVER が必須です。

OVER () のように中を空にすると、クエリの全行が1つのウィンドウになります。前の例の all_total がこれにあたります。

PARTITION BY とは - 行をグループに区切る

PARTITION BY は、行をどの単位で区切って計算するかを指定します。PARTITION BY dept と書けば、営業1課の行は営業1課の行だけを、営業2課の行は営業2課の行だけを使って計算されます。この区切りのひとつひとつをパーティションと呼びます。

GROUP BY dept と似ていますが、PARTITION BY は行をまとめません。「この行の計算に使ってよい行の範囲」を決めるだけです。省略した場合は、PostgreSQL・MySQL のどちらのドキュメントにも、すべての行が1つのパーティションとして扱われると書かれています。

ORDER BY - パーティションの中の並び順

OVER の中の ORDER BY は、パーティション内での行の並び順を決めます。順位を付ける関数や、前後の行を参照する関数は、この並び順を基準に動きます。

注意点が2つあります。

• 結果全体の並び順は決めない: MySQL のリファレンスマニュアルは、ウィンドウ定義の ORDER BY は各パーティションの中に適用されるもので、結果セット全体を並べたいならクエリの最上位に ORDER BY を書くように、と説明しています。PostgreSQL のドキュメントも、出力の並びがウィンドウ関数の ORDER BY に従うことを当てにせず、最上位に明示的な ORDER BY を書くことを勧めています。
• 集約関数では計算範囲まで変わる: SUM(...) OVER (ORDER BY ...) のように集約関数に ORDER BY を付けると、合計の対象が「先頭から現在の行まで」に変わります。これは後の「フレーム句」の節で詳しく扱います。

ROW_NUMBER・RANK・DENSE_RANK の違い

順位を付ける関数は3つあり、違いは同じ値の行（同順位）の扱いだけです。営業1課の4人を売上の多い順に並べ、3つの関数を同時に適用します。

SELECT name, amount,
       ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num,
       RANK()       OVER (ORDER BY amount DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rnk
FROM sales
WHERE dept = '営業1課'
ORDER BY amount DESC, id;

社員B と社員C は同額の 400 です。この2行と、その次の社員D の行に違いが現れています。

ROW_NUMBER は同順位の並びが決まらない

上の結果では社員B が 2、社員C が 3 になりましたが、これは保証された結果ではありません。ORDER BY amount DESC だけでは2人の順序が決まらないので、どちらが先になるかはデータベースの都合次第です。同じクエリでも、実行計画やデータの配置が変わると入れ替わる可能性があります。

ROW_NUMBER の結果を安定させたいときは、主キーなどの一意な列を並び順の最後に足します。

ROW_NUMBER() OVER (ORDER BY amount DESC, id)

同額の行を「どちらも2位」と扱いたいのか、「とにかく1行ずつ番号を振りたい」のかを先に決めると、3つのうちどれを使うかが決まります。

PARTITION BY と組み合わせる

PARTITION BY を加えると、順位はパーティションごとに 1 から振り直されます。

SELECT dept, name, amount,
       RANK() OVER (PARTITION BY dept ORDER BY amount DESC) AS rnk
FROM sales
ORDER BY dept, rnk, id;

営業2課では社員E と社員F がともに1位で、次の社員G は3位です。

LAG と LEAD - 前後の行の値を参照する

LAG は並び順で前の行の値を、LEAD は後の行の値を返します。「前回との差」「次の予定までの間隔」のような、隣の行との比較に使います。

SELECT id, paid_on, amount,
       LAG(amount)  OVER (ORDER BY id)          AS prev_amount,
       LEAD(amount) OVER (ORDER BY id)          AS next_amount,
       amount - LAG(amount) OVER (ORDER BY id)  AS diff,
       LAG(amount, 1, 0) OVER (ORDER BY id)     AS prev_or_zero
FROM payments
ORDER BY id;

押さえておきたい点は3つです。

• 端の行は NULL になる: 最初の行には前の行がなく、最後の行には後の行がないので、結果は NULL です。その NULL を使った diff も NULL になります。
• 引数は3つまで取れる: LAG(式, 何行前か, 行がないときの値) の形です。2つ目を省略すると1行前、3つ目を省略すると NULL になります。prev_or_zero は、前の行がないときに 0 を返すようにした例です。
• PARTITION BY を付ければ、パーティションをまたがない: 顧客ごとの前回購入日を出すなら LAG(paid_on) OVER (PARTITION BY 顧客ID ORDER BY paid_on) と書きます。別の顧客の行を「前の行」として拾うことはありません。

ウィンドウ関数がない環境では、同じことを「自分より1つ前の行」を探す自己結合や相関サブクエリで書く必要がありました。LAG はそれを1つの関数呼び出しに置き換えます。

累計と移動平均

累計（ランニングトータル）

集約関数の OVER に ORDER BY を書くと、先頭から現在の行までの集計になります。入金の累計は次のように書けます。

SELECT id, paid_on, amount,
       SUM(amount) OVER (ORDER BY id) AS running_total
FROM payments
ORDER BY id;

PARTITION BY を加えれば、顧客ごと・月ごとの累計になります。

移動平均

「直近3件の平均」のように、現在の行の周辺だけを対象にしたい場合は、フレーム句で範囲を指定します。

SELECT id, amount,
       ROUND(AVG(amount) OVER (
         ORDER BY id
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 1) AS moving_avg,
       COUNT(*) OVER (
         ORDER BY id
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS cnt
FROM payments
ORDER BY id;

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW は「2行前から現在の行まで」という意味です。cnt の列を見ると、最初の2行は対象の行が3行に満たないことがわかります。先頭付近の移動平均は少ない行数で計算されているので、3行そろった行だけを使いたい場合は cnt で判定して除外します。

なお、この書き方は「直近3行」であって「直近3日」ではありません。日付が飛んでいるデータで日数ベースの移動平均を出すには、先に日付ごとに1行へ集計して欠けた日を補うか、値の範囲で指定する RANGE を使います。

フレーム句 - ROWS と RANGE の違い

移動平均で使った ROWS BETWEEN ... の部分をフレーム句と呼びます。パーティションを ORDER BY で並べたうえで、そのうちのどこからどこまでを計算に使うか（フレーム）を決める句です。

ROWS BETWEEN 開始位置 AND 終了位置

開始位置と終了位置には次の指定を使います。

先頭の ROWS の部分はフレームの数え方を表し、ここを RANGE に変えると挙動が変わります。

• ROWS: 行の位置で数える。CURRENT ROW は文字どおり現在の1行を指す
• RANGE: ORDER BY の列の値で数える。CURRENT ROW は、現在の行と並び順が同じすべての行を含む

ORDER BY の値が等しく、並び順に差がない行どうしをピア（peer）と呼びます。RANGE ではピアがひとかたまりとして扱われます。PostgreSQL と SQLite には、ピアのかたまりの数で数える GROUPS もあります。

ORDER BY だけを書いたときのデフォルトは RANGE

ここからが落とし穴です。フレーム句を省略したときのデフォルトは ROWS ではありません。PostgreSQL のドキュメントは、デフォルトのフレームは RANGE UNBOUNDED PRECEDING で、RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW と同じだと説明しています。MySQL・SQLite・SQL Server の公式ドキュメントにも同じデフォルトが書かれています。

つまり、ORDER BY を付けた集約関数は「先頭から、現在の行と同じ並び順の最後の行まで」を集計します。payments を日付で並べて累計を取ると、その影響が見えます。

SELECT id, paid_on, amount,
       SUM(amount) OVER (ORDER BY paid_on) AS default_frame,
       SUM(amount) OVER (
         ORDER BY paid_on
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS rows_frame
FROM payments
ORDER BY paid_on, id;

id 2 の行に注目してください。フレーム句を省略した default_frame は 600 で、同じ 11月2日の id 3 の 300 まで足されています。11月2日の2行はピアなので、どちらの行にも「11月2日までの合計」が入ります。rows_frame のほうは1行ずつ積み上がり、300 になっています。

どちらが正しいかは目的によります。「その日までの累計」が欲しければデフォルトの挙動で合っていますし、「1件ずつ積み上げた残高」が欲しければ ROWS が必要です。問題は、並び順に重複がないテストデータでは両者が同じ結果になり、違いに気づけないことです。

1件ずつ積み上げたい場合は、ROWS を明示したうえで、並び順を一意にする列を足します。ROWS だけでは、同じ日付の行のどちらを先に足すかが決まらないためです。

SUM(amount) OVER (
  ORDER BY paid_on, id
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

ORDER BY を足すと合計が変わる

同じ理由で、OVER の中に ORDER BY を足すだけで集約の結果が変わります。

SELECT name, amount,
       SUM(amount) OVER (PARTITION BY dept)                      AS no_order,
       SUM(amount) OVER (PARTITION BY dept ORDER BY amount DESC) AS with_order
FROM sales
WHERE dept = '営業1課'
ORDER BY amount DESC, id;

ORDER BY がなければ、フレームはパーティション全体なので、どの行も部署合計の 1600 です。ORDER BY を足すと累計に変わり、同額の社員B と社員C はピアなのでどちらも 1300 になります。

MySQL のリファレンスマニュアルもこの点に触れており、デフォルトのフレームが ORDER BY の有無で変わるため、結果を決定的にしようとして ORDER BY を追加すると SUM() などの値が変わることがある、と注意しています。部署合計のつもりで書いた SUM に、あとから「並びをそろえたいから」と ORDER BY を足すと、エラーなしに数字が変わります。

LAST_VALUE の罠

デフォルトのフレームが原因で最もよく起きる誤解が LAST_VALUE です。部署内で売上が最も多い人と最も少ない人の名前を、各行に付けてみます。

SELECT name, amount,
       FIRST_VALUE(name) OVER (
         PARTITION BY dept ORDER BY amount DESC, id
       ) AS top_name,
       LAST_VALUE(name) OVER (
         PARTITION BY dept ORDER BY amount DESC, id
       ) AS last_default,
       LAST_VALUE(name) OVER (
         PARTITION BY dept ORDER BY amount DESC, id
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS last_fixed
FROM sales
WHERE dept = '営業1課'
ORDER BY amount DESC, id;

FIRST_VALUE は期待どおり全行で社員A を返しています。ところが、フレーム句を省略した LAST_VALUE（last_default）は、各行が自分自身の名前を返しています。デフォルトのフレームは現在の行で終わるので、「フレームの最後の行」は常に現在の行だからです。

PostgreSQL のドキュメントも、first_value・last_value・nth_value はウィンドウフレーム内の行だけを対象とし、デフォルトのフレームでは last_value が役に立たない結果になりやすい、と明記しています。パーティション全体の最後の値が欲しいときは、last_fixed のようにフレームを末尾（UNBOUNDED FOLLOWING）まで広げます。並び順を逆にして FIRST_VALUE を使う方法でも同じ結果が得られます。

フレーム句が効く関数と効かない関数

すべてのウィンドウ関数がフレームを使うわけではありません。MySQL と SQLite の公式ドキュメントの記述をまとめると、次のように分かれます。

順位や LAG / LEAD ではフレームを気にする必要はありません。集約関数と FIRST_VALUE / LAST_VALUE / NTH_VALUE に ORDER BY を付けるときだけ、フレームを明示すると覚えておけば十分です。

WHERE で直接使えない理由 - 論理的な評価順

「部署ごとの上位2人だけを取りたい」と考えて、ウィンドウ関数を WHERE に書くとエラーになります。

SELECT name
FROM sales
WHERE ROW_NUMBER() OVER (PARTITION BY dept ORDER BY amount DESC) <= 2;

SQLite 3.54.0 では misuse of window function ROW_NUMBER() というエラーになりました。これは SQLite に限った制限ではありません。PostgreSQL のドキュメントは、ウィンドウ関数が許されるのは SELECT リストと ORDER BY 句だけで、GROUP BY・HAVING・WHERE では禁止されていると述べ、その理由を「論理的に、それらの句の処理の後に実行されるため」と説明しています。

SQL の各句は、書いた順ではなく、おおよそ次の順で論理的に評価されます。MySQL のリファレンスマニュアルにも、ウィンドウ処理は WHERE・GROUP BY・HAVING の処理の後、ORDER BY・LIMIT・SELECT DISTINCT の前に行われると書かれています。

1. FROM（結合を含む）
2. WHERE
3. GROUP BY と集約
4. HAVING
5. ウィンドウ関数
6. SELECT DISTINCT
7. ORDER BY
8. LIMIT

WHERE が評価される時点では、ウィンドウ関数の結果はまだ存在しません。存在しない値で絞り込むことはできない、というのがエラーの理由です。

この順序からは、もう2つ実用的な性質が導かれます。

WHERE で絞ったあとの行だけがウィンドウ関数の対象になる。次のクエリは、売上が 500 未満の行に絞ってから順位を付けています。

SELECT name, amount,
       RANK() OVER (ORDER BY amount DESC) AS rnk
FROM sales
WHERE amount < 500
ORDER BY rnk, id;

全7人の中では4位タイの社員B と社員C が、ここでは1位になっています。「全体での順位を付けたうえで、一部の行だけ表示したい」場合は、WHERE で先に絞ってはいけません。

GROUP BY の結果に対してウィンドウ関数を使える。集約のあとに評価されるので、集約した値をさらに順位付けしたり、全体に対する比率を出したりできます。

SELECT dept,
       SUM(amount) AS dept_total,
       RANK() OVER (ORDER BY SUM(amount) DESC) AS rnk,
       ROUND(100.0 * SUM(amount) / SUM(SUM(amount)) OVER (), 1) AS pct
FROM sales
GROUP BY dept
ORDER BY rnk;

SUM(SUM(amount)) OVER () は見慣れない形ですが、内側の SUM(amount) が部署ごとの集約、外側の SUM(...) OVER () がその集約結果を全行で合計するウィンドウ関数です。

グループごとの上位N件を取る

ウィンドウ関数の結果で絞り込むには、ウィンドウ関数を含むクエリを CTE（WITH 句）かサブクエリにして、その外側の WHERE で絞ります。外側のクエリから見れば、順位はすでに計算済みのただの列だからです。PostgreSQL のチュートリアルも、ウィンドウ計算の後で絞り込みやグループ化をしたい場合はサブクエリを使う、と案内しています。

WITH ranked AS (
  SELECT dept, name, amount,
         ROW_NUMBER() OVER (PARTITION BY dept ORDER BY amount DESC, id) AS rn
  FROM sales
)
SELECT dept, name, amount, rn
FROM ranked
WHERE rn <= 2
ORDER BY dept, rn;

ROW_NUMBER を使ったので、各部署から必ず2行ずつ返ります。営業1課では、社員B と同額の社員C が落ちています。同額なら全員を含めたい場合は、RANK に変えます。

WITH ranked AS (
  SELECT dept, name, amount,
         RANK() OVER (PARTITION BY dept ORDER BY amount DESC) AS rnk
  FROM sales
)
SELECT dept, name, amount, rnk
FROM ranked
WHERE rnk <= 2
ORDER BY dept, rnk, name;

今度は営業1課が3行、営業2課が2行です。営業2課は1位が2人いるので次は3位になり、社員G は含まれません。「上位 N 件」と言われたときに、行数を固定したいのか、同順位をすべて含めたいのかを確認しておくと、関数の選択を誤りません。

同じ形は、重複行の整理にも使えます。「メールアドレスが同じ行のうち、最新の1行だけ残す」なら、PARTITION BY にメールアドレス、ORDER BY に更新日時の降順を指定して rn = 1 の行を取ります。ORM で「各ユーザーの最新の注文を1件ずつ」を取るためにループの中でクエリを発行している箇所（N+1問題）も、この形で1回のクエリにまとめられます。

QUALIFY とデータベースごとの対応差

QUALIFY は一部のデータベースだけの構文

CTE で包む手間をなくす構文として QUALIFY があります。Snowflake の公式ドキュメントは、QUALIFY はウィンドウ関数の結果を絞り込む句で、集約関数に対する HAVING と同じ役割をウィンドウ関数に対して果たす、と説明しています。BigQuery の公式ドキュメントにも同じ句があり、評価順は HAVING、WINDOW の後、DISTINCT の前とされています。

SELECT dept, name, amount
FROM sales
QUALIFY ROW_NUMBER() OVER (PARTITION BY dept ORDER BY amount DESC, id) <= 2;

ただし、PostgreSQL・MySQL・SQLite にはこの句がありません。PostgreSQL と MySQL 8.4 の公式ドキュメントの SELECT 構文には QUALIFY の記載がなく、SQLite 3.54.0 では上のクエリが構文エラーになりました。これらのデータベースでは、前の節の CTE かサブクエリで書きます。

対応バージョンと機能の差

• MySQL: ウィンドウ関数は 8.0.2（2017年7月17日、開発マイルストーン版）のリリースノートに追加が記載されています。5.7 以前では使えません。8.4 のリファレンスマニュアルでは、フレームの単位は ROWS と RANGE の2つだけです。
• SQLite: 3.25.0（2018年9月15日）でウィンドウ関数が追加され、3.28.0（2019年4月16日）で EXCLUDE 句、GROUPS フレーム、ウィンドウの連結、RANGE フレームでの数値指定の境界に対応しました。OS やアプリケーションに同梱された古い SQLite を使っている場合は、SELECT sqlite_version(); でバージョンを確認してください。
• SQL Server: 公式ドキュメントでは、OVER 句の ROWS / RANGE は SQL Server 2012 以降が対象とされています。RANGE で N PRECEDING のような数値指定はできません。名前付きウィンドウの WINDOW 句は、データベースの互換性レベルが 160 以上の場合に使えます。

MySQL 8.0.2 のリリースノートには、ウィンドウ関数の追加に伴って RANK、LAG、OVER、WINDOW などが予約語になったことも書かれています。古いバージョンから移行する場合、これらを列名やテーブル名に使っているとクォートが必要になります。

名前付きウィンドウ（WINDOW 句）

同じ OVER (...) を何度も書くと、クエリが長くなるうえに、片方だけ直し忘れる原因になります。WINDOW 句でウィンドウ定義に名前を付けると、OVER 名前 で使い回せます。

SELECT name, amount,
       RANK()      OVER w AS rnk,
       SUM(amount) OVER w AS running,
       AVG(amount) OVER (w ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS avg2
FROM sales
WHERE dept = '営業1課'
WINDOW w AS (PARTITION BY dept ORDER BY amount DESC, id)
ORDER BY amount DESC, id;

WINDOW 句は HAVING の後、ORDER BY の前に書きます（MySQL と SQLite のドキュメントに位置の記載があります）。3つ目の avg2 のように、OVER (w ...) と括弧の中に名前を書くと、名前付きウィンドウの定義にフレーム句を足して使えます。

この例では並び順に id を足して一意にしてあるので、RANK は 1, 2, 3, 4 と同順位なしになり、running も1行ずつ積み上がっています。前の節で見た 1, 2, 2, 4 との違いは、ORDER BY に書いた列だけが同順位の判定に使われることを示しています。

性能面の注意 - ソートとインデックス

ウィンドウ関数は便利ですが、内部では並べ替えが必要になる点を意識しておく必要があります。

• PARTITION BY と ORDER BY の列でソートが発生する: PostgreSQL のドキュメントには、現状ではウィンドウ関数は常に事前にソートされたデータを必要とする、と書かれています。行数が多いテーブルでは、このソートが処理時間の中心になります。
• インデックスでソートを省ける場合がある: SQLite でサンプルの部署別順位のクエリに EXPLAIN QUERY PLAN をかけると、インデックスがない状態では USE TEMP B-TREE FOR ORDER BY（一時的な B-tree での並べ替え）が表示されました。(dept, amount DESC) の複合インデックスを作成すると、この行が消えてインデックス順の走査に変わりました。「PARTITION BY の列、ORDER BY の列」の順で張るのが基本です。インデックスの仕組みはデータベースインデックス入門で解説しています。
• ウィンドウ定義をそろえる: PostgreSQL のドキュメントによると、PARTITION BY と ORDER BY が異なるウィンドウ関数が混在すると、その間に追加のソートが入るのが普通です。同じ定義で済むものは WINDOW 句でまとめておくと、読みやすさだけでなく処理の面でも有利になります。
• 不要な行は先に WHERE で落とす: ウィンドウ関数は WHERE の後に評価されるので、対象期間などで先に絞れば、並べ替える行数そのものが減ります。逆に、CTE の外側で rn <= 2 と絞る上位 N 件のクエリは、内側では全行に番号を振っています。対象が大きい場合は実行計画を確認してください。読み方はクエリ実行計画入門にまとめています。

なお、インデックスが実際に使われるかどうかは、データベースの種類、統計情報、クエリの形によって変わります。上の SQLite の結果は7行のサンプルテーブルでの確認であり、他のデータベースで同じ計画になることを保証するものではありません。

まとめ

• ウィンドウ関数は、行をまとめずに、関係する複数の行を使った計算結果を各行に付ける。OVER が付いていればウィンドウ関数。
• PARTITION BY は計算の区切り、ORDER BY は区切りの中の並び順、フレーム句は並べた中の計算範囲を決める。
• ROW_NUMBER は常に連番、RANK は同順位のあと番号が飛ぶ、DENSE_RANK は飛ばない。ROW_NUMBER を安定させるには並び順を一意にする。
• LAG / LEAD は前後の行の値を返す。端の行は NULL になり、第3引数で代わりの値を指定できる。
• 集約関数に ORDER BY を付けると累計になる。フレーム句を省略したときのデフォルトは RANGE で、並び順が同じ行はまとめて集計される。1行ずつ積み上げたいときは ROWS を明示する。
• LAST_VALUE はデフォルトのフレームでは現在の行の値を返す。末尾の値が欲しければ UNBOUNDED FOLLOWING まで広げる。
• ウィンドウ関数は WHERE・GROUP BY・HAVING の後に評価されるので、WHERE には書けない。結果で絞るときは CTE かサブクエリで包む。
• QUALIFY は Snowflake や BigQuery の構文で、PostgreSQL・MySQL・SQLite にはない。MySQL は 8.0 以降、SQLite は 3.25.0 以降でウィンドウ関数が使える。

ウィンドウ関数を含むクエリを書いたら、同じ値の行を含むデータで一度実行してみてください。同順位の扱いとフレームのデフォルトは、値が重複して初めて差が現れます。

参考リンク

• Window Functions（PostgreSQL 公式チュートリアル）
• Value Expressions - Window Function Calls（PostgreSQL 公式）
• Window Functions - 関数の一覧（PostgreSQL 公式）
• Table Expressions - Window Function Processing（PostgreSQL 公式）
• Window Function Concepts and Syntax（MySQL 8.4 リファレンスマニュアル）
• Window Function Frame Specification（MySQL 8.4 リファレンスマニュアル）
• Changes in MySQL 8.0.2（MySQL リリースノート）
• Window Functions（SQLite 公式）
• SELECT - OVER Clause（SQL Server 公式）
• SELECT - WINDOW Clause（SQL Server 公式）
• QUALIFY（Snowflake 公式）
• Query syntax - QUALIFY clause（BigQuery 公式）

関連記事

• SQL の JOIN の種類と違い - INNER / LEFT / FULL / CROSS の結果を例で理解する
• データベースインデックス入門 - B-treeの仕組みと、効くクエリ・効かないクエリ
• クエリ実行計画入門 - EXPLAINの読み方とプランナがJOINアルゴリズムを選ぶ理由
• N+1問題とは - ORM で起きる原因と Eager Loading・JOIN・バッチ取得での対策
• ページネーション設計 - オフセット方式とカーソル（キーセット）方式の使い分け
• データベース正規化 入門 - 第1〜第3正規形とBCNFを実例で理解する