SQLウィンドウ関数の使い方【ROW_NUMBER・RANK・LAG・累計・移動平均】
「顧客ごとに、何回目の注文かを番号で振りたい」「前日比を出したい」「売上の累計を出したい」──こうした集計は、GROUP BYだけでは書けません。そこで使うのが ウィンドウ関数 です。
ウィンドウ関数は、行をまとめずに、ほかの行の値を参照して計算する関数です。一度覚えると、分析のSQLが一気に書きやすくなります。この記事では、よく使うウィンドウ関数を、BigQueryでの実行結果付きで解説します。
参考記事
SQLの集計関数の使い方【SUM・AVG・COUNT・MAX・MIN/GROUP BY】
SQL JOINの使い方【INNER・LEFT・FULL OUTERの違いを実行結果で解説】
SQL CASE式の使い方【条件で値を分ける・集計をクロス表にする】
この記事で使うデータ
JOINの記事と同じ注文テーブル(shop.orders)と、2店舗の日別売上のテーブル(shop.daily_sales)を使います。BigQueryで同じデータを作る場合は、サンプルデータのSQL(shop_sample.sql)を実行してください。
shop.daily_sales(2026年3月1日〜28日、渋谷店・横浜店)
| sales_date | store | sales | customers |
|---|---|---|---|
| 2026-03-01 | 横浜店 | 165400 | 110 |
| 2026-03-01 | 渋谷店 | 244200 | 162 |
| 2026-03-02 | 横浜店 | 113200 | 75 |
| 2026-03-02 | 渋谷店 | 185900 | 123 |
| … | … | … | … |
ウィンドウ関数の書き方
関数() OVER (
PARTITION BY グループ分けする列
ORDER BY 並べる順番の列
)
| 部分 | 意味 |
|---|---|
PARTITION BY |
計算をグループごとに分ける(GROUP BYに近いが、行はまとめない) |
ORDER BY |
グループの中での並び順。順位や前の行の参照に使う |
GROUP BYとのいちばんの違いは、元の行がそのまま残ることです。
ROW_NUMBER:何回目の注文かを数える
顧客ごとに、注文日の順に1, 2, 3…と番号を振ります。
SELECT
customer_id
, order_id
, order_date
, amount
, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_seq
FROM
shop.orders
WHERE
customer_id IN ('C01', 'C02', 'C05')
ORDER BY
customer_id
, order_date
実行結果
| customer_id | order_id | order_date | amount | order_seq |
|---|---|---|---|---|
| C01 | 1001 | 2026-03-02 | 4400 | 1 |
| C01 | 1003 | 2026-03-07 | 2500 | 2 |
| C01 | 1012 | 2026-03-29 | 1200 | 3 |
| C02 | 1002 | 2026-03-03 | 1200 | 1 |
| C02 | 1006 | 2026-03-14 | 2800 | 2 |
| C05 | 1005 | 2026-03-10 | 5400 | 1 |
| C05 | 1009 | 2026-03-21 | 6800 | 2 |
PARTITION BY customer_id で顧客ごとに番号がリセットされています。「初回購入と2回目以降で、平均の購入金額を比べる」といった分析に使えます。
RANK:順位を付ける
顧客ごとの売上を集計して、売上の大きい順に順位を付けます。ウィンドウ関数は、GROUP BYの集計結果に対しても使えます。
SELECT
customer_id
, SUM(amount) AS sales
, RANK() OVER (ORDER BY SUM(amount) DESC) AS sales_rank
FROM
shop.orders
GROUP BY
customer_id
ORDER BY
sales_rank
実行結果
| customer_id | sales | sales_rank |
|---|---|---|
| C05 | 12200 | 1 |
| C01 | 8100 | 2 |
| C03 | 4400 | 3 |
| C02 | 4000 | 4 |
| C07 | 3000 | 5 |
| C06 | 2400 | 6 |
| C09 | 600 | 7 |
ROW_NUMBER・RANK・DENSE_RANKの違い
3つの違いは、同じ値(同点)があるときの番号の付け方です。金額が1,200円の注文が2件あるデータで比べてみます。
SELECT
order_id
, amount
, ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_number
, RANK() OVER (ORDER BY amount DESC) AS rank
, DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rank
FROM
shop.orders
WHERE
amount IN (3000, 2500, 1200, 800, 600)
ORDER BY
amount DESC
, order_id
実行結果
| order_id | amount | row_number | rank | dense_rank |
|---|---|---|---|---|
| 1011 | 3000 | 1 | 1 | 1 |
| 1003 | 2500 | 2 | 2 | 2 |
| 1002 | 1200 | 3 | 3 | 3 |
| 1012 | 1200 | 4 | 3 | 3 |
| 1010 | 800 | 5 | 5 | 4 |
| 1008 | 600 | 6 | 6 | 5 |
| 関数 | 同点のとき | 次の番号 |
|---|---|---|
ROW_NUMBER |
同点でも別の番号(3, 4) | 続き番号 |
RANK |
同じ順位(3, 3) | 人数分飛ぶ(5) |
DENSE_RANK |
同じ順位(3, 3) | 飛ばない(4) |
※ROW_NUMBER は同点のときの順番が決まらないので、実行するたびに入れ替わることがあります。順番を固定したいときは、ORDER BY amount DESC, order_id のように並べる列を追加します。
LAG:前日の値を参照する(前日比)
LAG(列) は、並び順で1つ前の行の値を取り出します。渋谷店の日別売上の前日比を出してみます。
SELECT
sales_date
, sales
, LAG(sales) OVER (ORDER BY sales_date) AS prev_sales
, sales - LAG(sales) OVER (ORDER BY sales_date) AS diff
FROM
shop.daily_sales
WHERE
store = '渋谷店'
AND sales_date <= '2026-03-07'
ORDER BY
sales_date
実行結果
| sales_date | sales | prev_sales | diff |
|---|---|---|---|
| 2026-03-01 | 244200 | NULL | NULL |
| 2026-03-02 | 185900 | 244200 | -58300 |
| 2026-03-03 | 164700 | 185900 | -21200 |
| 2026-03-04 | 166200 | 164700 | 1500 |
| 2026-03-05 | 198200 | 166200 | 32000 |
| 2026-03-06 | 193300 | 198200 | -4900 |
| 2026-03-07 | 225500 | 193300 | 32200 |
最初の日は「前の行」がないのでNULLになります。逆に、1つ後ろの行を取り出すときは LEAD を使います。
SUM OVER:累計を出す
SUM(列) OVER (ORDER BY …) と書くと、並び順で最初の行から今の行までの合計、つまり累計になります。
SELECT
sales_date
, sales
, SUM(sales) OVER (ORDER BY sales_date) AS cumulative_sales
FROM
shop.daily_sales
WHERE
store = '渋谷店'
AND sales_date <= '2026-03-07'
ORDER BY
sales_date
実行結果
| sales_date | sales | cumulative_sales |
|---|---|---|
| 2026-03-01 | 244200 | 244200 |
| 2026-03-02 | 185900 | 430100 |
| 2026-03-03 | 164700 | 594800 |
| 2026-03-04 | 166200 | 761000 |
| 2026-03-05 | 198200 | 959200 |
| 2026-03-06 | 193300 | 1152500 |
| 2026-03-07 | 225500 | 1378000 |
月の途中で「目標に対してどこまで来ているか」を見るときなどに使えます。
AVG OVER:7日移動平均
日別の売上は曜日による上下が大きいので、傾向を見るときは移動平均がよく使われます。ROWS BETWEEN 6 PRECEDING AND CURRENT ROW で、「6行前から今の行まで」の7行を計算の範囲にします。
SELECT
sales_date
, sales
, ROUND(AVG(sales) OVER (ORDER BY sales_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)) AS ma7
, COUNT(*) OVER (ORDER BY sales_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS n_days
FROM
shop.daily_sales
WHERE
store = '渋谷店'
AND sales_date <= '2026-03-10'
ORDER BY
sales_date
実行結果
| sales_date | sales | ma7 | n_days |
|---|---|---|---|
| 2026-03-01 | 244200 | 244200 | 1 |
| 2026-03-02 | 185900 | 215050 | 2 |
| 2026-03-03 | 164700 | 198267 | 3 |
| 2026-03-04 | 166200 | 190250 | 4 |
| 2026-03-05 | 198200 | 191840 | 5 |
| 2026-03-06 | 193300 | 192083 | 6 |
| 2026-03-07 | 225500 | 196857 | 7 |
| 2026-03-08 | 251400 | 197886 | 7 |
| 2026-03-09 | 164600 | 194843 | 7 |
| 2026-03-10 | 165900 | 195014 | 7 |
日別の売上は16万〜25万円と大きく上下していますが、7日移動平均は19万円台で安定しています。最初の6日間は7日分のデータがそろっていないので(n_days が7未満)、グラフにするときは7日目以降だけを使うのが一般的です。
※ROWS BETWEEN は「行の数」で範囲を決めます。日付が抜けている日(売上のない日など)があると、7行が7日間にならないので注意しましょう。
構成比:グループの合計に対する割合
SUM(列) OVER (PARTITION BY …) で、ORDER BYを付けずに書くと、グループ全体の合計が各行に付きます。これで割ると、グループの中での構成比が出せます。
SELECT
store
, order_id
, amount
, SUM(amount) OVER (PARTITION BY store) AS store_total
, ROUND(amount / SUM(amount) OVER (PARTITION BY store) * 100, 1) AS share_pct
FROM
shop.orders
WHERE
store IN ('横浜店', 'オンライン')
ORDER BY
store
, order_id
実行結果
| store | order_id | amount | store_total | share_pct |
|---|---|---|---|---|
| オンライン | 1003 | 2500 | 15300 | 16.3 |
| オンライン | 1005 | 5400 | 15300 | 35.3 |
| オンライン | 1008 | 600 | 15300 | 3.9 |
| オンライン | 1009 | 6800 | 15300 | 44.4 |
| 横浜店 | 1002 | 1200 | 7000 | 17.1 |
| 横浜店 | 1006 | 2800 | 7000 | 40 |
| 横浜店 | 1011 | 3000 | 7000 | 42.9 |
GROUP BYを使わずに、明細の行を残したまま「店舗の合計」と「その中での割合」を並べられるのが、ウィンドウ関数の便利なところです。
QUALIFY:初回購入だけを取り出す
ROW_NUMBERで番号を振ったあと、1番だけを残したいことはよくあります。BigQueryでは、QUALIFY でウィンドウ関数の結果を条件に絞り込めます。
SELECT
customer_id
, order_id
, order_date
, amount
FROM
shop.orders
QUALIFY
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) = 1
ORDER BY
customer_id
実行結果
| customer_id | order_id | order_date | amount |
|---|---|---|---|
| C01 | 1001 | 2026-03-02 | 4400 |
| C02 | 1002 | 2026-03-03 | 1200 |
| C03 | 1004 | 2026-03-08 | 3600 |
| C05 | 1005 | 2026-03-10 | 5400 |
| C06 | 1007 | 2026-03-15 | 2400 |
| C07 | 1011 | 2026-03-28 | 3000 |
| C09 | 1008 | 2026-03-18 | 600 |
顧客ごとの初回購入の注文だけが取り出せました。ORDER BY order_date DESC にすれば、最新の注文だけを取り出せます。重複したデータから1件だけ残す、といった用途にもよく使います。
※QUALIFYはBigQueryやSnowflakeなどで使える書き方です。使えないデータベースでは、サブクエリの外側のWHEREで絞り込みます。
よくある失敗:WHEREで先に絞ると前の行が見えない
2店舗の「先週の同じ曜日との比較(前週比)」を、3月8日〜10日の分だけ出そうとして、次のように書いたとします。LAG(sales, 7) は「7行前」の値です。
-- 間違い:WHEREで先に絞り込んでいる
SELECT
store
, sales_date
, sales
, LAG(sales, 7) OVER (PARTITION BY store ORDER BY sales_date) AS sales_last_week
FROM
shop.daily_sales
WHERE
sales_date BETWEEN '2026-03-08' AND '2026-03-10'
ORDER BY
store
, sales_date
実行結果
| store | sales_date | sales | sales_last_week |
|---|---|---|---|
| 横浜店 | 2026-03-08 | 182900 | NULL |
| 横浜店 | 2026-03-09 | 125800 | NULL |
| 横浜店 | 2026-03-10 | 146100 | NULL |
| 渋谷店 | 2026-03-08 | 251400 | NULL |
| 渋谷店 | 2026-03-09 | 164600 | NULL |
| 渋谷店 | 2026-03-10 | 165900 | NULL |
すべてNULLになってしまいました。SQLはWHEREで絞り込んでから、ウィンドウ関数を計算するので、1週間前の行がすでに消えているのです。
正しくは、サブクエリの中でウィンドウ関数を計算してから、外側で絞り込みます。
SELECT
*
FROM (
SELECT
store
, sales_date
, sales
, LAG(sales, 7) OVER (PARTITION BY store ORDER BY sales_date) AS sales_last_week
, ROUND((sales / LAG(sales, 7) OVER (PARTITION BY store ORDER BY sales_date) - 1) * 100, 1) AS wow_pct
FROM
shop.daily_sales
)
WHERE
sales_date BETWEEN '2026-03-08' AND '2026-03-10'
ORDER BY
store
, sales_date
実行結果
| store | sales_date | sales | sales_last_week | wow_pct |
|---|---|---|---|---|
| 横浜店 | 2026-03-08 | 182900 | 165400 | 10.6 |
| 横浜店 | 2026-03-09 | 125800 | 113200 | 11.1 |
| 横浜店 | 2026-03-10 | 146100 | 145100 | 0.7 |
| 渋谷店 | 2026-03-08 | 251400 | 244200 | 2.9 |
| 渋谷店 | 2026-03-09 | 164600 | 185900 | -11.5 |
| 渋谷店 | 2026-03-10 | 165900 | 164700 | 0.7 |
先週の同じ曜日の売上と、前週比(%)が出せました。
まとめ
| やりたいこと | 書き方 |
|---|---|
| グループ内の通し番号 | ROW_NUMBER() OVER (PARTITION BY … ORDER BY …) |
| 順位 | RANK()/DENSE_RANK() |
| 前の行・後ろの行の値 | LAG(列, n)/LEAD(列, n) |
| 累計 | SUM(列) OVER (ORDER BY …) |
| 移動平均 | AVG(列) OVER (ORDER BY … ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) |
| グループ内の構成比 | 列 / SUM(列) OVER (PARTITION BY …) |
| 1番だけ残す | QUALIFY ROW_NUMBER() OVER (…) = 1 |
ウィンドウ関数は、WHEREで絞り込む前のデータで計算されることだけ注意すれば、前日比・累計・順位など、レポートでよく使う数字をSQLだけで出せるようになります。
NEWSLETTER
Data Schoolの新着記事をメールで受け取る
新しい記事が公開されたら、メールでお知らせします。配信はいつでも停止できます。
