PostgreSQL Window Function窗口函数的排序与帧范围

掌握 ORDER BY 与帧范围子句是精准控制窗口函数计算逻辑的关键。本文将通过可直接运行的示例，拆解这两个核心组件的用法。

第一部分：理解窗口函数与基础语法

窗口函数对一组相关行（称为“窗口”）执行计算，并为结果集中的每一行返回一个值。它与普通聚合函数的核心区别在于不折叠行，而是将计算结果附加到每一行上。

一个完整的窗口函数语法如下：

函数名() OVER (
  [PARTITION BY 分区列]
  [ORDER BY 排序列 [ASC | DESC] [NULLS FIRST | LAST]]
  [ROWS | RANGE 帧范围定义]
)

• PARTITION BY：将数据划分为不同的分区，窗口函数在每个分区内独立计算。
• ORDER BY：定义分区内的排序，这是理解帧范围的基础。
• 帧范围 (Frame)：ORDER BY 定义了排序顺序后，ROWS 或 RANGE 子句精确指定每一行的“窗口”包含哪些相邻行。

创建一个演示数据表 sales：

CREATE TABLE sales (
    sale_date DATE,
    product_id INT,
    amount NUMERIC(10, 2),
    region VARCHAR(50)
);

INSERT INTO sales VALUES
('2023-01-01', 1, 100.00, 'East'),
('2023-01-02', 1, 150.00, 'East'),
('2023-01-03', 1, 120.00, 'East'),
('2023-01-04', 1, 180.00, 'East'),
('2023-01-01', 2, 200.00, 'West'),
('2023-01-02', 2, 210.00, 'West'),
('2023-01-03', 2, 190.00, 'West'),
('2023-01-04', 2, 220.00, 'West');

第二部分：ORDER BY 子句的决定性作用

当窗口函数中包含 ORDER BY 子句时，它会带来两个根本性变化：

1. 为聚合/排名函数引入顺序：函数如 ROW_NUMBER(), RANK(), SUM() 等，会依据排序顺序计算。
2. 隐式地定义默认帧范围：如果没有显式指定 ROWS 或 RANGE，系统会使用一个默认帧：RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

示例：计算每个区域按日期排序的累积销售额。

SELECT
    region,
    sale_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY region
        ORDER BY sale_date
    ) AS running_total
FROM sales
ORDER BY region, sale_date;

结果集中，running_total 是每个区域内，从第一笔销售日期到当前行日期（按日期排序）所有 amount 的累加。这里 ORDER BY sale_date 不仅排序，还决定了 SUM 的计算范围。

示例：为每个区域的销售额分配行号。

SELECT
    region,
    sale_date,
    amount,
    ROW_NUMBER() OVER (
        PARTITION BY region
        ORDER BY amount DESC
    ) AS rank_in_region
FROM sales;

ROW_NUMBER() 严格按照 ORDER BY amount DESC 降序分配唯一的序号。如果使用 RANK()，并列排名会占用名次，而 DENSE_RANK() 则不会。

第三部分：显式定义帧范围 (ROWS vs RANGE)

ROWS 和 RANGE 帧范围子句在存在 ORDER BY 时生效，它们定义了用于当前行计算的物理行集合。

• ROWS：基于物理行的偏移。
• RANGE：基于排序列的逻辑值范围。

两者都使用如下语法结构定义起点和终点：
{ ROWS | RANGE } { frame_start | BETWEEN frame_start AND frame_end }

其中 frame_start 和 frame_end 可以是：

• UNBOUNDED PRECEDING：分区第一行。
• N PRECEDING：当前行之前的第 N 行（仅 ROWS）。
• CURRENT ROW：当前行。
• N FOLLOWING：当前行之后的第 N 行（仅 ROWS）。
• UNBOUNDED FOLLOWING：分区最后一行。

1. ROWS 帧：基于物理行偏移

ROWS 子句精确地计数行来确定窗口成员。

示例：计算每个区域内当前行与前两行（共3行）的移动平均销售额。

SELECT
    region,
    sale_date,
    amount,
    AVG(amount) OVER (
        PARTITION BY region
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3
FROM sales
ORDER BY region, sale_date;

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 明确地表示：对于每一行，取当前行、前一行、再前一行（共3行，如果存在的话）计算平均值。

示例：计算从分区开始到当前行的累计和，等同于默认帧。

SUM(amount) OVER (PARTITION BY region ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum_rows;

2. RANGE 帧：基于逻辑值范围

RANGE 子句根据 ORDER BY 列的值来确定窗口成员。它会包含所有 值在指定范围内的行，即使物理行号不同。

示例：比较 ROWS 与 RANGE。先看一个具有重复 ORDER BY 列值的数据。

INSERT INTO sales VALUES
('2023-01-05', 1, 180.00, 'East'), -- 金额与 1月4日相同
('2023-01-06', 1, 180.00, 'East');

使用 ROWS：

SELECT
    sale_date,
    amount,
    SUM(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
    ) AS sum_rows
FROM sales
WHERE region = 'East' AND product_id = 1
ORDER BY sale_date;

ROWS 严格按物理行计算：对于1月5日（金额180），它与前一行（1月4日，金额180）求和，结果为360。对于1月6日，它与前一行（1月5日，金额180）求和，结果也是360。

使用 RANGE：

SELECT
    sale_date,
    amount,
    SUM(amount) OVER (
        ORDER BY sale_date
        RANGE BETWEEN INTERVAL '1 DAY' PRECEDING AND CURRENT ROW
    ) AS sum_range
FROM sales
WHERE region = 'East' AND product_id = 1
ORDER BY sale_date;

RANGE 按值范围计算。ORDER BY sale_date 列的值是日期。RANGE BETWEEN INTERVAL '1 DAY' PRECEDING AND CURRENT ROW 表示包含 sale_date 在 [当前日期 - 1天, 当前日期] 之间的所有行。

对于1月5日，窗口包含1月4日和1月5日的所有行（两行），所以总和是180+180=360。对于1月6日，窗口包含1月5日和1月6日的所有行（也是两行），总和也是180+180=360。虽然结果相同，但计算逻辑不同：RANGE 会将日期相同的所有行作为一个逻辑单元包含进来。

当 ORDER BY 列无重复值时，ROWS 与 RANGE 的默认行为 (UNBOUNDED PRECEDING TO CURRENT ROW) 结果相同。

第四部分：核心应用场景与示例

场景一：计算移动平均线

需求：计算每个产品最近3天的移动平均销售额。

SELECT
    product_id,
    sale_date,
    amount,
    AVG(amount) OVER (
        PARTITION BY product_id
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS ma_3day
FROM sales
ORDER BY product_id, sale_date;

关键点：使用 ROWS 进行物理行计数，2 PRECEDING 固定指向前两行。

场景二：计算累计百分比

需求：计算每个区域每日销售额占该区域总销售额的累计百分比。

SELECT
    region,
    sale_date,
    amount,
    SUM(amount) OVER w AS cumulative_sum,
    SUM(amount) OVER w * 100.0 / SUM(amount) OVER (PARTITION BY region) AS cumulative_pct
FROM sales
WINDOW w AS (PARTITION BY region ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
ORDER BY region, sale_date;

关键点：使用 WINDOW 子句定义窗口 w，在 OVER w 中复用，使查询更清晰。ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 明确指定从第一行累加到当前行。

场景三：识别前后相邻值

需求：查看每个区域销售额的前一个值和下一个值。

SELECT
    region,
    sale_date,
    amount,
    LAG(amount, 1) OVER (PARTITION BY region ORDER BY sale_date) AS prev_amount,
    LEAD(amount, 1) OVER (PARTITION BY region ORDER BY sale_date) AS next_amount
FROM sales
ORDER BY region, sale_date;

关键点：LAG 和 LEAD 是偏移函数，它们隐式地依赖 ORDER BY 来确定“前一个”和“下一个”。它们不显式地使用帧范围子句，但排序是必须的。

场景四：计算基于日期范围的滚动总和（使用 RANGE）

需求：计算每个产品，当前销售日前后3天（包含当前日）的销售总额。

SELECT
    product_id,
    sale_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY product_id
        ORDER BY sale_date
        RANGE BETWEEN INTERVAL '3 DAYS' PRECEDING AND INTERVAL '3 DAYS' FOLLOWING
    ) AS sum_7day_range
FROM sales
ORDER BY product_id, sale_date;

关键点：RANGE 与日期/时间类型或数值类型配合良好，这里基于 sale_date 列的逻辑值（日期）定义了一个7天的滑动窗口。

第五部分：关键总结与注意事项

1. ORDER BY 是帧的引擎：没有 ORDER BY，ROWS 或 RANGE 子句没有意义，也会导致语法错误。默认帧 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 也仅在存在 ORDER BY 时生效。

2. ROWS vs RANGE 的选择：

   • 需要基于物理行位置（如前3行、后5行）计算时，使用 ROWS。
   • 需要基于排序列的值（如前3天、金额差值在100以内）计算时，使用 RANGE。
   • 当 ORDER BY 列是唯一时，两者结果可能相同，但 ROWS 性能通常更好。

3. 默认帧的陷阱：如果只写了 ORDER BY 而没写帧子句，函数（如 SUM）计算的是从第一行到当前行的累积值。如果你想计算整个分区的总和，不要在聚合函数的 OVER 子句中加入 ORDER BY。

   -- 计算整个分区的总和（正确）
   SUM(amount) OVER (PARTITION BY region) AS region_total;
   
   -- 计算累积和（隐含默认帧）
   SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS running_total;

4. 性能考量：窗口函数，特别是带有复杂 ORDER BY 和帧范围的，可能消耗较多资源。确保在 ORDER BY 的列上有合适的索引。