sql 中 avg () over partition by 用法_sql 中 avg () over 分区求平均教程_技术教程

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 值，所以在计算前要确认数据质量