ウィンドウ関数の累積和と移動平均：ROWS BETWEENの使い方

SQLのウィンドウ関数を使うと、累積和と移動平均を1本のクエリで計算できます。売上レポートで「ここまでの累計」を行ごとに表示したり、「直近3日の平均」でデータのノイズを取り除いたりする場面は業務でよく起きます。この記事では SUM() OVER (ORDER BY ...) で累積和を求める方法と、ROWS BETWEEN 句で移動平均を求める方法を、日次売上データを使って段階的に解説します。フレーム指定の仕組みや ROWS と RANGE の使い分け、先頭行の扱いといった注意点も合わせて整理します。MySQL 8.0 以上を前提にしています。

このシナリオで考える
SUM() OVER で累積和を計算する
ROWS BETWEEN で移動平均を計算する
ROWS と RANGE の違い
よくある落とし穴 / 注意点
まとめ
参考リンク

このシナリオで考える

この記事全体では、店舗の日次売上を記録した daily_sales テーブルを使います。

CREATE TABLE daily_sales (
  sale_date DATE NOT NULL,
  amount    INT  NOT NULL
);

INSERT INTO daily_sales VALUES
  ('2024-01-01', 50000),
  ('2024-01-02', 80000),
  ('2024-01-03', 60000),
  ('2024-01-04', 90000),
  ('2024-01-05', 70000),
  ('2024-01-06', 110000),
  ('2024-01-07', 95000);

このデータに「毎日の累積売上」「3日移動平均」を付加していきます。1行1日のシンプルな構成で、フレーム指定の動きを確認するには十分な量です。

SUM() OVER で累積和を計算する

累積和（ランニングトータル）とは、先頭の行から現在の行までの合計値です。1月1日の累計・1月2日の累計と、日を追うごとに増えていく値を指します。ダッシュボードや月次レポートで「今月ここまでの売上」を行ごとに出したい場面によく使います。

SUM() OVER () にフレーム指定を追加すると累積和になります。

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_amount
FROM daily_sales
ORDER BY sale_date;

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW は「先頭の行から現在の行まで」を意味するフレーム指定です。フレーム（集計対象の範囲）を明示的に指定するのがポイントです。結果は以下になります。

sale_date	amount	cumulative_amount
2024-01-01	50,000	50,000
2024-01-02	80,000	130,000
2024-01-03	60,000	190,000
2024-01-04	90,000	280,000
2024-01-05	70,000	350,000
2024-01-06	110,000	460,000
2024-01-07	95,000	555,000

各行の cumulative_amount が「1月1日から当日まで」の合計になっています。1月1日は 50,000 のみ、1月2日は 50,000 + 80,000 = 130,000 という具合です。

ORDER BY だけを書いて ROWS BETWEEN ... を省略した場合、MySQL はデフォルトで RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW を適用します。多くのケースで結果は同じですが、同一日付のデータが複数あると挙動が変わります（後述します）。明示的に ROWS BETWEEN を書く習慣が安全です。

ROWS BETWEEN で移動平均を計算する

移動平均とは「直近N件の平均値」のことです。日々の売上の変動を平滑化してトレンドを読み取るのに使います。週次・月次の分析レポートでは頻出のテクニックです。ROWS BETWEEN N PRECEDING AND CURRENT ROW で「現在の行を含む直近N+1行」のフレームを指定できます。

-- 直近3日（当日を含む）の移動平均
SELECT
  sale_date,
  amount,
  AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3d
FROM daily_sales
ORDER BY sale_date;

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW は「2行前から現在の行まで」、合計3行分のフレームです。結果は以下です。

sale_date	amount	moving_avg_3d
2024-01-01	50,000	50,000.0
2024-01-02	80,000	65,000.0
2024-01-03	60,000	63,333.3
2024-01-04	90,000	76,666.7
2024-01-05	70,000	73,333.3
2024-01-06	110,000	90,000.0
2024-01-07	95,000	91,666.7

1月1日（データが1件）と1月2日（データが2件）はフレームに3行分のデータが揃っていません。この場合 MySQL は存在する行数だけで平均を計算します。NULL は返しません。

複数の店舗ごとに移動平均を計算する場合は PARTITION BY を組み合わせます。

-- 店舗ごとの3日移動平均
SELECT
  store_id,
  sale_date,
  amount,
  AVG(amount) OVER (
    PARTITION BY store_id
    ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3d
FROM daily_sales
ORDER BY store_id, sale_date;

PARTITION BY store_id を加えると、各店舗の先頭行からフレームが独立して構成されます。店舗をまたいで計算が混ざることはありません。

ROWS と RANGE の違い

フレーム指定には ROWS と RANGE の2種類があります。名前が似ているので混同しやすいですが、動作が異なります。

指定	フレームの単位	同値行の扱い
ROWS	物理的な行番号で指定	各行が独立して扱われる
RANGE	値の範囲（論理的）で指定	ORDER BY の値が同じ行はまとめて扱われる

同一日付のレコードが複数ある場合、RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW は「同じ日付の全行」をひとまとめにしてフレームに含めます。たとえば1月3日の行が3行あれば、「1月3日の3行目」を処理するときのフレームには1月3日の行が全部入ります。

-- daily_sales に同日付の行が複数あるとき

-- RANGE（ORDER BY のみ書いた場合のデフォルト）
-- 同日の行が全部フレームに入るため、累積和が意図と異なる可能性がある
SUM(amount) OVER (ORDER BY sale_date)

-- ROWS（明示）
-- 物理的な1行だけを「現在行」として扱うため安全
SUM(amount) OVER (
  ORDER BY sale_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

日次データで日付が一意であれば差は出ません。ただしデータの重複が起き得る場合は、常に ROWS を明示するのが無難です。

よくある落とし穴 / 注意点

ORDER BY を省略すると累積和にならない
SUM(amount) OVER () のように ORDER BY を省略すると、フレームは「パーティション全体」になります。累積和にはならず、全行で同じ合計値が返ります。ORDER BY は省略しないようにしましょう。

先頭行の移動平均を NULL にしたいとき
「3行揃ってから初めて計算する」仕様の場合、デフォルトでは先頭行も計算されます。ROW_NUMBER() と CASE を組み合わせて対応します。

SELECT
  sale_date,
  amount,
  CASE
    WHEN ROW_NUMBER() OVER (ORDER BY sale_date) >= 3
      THEN AVG(amount) OVER (
             ORDER BY sale_date
             ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
           )
  END AS moving_avg_3d
FROM daily_sales
ORDER BY sale_date;

ウィンドウ関数は WHERE・HAVING で直接使えない
累積和を絞り込み条件に使いたい場合、WHERE や HAVING にウィンドウ関数を直接書くとエラーになります。CTE でラップしてから WHERE を使います。

-- 累積売上が 300,000 を超えた最初の日を取る
WITH t AS (
  SELECT
    sale_date,
    amount,
    SUM(amount) OVER (
      ORDER BY sale_date
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_amount
  FROM daily_sales
)
SELECT * FROM t
WHERE cumulative_amount > 300000
ORDER BY sale_date
LIMIT 1;

ORDER BY が一意でないと行の並びが不定になる
ROWS を使うとき、ORDER BY の値が重複すると行の物理的な並びが不定です。日付が重複する可能性があるなら、主キーなど一意なカラムを ORDER BY に追加して並びを安定させましょう。

まとめ

目的	フレーム指定の例
累積和	`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`
N日移動平均	`ROWS BETWEEN N-1 PRECEDING AND CURRENT ROW`
グループ別	上記に `PARTITION BY group_col` を追加