Menu

PostgreSQL ウィンドウ関数:OVER・PARTITION BY・ROW_NUMBER

ウィンドウ関数は、関連する行のグループから各行の値を計算します。行はまとめません。順位付け、累計、移動平均、前の行との比較に使います。

このページのコードはエディタで実行できます。編集してすぐに結果を確認できます。

ウィンドウ関数は、関連する行の集合 (ウィンドウ) から各行の値を計算します。GROUP BY のように行をまとめることはしません。function() OVER (PARTITION BY ... ORDER BY ...) と書きます。PARTITION BY は行をグループに分け、ORDER BY は各グループ内の順序を決めます。

すべての行が結果に残り、それぞれが地域の合計を持ちます。north は 1000、south は 500 です。各担当者の割合も1つの式で出せます (ana は north の 50 パーセント)。集約関数 (sum、count、avg、min、max) はどれも、OVER を付ければウィンドウ関数になります。空の OVER () は結果全体を1つのウィンドウにします。

ROW_NUMBER、RANK、DENSE_RANK で行に番号を付ける

3つの順位関数は、ORDER BY の値が同じ行があるときだけ結果が変わります。

ben と cy はどちらも 85 です。row_number はそれでも 2 と 3 を付けます。ORDER BY で同順位を区別しない限り、どちらが 2 になるかは決まっていません。rank は両方に 2 を付け、dee は 4 に飛びます。dense_rank は両方に 2 を付け、次は 3 です。

関数同順位同順位の後の欠番
row_number()別々の番号なし
rank()同じ番号あり
dense_rank()同じ番号なし

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

ウィンドウ関数は WHERE の後に評価されるので、直接絞り込むことはできません。サブクエリか CTE で行に番号を付け、外側で絞り込みます。

各地域の上位2人の担当者が返ります。2位タイの行をすべて残したいなら、row_number() の代わりに rank() を使います。グループごとに最初の1行だけなら、DISTINCT ON のほうが短く書けます。

累計と移動平均を計算する

OVER の中に ORDER BY があると、集約関数はフレームに対して計算されます。デフォルトのフレームはパーティションの最初の行から現在の行までなので、累計になります。ROWS BETWEEN 句を使えば、現在の行とその前の2行のように別のフレームを指定できます。

累計は5日目に 130 になり、5日目の3日間平均は (30 + 20 + 50) / 3 = 33.3 です。

デフォルトのフレームは RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW です。ORDER BY の値が現在の行と同じ行をすべて含むので、同じ日の2行は同じ累計になります。1行ずつ足していくには ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW と書きます。

前の行と比べる: LAG と LEAD

lag(col) はウィンドウの順序で現在の行の1つ前の行から、lead(col) は1つ後の行から値を読みます。端を越えると、3つ目の引数でデフォルト値を渡さない限りどちらも NULL を返します。

1日目には前の行がないので、previous と change は NULL です。WINDOW 句はウィンドウに名前を付けて1回だけ定義し、複数の関数で共有できるようにします。

first_value はデフォルトのフレームで動きますが、last_value は違います。デフォルトのフレームは現在の行で終わるので、現在の行の値を返してしまいます。ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING を付けて、フレームをパーティション全体にします。ウィンドウ専用の関数にはほかに ntile(n)、percent_rank()、cume_dist()、nth_value() があります。すべて PostgreSQL のウィンドウ関数リファレンス に載っています。

集約が数える行を絞り込む

FILTER (WHERE ...) はウィンドウ集約にも使え、条件付きの合計をすべての行に付けられます。

SELECT day,
       count(*) FILTER (WHERE status = 'error') OVER (ORDER BY day) AS errors_so_far
FROM requests;

よくある質問

GROUP BY と PARTITION BY の違いは何ですか?

GROUP BY はグループごとに1行を返します。OVER の中の PARTITION BY はすべての行を残し、それぞれにグループ全体から計算した値を付けます。そのため、行の金額とそのグループの合計を並べて表示できます。

WHERE でウィンドウ関数を使えますか?

使えません。ウィンドウ関数は WHERE、GROUP BY、HAVING の後に実行されるので、使えるのは SELECT のリストと ORDER BY だけです。サブクエリか CTE で値を計算し、外側のクエリで絞り込みます。

LAST_VALUE が現在の行の値を返すのはなぜですか?

OVER に ORDER BY があると、デフォルトのフレームは現在の行 (と同順位の行) で終わるので、フレームの最後の行が現在の行になります。ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING を付けると、フレームがパーティション全体になります。

フレームの ROWS と RANGE の違いは何ですか?

ROWS は物理的な行を数えます。ROWS BETWEEN 2 PRECEDING AND CURRENT ROW は現在の行とその前の2行です。RANGE は ORDER BY の値で動くので、値が等しい行 (ピア) は常に一緒にフレームに入ります。

Coddyのプログラミング言語のイラスト

Coddyでコードを学ぼう

始める