avg() over (partition by ...) 是 sql 中用于计算分组平均值并广播到每行的窗口函数。1. 它在不合并行的前提下，按指定列分组（partition by）并计算字段平均值（avg），常用于数据分析与报表统计；2. 典型应用场景包括保留原始数据的同时添加统计信息、对比个体与群体差异、用于后续计算等；3. 基本结构为 avg(字段名) over (partition by 分组字段 [order by 排序字段] [窗口范围])，其中 order by 与窗口范围可选；4. 使用时需注意与 group by 的区别、默认窗口范围的影响、性能优化及 null 值处理等问题。
在 SQL 中，AVG() OVER (PARTITION BY ...) 是一种非常实用的窗口函数用法，它可以在不改变原始数据行的前提下，为每个分组计算平均值，并将结果“广播”回每一行。这在做数据分析、报表统计时特别有用。
下面我们就来看看它的具体使用方法和常见场景。
什么是 AVG() OVER (PARTITION BY ...)
简单来说，这个写法的作用是：对某个字段按指定列分组（PARTITION BY），然后在每组内计算该字段的平均值（AVG），并把这个平均值作为新列显示在每一行中。
举个例子，假设你有一张销售记录表，里面有销售人员和销售额两列，你想知道每个人对应的平均销售额，就可以这样写：
SELECT name, sales, AVG(sales) OVER (PARTITION BY name) AS avg_sales FROM sales_data;
这样每一行都会显示当前销售人员的平均销售额，而不是只返回聚合后的几行。
实际应用场景
这种写法在实际分析中很常见，尤其适用于以下几种情况：
- 保留原始数据的同时添加统计信息：比如在展示明细数据时，同时带上所属类别的平均值。
- 对比个体与群体差异：可以轻松看出某一行的数据是高于还是低于整体平均水平。
- 用于报表展示或进一步计算：例如计算每个人的销售额与部门平均的差值。
常见使用场景包括：
- 每个地区销售员的平均业绩
- 学生成绩表中各科目的班级平均分
- 不同产品类别下的平均价格等
写法结构详解
基本语法如下：
AVG(字段名) OVER (PARTITION BY 分组字段 [ORDER BY 排序字段] [窗口范围])
其中：
-
AVG(字段名)：你要计算平均值的字段 -
OVER (...)：表示这是一个窗口函数 -
PARTITION BY：类似 GROUP BY，但不会合并行 -
ORDER BY和窗口范围（如ROWS BETWEEN ...）可选，用于更精细地控制计算逻辑
一个完整例子：
SELECT
dept,
salary,
AVG(salary) OVER (PARTITION BY dept ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS avg_salary
FROM employees;这里不仅按部门分组，还按照入职时间排序，并定义了整个窗口范围，从而精确控制平均值的计算方式。
常见误区与注意事项
-
不要混淆 GROUP BY 和 PARTITION BY
-
GROUP BY会把数据压缩成一组聚合结果 -
PARTITION BY只是划分窗口范围，不会影响行数
-
-
注意默认窗口范围
- 如果没有指定
ORDER BY和窗口范围，默认是对整个分区内的所有行求平均 - 加上
ORDER BY后，窗口范围可能会变成从开始到当前行
- 如果没有指定
-
性能问题
- 对大数据量表使用窗口函数时要注意性能，尤其是加上复杂排序和范围限定时
- 可以考虑建立合适的索引或限制分区大小
-
NULL 值处理
-
AVG()会自动忽略 NULL 值，所以在计算前要确认数据质量
-
基本上就这些。掌握 AVG() OVER (PARTITION BY ...) 的使用，能让你在 SQL 查询中实现更灵活的统计分析，特别是在需要保留原始数据结构的情况下。虽然看起来不复杂，但细节容易忽略，建议多结合实际数据练习。
