ウィンドウ関数｜データエンジニアリング

🎓 レベル：標準　|　重要度：A（必須）

📎 前提：SQLの基礎（結合・集約・サブクエリ）　|　関連：インデックスと実行計画・ストリーム処理

要点（BLUF）

ウィンドウ関数は、行を畳まずに（明細を残したまま）、各行に「グループ内での集計・順位・前後行の値」を付け足す関数。
構文の核は 関数() OVER (PARTITION BY 区切り ORDER BY 順序)。PARTITION BYでグループを区切り、ORDER BYで枠内の順序を決める。
ランキング（ROW_NUMBER/RANK）、累積（SUM ... OVER）、移動平均、前後行参照（LAG/LEAD）が定番。「各行に文脈を添える」分析の必需品。

概念 ── GROUP BYとの決定的な違い

GROUP BYは行を畳んで「グループにつき1行」に減らします。ウィンドウ関数は行を減らさず、各行に「その行が属するグループの集計」を横に付け足すだけです。

flowchart LR
    G["GROUP BY：8行 -> 2行（畳む）"]
    W["ウィンドウ：8行 -> 8行（各行に集計を付与）"]

「顧客ごとの合計」を出しつつ「各注文の明細も残したい」ときに使います。

仕組み ── PARTITION BY と ORDER BY

顧客ごとに（PARTITION BY cust_id）、日付順に（ORDER BY ymd）、注文の連番と累積額を付けます。

SELECT cust_id, order_id, amount,
       ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY ymd) AS seq,
       SUM(amount)  OVER (PARTITION BY cust_id ORDER BY ymd) AS running_total
FROM orders
ORDER BY cust_id, ymd;

実行結果（実機・SQLite）:

cust order amount seq running_total
(1,   1,   1200,  1,  1200)
(1,   2,    800,  2,  2000)
(1,   6,    400,  3,  2400)
(2,   3,    500,  1,   500)
(2,   5,    700,  2,  1200)
(3,   4,   3000,  1,  3000)
(3,   8,   1500,  2,  4500)
(4,   7,    900,  1,   900)

running_totalは「同じ顧客の、その行までの累計」。ORDER BYが付いた集約は枠が先頭から現在行までに自動で広がる（デフォルトのRANGEフレーム）ため、累積和になります。ROW_NUMBERは枠内の通し番号です。

設計の勘所 ── 関数のカテゴリ

種類	関数	用途
順位	`ROW_NUMBER`, `RANK`, `DENSE_RANK`	グループ内ランキング・最新1件抽出
集約	`SUM`, `AVG`, `COUNT` `OVER`	累積和・移動平均・構成比
行参照	`LAG`, `LEAD`	前日比・前後の値との差分
分布	`NTILE`, `PERCENT_RANK`	四分位・パーセンタイル

「顧客ごとの最新注文1件」は ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY ymd DESC) が1の行、という形で頻出します。移動平均は AVG(x) OVER (ORDER BY t ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) のようにフレーム指定で枠を狭めます。

なぜそうするか ── 自己結合の置き換え

なぜウィンドウ関数を使うのか。自己結合（self-join）やサブクエリの繰り返しを1パスに畳めるからです。「各注文と、その顧客の累計」をサブクエリで書くと、行ごとに相関サブクエリが走り遅くなります。ウィンドウ関数は枠を一度ソートして舐めるだけで済むので、可読性も性能も上です。時系列の前日比・移動平均（→ 時系列分析）でも中心的な道具になります。

⚠️ よくある落とし穴

PARTITION BYを省く → 枠が全行になる（テーブル全体の累計・順位）。区切りたいなら必ず指定。
ORDER BYなしの累積を期待する → 順序がないと累積は定義できない。SUM OVER (PARTITION BY ...)だけだとグループ総計（全行同じ値）になる。
WHEREでウィンドウ関数の結果を絞ろうとする → ウィンドウはSELECT段で計算されるためWHEREでは使えない。CTEで一旦出してから外側で絞る。
RANKとROW_NUMBERの混同 → 同順位でRANKは番号を飛ばす、ROW_NUMBERは飛ばさない。

対応ラボ

data-engineering-study/labs/03_sql_optimization.py（PYTHONIOENCODING=utf-8 で実行・上記の累積/順位を確認済み）。