含GROUP BY的SQL窗口函数疑问：MAX移除后计算逻辑解析

一、GROUP BY场景下MAX窗口函数处理的是什么数据？ #

SQL的执行顺序决定了窗口函数是在GROUP BY和聚合计算完成之后运行的，具体逻辑如下：

1. 先通过GROUP BY将原始数据按指定维度（比如月份）分组，对每个分组计算聚合函数（如SUM(amount)得到该月销售总额），最终得到一个分组结果集——每一行对应一个分组（比如2005年1月、2月……），每行包含分组维度和聚合后的总额。
2. 窗口函数（如MAX(SUM(amount)) OVER()）是在这个分组后的结果集上运算的：
   • MAX(SUM(amount)) OVER()：遍历所有分组行，找出所有分组销售总额中的最大值（即全年最高月销售额）。
   • MAX(SUM(amount)) OVER(PARTITION BY quarter(payment_date))：先把分组行按季度划分窗口，再在每个季度窗口内，找出该季度内各月销售额的最大值。

简单来说，MAX窗口函数处理的是GROUP BY聚合后的分组结果数据，计算对象是每个分组的聚合值（如各月销售总额）。

二、移除MAX后出现异常极小值的原因 #

你看到的极小值（如19.96、8.98），核心问题在于窗口函数里的SUM(amount)引用的是原始订单的amount列，而非GROUP BY后的聚合总额，具体细节：

1. GROUP BY之后，结果集的每一行对应一个分组，但如果未对amount做聚合就直接引用（比如窗口函数中的amount），在非严格SQL模式下（如MySQL关闭ONLY_FULL_GROUP_BY），数据库会返回该分组中任意一条原始记录的amount值（而非整个分组的总额）；在严格模式下，这种写法会直接报错。
2. 当你写SUM(amount) OVER()时，窗口函数会遍历所有分组行，把每个分组里那条随机的原始amount值累加，最终得到的是零散订单金额的总和，而非各月总额的总和，因此数值远小于实际全年销售额。
3. 同理，SUM(amount) OVER(PARTITION BY quarter(payment_date))是把每个季度下各分组（月份）里的那条随机原始amount值累加，自然也是极小值。

正确写法示例 #

如果要计算全年各月总额的总和，或各季度内各月总额的总和，需先给聚合后的总额起别名，再在窗口函数里引用该别名：

SELECT
  MONTH(payment_date) AS month,
  QUARTER(payment_date) AS quarter,
  SUM(amount) AS monthly_total, -- 给聚合总额起别名
  MAX(SUM(amount)) OVER() AS max_yearly_total, -- 全年最高月总额
  MAX(SUM(amount)) OVER(PARTITION BY QUARTER(payment_date)) AS max_quarterly_total, -- 季度内最高月总额
  SUM(monthly_total) OVER() AS total_yearly_sales, -- 全年销售总额（各月总额之和）
  SUM(monthly_total) OVER(PARTITION BY QUARTER(payment_date)) AS total_quarterly_sales -- 季度销售总额（该季度各月总额之和）
FROM payments
WHERE YEAR(payment_date) = 2005
GROUP BY MONTH(payment_date), QUARTER(payment_date);

这样窗口函数里的SUM(monthly_total)就会基于GROUP BY后的聚合值计算，得到正确的总和结果。

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