如何正确使用PRECEDING窗口函数实现累计计算？

Question

在使用PRECEDING窗口函数进行累计计算时，常见的一个问题是：**如何结合ORDER BY与ROWS/RANGE子句，确保累计计算的范围准确无误？** 例如，在计算“近三日销售额累计”时，若仅使用`ROWS BETWEEN 2 PRECEDING AND CURRENT ROW`，可能会因数据排序或重复时间戳导致累计范围不准确。若未配合正确的ORDER BY子句，数据窗口可能无法按预期排列，从而导致累计逻辑出错。 本问题聚焦于如何正确搭配ORDER BY与ROWS/RANGE，确保PRECEDING在累计计算中精准定位窗口范围，避免数据偏移或遗漏，适用于SQL开发、报表统计、数据分析等场景。

Answer 1

1. 引入：窗口函数中的累计计算挑战

在SQL中，窗口函数（Window Function）提供了强大的分析能力，尤其适用于累计、排名、滑动平均等场景。其中，PRECEDING 关键字常用于定义当前行之前的若干行，结合 ROWS 或 RANGE 子句来限定累计范围。

然而，在实际应用中，特别是涉及时间序列数据（如销售额统计）时，开发者常常遇到一个问题：如何确保累计范围准确无误？

2. ORDER BY 的作用与重要性

ORDER BY 是窗口函数中至关重要的排序子句。它决定了数据在窗口内的排列顺序，从而影响 PRECEDING 所指代的“前几行”是否符合业务逻辑。

例如，在按日期排序的销售记录中：

SELECT 
    sale_date,
    amount,
    SUM(amount) OVER (
      ORDER BY sale_date
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_3day_sum
FROM sales;

如果没有显式指定 ORDER BY sale_date，数据库可能会按照物理存储顺序处理数据，导致累计逻辑混乱。

3. ROWS 与 RANGE 的区别

ROWS 和 RANGE 是两种不同的窗口框架类型，它们对累计范围的定义方式不同：

• ROWS：基于物理行数进行累计，严格依据行位置。
• RANGE：基于值范围进行累计，适用于有序且具有连续性的列（如时间戳或数值）。

子句	行为描述	适用场景
ROWS	按物理行偏移量定位窗口	需要精确控制行数时（如最近三行）
RANGE	按值范围定位窗口（如近三天）	时间或数值连续变化的数据

4. 实战案例：近三日销售额累计计算

假设我们有一张销售表 sales，包含字段 sale_date DATE 和 amount INT。目标是计算每日的“近三日累计销售额”。

错误示例（未使用 ORDER BY）：

SELECT 
    sale_date,
    amount,
    SUM(amount) OVER (
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS bad_rolling_sum
FROM sales;

该查询未指定 ORDER BY，可能导致累计行不按日期排列，结果不可靠。

正确写法：

SELECT 
    sale_date,
    amount,
    SUM(amount) OVER (
      ORDER BY sale_date
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS correct_rolling_sum
FROM sales;

此写法确保了数据按日期升序排列，并取当前行及前两行进行累计。

5. 边界情况与注意事项

在使用窗口函数时，还需注意以下几点：

• 重复的时间戳可能导致 RANGE 模式下窗口范围扩大，需谨慎处理。
• 当数据存在缺失日期时，仅依赖 ROWS 可能无法真正反映“前三天”的业务含义。
• 可以结合 PARTITION BY 对不同分组分别计算（如每个门店单独计算）。

推荐流程图如下：

graph TD
      A[开始] --> B{是否按时间排序?}
      B -- 否 --> C[添加 ORDER BY 时间字段]
      B -- 是 --> D[选择 ROWS 或 RANGE]
      D --> E{是否需要考虑时间跨度?}
      E -- 是 --> F[使用 RANGE + INTERVAL]
      E -- 否 --> G[使用 ROWS + 行数]
      F --> H[完成]
      G --> H

6. 高级用法：RANGE 结合时间间隔

对于更精确的时间窗口（如“过去三天”），可使用 RANGE 模式配合 INTERVAL：

SELECT 
    sale_date,
    amount,
    SUM(amount) OVER (
      ORDER BY sale_date
      RANGE BETWEEN INTERVAL '2' DAY PRECEDING AND CURRENT ROW
    ) AS range_rolling_sum
FROM sales;

此语句将累计所有日期在当前行往前推两天之内的记录，即使中间某些日期没有销售记录也不会遗漏。