『ウィンドウ関数』を使いこなすとデータ分析の生産性が一段上がる
SQL のウィンドウ関数は、サブクエリで複雑だった集計を1パスで簡潔に書けます。本記事では編集部の視点で、実務頻出パターンを公開情報をもとに整理します。SQL チューニング もご参考に。
基本構文
(1) FUNCTION() OVER (PARTITION BY ... ORDER BY ...)。(2) PARTITION BY：グループ化。(3) ORDER BY：順序指定。(4) ROWS / RANGE / GROUPS：フレーム指定。(5) 結果は行単位：GROUP BY と違う。
ランキング系
(1) ROW_NUMBER()：連番・一意。(2) RANK()：同値で同順位・次は飛ばす。(3) DENSE_RANK()：同値で同順位・次は連続。(4) NTILE(n)：n分位に分割。(5) 例：『顧客ごとの購入額TOP3』。
集計系
(1) SUM() OVER ()：累計。(2) AVG() OVER ()：移動平均。(3) COUNT() OVER ()：累積件数。(4) MAX() OVER ()：累積最大。(5) 例：『月次売上の前年比』。
オフセット系
(1) LAG(col, n)：n行前の値。(2) LEAD(col, n)：n行後の値。(3) FIRST_VALUE() / LAST_VALUE()。(4) NTH_VALUE()：n番目の値。(5) 例：『日次売上の前日比』。
実務頻出パターン
(1) 各カテゴリ TOP N：ROW_NUMBER + WHERE。(2) 累計売上：SUM() OVER (ORDER BY date)。(3) 移動平均：AVG() OVER ROWS BETWEEN 6 PRECEDING AND CURRENT ROW。(4) 前期比：LAG + 計算。(5) 累積パーセンタイル：PERCENT_RANK()。dbt 実践 もご参考に。
パフォーマンスの注意
(1> PARTITION BY のキーでインデックス活用。(2) ORDER BY も含む複合インデックス。(3) 巨大なウィンドウはメモリ消費。(4) フレーム指定を明示してパフォーマンス改善。(5) EXPLAIN ANALYZEで確認。
失敗しがちなパターン
(1) サブクエリで実装：可読性低下。(2) PARTITION BY なしで全行対象に。(3) フレーム指定なし：意図と違う集計。(4) 巨大データで OOM。(5) ORDER BY 不一致でランキング不安定。対策は、(1)ウィンドウ関数活用、(2)PARTITION明示、(3)ROWS指定、(4)パーティショニング+集計済テーブル、(5)tie-breaker、です。