What — 集約せずに集計する関数

GROUP BY の集約は、複数の行を1行に潰してしまう。ウィンドウ関数は元の行数を保ったまま、各行に対して「その行が属するグループでの集計値」や「並び順の中での位置」を付け足す。ユーザーごとの累計、日付順の前日比、カテゴリ内の順位といった「行を残したまま比べる」処理はこれで書ける。

How — 基本構文と3つのパーツ

ウィンドウ関数名 OVER (
PARTITION BY フィールド名
ORDER BY フィールド名
WINDOWフレーム
)

OVER () の中身が、その行から見える範囲（ウィンドウ）の定義になる。

• PARTITION BY … 集計を区切る単位。GROUP BY に相当するが、行は潰れない。省略するとテーブル全体が1つの区切りになる
• ORDER BY … 区切りの中での並び順。順位や累計、前後の行の参照はこの順序が基準になる
• ウィンドウフレーム … 並び順のどこからどこまでを計算に入れるか（ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW なら先頭から現在行まで＝累計）。省略時は ORDER BY の有無でデフォルトが変わる

よく使う関数はこのあたり。

• ROW_NUMBER() / RANK() / DENSE_RANK() … 区切りの中での順位。「ユーザーごとの最新1件」を取るときは ROW_NUMBER() で採番して1番だけ残す
• SUM() / AVG() / COUNT() … 通常の集約関数も OVER () を付ければウィンドウ関数として使える（累計・移動平均）
• LAG() / LEAD() … 前の行・次の行の値を持ってくる。前日比や離脱の判定に使う

関連

• Google Cloud
• データ基盤
• BigQuery
• Cloud StorageとBigQuery