MySQL中AVG()与AVG() OVER()的区别及适用场景咨询

先看你的SQL逻辑：你用GROUP BY gender, Churn把数据按「性别+是否流失」分成了多个组，每个组对应一行结果。

为什么这里用AVG(tenure)而不是AVG(tenure) OVER()？ #

当前的AVG(tenure)是普通聚合函数，它的作用是对每个GROUP BY分组内的所有tenure值求平均，得到的是每个(gender, Churn)组自己的用户平均在网时长——这正是你需要的结果。

如果换成AVG(tenure) OVER()，这是窗口函数，因为你已经做了GROUP BY，窗口会覆盖所有分组后的行，它会把所有组的平均在网时长再算一次平均值，然后每个分组行都显示这个全局平均值，完全不是你要的每组自身的平均。

什么时候用普通AVG()，什么时候用AVG() OVER()？ #

• 用普通AVG()的场景：

  • 你需要把数据按某些维度分组，每个分组只保留一行结果，同时计算该分组内的统计值。比如按性别分组求平均年龄、按地区分组求销售额总和，必须配合GROUP BY使用（如果不写GROUP BY，就是对全表做聚合，只返回一行结果）。

• 用AVG() OVER()的场景：

  • 你需要保留每一行原始数据，同时额外计算该行所在某个范围的统计值。比如：
    • 显示每个用户的在网时长，同时显示全用户的平均在网时长
    • 显示每个订单的金额，同时显示同品类订单的平均金额
      这类场景不需要GROUP BY（也可以和GROUP BY结合，但逻辑是基于窗口而非分组聚合），窗口函数会给每一行都添加上统计值，不会合并行。

举个窗口函数的例子：

SELECT customer_id, gender, tenure,
       -- 全表所有用户的平均在网时长，每一行都显示这个值
       AVG(tenure) OVER() AS global_avg_tenure,
       -- 同性别用户的平均在网时长，每一行显示对应性别的平均值
       AVG(tenure) OVER(PARTITION BY gender) AS gender_avg_tenure
FROM churn.telco;

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