SQL窗口函数默认帧详解：避免ROWS与RANGE误用导致跨数据库结果差异

在使用SQL窗口函数（Window Functions）进行数据分析时，我们常常会写出类似下面这样简洁的代码：

SELECT
   sale_date,
   AVG(amount) OVER (ORDER BY sale_date) AS avg_amount
FROM sales;

这段代码看似优雅，但如果同时在MySQL和PostgreSQL上运行，即使数据完全相同，也有可能得到不一致的计算结果。这并非数据库的bug，其根源在于：我们没有显式定义窗口的“帧范围”（frame clause），而是依赖了数据库的默认行为。虽然现代主流数据库（MySQL 8.0.2+, PostgreSQL, SQL Server, Oracle）的默认行为已遵循SQL标准，但默认的RANGE语义常常与开发者预期的“逐行累计”逻辑不符。特别是在处理重复的排序值或进行滑动窗口计算时，这种误解会被放大，甚至导致隐蔽的业务逻辑错误。

一、窗口函数与帧范围基础

窗口函数允许我们在不改变结果集行数的前提下，对一组相关的行进行聚合或排名计算。其基本语法结构如下：

function_name(...) OVER (
   [PARTITION BY partition_expr, ...]
   [ORDER BY sort_expr [ASC|DESC], ...]
   [frame_clause] -- 这就是关键的“帧子句”
)

什么是“帧”（Frame）？
可以将其类比为电影镜头：整个查询结果集是舞台，当前行是主角，而“帧”就是围绕主角（当前行）的、用于计算窗口函数值的那组配角行。例如，计算“最近7天平均销售额”，帧就定义为当前行及之前的6行数据。

如果没有显式指定frame_clause，数据库会提供一个默认帧。这个默认值虽然标准，但其语义往往与业务直觉相悖。

二、默认帧的核心陷阱：RANGE与ROWS的语义分歧

1. 一个危险的常见写法

-- 依赖默认帧范围的危险写法
SELECT
   sale_date,
   amount,
   AVG(amount) OVER (ORDER BY sale_date) AS avg_to_date
FROM sales;

我们的本意是“按日期排序，计算从第一天到当前日期的累计平均”。但“到当前日期”具体包含哪些行？如果同一天有多条记录，它们是否都被计入？这取决于数据库是使用ROWS还是RANGE来解释帧范围。

2. ROWS 与 RANGE 的本质区别

特性	ROWS	RANGE
计算单位	物理行（行号）	逻辑值（排序字段的值）
关注点	“当前行及之前的N行”	“排序值小于等于当前值的所有行”
重复值处理	每行独立计算	所有相同排序值的行共享同一个计算结果
行数是否固定	是（如：前6行+当前行=7行）	否（取决于数据中该值的分布）
典型场景	固定行数的滑动窗口、逐行累计	基于值范围的逻辑分组计算

简单比喻：在按时间排队的队伍中，ROWS是看“我前面的6个人”，而RANGE是看“所有比我早到或同时到的人”。两者结果可能天差地别。

三、默认行为解析与实验验证

SQL标准与各数据库实现
现代主流数据库（PostgreSQL, SQL Server, Oracle, MySQL 8.0.2+）对于带有ORDER BY的聚合窗口函数（如AVG(), SUM()），默认帧均已统一为：RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。跨数据库的结果差异，主要源于开发者对RANGE语义的误解，而非数据库实现不同。

创建测试数据
通过以下包含重复日期的数据，最能暴露RANGE的行为特征：

CREATE TABLE sales (
  id INT,
  sale_date DATE,
  amount INT
);
INSERT INTO sales VALUES
(1, '2025-12-01', 100),
(2, '2025-12-01', 200), -- 同一天第二笔
(3, '2025-12-02', 150),
(4, '2025-12-05', 300), -- 跳过12-03、12-04
(5, '2025-12-05', 250), -- 同一天第二笔
(6, '2025-12-06', 400);

实验1：使用默认帧的结果与分析
执行依赖默认帧的查询：

SELECT
   id,
   sale_date,
   amount,
   AVG(amount) OVER (ORDER BY sale_date) AS avg_default
FROM sales
ORDER BY id;

实际结果（所有现代数据库一致，但不符合“逐行”预期）：

• 2025-12-01的两行：avg_default均为 (100+200)/2 = 150（RANGE将同一天的两行视为一个逻辑组）。
• 2025-12-02的行：avg_default为 (100+200+150)/3 = 150。
• 2025-12-05的两行：avg_default均为 (100+200+150+300+250)/5 = 200。

结论：默认的RANGE行为是“按值分组”，而非我们通常想要的“逐行累计”。要获得逐行效果，必须显式使用ROWS。

实验2：显式使用ROWS实现逐行累计

SELECT
   id,
   sale_date,
   amount,
   AVG(amount) OVER (
     ORDER BY sale_date, id  -- 添加id确保顺序稳定
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
   ) AS avg_rows_unbounded
FROM sales
ORDER BY id;

这次的结果符合直觉：第1行=100，第2行=(100+200)/2=150，第3行=(100+200+150)/3=150... 这才是真正的逐行累计。

四、滑动窗口计算的正确定义

常见错误：误以为OVER (ORDER BY date)就能实现滑动窗口。
正确方法：必须明确指定帧的行数或范围。

1. 固定行数滑动窗口（跨数据库完全兼容）
   适用于“每天恰好一条记录”或明确需要N条记录的场景，例如在数据分析中计算近7条记录的平均值。

   SELECT
   sale_date,
   amount,
   AVG(amount) OVER (
    ORDER BY sale_date, id
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
   ) AS moving_avg_7rows
   FROM sales;

   注意：如果数据日期不连续，“7行”不等于“7天”。

2. 基于日期范围的滑动窗口
   更符合业务语义，但语法支持度因数据库而异，尤其是在处理MySQL的兼容性时需要特别注意。

   -- PostgreSQL/Oracle/BigQuery 支持
   SELECT
   sale_date,
   amount,
   AVG(amount) OVER (
    ORDER BY sale_date
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
   ) AS moving_avg_7days
   FROM sales;

   重要提示：MySQL对RANGE + INTERVAL的支持有限（8.0.34+为实验性功能），生产环境不推荐直接使用。更可靠的方法是使用“日历表填充法”，此方法通用且精确。

五、编写可靠窗口函数的黄金法则与模板

法则一：永远显式定义帧范围
只要使用了ORDER BY，就应使用ROWS或RANGE显式声明帧，消除歧义。

模板1：逐行累计计算

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

模板2：N天滑动窗口（通用日历表法）

WITH
-- 1. 生成连续日期序列（此处为PostgreSQL语法，其他数据库类似）
calendar AS (
  SELECT generate_series('2025-12-01'::date, '2025-12-31'::date, '1 day') AS sale_date
),
-- 2. 按天聚合
daily_sales AS (
  SELECT sale_date, SUM(amount) AS daily_amount
  FROM sales
  GROUP BY sale_date
),
-- 3. 关联日历，缺省日期补0
filled_data AS (
  SELECT
    c.sale_date,
    COALESCE(d.daily_amount, 0) AS daily_amount
  FROM calendar c
  LEFT JOIN daily_sales d ON c.sale_date = d.sale_date
)
-- 4. 对连续日期使用ROWS窗口
SELECT
  sale_date,
  daily_amount,
  AVG(daily_amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7days
FROM filled_data
ORDER BY sale_date;

模板3：排名函数（显式定义，增加可读性）

SELECT
   sale_date,
   amount,
   ROW_NUMBER() OVER (
     ORDER BY amount DESC
     ROWS UNBOUNDED PRECEDING  -- 显式但不必要，主要起文档作用
   ) AS rn
FROM sales;

总结
理解并显式指定SQL窗口函数的帧范围，是写出可靠、可预期SQL的关键。默认的RANGE行为在涉及重复值时极易导致逻辑错误，而跨数据库的语法差异（如日期间隔处理）则要求我们采用更通用的解决方案（如日历表法）。遵循“显式定义”这一原则，能有效提升数据库查询代码的健壮性和可维护性。