ウィンドウ関数 — 累計と前後の行

集計関数に OVER を付ける

前のレッスンでは 順位を付ける関数を見ました。 実は SUM / AVG / COUNT などの

集計関数にも OVER を付けられます。 付けた瞬間、 行をまとめずに

「まわりの行を見た集計値」 を各行に並べてくれます。

-- カテゴリごとの平均を、各商品の行に並べる (行は減らない)
SELECT name, category, price,
       AVG(price) OVER (PARTITION BY category) AS cat_avg
FROM products;

GROUP BY だと 3 行になりますが、 これは 10 行のままです。 だから

「自分の値と、 グループの平均との差」 をその場で計算できます。

SELECT name, category, price,
       price - AVG(price) OVER (PARTITION BY category) AS diff
FROM products;

累計 (running total) を出す

OVER の中に ORDER BY を書くと、 集計する範囲が 「先頭から今の行まで」 に変わります。

これが累計です。

SELECT id, order_date, quantity,
       SUM(quantity) OVER (
         ORDER BY order_date, id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM orders;

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW が

「一番上の行から、 今の行まで」 という範囲の指定です。 これを フレーム と呼びます。

ORDER BY を書けば このフレームが既定になるので 省略しても累計になりますが、

同じ値が並ぶときの挙動が変わるため、 明示的に書くほうが安全 です。

並び順が一意に決まるよう ORDER BY order_date, id のように

同着を崩す列を足しておくのもポイントです。

LAG / LEAD — 前後の行の値を持ってくる

SELECT name, price,
       LAG(price)  OVER (ORDER BY price DESC) AS prev_price,
       LEAD(price) OVER (ORDER BY price DESC) AS next_price
FROM products;

- LAG … 1 つ前の行の値 (先頭の行は NULL)

- LEAD … 1 つ後の行の値 (最後の行は NULL)

「前月比」 「前回注文からの間隔」 のような 行と行の差 は、 これがないと

自己結合を書くことになり ぐっと面倒になります。

第 2 引数で 何行ずらすか、 第 3 引数で NULL の代わりに何を返すかも指定できます。

LAG(price, 1, 0) OVER (ORDER BY price DESC)  -- 1 つ前、無ければ 0

全体に対する割合

PARTITION BY した合計で割れば 構成比が出せます。

SELECT name, category, price,
       ROUND(price * 100.0 / SUM(price) OVER (PARTITION BY category), 1) AS pct
FROM products;

100.0 と小数で書いているのは、 整数どうしの割り算だと 小数が切り捨てられるためです。

よくあるつまずき

- 累計のつもりが全体合計になる — OVER の中に ORDER BY が無いと、

範囲がパーティション全体になります。 累計には ORDER BY が必須です。

- 並び順が毎回変わる — OVER の ORDER BY が同着だらけだと累計の順番が

安定しません。 id など一意な列を足します。

- LAG の結果が NULL で計算が消える — 先頭行は必ず NULL です。

差を取るなら COALESCE か LAG の第 3 引数で埋めます。

実務での使いどころ

売上の累計推移、 在庫の増減の追跡、 前月比・前年比、 ランキングの変動 —

時系列を扱うレポートは ほぼこの 2 つ (累計 と LAG) で組み立てられます。