ウィンドウ関数は、関連する行の集合 (ウィンドウ) から各行の値を計算します。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 の値で動くので、値が等しい行 (ピア) は常に一緒にフレームに入ります。