SQL开窗函数ROWS BETWEEN详解

好的，我们来深入、系统地讲解一下 SQL 开窗函数（Window Function）中非常核心且强大的部分：ROWS BETWEEN。

这可以说是开窗函数中的精髓，理解了它，你就能解决非常多复杂的、之前需要多层子查询或自连接才能解决的业务问题。

---

1. 核心概念回顾：什么是开窗函数？

开窗函数不同于普通的聚合函数（如 SUM, AVG, MAX）。普通聚合函数会将多行数据“折叠”成一行结果。而开窗函数会为每一行都返回一个计算结果，同时保留原始行的所有细节。

它的基本语法是：

<窗口函数> OVER (
  [PARTITION BY <列名>]
  [ORDER BY <列名> [ASC|DESC]]
  [<窗口框架>] -- 这就是我们今天要讲的 ROWS BETWEEN
)

• PARTITION BY： 将数据分成不同的“窗口”或组。计算在每个组内独立进行。
• ORDER BY： 决定了窗口内数据的排序顺序。非常重要！它决定了 ROWS BETWEEN 的基准。
• 窗口框架 (Window Frame)： 定义了对于当前行，计算所要使用的数据子集的范围。ROWS BETWEEN 就是用来定义这个范围的。

---

2. 为什么需要 ROWS BETWEEN？

假设我们有一个简单的销售表 sales：

sale_id	sale_date	amount
1	2023-01-01	100
2	2023-01-02	200
3	2023-01-03	150
4	2023-01-04	300
5	2023-01-05	250

如果我们写一个简单的窗口函数：

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

结果会是：

sale_id	sale_date	amount	running_total
1	2023-01-01	100	100
2	2023-01-02	200	300
3	2023-01-03	150	450
…	…	…	…

问题来了：这个默认的窗口范围是什么？
在标准 SQL 中，当使用 ORDER BY 时，默认的窗口框架是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。这意味着计算从分区（本例中没有分区，所以是整个表）的第一行开始，到与当前行具有相同 ORDER BY 值的所有行（如果排序值相同）结束。

这有时不是我们想要的。我们可能想要一个更精确、基于行物理位置的范围，而不是基于排序值的逻辑范围。这时就需要显式地使用 ROWS BETWEEN 来定义。

---

3. ROWS BETWEEN 详解

ROWS BETWEEN 允许你以当前行为基准，通过行相对于当前行的位置来精确地划定一个窗口框架。

语法结构

ROWS BETWEEN <start_bound> AND <end_bound>

<start_bound> 和 <end_bound> 可以是以下之一：

边界选项	含义
UNBOUNDED PRECEDING	窗口从分区的第一行开始。
[number] PRECEDING	窗口从当前行之前第 number 行开始。
CURRENT ROW	窗口开始或结束于当前行。
[number] FOLLOWING	窗口结束于当前行之后第 number 行。
UNBOUNDED FOLLOWING	窗口结束于分区的最后一行。

重要规则：

1. start_bound 必须在 end_bound 之前。你不能写 ROWS BETWEEN CURRENT ROW AND 2 PRECEDING。
2. 不能使用负数。

---

4. 常用模式与实战示例

让我们用上面的 sales 表来演示。

示例 1：移动平均 (Moving Average)

需求：计算每行及其前两行（共3行）的移动平均。

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

结果：

sale_id	sale_date	amount	moving_avg_3day	计算说明
1	2023-01-01	100	100.00	(100) / 1
2	2023-01-02	200	150.00	(100+200) / 2
3	2023-01-03	150	150.00	(100+200+150) / 3
4	2023-01-04	300	216.67	(200+150+300) / 3
5	2023-01-05	250	233.33	(150+300+250) / 3

关键点：对于第3行，窗口包含了第1、2、3行。对于第4行，窗口包含了第2、3、4行。窗口在“滑动”。

示例 2：前后对比分析

需求：查看每一笔销售额，以及前一笔和后一笔的金额。

SELECT
  sale_id,
  sale_date,
  amount,
  LAG(amount) OVER (ORDER BY sale_date) AS prev_amount,
  amount - LAG(amount) OVER (ORDER BY sale_date) AS diff_from_prev,
  LEAD(amount) OVER (ORDER BY sale_date) AS next_amount
FROM sales;

LAG 和 LEAD 是特殊的窗口函数，它们本质上等价于：

• LAG(amount) = FIRST_VALUE(amount) OVER (ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
• LEAD(amount) = FIRST_VALUE(amount) OVER (ORDER BY sale_date ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)

示例 3：累计至今 vs 阶段累计

需求：计算累计总额（从开始到当前行）和阶段总额（从当前行到分区结束）。

SELECT
  sale_id,
  sale_date,
  amount,
  SUM(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_total, -- 这是默认行为，可省略 ROWS 子句

  SUM(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  ) AS remaining_total
FROM sales;

结果：

sale_id	…	amount	cumulative_total	remaining_total	计算说明
1	…	100	100	1000	100+200+150+300+250
2	…	200	300	900	200+150+300+250
3	…	150	450	700	150+300+250
4	…	300	750	550	300+250
5	…	250	1000	250	250

---

5. ROWS vs RANGE

这是一个非常重要的区别！

• ROWS：基于行的物理位置。2 PRECEDING 就是物理上的前两行。
• RANGE：基于 ORDER BY 列的值。RANGE BETWEEN 2 PRECEDING AND CURRENT ROW 意味着所有 ORDER BY 列的值落在 [当前行值-2, 当前行值] 这个区间内的行。

示例：假设数据如下，date 上有重复值。

sale_id	sale_date	amount
1	1	100
2	1	50
3	3	200
4	5	150

-- 使用 ROWS
SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS sum_rows,

-- 使用 RANGE
SUM(amount) OVER (ORDER BY sale_date RANGE BETWEEN 1 PRECEDING AND CURRENT ROW) AS sum_range

结果：

sale_id	sale_date	amount	sum_rows	sum_range	解释
1	1	100	100	150 (100+50)	sum_rows: 前1行(无) + 当前行。 sum_range: 所有 sale_date 在 [0, 1] 的行 (第1,2行)。
2	1	50	150	150 (100+50)	sum_rows: 前1行(第1行) + 当前行。 sum_range: 所有 sale_date 在 [0, 1] 的行 (第1,2行)。
3	3	200	250	200 (200)	sum_rows: 前1行(第2行) + 当前行。 sum_range: 所有 sale_date 在 [2, 3] 的行 (只有第3行)。
4	5	150	350	350 (200+150)	sum_rows: 前1行(第3行) + 当前行。 sum_range: 所有 sale_date 在 [4, 5] 的行 (第3行值3在范围[4,5]吗？不在。第4行自己值5在[4,5]内，所以只有自己？等等，这里需要仔细看) 注意：这个例子中 RANGE 的行为可能因数据库实现略有差异，但核心逻辑是基于值。

简单总结：在绝大多数情况下，当你需要精确控制参与计算的行数时（如计算N天移动平均），应该使用 ROWS。RANGE 更适合处理数值范围，并且性能通常不如 ROWS，因为它需要处理所有具有相同值的对等行。

---

6. 在 PARTITION BY 中使用 ROWS BETWEEN

ROWS BETWEEN 的作用域永远在同一个 PARTITION 内。它不会跨越分区。

示例：计算每个销售员每天的销售额，以及该销售员在过去3天内的移动平均销售额。

SELECT
  salesperson_id,
  sale_date,
  daily_sales,
  AVG(daily_sales) OVER (
    PARTITION BY salesperson_id
    ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3day
FROM sales_daily;

对于每个不同的 salesperson_id，窗口计算都会重置。第一个销售员的 UNBOUNDED PRECEDING 不会跑到第二个销售员的数据里去。

---

总结

特性	说明
目的	精确定义一个基于物理行偏移量的窗口框架，用于窗口函数的计算。
核心	以当前行为基准，使用 PRECEDING（之前）、CURRENT ROW（当前）、FOLLOWING（之后）来划界。
常用场景	移动平均、移动求和、累计计算（从开始到当前、从当前到结束）、访问前后行的值。
与 RANGE 区别	ROWS 看行位置，RANGE 看 ORDER BY 列的值范围。优先使用 ROWS，更直观且性能更好。
与 PARTITION BY 关系	ROWS BETWEEN 的滑动范围被严格限制在其所在的 PARTITION 内部。

mastering ROWS BETWEEN 将极大提升你利用 SQL 处理复杂数据分析任务的能力，是进阶 SQL 使用的必备技能。