SQL練習室
← SQLガイド

SQLのウィンドウフレーム(ROWS BETWEEN)とは?累計・移動平均・RANGEとの違いを解説

ウィンドウフレームは、各行の計算に「どの行までを含めるか」を決める範囲です。ROWS BETWEEN 2 PRECEDING AND CURRENT ROWのように書くと、直近3行の移動平均や移動合計になります。既定の範囲、ROWSとRANGEの違い、LAST_VALUEの落とし穴を、同じ売上データで確認します。

例で使う表

daily_salesの1行は1件の売上で、sale_dayは営業日の番号です。id 3と4は同じ3日目の売上で、4日目と5日目は営業がなく、次は6日目です。同じ日付の行と日付が飛ぶ行があるので、ROWSとRANGEの違いが結果に出ます。各例のORDER BYは、結果表の表示順を決めるために付けています。

daily_sales:日別の売上6件(amountは万円)
idsale_dayamount
1110
2230
3320
4340
5660
6750

ウィンドウフレーム(ROWS BETWEEN)の基本構文

sql
SELECT id, sale_day, amount,
  SUM(amount) OVER (
    ORDER BY id
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  )
FROM daily_sales;

ORDER BYだけを書いたときの既定フレーム

OVERにORDER BYがあってフレームを書かないと、既定は「先頭の行から、現在行と同じ並び順の値を持つ行(ピア)の最後まで」です。SUM(amount) OVER (ORDER BY sale_day)は累計になりますが、3日目のid 3とid 4は同じ並び順の値なので、どちらも10+30+20+40=100と同じ値になります。ORDER BYを付けない場合は全行がフレームに入り、全体の合計になります。

sql
SELECT id, sale_day, amount,
  SUM(amount) OVER (ORDER BY sale_day) AS running_total
FROM daily_sales
ORDER BY id;
実行結果
idsale_dayamountrunning_total
111010
223040
3320100
4340100
5660160
6750210

ROWSとRANGEの違い:同値の行を1行ずつ数えるか

ROWSは物理的な行の数で範囲を決め、RANGEは並び順の値で決めます。同じ3日目のid 3とid 4は、RANGE(既定)ではまとめて100ですが、ROWSでは1行ずつ積み上がって60と100になります。ROWSで同日の順番を安定させるには、ORDER BYにidのような一意の列を加えます。順序が一意でないと、同値の行の順番が実行ごとに変わり得ます。

sql
SELECT id, sale_day, amount,
  SUM(amount) OVER (
    ORDER BY sale_day
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS range_total,
  SUM(amount) OVER (
    ORDER BY sale_day, id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS rows_total
FROM daily_sales
ORDER BY id;
実行結果
idsale_dayamountrange_totalrows_total
11101010
22304040
332010060
4340100100
5660160160
6750210210

n PRECEDINGで直近の行だけの移動平均を作る

ROWS BETWEEN 2 PRECEDING AND CURRENT ROWは、現在行と、その前の2行の最大3行を計算に使います。AVGなら3行移動平均、SUMなら移動合計です。先頭付近は前の行が足りず、1行目は1行、2行目は2行だけの平均になります。件数も一緒に見ると、何行で計算した値かが分かります。

sql
SELECT id, amount,
  AVG(amount) OVER (
    ORDER BY id
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg,
  COUNT(*) OVER (
    ORDER BY id
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS frame_rows
FROM daily_sales
ORDER BY id;
実行結果
idamountmoving_avgframe_rows
110101
230202
320203
440303
560403
650503

UNBOUNDED FOLLOWINGで現在行から最後までの合計を出す

フレームの終わりをUNBOUNDED FOLLOWINGにすると、パーティションの最後の行までを含められます。ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWINGなら「その行から最後までの残り合計」で、全体の210から順に減ります。開始側にUNBOUNDED FOLLOWING、終了側にUNBOUNDED PRECEDINGは書けません。

sql
SELECT id, amount,
  SUM(amount) OVER (
    ORDER BY id
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  ) AS remaining
FROM daily_sales
ORDER BY id;
実行結果
idamountremaining
110210
230200
320170
440150
560110
65050

LAST_VALUEが期待どおりにならない理由

LAST_VALUEはフレームの最後の行の値を返します。既定のフレームは現在行までなので、LAST_VALUE(amount) OVER (ORDER BY id)は常に現在行自身の値になり、「最後の売上」にはなりません。パーティション全体の最後を取るには、ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGでフレームを広げます。FIRST_VALUEは既定のフレームでも先頭を返すので問題になりません。

sql
SELECT id, amount,
  LAST_VALUE(amount) OVER (ORDER BY id) AS default_last,
  LAST_VALUE(amount) OVER (
    ORDER BY id
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS whole_last
FROM daily_sales
ORDER BY id;
実行結果
idamountdefault_lastwhole_last
1101050
2303050
3202050
4404050
5606050
6505050

日付が飛ぶとき:ROWSとRANGEのn PRECEDINGの違い

RANGE BETWEEN 2 PRECEDING AND CURRENT ROWは、並び順の値(sale_day)が「現在の値-2」以上の行を含めます。つまり「直近2日間」の範囲です。ROWSの2 PRECEDINGは、日付に関係なく前の2行です。6日目のid 5は、RANGEでは4〜6日の60だけですが、ROWSでは前の2行を含めて120になります。「直近N行」か「直近N日」かで使い分けます。RANGEにオフセットを付けるときはORDER BYの列が1つだけで、数値や日時の列である必要があります。

sql
SELECT id, sale_day, amount,
  SUM(amount) OVER (
    ORDER BY sale_day
    RANGE BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS last_2_days,
  SUM(amount) OVER (
    ORDER BY sale_day, id
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS last_3_rows
FROM daily_sales
ORDER BY id;
実行結果
idsale_dayamountlast_2_dayslast_3_rows
11101010
22304040
332010060
434010090
566060120
6750110150

ウィンドウフレーム(ROWS BETWEEN)でよくある間違い

ORDER BYを付けて全体合計のつもりが累計になる

各行に全体の合計を添えたいとき、OVER (ORDER BY ...)と書くと既定のフレームが先頭から現在行(とそのピア)までになり、累計に変わります。全体の合計は、OVER ()かUNBOUNDED PRECEDING〜UNBOUNDED FOLLOWINGで求めます。

sql
SELECT id, amount,
  SUM(amount) OVER () AS total,
  SUM(amount) OVER (ORDER BY id) AS running_total
FROM daily_sales
ORDER BY id;

一意でないORDER BYでROWSを使う

ROWSの累計や移動平均で、ORDER BY sale_dayのように同値の行がある並びを使うと、同日の行の順番が保証されず、行ごとの結果が実行によって変わり得ます。idなどの一意の列を加えます。

sql
SELECT id, sale_day, amount,
  SUM(amount) OVER (
    ORDER BY sale_day
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS rows_total
FROM daily_sales
ORDER BY id;

実際に1問解いてみる

既定のフレーム

id、sale_day、amountに、sale_dayの順の累計を添えて、id順に取得してください。フレームは書かず、累計の列にはAS別名を付けずSUMの既定名を使います。同じ日の売上がどう扱われるかを確認します。

テーブル

読み込み中…

実行結果

関連構文

ウィンドウフレーム(ROWS BETWEEN)についてよくある質問

ウィンドウフレームの既定値は何ですか?
ORDER BYがあるときはRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWで、先頭行から現在行と同値の行の最後までです。ORDER BYがなければ、全行が現在行のピアになるので、パーティション全体がフレームです。
ROWS BETWEEN 2 PRECEDING AND CURRENT ROWは何行を使いますか?
現在行と、その前の2行の最大3行です。先頭付近では前の行が足りないので、1行目は1行、2行目は2行だけで計算します。3行そろった行だけを使いたいときは、サブクエリでCOUNTなどを併せて求めて絞り込みます。
RANGEとROWSはどちらを使うべきですか?
「直近N行」ならROWS、「直近N日」のように並び順の値の範囲なら、数値や日時のORDER BYに対するRANGEです。同値の行を同じ扱いにしたくない累計では、一意な列を加えたROWSを使います。

参考文献

内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。