ウィンドウ関数 ② — ORDER BY とフレーム（ROWS / RANGE）

ORDER BY 付きのウィンドウ — 累積和（running total）を出す

OVERの中にORDER BYを足すと、集計の対象が「ウィンドウの先頭から今の行まで」に変わり、行ごとに値が増えていく累積和（running total = 先頭からその行までの合計）を計算できます。

書き方はSUM(amount) OVER (PARTITION BY emp_id ORDER BY sale_date)です。

PARTITION BY emp_idで社員ごとにウィンドウを区切り、ORDER BY sale_dateでその社員の売上を日付順に並べます。そして各社員に対して累計を計算できます。

下の図で、emp_id=29 の 5 件の売上を日付順に見たとき、累計がどう増えていくかを確認してください。

-- ORDER BY なし: 社員ごとの「変わらない合計」が各行に付く
SELECT emp_id, sale_date, amount,
  SUM(amount) OVER (PARTITION BY emp_id) AS emp_total
FROM sales
ORDER BY emp_id, sale_date;

-- ORDER BY あり: 社員ごとの「先頭から今の行まで」の累計
SELECT emp_id, sale_date, amount,
  SUM(amount) OVER (PARTITION BY emp_id ORDER BY sale_date) AS running_total
FROM sales
ORDER BY emp_id, sale_date;

「社員ごとに、売上を日付順に並べて、その時点までの累計売上を出したい」という要件を想定します。（正しく実行できれば解説が表示されます）

① salesテーブルからemp_id、sale_date、amountの 3 列を取り出してください。

② SUM(amount)を社員ごとに区切り、日付順に並べたウィンドウで計算し、running_totalという別名で 4 列目に追加してください。

③ 判定は行の並び順を見るため、emp_idの昇順、同じ社員内はsale_dateの昇順で並べてください。

フレーム — ウィンドウ内で実際に集計される行の範囲

ウィンドウ関数でORDER BYを付けたとき、各行が集計する範囲をフレーム（frame）と呼びます。

フレームを明示しないと、既定でRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（ウィンドウの先頭から現在行まで）が適用されます。

前の演習で累計になったのは、この既定フレームが効いていたからです。

-- A: フレーム省略（既定 = RANGE UNBOUNDED PRECEDING 〜 CURRENT ROW）
SELECT emp_id, sale_date, amount,
  SUM(amount) OVER (PARTITION BY emp_id ORDER BY sale_date) AS run_default
FROM sales WHERE emp_id = 14
ORDER BY sale_date;

-- B: 既定フレームを明示的に書いたもの（A と同じ結果）
SELECT emp_id, sale_date, amount,
  SUM(amount) OVER (
    PARTITION BY emp_id ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS run_explicit
FROM sales WHERE emp_id = 14
ORDER BY sale_date;

フレームに使うキーワードは次の 5 つです。現在行を基準に「どこから / どこまで」を指定します。

「累積和の挙動を、フレームを明示的に書いて確認したい」という要件を想定します。

① salesテーブルからemp_id、sale_date、amountの 3 列を取り出してください。

② 社員ごとに区切り、sale_date順に並べたウィンドウに、フレームとしてROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWを明示したSUM(amount)を計算し、running_totalという別名で 4 列目に追加してください。

③ 判定は行の並び順を見るため、emp_idの昇順、同じ社員内はsale_dateの昇順で並べてください。

ROWS BETWEEN n PRECEDING — 移動合計・移動平均

フレームをROWS BETWEEN n PRECEDING AND CURRENT ROWにすると、集計対象が「手前 n 行 ＋ 現在行」に絞られます。

ROWS BETWEEN 2 PRECEDING AND CURRENT ROWなら、現在行を含めて直近 3 行が対象になり、SUMなら移動合計、AVGなら移動平均になります。

直近の傾向をならして見たいときの基本形です。

先頭付近は手前の行が足りないぶん、対象行数が少なくなります（1 行目は 1 行のみ、2 行目は 2 行、3 行目以降が 3 行）。

下の図で、emp_id=14 の 5 件を例に、各「現在行」のときフレームに何が入るかを 1 行ずつ確認してください。

-- 直近 3 件の移動平均（手前 2 行 + 現在行）
SELECT emp_id, sale_date, amount,
  AVG(amount) OVER (
    PARTITION BY emp_id ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg3
FROM sales WHERE emp_id = 2
ORDER BY sale_date;

-- 直近 2 件の移動合計（手前 1 行 + 現在行）も同じ書き方で作れる
SELECT emp_id, sale_date, amount,
  SUM(amount) OVER (
    PARTITION BY emp_id ORDER BY sale_date
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS moving_sum2
FROM sales WHERE emp_id = 2
ORDER BY sale_date;

「社員ごとに、売上を日付順に並べて、直近 3 件（手前 2 件と現在）の移動合計を見たい」という要件を想定します。

① salesテーブルからemp_id、sale_date、amountの 3 列を取り出してください。

② 社員ごとに区切り、sale_date順に並べたウィンドウに、フレームROWS BETWEEN 2 PRECEDING AND CURRENT ROWを指定したSUM(amount)を計算し、moving_sum3という別名で 4 列目に追加してください。

③ 判定は行の並び順を見るため、emp_idの昇順、同じ社員内はsale_dateの昇順で並べてください。

ROWS と RANGE の違い — 同じ値の行をどう扱うか

フレームにはROWSとRANGEの 2 つの数え方があります。

ROWSは物理的な行数で数えます（手前 2 行ならきっかり 2 行）。同点があっても 1 行ずつ別に数えるので、累計値が 1 行ごとに増えます。

RANGEはORDER BYの値が同じ行（同点）をひとまとめに扱います。同点の行はすべて同じフレームに入り、同じ累計値になります。

下の 2 つの図で、amount = 100000が 3 件あるとき、ROWS と RANGE がそれぞれどう動くかを確認してください。

-- amount を ORDER BY すると 100000 が 3 件あり ROWS と RANGE で差が出る
SELECT sale_id, amount,
  SUM(amount) OVER (ORDER BY amount ROWS  UNBOUNDED PRECEDING) AS rows_run,
  SUM(amount) OVER (ORDER BY amount RANGE UNBOUNDED PRECEDING) AS range_run
FROM sales
WHERE amount = 100000
ORDER BY sale_id;
-- ROWS は 1 行ずつ、RANGE は同点 100000 の 3 件を 1 まとめに数える

「同じ金額の売上が複数あるとき、ROWS と RANGE で累計がどう変わるかを 1 つの結果で比較したい」という要件を想定します。amount = 100000の売上は 3 件あります。

① salesテーブルからsale_id、amountを取り出してください。

② amountで並べたウィンドウで、フレームROWS UNBOUNDED PRECEDINGのSUM(amount)をrows_run、フレームRANGE UNBOUNDED PRECEDINGのSUM(amount)をrange_runという別名で追加してください。

③ amount = 100000の行だけに絞り込んでください。

④ 判定は行の並び順を見るため、sale_idの昇順で並べてください。