MySQL窗口函数在GROUP BY分组数据上的执行逻辑疑问

前提：操作对象为ordersdetails表，字段包含order_id、product_id、quantity，以下针对两个SQL的疑问逐一解析：

1. max(avg(quantity)) over()的执行逻辑 #

• 第一步：执行GROUP BY order_id，将原表数据按订单ID分组，每个分组对应某一订单下的所有商品记录。
• 第二步：对每个分组做聚合计算：
  • 计算max(quantity)，得到该订单中单个商品的最大数量；
  • 计算avg(quantity)，得到该订单下所有商品数量的平均值。
    此时生成的中间结果集里，每一行对应一个订单，包含order_id、该订单的max(quantity)、该订单的avg(quantity)三个值。
• 第三步：执行窗口函数max(...) over()：输入为第二步中各分组的avg(quantity)值，over()表示不对结果集分区，直接针对整个中间结果集计算最大值——也就是所有订单平均数量中的最大值。最终这个最大值会作为max_avg_qty_all_orders列，出现在每一行结果里。

2. avg(quantity) over()执行报错的原因 #

• 执行GROUP BY order_id后，分组后的中间结果集仅允许包含两类内容：GROUP BY指定的分组字段（此处为order_id），以及通过聚合函数生成的计算值（比如max(quantity)）。原表的原始字段quantity不会被保留在这个中间结果中。
• 窗口函数avg(quantity)试图直接引用原表的quantity字段，但该字段在分组后的结果集里已不存在，数据库无法找到对应列，因此抛出执行错误。
• 对比第一个语句，它是先通过聚合函数avg(quantity)在分组阶段得到合法计算值，再对这个聚合结果执行窗口max计算，全程引用的都是中间结果集里存在的值，所以能正常执行。

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