用 ROWS 和 RANGE 设置窗口帧

用窗口函数的时候，你肯定会想：“窗口里到底有多少行会参与当前行的计算？” 这个问题的答案其实取决于 窗口帧。

窗口帧 就是用来计算窗口函数结果的那一段行的范围。这个范围是基于当前行再加上通过 ROWS 或 RANGE 指定的额外条件来定的。

举个简单例子：算累计和的时候，你可以指定：

• 只算当前这一行。
• 算当前行和上面所有行。
• 算当前行和上/下固定数量的行。

就是 ROWS 和 RANGE 决定了哪些行会进窗口帧。

怎么用 ROWS

ROWS 是按 物理行位置 来定窗口帧的。也就是说，它 就是按顺序一行一行往下数，不管这些行的值是多少。

语法

窗口函数 OVER (
    ORDER BY 列名
    ROWS BETWEEN 开始 AND 结束
)

关键表达式：

• CURRENT ROW — 当前行。
• 数字 PRECEDING — 当前行上面指定数量的行。
• 数字 FOLLOWING — 当前行下面指定数量的行。
• UNBOUNDED PRECEDING — 从窗口开头开始。
• UNBOUNDED FOLLOWING — 到窗口结尾为止。

例子：当前行和前两行的累计和

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 意思是：拿当前行和上面两行。
• 累计和只会算这三行。

结果：

例子：固定行数的“滑动窗口”分析

任务：算当前行和后面两行的平均工资。

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 会把 重复值 都算进来。

实际任务例子

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 会把所有重复值都算进去，这可能会让结果和你想的不一样。