ROWS BETWEEN 与 RANGE BETWEEN 有什么区别？滑动窗口怎么写？

考察要点

这道题面试官想看你是否深入理解了 SQL 窗口函数的核心——窗口帧（Window Frame）。具体来说，考察以下三点：

1. 语义区分能力：你是否能清晰阐述 ROWS（物理行）和 RANGE（逻辑值）在定义窗口范围时的根本区别。
2. 边界处理意识：你是否知道在 ORDER BY 的列存在重复值（Ties）时，ROWS 和 RANGE 的行为会产生巨大差异，这是区分两者的关键场景。
3. 实践应用能力：你是否能熟练运用 ... BETWEEN ... PRECEDING AND ... FOLLOWING 语法来解决滑动平均、累计求和等常见的业务问题。

核心概念

窗口函数中的 Frame 子句（即 ROWS BETWEEN 或 RANGE BETWEEN）定义了在当前分区的有序数据中，一个以当前行为基准的、用于计算的子集。

ROWS 和 RANGE 的核心区别在于它们定义这个子集的方式：

• ROWS：基于物理行偏移量 它根据当前行在分区中的物理位置，向前或向后数固定的行数来定义窗口帧。它不关心 ORDER BY 列的值具体是什么，只关心行的位置。

• RANGE：基于逻辑值偏移量 它根据当前行在 ORDER BY 列上的值，加上或减去一个偏移量来定义一个值的范围，所有落入这个值范围内的行都会被包含在窗口帧中。关键在于：当 ORDER BY 的列有重复值时，所有重复的行被视为一个整体。

示例对比

假设我们有一张员工销售记录表 sales，我们要计算每个员工每笔销售及其前一笔销售的平均额。

原始数据 (sales):

注意 2023-01-03 有两笔销售，这是测试 ROWS 和 RANGE 区别的关键。

1. 使用 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW

这个子句的含义是：“窗口帧包含当前行和它物理位置上的前一行”。

 1SELECT
 2    employee,
 3    sale_date,
 4    amount,
 5    AVG(amount) OVER (
 6        PARTITION BY employee
 7        ORDER BY sale_date, amount -- 使用 amount 排序以保证行序唯一
 8        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
 9    ) AS avg_last_2_rows
10FROM sales;

输出结果:

分析：ROWS 严格遵守物理行顺序。对于 2023-01-03 的两行，计算是独立的，每一行的窗口只包含它自己和它紧邻的前一行。

2. 使用 RANGE BETWEEN 1 PRECEDING AND CURRENT ROW

这个子句的含义是：“窗口帧包含 ORDER BY 列的值在 [current_value - 1, current_value] 范围内的所有行”。对于日期类型，1 代表 1 天。

 1SELECT
 2    employee,
 3    sale_date,
 4    amount,
 5    AVG(amount) OVER (
 6        PARTITION BY employee
 7        ORDER BY sale_date
 8        RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND CURRENT ROW
 9    ) AS avg_range
10FROM sales;

注意：对于日期/时间类型，RANGE 的偏移量需要指定单位，如 INTERVAL '1' DAY。对于数字类型，可以直接写 1。

输出结果:

分析：RANGE 基于值。当计算 2023-01-03 的行时，CURRENT ROW 的值是 2023-01-03。窗口帧会包含所有 sale_date 在 [2023-01-02, 2023-01-03] 区间内的行。因此，2023-01-02 的一笔和 2023-01-03 的两笔销售都被包含进来，导致这两行的计算结果完全相同。

滑动窗口的写法

滑动窗口通常指一个大小固定、随当前行移动的窗口，常用于计算移动平均、移动求和。一般使用 ROWS 来实现精确的行数控制。

经典案例：计算最近7天的销售总额

 1-- 假设 sale_date 是连续的
 2-- 如果日期不连续，用 ROWS 更能精确表达“最近7条记录”
 3SELECT
 4    sale_date,
 5    daily_sales,
 6    SUM(daily_sales) OVER (
 7        ORDER BY sale_date
 8        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
 9    ) AS moving_7day_sales_rows,
10    -- 如果想表达“包含今天在内的过去7天（日历天）”，用 RANGE 更合适
11    SUM(daily_sales) OVER (
12        ORDER BY sale_date
13        RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW
14    ) AS moving_7day_sales_range
15FROM daily_summary;

• ROWS BETWEEN 6 PRECEDING AND CURRENT ROW：窗口大小最多为 7 行（当前行 + 前面 6 行）。如果前面不够 6 行，则有多少算多少。这代表最近7条记录。
• RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW：窗口包含日历上从“今天-6天”到“今天”的所有记录。如果某天没有数据，ROWS 会跳过，而 RANGE 的窗口期是固定的。这代表最近7天内。

默认窗口帧

如果只写 ORDER BY 而不指定 Frame 子句，默认行为是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。这是一个巨大的坑，因为在有重复值时，它会把所有与当前行值相同的行都计算进来，导致结果可能不符合"截至当前"的直觉。 为了得到一个标准的累计值（Running Total），最稳妥的写法是明确指定 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

常见低分回答

1. 混淆概念：“ROWS 和 RANGE 差不多，只是语法不同。” —— 这是最严重的错误，完全没有理解两者的核心区别。
2. 忽略重复值：只用没有重复 ORDER BY 值的例子来解释，导致无法体现 RANGE 的关键行为，说明理解不深入。
3. 错误的默认值：当被问到 ORDER BY 存在但没有 Frame 子句时的默认行为时，回答 ROWS ... 或者 整个分区。正确答案是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，能答出这一点是加分项。
4. 对 RANGE 的值偏移理解错误：认为 RANGE BETWEEN 1 PRECEDING 总是指前一个值。实际上它依赖于 ORDER BY 列的数据类型，对于数字是 value-1，对于日期是 date - 1 day，并且需要使用 INTERVAL 语法。
5. 滑动窗口只知道一种写法：只知道 ROWS BETWEEN ...，不清楚在某些场景下（如按日历天统计），RANGE BETWEEN ... 可能是更符合业务需求的语义。

工程实践与优化

• 性能考量：窗口函数通常比自连接或关联子查询实现相同逻辑的性能要好得多。因为数据库只需对数据进行一次排序（如果需要），然后进行一次扫描即可完成计算。自连接会产生笛卡尔积，计算量呈指数级增长。
• 索引优化：窗口函数的性能瓶颈通常在排序。为 PARTITION BY 和 ORDER BY 的列建立复合索引 (partition_key, order_key) 至关重要。这能让数据库引擎直接利用索引的有序性，避免昂贵的 filesort 操作。
• 执行计划分析：在 MySQL 中，使用 EXPLAIN 查看执行计划。如果 Extra 列出现 Using filesort，说明排序无法利用索引，需要优化。理想情况下，ORDER BY 的顺序应与索引顺序一致。
• ROWS vs RANGE 的选择：
  • 当业务需求是“最近N条记录”、“前N名”等基于计数的场景时，使用 ROWS。这是最常见的情况。
  • 当业务需求是“1小时内的订单”、“价格相近的商品”等基于值范围的场景，或需要将 ORDER BY 列的重复值视为一个整体时，使用 RANGE。
  • 为避免默认 RANGE 行为带来的意外结果，建议在写累计聚合时，总是显式指定 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

常见追问与延伸题目

1. 追问：如果 ORDER BY 的列是字符串类型，RANGE 还能用吗？

   • 回答要点：可以，但行为可能不直观。RANGE 可以在任何支持比较操作的类型上工作。但对于字符串，RANGE BETWEEN 'A' PRECEDING AND CURRENT ROW 这样的语法是没有意义的，因为数据库不知道如何从一个字符串“减去”另一个。此时，RANGE 主要用于处理重复值，其默认行为 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 仍然有效，它会把所有值小于等于当前行值的行都包含进来，并且所有与当前行值相同的行都会被一起处理。

2. 追问：在 MySQL 5.7 中没有窗口函数，如何实现一个“最近7条记录的移动平均”？

   • 回答要点：需要使用自连接或关联子查询。例如，使用自连接：
     sql

     ⧉⛶

      1SELECT
      2    t1.sale_date,
      3    AVG(t2.amount)
      4FROM
      5    sales t1
      6JOIN
      7    sales t2 ON t1.employee = t2.employee AND t2.sale_date <= t1.sale_date
      8GROUP BY
      9    t1.employee, t1.sale_date, t1.id -- 假设有唯一id
     10HAVING
     11    COUNT(t2.id) <= 7; -- 这是一个近似实现，逻辑复杂且性能差

     需要强调这种方法的性能极差，时间复杂度近似为 $O(N \cdot W)$，其中 W 是窗口大小，而窗口函数的复杂度是 $O(N \log N)$（排序）或 $O(N)$（已排序）。这正体现了 MySQL 8.0 引入窗口函数的巨大价值。

3. 追问：RANGE 的性能会比 ROWS 差吗？为什么？

   • 回答要点：可能会。ROWS 的窗口帧边界是固定的物理偏移，实现起来非常直接，只需在有序数据上移动指针。而 RANGE 在每次计算时，需要根据当前行的值动态查找边界，尤其是在有大量重复值时，它需要向前或向后扫描以找到所有符合值范围的行。这个查找过程可能会引入额外的开销。不过，在有合适索引的情况下，这种差异通常不显著。在实践中，应优先考虑语义的正确性，再进行性能测试和优化。