Day10 前半のゴール
「“行ごとにグラフの線を描く”イメージを持つ」
Day10 はウィンドウ関数の第2弾、「累積」と「移動平均」です。
どちらも「時間の流れに沿って、値がどう変化していくか」を見るときにめちゃくちゃ役立ちます。
前半のゴールはこうです。SUM(...) OVER (...) を使って「累積（積み上げ）」を出すイメージを持てる。
「ウィンドウフレーム（どこからどこまでを足すか）」という考え方をざっくり理解できる。
移動平均が「“直近N日分だけ”の平均」で、その範囲をSQLで指定できることをイメージできる。
ここではまず、「累積」と「移動平均」の“見た目の意味”をしっかり掴みにいきます。
累積とは何か
「その日までの合計を、毎行にくっつける」
まずは「累積」のイメージからいきます。
日別売上テーブルを想像してください。
day | amount
-----------+--------
2024-01-01 | 100
2024-01-02 | 200
2024-01-03 | 150
2024-01-04 | 300
ここで、「その日までの累積売上」を出したいとします。
手で計算するとこうなります。
2024-01-01 の累積：100
2024-01-02 の累積：100 + 200 = 300
2024-01-03 の累積：100 + 200 + 150 = 450
2024-01-04 の累積：100 + 200 + 150 + 300 = 750
これをSQLでやるのが、「累積のウィンドウ関数」です。
PostgreSQLでは、こう書けます。
SELECT
day,
amount,
SUM(amount) OVER (
ORDER BY day
) AS cumulative_amount
FROM daily_sales
ORDER BY day;
SUM(amount) OVER (ORDER BY day) がポイントです。
「day の昇順に並べたときに、“そこまでの行”の amount を全部足す」という意味になります。
結果はこういうイメージです。
day | amount | cumulative_amount
-----------+--------+------------------
2024-01-01 | 100 | 100
2024-01-02 | 200 | 300
2024-01-03 | 150 | 450
2024-01-04 | 300 | 750
「元の行を残したまま、“その日までの合計”という列を後付けしている」感じです。
これが、累積の基本形です。
ウィンドウフレームの考え方
「“どこからどこまでを足すか”を、SQLで指定できる」
さっきの SUM(amount) OVER (ORDER BY day) は、実は省略形です。
PostgreSQLは、内部的にはこう解釈しています。
SUM(amount) OVER (
ORDER BY day
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
ここで出てきたのが、「ウィンドウフレーム」です。
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
＝「一番最初の行（制限なしの先頭）から、今の行まで」
つまり、「累積」の正体は、「先頭から現在行までの範囲を足している」だけなんです。
この「どこからどこまでを足すか」を変えると、「累積」ではなく「移動平均」など、別の計算ができます。
Day10 前半では、「累積＝先頭から現在行まで」というフレームをまずしっかり押さえます。
グループごとの累積
「ユーザーごと・カテゴリごとに積み上げる」
累積は、「全体」だけでなく「グループごと」にもよく使います。
例えば、「ユーザーごとに、購入金額の累積を出したい」というケースです。
注文テーブル orders があるとします。
user_id | created_at | amount
--------+-------------+-------
1 | 2024-01-01 | 100
1 | 2024-01-05 | 200
2 | 2024-01-02 | 300
1 | 2024-01-10 | 150
2 | 2024-01-03 | 100
ユーザーごとに、「そのユーザーの累積購入金額」を出したいときは、こう書きます。
SELECT
user_id,
created_at,
amount,
SUM(amount) OVER (
PARTITION BY user_id
ORDER BY created_at
) AS cumulative_amount
FROM orders
ORDER BY user_id, created_at;
ここでのポイントは、
PARTITION BY user_id で「ユーザーごとにグループ分け」ORDER BY created_at で「そのユーザーの注文を古い順に並べる」
という2つを組み合わせていることです。
結果はこういうイメージになります。
ユーザー1
2024-01-01: 100 → 累積 100
2024-01-05: 200 → 累積 300
2024-01-10: 150 → 累積 450
ユーザー2
2024-01-02: 300 → 累積 300
2024-01-03: 100 → 累積 400
「PARTITION BY を付けると、“そのグループの中だけで累積する”」という感覚を、ここでしっかり掴んでおきましょう。
移動平均とは何か
「“直近N日だけ”の平均を、毎行にくっつける」
次に、「移動平均」のイメージに進みます。
また日別売上を例にします。
day | amount
-----------+--------
2024-01-01 | 100
2024-01-02 | 200
2024-01-03 | 150
2024-01-04 | 300
2024-01-05 | 250
ここで、「3日移動平均」を考えます。
「その日を含む直近3日間の平均」です。
手で計算するとこうなります。
2024-01-01 の3日移動平均：100（まだ1日分しかないので、そのまま）
2024-01-02 の3日移動平均：(100 + 200) / 2 = 150
2024-01-03 の3日移動平均：(100 + 200 + 150) / 3 = 150
2024-01-04 の3日移動平均：(200 + 150 + 300) / 3 = 216.66…
2024-01-05 の3日移動平均：(150 + 300 + 250) / 3 = 233.33…
これをSQLでやるには、「ウィンドウフレーム」を少し工夫します。
SELECT
day,
amount,
AVG(amount) OVER (
ORDER BY day
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3
FROM daily_sales
ORDER BY day;
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW がポイントです。
「現在行から見て、2行前〜現在行まで」
＝「最大3行分（足りなければその分だけ）」
つまり、「直近3行の平均」を取っています。
これが「3日移動平均」の正体です。
累積と移動平均の“フレームの違い”を整理する
「先頭から今まで」か、「今を中心に直近N行」か
ここまでの話を、ウィンドウフレームの観点でまとめます。
累積（先頭から現在行まで）
SUM(amount) OVER (
ORDER BY day
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
移動平均（直近3行）
AVG(amount) OVER (
ORDER BY day
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)
どちらも「ORDER BY day」で時間順に並べているのは同じです。
違うのは、「どこからどこまでを対象にするか」です。
累積：一番最初から今まで全部
移動平均：今から見て、N行前〜今まで
この「ウィンドウフレームをどう切るか」が、累積・移動平均・その他の“時間系の計算”を分けるカギになります。
Day10 前半では、「累積＝UNBOUNDED PRECEDING〜CURRENT ROW」「移動平均＝N PRECEDING〜CURRENT ROW」という2つのパターンを、まずは感覚で覚えておいてください。
Day10 前半のまとめ
累積は、「時間順に並べたときに、先頭から現在行までの合計を毎行にくっつける」計算であり、SUM(amount) OVER (ORDER BY day)（＝内部的には ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）の形で、「その日までの累積売上」や「ユーザーごとの累積購入額」（PARTITION BY user_id ORDER BY created_at）を簡単に出せる。
移動平均は、「現在行から見て直近N行だけを対象にした平均」であり、AVG(amount) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) のようにウィンドウフレームを「2行前〜現在行」と指定することで、「3日移動平均」のような“滑らかな線”を行ごとに計算できる。
どちらも「元の行を残したまま、“グラフの線を描くための値”を列として後付けする」イメージで、Day10 前半では「累積＝先頭から今まで」「移動平均＝今から見た直近N行」という2つのフレームの違いをしっかり掴むところまでをゴールにする。