SQL

SQLウィンドウ関数の使い方【ROW_NUMBER・RANK・LAG・累計・移動平均】

酒井 裕麻株式会社Bistro 代表取締役社長

「顧客ごとに、何回目の注文かを番号で振りたい」「前日比を出したい」「売上の累計を出したい」──こうした集計は、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だけで出せるようになります。

CONTACT

データ活用のお悩み、
直接相談しませんか。

「何から始めればいいかわからない」段階でも大丈夫です。課題の整理からお手伝いします。