为什么仅使用ORDER BY会改变SQL分析函数的计算结果？

核心原理：OVER() 子句内的 ORDER BY 是窗口定义的一部分，和查询末尾的 ORDER BY 作用完全不同 #

普通查询末尾的 ORDER BY 仅负责最终结果集的行排序，不会修改每行的计算值；但分析函数的 OVER() 子句是用来定义「计算每行指标时参考的行范围（即窗口）」，其中的 ORDER BY 会同时决定分区内的行排序规则，以及触发默认的窗口帧范围，这是结果变化的根本原因。

你测试用的SQL如下：

with SalesX as (
    select 'Office Supplies' Category , 2014 Year,22593.42 Profit UNION all
    select 'Technology', 2014, 21492.83 UNION all
    select 'Furniture', 2014,   5457.73 UNION all
    select 'Office Supplies',   2015,   25099.53  UNION all
    select 'Technology',    2015,   33503.87  UNION all
    select 'Furniture', 2015,   50000.00  UNION all
    select 'Office Supplies',   2016,   35061.23  UNION all
    select 'Technology',    2016,   39773.99  UNION all
    select 'Furniture', 2016,   6959.95
) select Category, Year, Profit,
    SUM(Profit) OVER (),
    SUM(Profit) OVER (ORDER BY Category, Year)
 from SalesX order by category, year

运行结果示例：

具体规则拆解 #

标准SQL中分析函数的窗口定义完整结构为：

分析函数() OVER (
  [PARTITION BY 分区字段]  -- 可选，将结果集拆分为多个独立分区，计算在分区内执行
  [ORDER BY 排序字段 [ASC/DESC]]  -- 可选，定义分区内的行排序规则
  [窗口帧定义]  -- 可选，定义分区内参与计算的行范围
)

两种场景的默认规则差异如下：

1. OVER() 不加 ORDER BY

你的第一个求和语句 SUM(Profit) OVER () 没有指定分区、排序和窗口帧：

• 无 PARTITION BY：整个结果集作为1个独立分区
• 无 ORDER BY：不需要对分区内行排序
• 无显式窗口帧：默认窗口帧为整个分区的所有行
  因此每一行的SUM结果都是整个分区所有行的利润总和，也就是你看到的固定总利润值。

2. OVER() 加 ORDER BY

第二个求和语句 SUM(Profit) OVER (ORDER BY Category, Year) 指定了排序规则，没有显式指定窗口帧：

• 无 PARTITION BY：整个结果集还是1个独立分区
• 有 ORDER BY：分区内的行先按Category、Year升序排序
• 无显式窗口帧：对于SUM、COUNT、AVG这类聚合类分析函数，默认窗口帧会自动变为 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，也就是「从分区的第一行，到当前排序位置对应的行」
  因此每一行的SUM结果是排序后当前行及之前所有行的利润总和，也就是累计求和值。

规则验证 #

你可以手动指定窗口帧为全分区，即使加了ORDER BY也会返回总利润，验证上述逻辑：

SUM(Profit) OVER (ORDER BY Category, Year ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

该规则是SQL:2003标准统一规定的，所有支持分析函数的数据库（包括你用的BigQuery，以及MySQL 8+、PostgreSQL、Oracle、SQL Server等）都遵循该逻辑，不属于某个数据库的特殊实现。

内容的提问来源于stack exchange，提问作者David542