SQL window functions：ランキング、累積合計、分析

Published: 2026-03-24

window functionsは、現在の行に関連する行の集合に対して計算を行いますが、GROUP BY のように結果を1行にまとめてしまうことはありません。これは現代的なSQLで最も強力な機能の1つであり、SQLite (3.25+)、PostgreSQL、MySQL (8.0+)、SQL Server、Oracle でサポートされています。

window functionsの構文

すべてのwindow functionsは、どの行に対して処理を行うかを定義するために OVER 句を使用します。

function_name(...) OVER (
  [PARTITION BY column]
  [ORDER BY column]
  [frame_clause]
)

• PARTITION BY — 行をグループに分割します（GROUP BY に似ていますが、行は保持されます）
• ORDER BY — 各パーティション内での行の並び順を定義します
• フレーム句 (frame clause) — 現在の行を基準に、ウィンドウに含める行の範囲を制限します

ROW_NUMBER()

ROW_NUMBER() は、パーティション内の各行に一意の連番を割り当てます。番号は常に 1 から始まり、欠番や同順位はありません。

例: 給与順に全従業員へ番号を振る

例: 部門ごとに従業員へ番号を振る

PARTITION BY を使うと、部門ごとに番号付けが 1 から再開されます。

RANK() と DENSE_RANK()

RANK() と DENSE_RANK() は、ROW_NUMBER() と比べて同順位の扱いが異なります。

• ROW_NUMBER() — 常に一意。タイのときの順序は任意
• RANK() — 同じ値は同じ順位になり、その後の順位は飛びます (1, 2, 2, 4)
• DENSE_RANK() — 同じ値は同じ順位で、欠番はありません (1, 2, 2, 3)

例: 3種類のランキング関数を比較する

Bob と Carol はどちらも 85000 を稼いでいます — 各関数が同順位をどう扱うかに注目してください。

例: 部門内で従業員に順位を付ける

NTILE()

NTILE(n) は、行をおおよそ同じサイズの n 個のグループ（バケット）に分割し、1 から n までの番号を付けます。

例: 従業員を給与の四分位に分ける

例: 部門ごとに従業員を2つの層に分ける

LAG() と LEAD()

LAG(column, offset) は前の行の値にアクセスします。LEAD(column, offset) は次の行の値にアクセスします。どちらもデフォルトの offset は 1 で、該当する行がない場合は NULL を返します。

例: 月次売上の前月比較

各従業員について、各月の売上を前月と比較します。

例: 翌月の売上を先読みする

集約window functions

標準的な集約関数（SUM, AVG, COUNT, MIN, MAX）は、OVER と組み合わせることでwindow functionsとして使用できます。GROUP BY と異なり、出力ではすべての行が保持されます。

例: 各従業員の給与と部門平均の比較

例: 給与総額に対する割合

各従業員が給与総額のうちどのくらいの割合を占めるかを計算します。

例: 累積合計と累積件数

パーティションごとの累積合計

PARTITION BY と ORDER BY を組み合わせて、グループ内での累積合計を計算します。

例: 従業員ごとの累積売上

例: 各月における全従業員の売上順位

各月ごとに、従業員がどの順位だったかを求めます。

ウィンドウフレーム

ウィンドウフレームは、現在の行を基準に、計算に含める行を正確に定義します。フレーム句は OVER の中の ORDER BY の後に記述します。

構文

ROWS BETWEEN <start> AND <end>

よく使われる境界:

• UNBOUNDED PRECEDING — パーティションの最初の行から
• N PRECEDING — 現在の行から N 行前まで
• CURRENT ROW — 現在の行
• N FOLLOWING — 現在の行から N 行後まで
• UNBOUNDED FOLLOWING — パーティションの最後の行まで

例: 2ヶ月移動平均

例: 売上のランニング最小値と最大値

FIRST_VALUE と LAST_VALUE

FIRST_VALUE(column) はウィンドウフレーム内の最初の値を返します。LAST_VALUE(column) は最後の値を返します。注意点として、デフォルトのフレーム（ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）では、LAST_VALUE は現在の行までしか見えません。

例: 各月を最初の月と比較する

例: 各月を最良の月と比較する

パーティション全体を参照するために ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING を使います。

実用例: 部門ごとの最高給与者

よくあるパターンとして、サブクエリ内でwindow functionsを使い、その結果に対して順位でフィルタします。

例: 各部門で最も給与が高い従業員

例: 従業員ごとの最高売上月

window functionsと GROUP BY の違い

重要な違いとして、GROUP BY は行を集約してサマリ行にまとめますが、window functionsはすべての行を保持したまま、計算結果の列を追加します。

例: 並べて比較する

GROUP BY は部門ごとに1行を返します。

window functionsはすべての行を保持します。

window functionsの主なユースケース

• ランキングやリーダーボード — 上位N件の取得に ROW_NUMBER, RANK, DENSE_RANK
• 累積合計 — 財務・在庫トラッキングのための累積 SUM
• 期間比較 — 月次・年次比較のための LAG/LEAD
• 移動平均 — フレームを使った AVG によるデータ平滑化
• パーセンタイル分析 — データを等しいグループに分割する NTILE
• 重複排除 — グループごとに1行を選ぶための ROW_NUMBER
• ギャップ分析 — 欠番や異常な変化を検出するための LAG

パフォーマンス上のヒント

1. ORDER BY 列にインデックスを張る — window functionsは内部でソートを行うため、インデックスが有効です
2. パーティション数を最小限にする — パーティションが多いと計算コストが増えます
3. ウィンドウ定義を再利用する — 多くのデータベースは、同じ OVER 指定を繰り返さないようにする WINDOW 句をサポートしています
4. ウィンドウ計算の後でフィルタする — window functionsは WHERE より前に実行されるため、順位でフィルタするにはサブクエリやCTEを使います
5. 不要なフレーム指定を避ける — デフォルト以外の挙動が必要な場合だけフレーム句を指定します

まとめ

• window functionsは、結果セットを 集約せずに 関連する行にまたがる値を計算します
• ROW_NUMBER, RANK, DENSE_RANK は行に順位（序数）を付けます
• LAG と LEAD は前後の行の値にアクセスします
• NTILE は行を等しいサイズのバケットに分割します
• FIRST_VALUE と LAST_VALUE はウィンドウ内の境界値を返します
• OVER と組み合わせた集約関数（SUM, AVG, COUNT, MIN, MAX）は、行を保持したままグループレベルの指標を計算します
• PARTITION BY は独立したグループを作り、ORDER BY は行の順序を定義します
• ウィンドウフレーム（ROWS BETWEEN ... AND ...）は、計算に含める行を細かく制御します
• サブクエリを使ってwindow functionsの結果（例: グループごとの上位N件）でフィルタします
• SQLite 3.25+、PostgreSQL、MySQL 8.0+、SQL Server、Oracle でサポートされています

パーティション、並び順、フレーム句を変えながら例を改造してみて、window functionsが分析クエリをどれだけシンプルにできるか体感してみてください。