集計範囲を細かく指定 - ウィンドウフレーム (ROWS / RANGE)

OVER の中の「どこからどこまでを集計するか」を決めるフレーム。デフォルトの落とし穴に注意

概念図

構文

sql

OVER ( ORDER BY ... ROWS | RANGE BETWEEN <start> AND <end> )

サンプル

直近 7 日間のローリング合計（今日を含む過去 6 行 + 今日）

sql

SELECT
  sales_date,
  revenue,
  SUM(revenue) OVER (
    ORDER BY sales_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7d_sum
FROM daily_sales;

ROWS と RANGE の違い

ROWS: 物理的な行数で範囲を指定する。ROWS BETWEEN 6 PRECEDING AND CURRENT ROW は「前 6 行 + 現在行」。順序列の値がどうであれ 7 行
RANGE: ORDER BY の値で範囲を指定する。同じ順序値の行はまとめて扱われる

日付ベースの移動平均で「日が飛び飛びでも直近 7 日」と言いたいなら、時系列を詰めて ROWS で書くか、日付型に対応した RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW（PostgreSQL）を使います。

フレーム指定の書式

境界の指定は次のいずれか。

UNBOUNDED PRECEDING — パーティションの先頭から
N PRECEDING — N 行（または N 単位）前から
CURRENT ROW — 現在行
N FOLLOWING — N 行先まで
UNBOUNDED FOLLOWING — パーティションの末尾まで

よく使う組み合わせ:

累計: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
N 日移動平均: ROWS BETWEEN N-1 PRECEDING AND CURRENT ROW
中央移動平均: ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING

デフォルトフレームの落とし穴

ORDER BY を書いてフレームを省略すると、標準 SQL のデフォルトは RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW です。累計のつもりで書いても、同じ順序値の行（タイ）は全部まとめて同じ値になるので注意。

「日ごとに一意な行ならいいが、同じ日に複数注文があるテーブルで累計がおかしい」とハマりやすいポイントです。ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW と明示的に書くのが安全です。

sql

-- 注意: ORDER BY のみだとタイが同じ値に潰れる
SUM(x) OVER (ORDER BY d)
-- ↑ 実質 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- 意図が明確で安全
SUM(x) OVER (ORDER BY d
             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

RDBMS の差分

PostgreSQL 11+: ROWS / RANGE / GROUPS、INTERVAL ベースの RANGE すべて対応
SQL Server: ROWS / RANGE 対応。INTERVAL 数値指定の RANGE は限定的
MySQL 8.0: ROWS / RANGE 対応。INTERVAL ベースの RANGE は非対応（数値列で代用）
SQLite 3.28+: ROWS / RANGE / EXCLUDE も対応