用 ROWS 和 RANGE 來設定 window frame

當你在用 window function 的時候，會有個問題：「這個 window 裡到底有幾行會被拿來算目前這一行的值？」這個答案就看 window frame 怎麼設。

Window frame 就是 window function 在計算結果時會用到的那個行範圍。這個範圍是根據目前這一行，還有你用 ROWS 或 RANGE 設的其他條件來決定的。

簡單舉個例子：你在算累積總和的時候，可以指定：

• 只算目前這一行。
• 算目前這一行跟上面所有行。
• 算目前這一行跟上面/下面固定幾行。

就是 ROWS 跟 RANGE 會決定哪些行會進到 window frame 裡。

怎麼用 ROWS

ROWS 是根據 實際行的位置 來決定 window frame。意思就是它 會照順序從上到下數行，不管那些行裡面的值是什麼。

語法

window_function OVER (
    ORDER BY 欄位
    ROWS BETWEEN 開始 AND 結束
)

重點語法：

• CURRENT ROW — 目前這一行。
• 數字 PRECEDING — 目前這一行上面幾行。
• 數字 FOLLOWING — 目前這一行下面幾行。
• UNBOUNDED PRECEDING — 從 window 開頭開始。
• UNBOUNDED FOLLOWING — 到 window 結尾。

範例：算目前這一行跟前兩行的累積總和

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 就是：抓目前這一行跟上面兩行。
• 累積總和只會算這三行。

結果：

範例：用固定行數做「滑動 window」分析

任務： 算目前這一行跟下面兩行的平均薪水。

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 是根據值來決定 window frame，不是看行的位置。意思就是，只要 ORDER BY 欄位的值有在你設的範圍內，那行就會被包含進來。

語法

window_function 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 會把 重複的值 都算進來。

實際任務範例

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 會把所有重複值都算進來，這會讓結果差很多。