ROWS と RANGE でウィンドウフレームをカスタマイズする

ウィンドウ関数を使うとき、「今の行の値を計算するのに、ウィンドウの中で何行が使われるの？」って疑問が出てくるよね。その答えは ウィンドウフレーム によるんだ。

ウィンドウフレーム っていうのは、ウィンドウ関数の結果を計算するために使う行の範囲のこと。これは今の行を基準にして、さらに ROWS や RANGE で指定した追加条件で決まるよ。

簡単な例：累積合計を計算するとき、こんな指定ができる：

• 今の行だけを考慮する。
• 今の行と、それより上のすべての行を考慮する。
• 今の行と、上/下に決まった数の行を考慮する。

この「どの行がウィンドウフレームに入るか」をコントロールするのが ROWS と RANGE なんだ。

ROWS の使い方

ROWS は 物理的な行の並び順 でウィンドウフレームを決める。つまり、上から下に並んだ順番で行を数える ってこと。行の値は関係ないよ。

シンタックス

ウィンドウ関数 OVER (
    ORDER BY カラム
    ROWS BETWEEN 開始 AND 終了
)

主な表現：

• CURRENT ROW — 今の行。
• 数値 PRECEDING — 今の行より上にある指定した数の行。
• 数値 FOLLOWING — 今の行より下にある指定した数の行。
• UNBOUNDED PRECEDING — ウィンドウの最初から。
• UNBOUNDED FOLLOWING — ウィンドウの最後まで。

例：今の行と2つ前までの累積合計

SELECT
    employee_id,
    salary,
    SUM(salary) OVER (
        ORDER BY employee_id
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_sum
FROM employees;

説明：

• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW は「今の行と、その2つ上の行まで」を意味するよ。
• 累積合計はこの3行だけで計算される。

結果：

例：「スライディングウィンドウ」で決まった数の行を分析

やること：今の行と下2行の平均給与を計算する。

SELECT 
    employee_id,
    salary,
    AVG(salary) OVER (
        ORDER BY employee_id
        ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
    ) AS rolling_avg
FROM employees;

結果：

RANGE の使い方

RANGE は行の並びじゃなくて、値の範囲でウィンドウフレームを作る。つまり、ORDER BY で指定したカラムの値が、指定した範囲に入ってる行 がフレームに入るってこと。

シンタックス

ウィンドウ関数 OVER (
    ORDER BY カラム
    RANGE BETWEEN 開始 AND 終了
)

例：値の範囲で累積合計

やること：今の行の給与と±2000以内の行の累積合計を計算する。

SELECT 
    employee_id,
    salary,
    SUM(salary) OVER (
        ORDER BY salary
        RANGE BETWEEN 2000 PRECEDING AND 2000 FOLLOWING
    ) AS range_sum
FROM employees;

説明：

• RANGE BETWEEN 2000 PRECEDING AND 2000 FOLLOWING は「今の行の salary から±2000の範囲にある行」を意味するよ。

結果：

ROWS と RANGE の比較

• ROWS は実際の行数で動く。値には依存しない。
• RANGE は ORDER BY で指定したカラムの値の範囲で動く。

比べてみよう。 たとえば、sales ってテーブルがあって：

クエリを比較：

ROWS：

SELECT
    id,
    SUM(amount) OVER (
        ORDER BY amount
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_rows
FROM sales;

結果：

ここでは、各行が 実際に現れる順番 で合計に加算されていく。

RANGE：

SELECT 
    id,
    SUM(amount) OVER (
        ORDER BY amount
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_range
FROM sales;

結果：

ここでは 1 と 2 の行がまとめられてる。なぜなら amount = 100 で同じだから。RANGE は amount カラムの重複値 をまとめて扱うんだ。

実際のタスク例

1. 収入の増加を計算する

やること：前の行と比べて収入がどう変わったか計算する。

SELECT 
    month,
    revenue,
    revenue - LAG(revenue) OVER (
        ORDER BY month
    ) AS revenue_change
FROM sales_data;

2. 今の行とグループ平均の比較

やること：各部署ごとに、社員の給与と部署の平均給与の差を計算する。

SELECT 
    department_id,
    employee_id,
    salary,
    salary - AVG(salary) OVER (
        PARTITION BY department_id
    ) AS salary_diff
FROM employees;

ROWS と RANGE でよくあるミス

ORDER BY の指定ミス： ソート順を指定しないと、PostgreSQL はエラーを出すよ。今の行がどれか分からなくなるから。

ROWS と RANGE を混ぜて使う： データに合わせてどっちか選ぼう。ROWS は決まった行数のタスク向き、RANGE は値の範囲で分析したいときに使う。

RANGE で重複値を見落とす： RANGE は重複値も全部含めるから、結果が大きく変わることもあるよ。気をつけて！