PostgreSQL 窗口函数 RANGE 和 ROWS 框架深度解析与性能对比

PostgreSQL 窗口函数 RANGE 和 ROWS 框架深度解析与性能对比

大家好，我是你们的硬核技术宅“码农老炮儿”。今天咱们来聊聊 PostgreSQL 窗口函数中一个容易让人迷惑的点：RANGE 和 ROWS 这两种窗口框架的区别。相信很多 PostgreSQL 开发者，尤其是对窗口函数有深入需求的兄弟们，都曾在这个问题上纠结过。别担心，今天老炮儿就带你彻底搞懂它们，并用实际的性能测试数据说话，让你在实际应用中游刃有余。

1. 窗口函数基础回顾

在深入 RANGE 和 ROWS 之前，咱们先简单回顾一下 PostgreSQL 窗口函数的基本概念。窗口函数允许我们对查询结果集中的“窗口”进行计算，这个“窗口”是由 OVER() 子句定义的。OVER() 子句中可以包含以下几个部分：

• PARTITION BY：将结果集按照指定列进行分区，类似于 GROUP BY，但不会将多行合并成一行。
• ORDER BY：指定分区内的排序规则，这是窗口函数的关键，因为窗口框架是基于排序的。
• 窗口框架：定义当前行参与计算的相邻行的范围，也就是“窗口”的具体大小。这就是我们今天要重点讨论的 RANGE 和 ROWS。

一个典型的窗口函数如下所示：

SELECT
    column1,
    column2,
    aggregate_function(column3) OVER (
        PARTITION BY column1
        ORDER BY column2
        frame_clause
    ) AS window_function_result
FROM
    table_name;

其中 aggregate_function 可以是任何聚合函数（如 SUM, AVG, RANK, ROW_NUMBER 等），frame_clause 就是我们今天要讲的窗口框架。

2. ROWS 框架：基于物理行

ROWS 框架是基于物理行的。它根据 ORDER BY 子句定义的排序，选取当前行之前或之后的指定数量的行作为窗口。常用的 ROWS 框架子句有：

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：从分区的第一行到当前行。
• ROWS BETWEEN n PRECEDING AND CURRENT ROW：从当前行之前的 n 行到当前行。
• ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING：从当前行到分区的最后一行。
• ROWS BETWEEN CURRENT ROW AND n FOLLOWING：从当前行到当前行之后的 n 行。
• ROWS BETWEEN n PRECEDING AND m FOLLOWING: 从之前的n行到之后的m行。

ROWS 框架的关键在于，它只关心物理行的数量，不考虑行中具体的值。 无论ORDER BY排序列的值是多少。它仅仅根据当前行往前或者往后数n行。

举个例子：

假设我们有一个 sales 表，记录了每个销售员每天的销售额：

CREATE TABLE sales (
    salesperson VARCHAR(50),
    sale_date DATE,
    amount DECIMAL(10, 2)
);
 
INSERT INTO sales (salesperson, sale_date, amount) VALUES
('Alice', '2023-10-26', 100.00),
('Alice', '2023-10-27', 150.00),
('Alice', '2023-10-28', 200.00),
('Bob', '2023-10-26', 80.00),
('Bob', '2023-10-27', 120.00),
('Bob', '2023-10-28', 180.00);

我们想计算每个销售员每日销售额的3日移动平均（包括当天）：

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

结果如下：

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 表示窗口包含当前行和之前的两行，共三行。这就是ROWS的物理行概念。

3. RANGE 框架：基于逻辑值

RANGE 框架则不同，它是基于逻辑值的。它根据 ORDER BY 子句定义的排序，选取与当前行在排序列上具有相同值的所有行，以及满足指定偏移量的行作为窗口。常用的 RANGE 框架子句与 ROWS 类似：

• RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：从分区的第一行到当前行以及与当前行在排序列上具有相同值的所有行。
• RANGE BETWEEN n PRECEDING AND CURRENT ROW：从当前行之前值小于或等于当前行值减去 n 的行到当前行以及与当前行在排序列上具有相同值的所有行。这里的 n 是数值类型或时间间隔类型。
• RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING：从当前行以及与当前行在排序列上具有相同值的所有行到分区的最后一行。
• RANGE BETWEEN CURRENT ROW AND n FOLLOWING：从当前行以及与当前行在排序列上具有相同值的所有行到值大于或等于当前行值加上 n 的行。
• RANGE BETWEEN n PRECEDING AND m FOLLOWING: 含义以此类推。

RANGE 框架的关键在于，它会考虑排序列的值。CURRENT ROW 指的是所有与当前行在ORDER BY的列上有相同值的行组成的集合。如果ORDER BY的列不能区分行(存在重复值)，那么使用RANGE就可能会出现一些意想不到的结果。

继续上面的例子，稍微修改一下数据：

TRUNCATE TABLE sales;
 
INSERT INTO sales (salesperson, sale_date, amount) VALUES
('Alice', '2023-10-26', 100.00),
('Alice', '2023-10-27', 150.00),
('Alice', '2023-10-27', 180.00), -- 注意这里有两天是相同的日期
('Alice', '2023-10-28', 200.00),
('Bob', '2023-10-26', 80.00),
('Bob', '2023-10-27', 120.00),
('Bob', '2023-10-28', 180.00);

我们使用与之前相同的查询，但将 ROWS 换成 RANGE：

SELECT
    salesperson,
    sale_date,
    amount,
    AVG(amount) OVER (
        PARTITION BY salesperson
        ORDER BY sale_date
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS moving_average
FROM
    sales;

结果如下：

注意 Alice 在 2023-10-27 的两条记录，由于它们的 sale_date 相同，RANGE 框架将它们视为一个“对等组”（peer group），因此它们的 moving_average 相同，都是 (100 + 150 + 180) / 3 = 143.33。而2023-10-28, moving_average 为 (100+150+180+200)/4=157.50。

如果使用RANGE BETWEEN 2 PRECEDING AND CURRENT ROW，并且ORDER BY的列是日期。 2 PRECEDING指的是当前日期减去两天。例如当前日期是2024-10-28, 则2 PRECEDING为 2024-10-26。

4. RANGE 和 ROWS 的区别总结

应用场景举例：
ROWS: 计算过去7天的移动平均值，计算每个用户过去3次购买的平均金额。
RANGE: 计算所有订单金额大于等于当前订单金额的订单总数，统计工资在某个范围内的员工人数。

5. 性能对比测试

理论说了这么多，咱们来点实际的。我做了一个简单的性能对比测试，比较 RANGE 和 ROWS 在不同数据量和不同窗口大小下的性能差异。

测试环境：

• PostgreSQL 14
• 机器配置：普通笔记本（i7 CPU，16GB 内存）

测试数据：

创建了一张 test_data 表，包含 id（自增主键）、group_id（分组列）和 value（排序列）三个字段。分别插入了 10 万、100 万和 1000 万条数据。

CREATE TABLE test_data (
    id SERIAL PRIMARY KEY,
    group_id INTEGER,
    value INTEGER
);
 
-- 插入 100 万条数据
INSERT INTO test_data (group_id, value)
SELECT
    (random() * 10)::INTEGER,
    (random() * 1000)::INTEGER
FROM
    generate_series(1, 1000000);

测试查询：

分别使用 ROWS 和 RANGE 计算每个分组内 value 列的累计和，窗口大小分别为当前行之前的所有行、当前行之前的 10 行和当前行之前的 100 行。

-- ROWS
SELECT
    id,
    group_id,
    value,
    SUM(value) OVER (
        PARTITION BY group_id
        ORDER BY value
        ROWS frame_clause
    ) AS cumulative_sum
FROM
    test_data;
 
-- RANGE
SELECT
    id,
    group_id,
    value,
    SUM(value) OVER (
        PARTITION BY group_id
        ORDER BY value
        RANGE frame_clause
    ) AS cumulative_sum
FROM
    test_data;

其中 frame_clause 分别为：

• BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
• BETWEEN 10 PRECEDING AND CURRENT ROW
• BETWEEN 100 PRECEDING AND CURRENT ROW

测试结果：

测试结论：

• 在窗口较小的情况下（如 10 PRECEDING），ROWS 的性能明显优于 RANGE。这是因为 ROWS 只需处理固定数量的行，而 RANGE 需要进行值比较，开销更大。
• 当窗口较大时（如 UNBOUNDED PRECEDING），ROWS 和 RANGE 的性能差距缩小，但 ROWS 仍然略有优势。
• 随着数据量的增加，RANGE 的性能下降幅度比 ROWS 更大。尤其当ORDER BY的列存在很多重复值的时候，RANGE的性能会大幅下降。

性能优化建议：

• 如果业务场景允许，尽量使用 ROWS 替代 RANGE。
• 如果必须使用 RANGE，尽量避免在具有大量重复值的列上使用 ORDER BY。
• 对于 RANGE 窗口，可以考虑在 ORDER BY 列上创建索引，但这可能会影响写入性能。
• 对于大数据量，可以考虑将查询拆分成多个小查询，或者使用其他技术（如物化视图）来优化性能。

6. 总结与建议

RANGE 和 ROWS 是 PostgreSQL 窗口函数中两种重要的窗口框架。ROWS 基于物理行，RANGE 基于逻辑值。它们各有优缺点，适用于不同的场景。在实际应用中，我们需要根据业务需求和数据特点选择合适的窗口框架。同时，我们也需要注意性能问题，尽量避免不必要的开销。

希望这篇文章能够帮助你彻底理解 RANGE 和 ROWS 的区别，并在实际工作中灵活运用它们。如果你还有其他关于 PostgreSQL 的问题，欢迎在评论区留言，我会尽力解答。记住，我是码农老炮儿，一个追求极致技术的硬核宅男！