はじめに
SQL を書いていると、「集計したいけどグループ化したくない」「レコードごとにランキングを付けたい」といった場面が必ず出てきます。
そんなときに威力を発揮するのが window関数（分析関数） です。
BigQuery でも標準 SQL 構文としてサポートされており、データ分析やレポーティングで頻繁に使われます。
本記事ではウィンドウ関数の基本概念から具体例までを整理します。
ウィンドウ関数とは
ウィンドウ関数とは、「集計の単位（ウィンドウ）」をレコードごとに指定し、その範囲内で集計や計算を行う関数のことです。
通常の集計（GROUP BY）は結果をまとめて1行にしてしまいますが、ウィンドウ関数は「元の行を残しつつ集計結果を付与」できるのが大きな特徴です。
基本構文
ウィンドウ関数の構文は以下のようになります。
<関数名>(<列>)
OVER (
PARTITION BY <列>
ORDER BY <列>
ROWS BETWEEN ...
)
PARTITION BY : ウィンドウを分ける単位。例：顧客ごと、部門ごと
ORDER BY : ウィンドウ内での並び順。例：日付順、金額順
ROWS BETWEEN : 範囲をさらに絞る指定。例：直近3行だけ
よく使うウィンドウ関数
-
ROW_NUMBER
行番号を振る関数。グループごとに連番を付けたいときに便利です。
SELECT user_id, order_id, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS rn FROM orders;ユーザーごとに、注文日順で 1, 2, 3... と番号が付きます。
-
RANK / DENSE_RANK
ランキングを付ける関数。
RANK は同順位の次の順位を飛ばしますが、DENSE_RANK は飛ばしません。SELECT product_id, sales, RANK() OVER (ORDER BY sales DESC) AS rank1, DENSE_RANK() OVER (ORDER BY sales DESC) AS rank2 FROM products;RANK: 1, 2, 2, 4, ...
DENSE_RANK: 1, 2, 2, 3, ...
-
SUM / AVG / MAX / MIN
集計関数をウィンドウ関数として使えます。
グループ化せずに「ユーザーごとの合計」や「累積合計」を出すのに便利です。SELECT user_id, order_date, amount, SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS cum_sum FROM orders;ユーザーごとに注文金額の累計が取れます。
-
LAG / LEAD
前後の行を参照できる関数。
差分計算や比較に欠かせません。SELECT user_id, order_date, amount, LAG(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS prev_amount, amount - LAG(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS diff FROM orders;1つ前の注文額との差分が簡単に算出できます。
実践例
例1: 日次売上の前日比を出す
SELECT
order_date,
SUM(amount) AS daily_sales,
SUM(amount) - LAG(SUM(amount)) OVER (ORDER BY order_date) AS diff
FROM orders
GROUP BY order_date
ORDER BY order_date;
ウィンドウ関数を使うことで、集計後の結果に対して前日比をすぐに計算できます。
例2: 最新のデータだけを残す
よくあるユースケースが「最新のレコードだけ抽出する」処理です。
SELECT *
FROM (
SELECT
user_id,
order_id,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) AS rn
FROM orders
)
WHERE rn = 1;
ユーザーごとの最新注文だけを取り出せます。
例3: ローリング平均
直近7日間の移動平均を計算する例です。
SELECT
order_date,
AVG(amount) OVER (
ORDER BY order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_avg
FROM daily_sales;
時系列データのスムージングに役立ちます。
BigQueryでの注意点
-
パーティション分けすぎ注意：PARTITION BY のキーが細かすぎると処理コストが増大します。
-
ORDER BY 必須かどうか：ROW_NUMBER, LAG などは順序が重要ですが、SUM などは不要な場合もあります。
-
NULLの扱い：LAG / LEAD はデフォルトで NULL を返すので、LAG(col, 1, 0) のようにデフォルト値を指定すると便利です。
まとめ
ウィンドウ関数は、一度理解してしまえば 「集計しながらも元の行を残せる」 という便利さを実感できる機能です。
通常の GROUP BY ではデータが集約されてしまい、行ごとの情報を失いますが、ウィンドウ関数ならその心配はありません。集計値と明細を同時に扱えるため、分析の幅が一気に広がります。
実際のユースケースを振り返ると、その強みがよく分かります。売上データに対して「前日比を計算する」「累積合計を求める」といった時系列分析はもちろん、ユーザーごとに「最新のレコードを抽出する」「購入回数に応じて順位を付ける」など、実務で直面する課題をスマートに解決できます。
従来ならサブクエリや複雑な結合が必要だった処理が、数行で書けるようになるのは大きなメリットです。
一方で、注意点もあります。PARTITION BY のキーを細かくしすぎると処理が重くなりますし、LAG や LEAD を使う際には NULL の扱いに気を配る必要があります。とはいえ、これらは使い慣れるうちに自然と感覚が掴めるようになる部分でもあります。
総じて、ウィンドウ関数は「SQLを分析ツールとして使う」ための基礎であり、BigQuery を日常的に使う人にとっては 必修スキル だと言えるでしょう。
一度パターンを身につけてしまえば、さまざまな分析をシンプルに書けるようになります。まだあまり触れていない方は、ぜひ小さなクエリから試してみて、その便利さを体感してみてください！！