ウィンドウ関数 — 畳まずに隣を見る方法
一言でいうと
ウィンドウ関数は、行を畳まないまま、その行の周りの他の行を参照させてくれます。順位、累積、直前の値との比較のように、集計だけでは難しい質問を、1回のスキャンで解きます。その代わり、ウィンドウ(パーティション)とフレームが何かを正確に知って使う必要があります。デフォルトが直感と違う場所があるからです。
なぜ必要なのか
「カテゴリごとに最も高い商品3つ」を、GROUP BYだけで求めようとすると、自己結合や相関サブクエリが必要になります。商品ごとに「自分より高い同じカテゴリの商品がいくつあるか」を数える方式ですが、コードが長くなり、表を何度も読みます。「前月比の売上の増減」も似ています。月別集計を、自分自身と1か月ずらして結合する必要があり、最初の月と空の月を別に処理しなければなりません。
2つの質問の共通点は、各行を維持したまま、隣の行を見る必要があることです。集計は行を畳んでしまうので、これができません。各行が自分のグループの中で何番目なのか、直前の行の値が何なのかがわかれば、問題が単純になります。ウィンドウ関数が、その場所を埋めます。
どう動くのか
ウィンドウ関数は、func() OVER (PARTITION BY ... ORDER BY ... frame)の形です。
PARTITION BYは、ウィンドウを分ける基準です。GROUP BYと似ていますが、行を畳みません。省略すると、結果全体が1つのウィンドウです。ORDER BYは、ウィンドウの中での順序です。順位と累積の基準になります。- フレームは、現在の行を計算するときに、ウィンドウの中のどの行まで見るかを決めます。
sum、avg、last_valueのように、複数の行を見る関数が、この影響を受けます。
評価されるタイミングも知っておく必要があります。ウィンドウ関数は、WHERE、GROUP BY、HAVINGがすべて終わった後で計算されます。そのため、2つのことが導かれます。1つ目は、ウィンドウ関数の結果は、同じクエリのWHEREで使えないことです。順位で絞り込むには、サブクエリやCTEで1段包んで、外側で絞り込みます。2つ目は、集計が先に終わるので、ウィンドウ関数の引数に集計を入れられることです。sum(sum(total_amount)) OVER ()は、グループごとの合計を先に作り、その合計の全体の合計を各行に付けます。割合を求めるときに、クエリを2回動かす必要がない理由です。
SELECT category, id, price
FROM (
SELECT category, id, price,
row_number() OVER (PARTITION BY category ORDER BY price DESC, id) AS rn
FROM products
) t
WHERE rn <= 3;
よく使う関数は、次のように分かれます。
| 関数 | 動作 | 同点と境界 |
|---|---|---|
row_number() |
1からの連番 | 同点でも別の番号で、どちらが先になるかは並べ替えの基準が決める |
rank() |
順位 | 同点は同じ順位で、次は飛ばす(1,1,3) |
dense_rank() |
順位 | 同点は同じ順位で、次は連続(1,1,2) |
sum() OVER (ORDER BY ...) |
累積合計 | デフォルトのフレームが同点の行まで含む |
lag() / lead() |
前 / 後の行の値 | なければデフォルト値、省略するとNULL |
ntile(n) |
1–nのグループ番号 | できるだけ均等に分け、サイズの差は最大1 |
最も注意すべきなのが、デフォルトのフレームです。ORDER BYがあると、デフォルトのフレームはRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWで、これは「パーティションの最初から現在の行まで」ではなく、「最初から、現在の行と並べ替えの値が同じ最後の行まで」を意味します。ORDER BYがなければ、すべての行が互いに同点なので、フレームがパーティション全体になります。
現場で何が問題になるのか
累積合計が階段のように跳ねます。注文時刻で累積売上を求めたのに、同じ時刻の注文2件が、まったく同じ累積値を持ちます。デフォルトのフレームが、同点の行まで一度に含むからです。1行ずつ積み上げるには、ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWを明示し、並べ替えの基準にidのようなtie-breakのカラムを加えます。
last_valueが現在の行を返します。パーティションの最後の値が欲しかったのに、デフォルトのフレームが現在の行で終わるので、現在の行(またはその同点)の値が出ます。PostgreSQLのドキュメントも、この組み合わせが役に立たない結果を出しやすいと警告しています。ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGでフレームを広げるか、並べ替えを逆にしてfirst_valueを使います。
最初の注文が、実行のたびに変わります。顧客ごとの最初の注文をrow_number() ... ORDER BY ordered_atで選んだのに、同じ時刻の注文が2件あると、どちらが1番になるかが決まっていません。今日は合っていて、明日は別の答えが出ます。並べ替えの基準が一意になるまで、カラムを加えます。
lag()が空の月を飛ばします。売上がない月は、集計結果に行そのものがないので、3月のlag()は、1月の値を返します。結果には「前月比」と書かれています。空の月まで比較する必要があるなら、generate_seriesでカレンダーを先に作り、LEFT JOINします。
WHEREが分母を変えます。ウィンドウ関数はWHEREの後に計算されるので、WHEREで特定のステータスだけを残して割合を求めると、それは「残った行の中での割合」です。全体に対する割合が必要なら、絞り込む前に計算して、外側で絞り込みます。
丸めた割合の合計が100になりません。チャネル別の割合を、それぞれ小数第2位で丸めると、合計が99.99や100.01になることがあります。計算が間違っているのではなく、丸めの誤差です。レポートに合計も載せるなら、その差がなぜ生じるのかを書いておきます。
どう確認するのか
結果を信じる前に、不変条件をクエリで確認します。
-- every partition has exactly one row numbered 1
SELECT count(*) FILTER (WHERE rn = 1) = count(DISTINCT category) FROM v_ranked_products;
-- the last running total equals the plain total
SELECT max(cum_revenue) = sum(revenue) FROM v_running_revenue;
-- the first month has no previous value, the others do
SELECT count(*) FILTER (WHERE prev_revenue IS NULL) FROM v_mom;
-- bucket sizes differ by at most one
SELECT quartile, count(*) FROM v_price_quartile GROUP BY quartile ORDER BY 1;
実行計画でも確認できます。EXPLAINを付けると、ウィンドウ関数はWindowAggノードとして現れ、たいていその下に、PARTITION BYとORDER BYの基準のSortがあります。並べ替えの基準が異なるOVER句を複数使うと、SortとWindowAggがその分だけ重なって積まれるのも見えます。大きな表でウィンドウのクエリが遅いなら、この並べ替えからまず見ます。
次のラボですること
まず前の理論の集計のラボで、GROUP BY、HAVING、FILTERと月別集計を作り、その後のウィンドウ関数のラボで、カテゴリ別の連番、2種類の順位の比較、累積売上、前月比、チャネル別の割合、グループ別の上位3つ、価格の四分位、顧客ごとの最初の注文を順に実装します。上の確認クエリを、ラボで作ったビューにそのまま動かしてみれば、採点の前に、自分で答えを検証できます。