SQLのウィンドウ関数:OVER、PARTITION BY、ROW_NUMBER
SQLのウィンドウ関数(window関数、分析関数とも呼ばれます)は、複数の行にまたがって計算しながら、すべての行をそのまま返します。GROUP BYはグループを1行にまとめますが、ウィンドウ関数は行を残したまま、順位、累計、前月の値のような列を横に足します。ROW_NUMBERにPARTITION BYを組み合わせる書き方は、その代表です。
例のテーブル
| region | month | revenue |
|---|---|---|
| North | 2026-01 | 120 |
| North | 2026-02 | 150 |
| North | 2026-03 | 150 |
| South | 2026-01 | 90 |
| South | 2026-02 | 80 |
| South | 2026-03 | 110 |
書き方
function_name(...) OVER (PARTITION BY ... ORDER BY ...)
OVERがあるとウィンドウ関数になります。PARTITION BYは行をグループに分け、グループごとに計算をやり直します。省くと、テーブル全体が1つのウィンドウです。OVERの中のORDER BYは、関数が行をたどる順番を決めるだけで、出力は並べ替えません。出力を並べるには、クエリの最後にORDER BYを書きます。
ROW_NUMBER、RANK、DENSE_RANK
SELECT region, month, revenue,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) AS row_num,
RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS dense_rnk
FROM sales;
Northでは2月と3月が150で同じです。ROW_NUMBERはそれでも1と2を振り、どちらが1になるかはデータベースしだいです。RANKは両方を1にして、1月は3に飛びます。DENSE_RANKは両方を1にして、1月は2です。Southには同じ値がないので、3つとも1、2、3を返します。同じ値をどう扱いたいかで関数を選び、毎回同じ結果が必要ならORDER BYに2つ目の列を足してください。
グループごとの上位の行
WHEREはウィンドウが計算される前に動くので、ウィンドウ関数の結果では絞り込めません。サブクエリかWITHの中で番号を振り、その外で絞り込みます。
WITH ranked AS (
SELECT region, month, revenue,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC, month) AS rn
FROM sales
)
SELECT region, month, revenue FROM ranked WHERE rn = 1;
結果はNorthの2月とSouthの3月です。Snowflake、BigQuery、DuckDBでは、最初のクエリの最後にQUALIFY rn = 1と書くこともできます。
累計と移動平均
SUM(revenue) OVER (PARTITION BY region ORDER BY month
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
Northの累計は120、270、420、Southは90、170、280です。フレームをROWS BETWEEN 2 PRECEDING AND CURRENT ROWに変えてAVGを使えば、3か月の移動平均になります。フレームは省かずに書きましょう。ORDER BYがあってフレームがないと、多くのデータベースはRANGEを使い、同じ値の行をまとめて足すので、累計が急に跳ねることがあります。
LAGとLEAD
LAG(revenue) OVER (PARTITION BY region ORDER BY month)は各行に前月の売上を、LEADは翌月の売上を返します。引き算すれば増減がわかります。Northは1月がNULL、そのあと+30、0です。Southは-10、+30です。各地域の最初の行には前月がないので、レポートでNULLと0のどちらが正しいかを決めてください。
対応しているデータベース
PostgreSQL、SQL Server、Oracle、MySQL 8.0以降、SQLite 3.25以降、BigQuery、Snowflake、DuckDBは、どれもウィンドウ関数に対応しています。このレッスンの練習問題は、架空のオンラインストアKestrelのテーブルに対してDuckDBで動き、返ってくる行でクエリを確認します。